Change Data Capture vs. Change Tracking for ETL

Every incremental ETL load needs to answer one question: what changed since the last run? SQL Server ships two built-in mechanisms for answering it, Change Data Capture and Change Tracking, and they sound similar enough that picking one without checking what it actually stores is how ETL processes get rebuilt six months later.

What Each One Actually Stores

	Change Data Capture (CDC)	Change Tracking (CT)
What it captures	Every DML operation, with full before/after column values, sourced from the transaction log	Only the fact that a row changed and its primary key; no historical column values
Mechanism	Asynchronous capture job reads the transaction log and populates change tables	Synchronous, tracked directly as part of the transaction, no separate capture job or log reader
History depth	Every intermediate change is retained until cleanup runs	Only current state versus a given tracking version; intermediate changes are not retained
Edition requirement	Enterprise Edition (historically Enterprise-only; check current edition support for your version)	Available on Standard Edition
Storage overhead	Higher, full change tables with column values, retained for a configurable period	Lower, just tracked primary keys and version numbers
Typical use case	Data warehouse ETL that needs to know exactly what a row looked like before and after a change	Lightweight sync scenarios that only need "did this row change, yes or no"

Why the Difference Matters for ETL Specifically

Change Tracking tells you a row's primary key changed since version N. It does not tell you which column changed, or what the old value was. If your incremental load only needs to know "re-extract this row from the source, current state," that's enough, and it's cheaper to run. If your ETL needs to detect a Type 2 attribute change and preserve the historical value that was overwritten, Change Tracking cannot supply that value at all, you'd have to re-query the source and compare against your own staged copy, at which point you're rebuilding what CDC already gives you for free.

Change Tracking has no memory of intermediate states. If a row changed three times between your last sync and this one, Change Tracking shows you the current value once. CDC's change tables would show all three versions, in order, with the actual before/after values for each. Choosing Change Tracking for a warehouse load that later needs full auditability of every intermediate change is a rebuild waiting to happen, not usually visible until someone asks for it.

A CDC-Based Incremental Load, Concretely

-- One-time setup
EXEC sys.sp_cdc_enable_db;
EXEC sys.sp_cdc_enable_table
    @source_schema = N'dbo',
    @source_name   = N'Orders',
    @role_name     = NULL;

-- Each ETL run: pull only what changed since the last processed LSN
DECLARE @from_lsn BINARY(10) = sys.fn_cdc_get_min_lsn('dbo_Orders');
DECLARE @to_lsn   BINARY(10) = sys.fn_cdc_get_max_lsn();

SELECT
    __$operation,   -- 1 = delete, 2 = insert, 3 = update (before), 4 = update (after)
    OrderId, CustomerKey, OrderDate, SalesAmount
FROM cdc.fn_cdc_get_all_changes_dbo_Orders(@from_lsn, @to_lsn, 'all')
ORDER BY __$start_lsn;

Store the last processed LSN somewhere durable (a control table), and next run's @from_lsn picks up exactly where the previous run left off. This is set-based, bounded by actual change volume rather than full table size, the same set-based principle behind avoiding the SSIS SCD wizard's row-by-row approach: detect what changed with the engine's own mechanism, then apply it in bulk.

Common Gotchas

CDC's capture job lags the transaction log. It's asynchronous by design; under heavy write load or capture job contention, there can be a real delay between a committed change and its appearance in the change table. Don't assume it's instantaneous.
Cleanup jobs delete history you might still need. CDC's default retention window quietly prunes old changes; if an ETL run is skipped or fails for longer than the retention period, the gap in history is unrecoverable, monitor both the capture and cleanup job health, not just whether your ETL job succeeded.
Change Tracking's auto-cleanup has the same trap. If your ETL doesn't run for longer than CHANGE_RETENTION, the tracked version you need has already been cleaned up, forcing a full reload rather than an incremental one.
Neither one captures schema changes. A column rename or type change on the source table isn't something either mechanism tracks or protects your ETL against; that's still a manual coordination point.
Turning on CDC without checking transaction log growth. The capture job reads the log, but until it processes a given point, the log can't fully truncate past it, monitor log growth after enabling CDC on a busy table, the same way you would for any other log reader.

The Bottom Line

If your ETL only needs to know a row changed, Change Tracking is lighter, cheaper, and available on Standard Edition. If it needs to know what changed, every intermediate value, not just the latest, CDC is the only one of the two that actually stores that. Decide based on what the load logic will need six months from now, not just what today's simplest query requires, because retrofitting history you didn't capture is not something either feature can do retroactively.

← Back to Blog