Feasibility of using DMS filter feature to load tables with referential integrity

AWS DMS can be used to migrate tables with referential integrity from a PostgreSQL source while applying filters, but there are some important considerations for your specific use case.

When you're filtering data on parent tables and need to maintain referential integrity with child tables, you need to be careful about how you set up your migration tasks. DMS doesn't automatically maintain referential integrity constraints during migration when filters are applied.

For your scenario where the filtering column only exists in the parent tables, you have a few options:

1. You can use transformation rules to include only specific tables in your migration. This allows you to control which tables are migrated.

2. You can use column filtering to exclude columns you don't need, which can improve performance, especially if those columns contain large data like LOBs or CLOBs.

3. For maintaining referential integrity with filtered parent tables, you'll need to ensure that the child tables only contain records that reference the filtered parent records. This might require custom SQL or post-processing.

When setting up your DMS task, you'll need to create selection rules to include the specific tables you want to migrate, and then add transformation rules to filter the data based on your requirements. For example, you can use selection rules to include specific tables and then apply filters to the parent tables.

Keep in mind that during the migration process, especially if you're using CDC (Change Data Capture), DMS will place some additional load on your source PostgreSQL database. It's recommended to schedule the initial full load during off-peak hours if possible and monitor your source database's performance closely during the migration process.

Also, ensure that you have the necessary PostgreSQL configuration for CDC, including setting up logical replication slots if you plan to use ongoing replication after the initial load.
Sources
DMS with PostgreSQL as source | AWS re:Post
DMS Transformation and Mapping Rules Example - Oracle to PostgreSQL | AWS re:Post
Feature Request: True column exclusion in DMS source queries | AWS re:Post

To load tables with referential integrity, you can use load-order in the table mapping selection rules to define the order in which to load the tables. The parent tables can be loaded first and then the child table. You will also have to set MaxFullLoadSubTasks to 1 so that the table order is followed. This means that the task will load only one table at a time. The load-order is applicable for table initialization. The load of a successive table won't wait for a previous table load to complete if MaxFullLoadSubTasks is greater than 1.

Below document has more details on the load-order rule: [] Selection rules and actions - https://docs.aws.amazon.com/dms/latest/userguide/CHAP_Tasks.CustomizingTasks.TableMapping.SelectionTransformation.Selections.html

Can you elaborate on what you mean by "The filtering column only exists with the parent tables and the other tables maintain referential integrity with the parent table"? with an example?