I have multiple schemas (One for each client) with the same tables in Aurora - MySql. I want to collect the data from all the tables and create a new table in RedShift that would include all the columns from the source tables adding a new "schema" column that would be part of the primary key.
Example:
In Mysql I have:
Schema1.user (id, name) pk (id)
Schema2.user (id, name) pk (id)
...
Schema96.user (id, name) pk (id)
In Redshift I need:
dw.user (schema, id, name) pk (schema + id)
Is it possible to do this with transformation rules in AWS DMS?
This looks like it is better done through a consolidation job in post-processing with Glue. Not sure you can do this with DMS currently, but you can explore the DMS transformations here.