CDC (Change Data Capture) Explained with Examples

Change data capture, CDC, is the practice of recording every change made to a database, each insert, update and delete, so other systems can apply the same changes. Debezium's documentation defines it simply: a system that monitors and captures changes in data so that other software can respond to them (Debezium FAQ). In data engineering it is the standard way to keep a warehouse or lake copy of an operational database current without reloading it.

What CDC solves that watermarks cannot

Incremental loads with a watermark, a last-updated timestamp or increasing ID, work only when the source cooperates; incremental data loads shows how easily they miss rows. CDC closes three gaps:

• Deletes. A deleted row leaves nothing for a query to find. CDC records the deletion as an event.
• Tables without change columns. Many operational tables have no reliable last-modified column. CDC does not need one.
• Every intermediate change. A watermark sees only a row's latest state; CDC sees each change in order, which matters for audit trails and slowly changing history.

How CDC is implemented

Log-based capture reads the database's own transaction log, which already records every committed change. SQL Server's CDC feature does this: a capture process reads the log and writes each insert, update and delete into a change table that mirrors the source columns, with before and after images of updates and a log sequence number that orders the changes (Microsoft Learn: SQL Server CDC). Debezium does the same for many databases, reading the MySQL binlog or PostgreSQL's logical replication stream and publishing each table's changes to a Kafka topic (Debezium architecture). Log-based capture adds little load to the source and cannot miss a committed change.

Trigger-based capture uses database triggers to write a row to a change table whenever the source table changes. It works on almost any database without special features, at the cost of extra work inside every transaction. Snapshot comparison, diffing full copies of a table, finds changes too, but must read everything each time.

A working example

The example below uses trigger-based capture, because it runs anywhere with nothing but Python and SQLite, on the Customer table of the Chinook sample database (Chinook on GitHub). A copy of the table is loaded once as a snapshot; then the source changes, and a consumer applies the captured changes to the copy:

import shutil
import sqlite3
import urllib.request
from pathlib import Path

url = "https://github.com/lerocha/chinook-database/releases/download/v1.4.5/Chinook_Sqlite.sqlite"
if not Path("chinook.sqlite").exists():
    urllib.request.urlretrieve(url, "chinook.sqlite")
shutil.copy("chinook.sqlite", "source.sqlite")                 # the operational database
COLS = "CustomerId, Email, Country, SupportRepId"

source = sqlite3.connect("source.sqlite")
source.executescript(f"""
CREATE TABLE customer_changes (change_id INTEGER PRIMARY KEY AUTOINCREMENT, op TEXT, {COLS});
CREATE TRIGGER cdc_insert AFTER INSERT ON Customer BEGIN
  INSERT INTO customer_changes (op, {COLS}) VALUES ('I', NEW.CustomerId, NEW.Email, NEW.Country, NEW.SupportRepId); END;
CREATE TRIGGER cdc_update AFTER UPDATE ON Customer BEGIN
  INSERT INTO customer_changes (op, {COLS}) VALUES ('U', NEW.CustomerId, NEW.Email, NEW.Country, NEW.SupportRepId); END;
CREATE TRIGGER cdc_delete AFTER DELETE ON Customer BEGIN
  INSERT INTO customer_changes (op, {COLS}) VALUES ('D', OLD.CustomerId, OLD.Email, OLD.Country, OLD.SupportRepId); END;
""")

# the warehouse copy starts as a full snapshot
target = sqlite3.connect("target.sqlite")
target.executescript("DROP TABLE IF EXISTS customer; CREATE TABLE customer (CustomerId INTEGER PRIMARY KEY, Email TEXT, Country TEXT, SupportRepId INTEGER);")
target.executemany("INSERT INTO customer VALUES (?, ?, ?, ?)", source.execute(f"SELECT {COLS} FROM Customer"))
target.commit()
last_applied = 0

# business activity in the source system
with source:
    source.execute("UPDATE Customer SET Email = 'frantisek.w@example.com' WHERE CustomerId = 5")
    source.execute("UPDATE Customer SET SupportRepId = 5 WHERE Country = 'Brazil'")
    source.execute("DELETE FROM Customer WHERE CustomerId = 59")
    source.execute("""INSERT INTO Customer (CustomerId, FirstName, LastName, Email, Country, SupportRepId)
                      VALUES (60, 'Ana', 'Lima', 'ana.lima@example.com', 'Portugal', 3)""")

