How can I do Continuous data ingestion from on prem data sources to redshift

Question

I have a required to ingest data from multiple on-prem data sources into my Redshift. This ingestion will be a scheduled activity running every 6 hours in a day. The process should be able to identify the delta records and load only new/changed records in Redshift. In all these processes, restart option should also be made available.I am trying to do this using either entirely AWS services or with a combination of python programs and aws services.

My idea is to setup a data flow from external sources to s3, then temporarily launch a ec2 instance for any data processing/wrangling requirement, then write the curated data back to s3, terminate the ec2 instance and load data into redshift using datapipeline.

Can you suggest some pointers to start with. If you have experience with a similar project , do share your experiences. Also if possible, please share a design and associated code for reference.

Answer 1

I can Recommend looking into AWS Schema Conversion Tool (AWS SCT) and AWS Database Migration Service (AWS DMS).

DMS can help you establish ongoing movement of data from on prem sources to Redshift, including staging the data to S3. Supported sources are list in the docs.

Start with the walk through in this blog post: "How to Migrate Your Oracle Data Warehouse to Amazon Redshift Using AWS SCT and AWS DMS"

If you still don’t get an answer do comment i would then look into this and surely help you.