Do New CDC-Enabled Tables Default to Soft Delete or History Mode?

Question

When I add a new source table that has change data capture (CDC) enabled, does it default to soft delete mode or history mode?

Environment

Connector: SQL Server

Answer

When you add a new table with CDC enabled in the source, Fivetran defaults to syncing it in soft delete mode, which preserves deleted rows in the destination by marking them with _fivetran_deleted = TRUE.

To use history mode instead, which captures every version of every row over time, you must manually change the sync mode before data is synced. To ensure the new tables are set to history mode before we sync the relevant data, perform the following steps immediately after adding the table:

1. In Fivetran, go to your SQL Server connection page.
2. In the top right, pause the connection by setting the ENABLED toggle to OFF.
3. Go to the Schema tab, then click the refresh icon to fetch the latest schema from your source.
4. Find the relevant table, click its sync mode menu, and select History mode.
5. Resume the connection by setting the ENABLED toggle to ON.