# the CDC consumer: read changes after the last applied id, apply them in order
def apply(changes):
    with target:
        for change_id, op, *row in changes:
            if op == "D":
                target.execute("DELETE FROM customer WHERE CustomerId = ?", (row[0],))
            else:
                target.execute("""INSERT INTO customer VALUES (?, ?, ?, ?) ON CONFLICT (CustomerId) DO UPDATE
                                  SET Email = excluded.Email, Country = excluded.Country, SupportRepId = excluded.SupportRepId""", row)
    return changes[-1][0]

changes = source.execute(f"SELECT change_id, op, {COLS} FROM customer_changes WHERE change_id > ? ORDER BY change_id",
                         (last_applied,)).fetchall()
last_applied = apply(changes)
print("changes captured:", {op: sum(1 for c in changes if c[1] == op) for op in "IUD"})
print("last applied change id:", last_applied)

snapshot = lambda: (source.execute(f"SELECT {COLS} FROM Customer ORDER BY CustomerId").fetchall(),
                    target.execute(f"SELECT {COLS} FROM customer ORDER BY CustomerId").fetchall())
in_source, in_target = snapshot()
print("source rows:", len(in_source), "| target rows:", len(in_target), "| identical:", in_source == in_target)
apply(changes)                                              # the same batch delivered twice, as after a crash
print("after replaying the batch, identical:", snapshot()[0] == snapshot()[1])
print("customer 59 in target:", target.execute("SELECT COUNT(*) FROM customer WHERE CustomerId = 59").fetchone()[0])
print("columns a watermark could use:", [c[1] for c in source.execute("PRAGMA table_info(Customer)") if "date" in c[1].lower() or "updated" in c[1].lower()])
source.close(); target.close()

changes captured: {'I': 1, 'U': 6, 'D': 1}
last applied change id: 8
source rows: 59 | target rows: 59 | identical: True
after replaying the batch, identical: True
customer 59 in target: 0
columns a watermark could use: []

What the example shows

Every change was captured, in order. One email change and one statement reassigning Brazil's five customers produced six update events, one per row; the delete and the insert added one each. The consumer applied all eight, and the copy matches the source exactly.

The delete reached the copy. Customer 59 is gone from the target. A watermark-based load could never have noticed, and here it could not even start: the Customer table has no date or last-updated column to use.

Replays are harmless. The consumer applied the same batch a second time, as happens when a consumer crashes after applying changes but before saving its position. Debezium's FAQ is explicit that after a crash consumers may see events more than once. Because inserts and updates are applied as upserts keyed on CustomerId, and a repeated delete deletes nothing, the result is unchanged. That property is idempotency; idempotency in data pipelines covers it in depth.

The bookmark matters. The consumer records the last applied change id. In production that position, a log sequence number or Kafka offset, must be saved with or after the applied changes, never before, or a crash loses changes.

Choosing an approach

• Small tables that change little: a periodic full reload is simpler than any CDC setup, and naturally handles deletes.
• A database with native CDC or logical replication, and a team running Kafka: a log-based connector such as Debezium, publishing changes as events other systems can also use.
• A cloud platform with a managed replication service: often the least effort, since the service handles snapshots, positions and retries.
• No log access at all: triggers, accepting their overhead on the source.

Pitfalls in production

• Apply in order per key. An update applied before the insert it follows, or after a later update, leaves the wrong state. Log sequence numbers, or a Kafka topic partitioned by primary key, preserve order.
• Retention windows. SQL Server keeps change data for three days by default; a consumer that falls further behind loses changes and needs a fresh snapshot.
• Schema changes. New or dropped columns in the source must flow through to consumers deliberately.
• Initial snapshots. CDC starts from a point in time, so it is always paired with an initial full load, taken consistently with the starting position.

Where CDC fits alongside batch loading is covered in ETL vs ELT, and the streaming platform most CDC tools publish to in Apache Kafka explained.

Data extraction techniques, loading strategies and stream processing are part of the data processing and streaming modules of the Data Engineering course.

Sources

• What is change data capture (CDC)? (SQL Server, Microsoft Learn) — Microsoft Learn
• Debezium architecture (Debezium documentation) — Debezium
• Debezium FAQ (Debezium documentation) — Debezium
• Chinook sample database (GitHub) — GitHub