AWS DMS Table Event Handling Settings Explained

Let me explain these AWS DMS (Database Migration Service) settings that control how DDL (Data Definition Language) changes are handled during Change Data Capture (CDC) replication:

1. HandleSourceTableDropped: true

What it does: When set to true, this setting automatically drops (deletes) the corresponding table in your target database whenever a table is dropped in your source database.

Example scenario:

You have a table called customer_legacy in your source database being replicated to your target
A developer runs DROP TABLE customer_legacy; on the source database
With this setting enabled, AWS DMS will automatically execute a similar DROP TABLE command on the target database
Without this setting (false), the table would remain in your target database even though it no longer exists in the source

When to use: Enable this when you want your target database schema to stay in perfect sync with your source, including table removals.

2. HandleSourceTableTruncated: true

What it does: When set to true, this setting automatically truncates (removes all data while keeping the table structure) the corresponding table in your target database whenever a table is truncated in your source database.

Example scenario:

You have a table called transaction_history in your source database with millions of records
An administrator runs TRUNCATE TABLE transaction_history; on the source to clear out old data
With this setting enabled, AWS DMS will automatically truncate the target table as well, removing all records
Without this setting (false), the target table would retain all its old data even though the source was cleared

When to use: Enable this for scenarios where periodic data clearing is part of your operational process and you need the target to mirror these operations exactly.

3. HandleSourceTableAltered: true

What it does: When set to true, this setting propagates structure modifications (like adding, dropping, or modifying columns) from your source tables to the corresponding target tables.

Example scenarios:

A new column customer_preference is added to the customers table in your source database
With this setting enabled, AWS DMS automatically adds the same column to your target database table
Similarly, if a column type is changed from VARCHAR(50) to VARCHAR(100) in the source, the same change is applied to the target
Without this setting (false), schema changes would not be synchronized, potentially causing replication failures when new data doesn't match the target schema

When to use: Enable this for most production environments where you want schema changes to be automatically propagated, keeping your target database structurally consistent with your source.

Configuration Example

To enable these settings in your AWS DMS task, include them in your task settings JSON configuration:

"ChangeProcessingDdlHandlingPolicy": {
   "HandleSourceTableDropped": true,
   "HandleSourceTableTruncated": true,
   "HandleSourceTableAltered": true
}

Important Considerations

1. Dependent Applications: If applications are dependent on the target database, be aware that schema changes will occur automatically and may affect those applications.

2. Limited Support: Not all DDL statements are supported for all database types. Check AWS DMS documentation for specific limitations for your source/target combination.

3. Testing: Always test these settings in a non-production environment first to understand exactly how they behave with your specific database engines.

4. Full Control: These settings give you fine-grained control over how schema changes propagate, allowing you to choose whether to synchronize drops, truncates, and alters independently.