Migration of selected fields from multiple tables on-premise to single table in AWS

Yes, it is feasible to use AWS Database Migration Service (AWS DMS) to migrate selected fields from multiple tables in your on-premise Oracle database to a single table in Amazon Aurora PostgreSQL, including support for Change Data Capture (CDC).

AWS DMS provides powerful transformation and mapping capabilities that can handle this scenario. Here's how you can approach this:

1. Table Selection: Use the selection rules in AWS DMS to specify which tables from your Oracle database you want to include in the migration.

2. Column Filtering: Utilize the column-level transformations to include only the 10 specific columns you're interested in from each source table.

3. Data Consolidation: You can use a custom SQL statement as your source in AWS DMS. This allows you to join data from multiple tables based on the common column/field you mentioned. This joined result can then be migrated to your single target table in Aurora PostgreSQL.

4. Transformation Rules: Apply transformation rules to handle any necessary data type conversions or modifications required when moving from Oracle to PostgreSQL.

5. CDC Support: AWS DMS supports ongoing replication (CDC) for heterogeneous migrations, so you can set up continuous replication from your Oracle source to the PostgreSQL target.

While AWS DMS Transformation and Mapping Rules should be sufficient for this task, depending on the complexity of your data and any specific business logic, you might want to consider using AWS Schema Conversion Tool (AWS SCT) in conjunction with DMS. AWS SCT can help with converting the schema and any custom code from Oracle to PostgreSQL format.

The basic architecture would look like this:

1. Source: On-premise Oracle Database
2. AWS DMS Replication Instance: Set up with appropriate rules for selection, transformation, and mapping
3. Target: Amazon Aurora PostgreSQL table

If you need to use AWS SCT, it would be used before the DMS migration to convert schemas and code, but the data movement would still be handled by DMS.

Remember to thoroughly test your migration setup, especially the custom SQL for joining tables and the transformation rules, to ensure data integrity and completeness in the target Aurora PostgreSQL table.
Sources
DMS Transformation and Mapping Rules Example - Oracle to PostgreSQL | AWS re:Post
Migrate data from an on-premises Oracle database to Aurora PostgreSQL - AWS Prescriptive Guidance
Data replication using AWS Database Migration Service - Strategies for Migrating Oracle Databases to AWS
Migrate an Oracle database to Aurora PostgreSQL using AWS DMS and AWS SCT - AWS Prescriptive Guidance