Question
How do 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 specific information regarding this, please see this section of 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.
Steps for syncing the new column:
- Pause connector in the Fivetran Dashboard
- Add new column in source and create new CDC instance for table
- Delete old CDC instance (ensure that there is only one instance for the given table)
- Unpause and re-sync table within the Fivetran Dashboard
Cause
SQL Server CDC instances do not automatically add new columns.