Title: Migrate database and data warehouse into AWS
Tags: amazon-web-services;amazon-s3;aws-lambda;amazon-redshift;amazon-kinesis-firehose
Question: I want to migrate our database and data warehouse into AWS.

Our on-prem database is Oracle and we use Oracle Data Integrator for data warehousing in IBM AIX.

My first thought was to migrate our database with AWS DMS (Data Migration Services) into a staging point (S3) and then using Lambda (for creating the trigger when data is updated, deleted or inserted) and Kinesis Firehose (For streaming and do the ETL) to send the data into Redshift.

The data in Redshift must be the replica of our on-prem data warehouse (containing facts and dimensions, aggregation and multiple joins) and I want whenever any changes happened in the on-prem database, it automatically updates the AWS S3 and Redshift so I can have near real-time data in my Redshift.

I was wondering if my architecture is correct and/or is there a better way to do it?

Thank you

Comment: Why not just have AWS DMS directly insert the data into Redshift?

Comment: We do not want to use ODI anymore so we want to migrate the ETL processes into AWS.



{'source': 'StackExchangeDataset', 'subset': 'stackoverflow.com', 'metadata': {'AnswerCount': 0, 'CommentCount': 2, 'ContentLicense': 'CC BY-SA 4.0', 'CreationDate': '2018:00:06/07/18', 'FavoriteCount': None, 'PostTypeId': 1, 'Tags': '<amazon-web-services><amazon-s3><aws-lambda><amazon-redshift><amazon-kinesis-firehose>', 'Title': 'Migrate database and data warehouse into AWS', 'ViewCount': 108, 'id': 50744376, 'user_AboutMe': None, 'user_AccountId': 10108810.0, 'user_DisplayName': 'Mehrdad', 'user_Reputation': 1.0, 'user_Views': 6.0, 'user_id': 7470494.0}}