Question
How can I add a new column while I have Change Data Capture (CDC) enabled?
Environment
Connector: SQL Server
Answer
CDC instances do not automatically add new columns, you will need to re-initiate the CDC instance. For more information, please visit Microsoft's documentation:
To accommodate a fixed column structure change table, the capture process responsible for populating the change table will ignore any new columns that are not identified for capture when the source table was enabled for change data capture. If a tracked column is dropped, null values will be supplied for the column in the subsequent change entries. However, if an existing column undergoes a change in its data type, the change is propagated to the change table to ensure that the capture mechanism does not introduce data loss to tracked columns.
It is possible to create a second capture instance for the table that reflects the new column structure... When the transition is effected, the obsolete capture instance can be removed.
To sync the new column, please follow the steps below:
- Pause the connector in the Fivetran Dashboard.
- Add the new column in the source and create a new CDC instance for the table.
- Delete the old CDC instance (make sure that there is only one instance for the given table).
- Unpause and re-sync the table within the Fivetran Dashboard.
Cause
SQL Server CDC instances do not automatically add new columns.