Change data capture explained: catching inserts, updates and deletes

Change data capture reads the changes a database records about itself, including deletes. It is the most reliable way to keep a copy in step with a busy source, as long as you plan for what it cannot do.

The previous post explained incremental loading with watermarks, and its weak spot: a watermark cannot find a row that has been deleted. Change data capture solves that, and several other problems besides.

What change data capture is

Change data capture, or CDC, is a way of recording every change made to a table as a separate stream of events. Each event says what happened, to which row, and when:

• An insert, with the new row.
• An update, with the new values and often the old ones too.
• A delete, with the key of the row that was removed.

Instead of asking the source "what does this table look like now?", a CDC process asks "what has happened to this table since I last looked?" and applies those changes to the copy.

How it works

Most databases already keep a detailed log of every change for their own recovery purposes. CDC reads that log rather than querying the tables themselves. That has two advantages: it captures everything, including deletes and changes that did not touch a modified date, and it puts relatively little load on the source.

SQL Server and Azure SQL offer built-in change data capture, and a lighter feature called change tracking, which records that a row changed without recording every value. Other databases have their own equivalents. Delta tables in Fabric can record a change data feed, which lets one layer of the lakehouse pass only its changes to the next.

Applying the changes

Capturing changes is half the job. The other half is applying them to the target correctly and in order. This is normally done with a merge: insert new keys, update existing ones, and delete or flag removed ones.

Two decisions matter here:

• Whether deletes remove the row from the target or mark it as deleted. For reporting, a soft delete flag is often safer, because it keeps an audit trail and avoids breaking history.
• Whether updates overwrite or create history. CDC is a natural feed for Type 2 slowly changing dimensions, because it tells you exactly when each value changed.

Where Fabric fits

In Microsoft Fabric, mirroring uses CDC-style replication under the covers for supported sources, so you get the benefits without building it. For other sources, pipelines, notebooks and partner tools can read CDC feeds and merge them into lakehouse or warehouse tables.

Inside the platform, materialized lake views use the Delta change data feed to refresh incrementally, and notebooks can use it to move only changed rows from Silver to Gold. We compare those tools in notebooks, Dataflows Gen2 or pipelines.

What CDC does not solve

CDC is reliable, not magic. A few things still need planning:

• The source has to have CDC switched on, which usually needs a database administrator and sometimes a licence or configuration change.
• Change logs are kept for a limited time. If a load stops for longer than the retention period, changes are lost and a full reload is needed.
• Schema changes in the source, such as a new column, need handling in the process that applies the changes.
• The raw change stream is worth keeping, untouched, as described in why we never let anyone touch the Bronze layer. It is what lets you rebuild if the merge logic is ever wrong.

Next in the series

Applying changes correctly depends on matching rows reliably, which comes down to keys. The next post covers natural keys, surrogate keys and hash keys. If you want help setting up CDC for your own sources, our Microsoft Fabric team does this regularly.

Ask the source what happened, not just what it looks like now.