Change Data Capture Logs and Database Replication

Published by Programming Line in Data Engineering in Depth. Last updated .

Purpose and Outcome

Change data capture, usually shortened to CDC, is the practice of turning database changes into an ordered stream of events. Instead of asking a source database every few minutes which rows changed, a CDC pipeline reads the same durable log the database uses for crash recovery and replication. For data engineering, this matters because the log records inserts, updates, deletes, transaction boundaries, and commit order close to the moment they happen.

By the end of this lesson you should be able to explain how log-based CDC works, what a replication slot or log position represents, why snapshots are different from streaming changes, and how to design a consumer that can resume without duplicating or losing data. The lesson focuses on the mechanics common to systems such as PostgreSQL WAL logical decoding, MySQL binlog replication, SQL Server transaction log CDC, and connector frameworks such as Debezium.

How Log-Based CDC Works Internally

Most relational databases write changes to an append-only recovery log before or while committing data pages. PostgreSQL calls this the write-ahead log, MySQL calls it the binary log when configured for replication, and other engines use similar terms. The log is not a table of business events. It is a database engine artifact containing enough information to replay changes, recover from crashes, or ship changes to replicas.

A CDC reader attaches to that log through a replication protocol or database-provided API. It records a position, often called an offset, log sequence number, binlog filename and offset, or system change number. That position is the bookmark that lets the reader say, I have safely processed changes through here. A good CDC pipeline treats this offset as part of its state, not as an incidental metric.

There are usually two phases. The first is an initial snapshot, where existing rows are copied from source tables. The second is continuous streaming, where newly committed changes are read from the log. The difficult part is the handoff. If a snapshot takes ten minutes, source tables keep changing during those ten minutes. Mature CDC systems capture a low and high log watermark, scan the table contents, then replay changes from the captured log position so the target ends in the same state as the source.

CDC events commonly include an operation type, before and after row images, source metadata, transaction id, commit timestamp, and log position. Some systems emit one event per row. Others can expose transaction metadata so consumers know which row events committed together. For analytical targets, row-level ordering within a key is often more important than global total ordering across all tables, but the log still provides the foundation for deterministic recovery.

Syntax and Configuration Anatomy

A connector configuration names the source database, the tables to capture, the logical replication identity, the destination topic or stream naming pattern, and the place where offsets are stored. It also declares snapshot behavior. A snapshot mode of initial means copy current rows and then stream changes. A schema-only mode records table structures without copying data. A never mode expects the target to already be initialized.

{
  "connector.class": "io.debezium.connector.postgresql.PostgresConnector",
  "database.hostname": "postgres",
  "database.dbname": "appdb",
  "plugin.name": "pgoutput",
  "slot.name": "orders_cdc_slot",
  "publication.name": "orders_publication",
  "table.include.list": "public.customers,public.orders",
  "snapshot.mode": "initial",
  "topic.prefix": "appdb",
  "tombstones.on.delete": "false"
}

This fragment says the connector should use PostgreSQL logical decoding through a named replication slot. The slot retains WAL needed by the connector until the connector confirms progress. The publication limits which tables are made available. The include list narrows the connector output, and the topic prefix controls how downstream streams are named, for example appdb.public.orders.

Worked Example 1: Reading the Shape of Changes

Consider an orders table. A CDC stream should represent a created order, a status update, and a delete as distinct operations. In SQL, those source changes might look like this:

CREATE TABLE orders (
  order_id bigint PRIMARY KEY,
  customer_id bigint NOT NULL,
  status text NOT NULL,
  total_cents integer NOT NULL,
  updated_at timestamp NOT NULL
);

INSERT INTO orders VALUES (101, 7, 'created', 2599, '2026-09-07 10:00:00');
UPDATE orders SET status = 'paid', updated_at = '2026-09-07 10:01:00' WHERE order_id = 101;
DELETE FROM orders WHERE order_id = 101;

A row-level CDC connector would typically emit three ordered events for key 101: create, update, and delete. For the update, consumers may receive the previous row, the new row, or only the changed columns depending on source settings. For the delete, some pipelines emit a delete event followed by a tombstone marker for compaction; others suppress the tombstone when the sink does not need it.

Worked Example 2: Applying Events Idempotently

A target table or lakehouse merge job should tolerate replay. If a consumer crashes after writing a change but before committing its offset, the same event may be delivered again. The usual correction is not to hope duplicates never occur; it is to store the latest applied source position per key or per partition and ignore older events.

events = [
    {"key": 101, "op": "c", "lsn": 10, "after": {"status": "created", "total_cents": 2599}},
    {"key": 101, "op": "u", "lsn": 12, "after": {"status": "paid", "total_cents": 2599}},
    {"key": 101, "op": "u", "lsn": 12, "after": {"status": "paid", "total_cents": 2599}},
    {"key": 101, "op": "d", "lsn": 15, "after": None},
]

target = {}
applied_lsn = {}

for event in events:
    key = event["key"]
    if event["lsn"] <= applied_lsn.get(key, -1):
        continue
    if event["op"] == "d":
        target.pop(key, None)
    else:
        target[key] = event["after"]
    applied_lsn[key] = event["lsn"]

print(target)
print(applied_lsn)

The expected output is an empty target and {101: 15} as the latest applied position. The duplicate update at log position 12 does not change the result. This is the behavior you want when offsets and sink writes are not committed in a single atomic transaction.

Worked Example 3: Snapshot Plus Stream

Suppose the source contains customers 1 and 2 when the snapshot starts. During the snapshot, customer 2 changes email and customer 3 is inserted. A correct pipeline copies the snapshot rows, then applies log events after the snapshot watermark. The target should contain customer 1 unchanged, customer 2 with the new email, and customer 3 present.

