Our ELT & CDC offerings support various schema changes in connectors with CDC replication. Below are the details of how each type of schema change is handled:
Supported Schema Changes
Column Addition
- Automatic Addition: Newly added columns will automatically be added to the replication target.
- MySQL: The column is created once there is a data record change on the table.†
- Postgres: The column is created once there is a data record change on the table.†
- SQL Server: New columns are not automatically added. Restarting the pipeline will reflect the new column in the destination.
- No Backfilling: Newly added columns will not have data from previously created rows. If backfilling is needed, re-syncing the table is recommended to include previous data.
- Column Order: The column order in the source must be maintained to ensure continuous replication. Altering the order, such as inserting a column in the middle of an existing schema, can lead to pipeline errors or data inconsistencies at the destination. We recommend adding new columns to the end of the table schema whenever possible.
Column Deletion
- Handling in Destination: Deleted columns from the source will not be removed from the destination; new records will have null values for the deleted column.
- Supported Connectors: All database connectors support column deletion.
- Optional Removal: To remove the column from the destination, a table re-sync is required.
Column Renaming
- Handling Renaming: Treated as a combination of column deletion and addition. ‡
- No Backfilling: Similar to column addition, there is no backfilling, and a re-sync is needed to reflect previous rows' data in the new column.
- MySQL: Supports column renaming once there is a data record change on the table.†
- Postgres: Supports column renaming once there is a data record change on the table.†
- SQL Server: Does not support column renaming due to the lack of support for adding new columns.
Column Data Type & Properties Change
- Automatic Detection: The pipeline detects and applies changes in column data types, precision, scale, or string length from the source to the destination depending on the type of change that is made.
- Narrowing Changes: These changes are ignored to prevent data loss from the old data type.
- Widening Changes: Applied automatically if supported by the destination.†
- Optional Setting: This feature can be toggled from the pipeline settings dashboard and is disabled by default. If the feature is disabled, data type or property changes might prompt a re-sync for the affected table. ‡
- Destination Handling: If a column change performed on the source requires an action at the destination, a new column will be created, and the old column will be renamed as
<column_name>_archived_TIMESTAMP where “TIMESTAMP” is the timestamp when the change was applied.
- Backfilling: New columns are created with updated data types, and old data is copied over to ensure consistency. Note: Please ensure the change supports backfilling by checking the backfilling table for the destination's specific behavior.
- Supported Connectors:
Table Addition
- Automatic Addition: Newly inserted tables will be automatically added to the ongoing sync.
- Optional Setting: This feature can be toggled from the pipeline settings dashboard and is disabled by default. ‡
- Supported Connectors: Only MySQL supports this feature. Postgres and SQL Server are not yet supported.
Table Deletion
- Handling in Destination: Deleted tables will remain in the destination.
- Supported Connectors: All database connectors support table deletion.
Unsupported Schema Changes
Certain schema changes are not supported directly by our ELT & CDC offerings and will require manual intervention:
Table Renaming
- Impact: Renamed tables are not automatically recognized.
- Action Required: The renamed table must be selected manually from the dashboard as a new table.
Primary Key Column Change
- Impact: Changes in the primary key column can cause issues with replication and may compromise data integrity at the destination.
- Action Required: We strongly recommend a re-sync of the table with the changed primary key. Please contact our support team to request a re-sync.
- † Replication is not instant, and changes will only be applied to the destination once we receive a new record from the table.
- ‡ Some features listed may not be available on all pipelines, particularly if the pipeline is running on an older version. To access the full range of features, please contact our support team to upgrade your pipeline to the latest version.