How do I add a new column while I have change data capture (CDC) enabled on my connector?
SQL Server connector that uses CDC as its incremental update mechanism.
SQL Server CDC instances do not automatically add new columns, so you must create a new CDC instance to replace your existing instance. For more information, read this excerpt from Microsoft’s CDC 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 a new column, do the following:
- In your Fivetran dashboard, pause the connector.
- In your source database, add a new column.
- Create a new CDC instance for the table that contains that new column.
- Delete the old CDC instance. Ensure that there is only one CDC instance for the table.
- In your Fivetran dashboard, unpause your connector and re-sync the table.