snapshot_rows = {
    1: {"email": "a@example.com"},
    2: {"email": "old@example.com"},
}
log_events_after_watermark = [
    {"key": 2, "op": "u", "after": {"email": "new@example.com"}},
    {"key": 3, "op": "c", "after": {"email": "c@example.com"}},
]

target = dict(snapshot_rows)
for event in log_events_after_watermark:
    if event["op"] == "d":
        target.pop(event["key"], None)
    else:
        target[event["key"]] = event["after"]

print(target)

The deterministic result is customer 2 holding new@example.com and customer 3 included. If the connector started streaming only after the snapshot finished without remembering a log watermark, it could miss changes committed during the scan.

Design Choices and Trade-Offs

Log-based CDC is efficient because it reads changes once from the database log instead of repeatedly scanning source tables. It also captures deletes and transaction order, which timestamp polling often misses. The trade-off is operational coupling to database internals. The source must retain logs long enough for the connector, and a stalled connector can cause log storage to grow.

Replication identity is another design choice. If a table has a stable primary key, CDC events can be keyed cleanly. If a table lacks a key, updates and deletes may require a full before image or may be impossible to apply safely downstream. Adding a primary key is usually better than asking downstream systems to infer identity from mutable columns.

Snapshot strategy depends on table size and tolerance for load. A blocking snapshot is simpler but can hold locks or pressure the source. An incremental snapshot breaks the scan into chunks and interleaves it with streaming changes. Incremental snapshots are more complex, but they are often the right choice for large operational tables that cannot pause writes.

Failure Modes and Troubleshooting

Symptom: WAL or binlog storage grows quickly. The likely cause is a replication slot or CDC reader that has stopped advancing. Check connector task status, source log retention metrics, and the confirmed flush position. Correct it by restarting the connector, fixing sink errors, or temporarily increasing disk capacity. Do not simply delete retained logs unless you accept rebuilding the pipeline from a new snapshot.

Symptom: target rows show old values after newer updates. This usually means events are being applied out of order or a sink merge lacks a source-position comparison. Inspect the event key, partitioning strategy, and applied offset stored with each row. Correct it by partitioning consistently by primary key and rejecting events whose source position is older than the row already stored.

Symptom: deletes are missing in the warehouse. The cause may be a connector configured to omit delete events, a downstream topic compaction policy, or a merge job that only handles inserts and updates. Verify that delete events are present in the raw stream before debugging the warehouse. Correct the sink merge to delete or mark records as inactive when it sees a delete operation.

Symptom: the connector fails after a schema change. The source emitted a column addition, rename, type change, or dropped column that the serializer or sink cannot handle. Inspect schema history and the exact event that failed. Correct by applying compatible schema evolution rules, updating the sink table, or replaying from the last valid offset after the schema registry and target schema are fixed.

Security, Performance, and Reliability

A CDC account should have only the privileges needed to read replication logs and selected tables. It should not be the application owner account. Network paths between source, connector, broker, and sink should be encrypted where the platform supports it, because row images may include sensitive fields that never appear in application logs.

Performance tuning starts with table selection. Capturing every table creates noise, storage cost, and schema churn. Capture tables that downstream products actually consume. For high-volume tables, watch connector lag, broker partition throughput, sink merge duration, and source log retention. The important reliability invariant is simple: acknowledge an offset only after the corresponding change is durable in the next system of record.

Hands-On Lab: Simulate a CDC Apply Loop

Prerequisites: a shell with Python 3 available. No database is required because this lab models the downstream logic a CDC consumer needs.

  1. Create a file named cdc_apply_lab.py.
  2. Paste the following program into the file.
  3. Run python3 cdc_apply_lab.py.
  4. Verify that duplicate and older events are skipped while newer changes are applied.
  5. Cleanup by deleting the file after the lab if you do not need it.
events = [
    {"key": "A", "op": "c", "pos": 100, "after": {"balance": 30}},
    {"key": "A", "op": "u", "pos": 110, "after": {"balance": 45}},
    {"key": "A", "op": "u", "pos": 105, "after": {"balance": 35}},
    {"key": "B", "op": "c", "pos": 120, "after": {"balance": 70}},
    {"key": "A", "op": "d", "pos": 130, "after": None},
]

rows = {}
positions = {}
skipped = []

for event in events:
    key = event["key"]
    if event["pos"] <= positions.get(key, -1):
        skipped.append(event)
        continue
    if event["op"] == "d":
        rows.pop(key, None)
    else:
        rows[key] = event["after"]
    positions[key] = event["pos"]

print("rows", rows)
print("positions", positions)
print("skipped", len(skipped))

Verification should show only row B remaining, positions for A and B recorded, and one skipped event. That skipped event is the late update at position 105, which arrived after position 110 had already been applied for key A.

Assessment Exercises

  1. A connector snapshots a table for twenty minutes while the application keeps writing. What watermark or offset information must be preserved so the target does not miss writes during the snapshot?
  2. Your warehouse table has duplicate versions of the same order after a connector restart. Describe where you would store source log position and how the merge condition should use it.
  3. A table has no primary key and emits updates. What problems does this create for a CDC sink, and what source-side change would you recommend?
  4. Connector lag is zero, but the source database disk is filling with retained logs. What two source-side settings or states would you inspect first?
  5. Design a delete-handling policy for an analytical table that must support historical reporting and current-state queries.

Summary

CDC turns database recovery and replication logs into data engineering streams. The core concepts are log position, transaction order, snapshot watermarks, row identity, and durable offset management. When those pieces are handled carefully, CDC can keep replicas, search indexes, caches, and analytical stores close to the operational source without expensive polling. When they are handled casually, the symptoms are predictable: missed snapshot-era writes, unbounded log retention, duplicate rows, stale updates, and broken deletes.