AWS setup for Migration of Data

Question

Current setup:

1. We have users table in database clientdb1 (RDS)
2. We have users table in database clientdb2 (RDS)
3. We have users_profile table in database clientdb3 (RDS)

Details:

1. users table in clientdb2 has a different structure from users in clientdb1
2. clientdb1 and clientdb2 although different database, are both Oracle 12c
3. clientdb3 is a SQL Server db

Problem:

1. migrate data from users (in clientdb1) to users (clientdb2). All fields in both tables are the same, they are just structured differently and with different column names, plus the latter has some additional columns.
2. this is what makes it complicated, in the process of migration, each user record needs to get additional information from users_profile table in clientdb3 before the record is inserted in clientdb2.

My design options:

1. Is this possible to do via DMS? I tried doing research and all I could find were samples of data movement between two databases, no third one.

2. Is the best solution for this combination of DMS and Lambda? This is the one I have come up with let me know if this is ok, or is there a better way?

Part 1: Setup dms to migrate data from users (clientdb1) to a temp table in clientdb2 Part 2: Create a lambda worker that listen to data inserts in the temp table when query the needed data in clientdb3 then finally insert it in users (clientdb2).

Any advice would be appreciated. Thanks

Answer 1

It sounds like you simply need columns renamed from DB1 to DB2. You can do that on the fly with DMS transformations. Read more on that here: AWS DMS Transformations. Have a look, you can do a lot within DMS.

To add additional columns, just add them to the table in the 2nd database, as empty columns, and then DMS will populate them from table in 1st database.