Change Data Capture¶

Time: 55 minutes reading + 45 minutes exercise
Prerequisites: database transactions, Kafka partitions, idempotency
Outcomes: design snapshot-to-stream handoff; preserve per-key order; handle deletes and schema changes; reconcile a sink.

02:17 AM page: the orders sink in the lake is missing 40 rows that definitely exist in Postgres. The connector logs show no errors. Someone ran a one-time COPY of the table into Kafka last week to "backfill it faster."

Before you read on, pick one: were those 40 rows lost because (A) the COPY ran before the log position was established, (B) a later update raced an earlier snapshot row into the sink, or (C) the primary key changed underneath the connector?

It's almost always (A) or (B), because CDC is not "send database rows to Kafka" — it is a protocol for reproducing committed database history from a snapshot plus a position in the transaction log, and the two must be sequenced correctly or rows silently vanish or get overwritten.

Contract¶

For every change retain:

source_table, primary_key, operation, before, after,
source_commit_position, source_commit_time, transaction_id,
connector_ingest_time, schema_version

The source log position—not arrival time—is the ordering authority. Partition Kafka by the source primary key when consumers require per-row order.

What "the ordering authority" guarantees is source-specific: a single-primary Postgres WAL gives a global commit order; a sharded MySQL fleet, Cassandra, or a multi-writer system only gives per-shard or per-partition order, with no cheap way to compare positions across shards. Know which scope your source actually offers before you promise a consumer "in order."

Snapshot plus stream¶

The unsafe sequence is “copy table, then start the log.” Changes committed between those actions disappear. A connector must establish a log position and snapshot under a database-specific consistency protocol, then stream changes after that position.

During the overlap, snapshot rows and live updates can arrive close together. Sinks must compare source positions or source versions so an older snapshot row cannot overwrite a newer streamed update.

Operations¶

Kafka log compaction preserves the latest keyed record, but retention, downstream snapshots, and physical GDPR deletion are separate concerns.

The recovery contract: offsets and log retention are one thing¶

A CDC connector's ability to resume without loss depends on two pieces of state that must refer to the same retained history:

connector offset (Kafka Connect / connector store)   "I have consumed up to position X"
        +
source-side log retention (Postgres replication      "I am retaining everything from X onward"
slot, MySQL binlog retention, Oracle logminer
window)
        =
        one recovery contract

Destroying either half alone creates a silent gap. The most common production route to this is a replication slot dropped during maintenance because it is blocking WAL cleanup: the connector restarts, a new slot is created at the current position, the stored offset still names an older one, and everything between them is unrecoverable from the log. The connector reports healthy the whole time, because from its point of view it resumed and is now streaming.

Treat "drop the replication slot" (or "shorten binlog retention," or "recreate the connector's offsets") as a change-controlled action with an explicit plan for the range it strands — usually a bounded, filtered snapshot of that range, not a full re-snapshot. Debezium's documentation carries the same warning: a recreated slot can make older changes unavailable and lead to skipped events.

Two operational consequences:

• Alert on slot WAL retention early, so nobody ever resolves a disk-space emergency by deleting the slot.
• Alert on slot identity/creation time, not only connector health — a connector streaming happily from a brand-new slot looks exactly like one that resumed correctly.

Schema evolution¶

Additive nullable fields are the easy case. Renames, type narrowing, semantic changes, and table splits require a versioned contract and consumer migration. Database DDL appearing in the WAL does not prove every sink can apply it.

Deploy in this order:

1. Make consumers tolerate both schemas.
2. Publish the compatible producer/source change.
3. Backfill or dual-read where needed.
4. Observe old-schema traffic reach zero.
5. Remove compatibility code.

Idempotency and ordering¶

Key a merge by source primary key and compare an ordering field from the source log. Connector ingest time is not safe: retries and backfills can arrive after newer events.

For append-only history, key each change by a stable identity such as (source_partition, source_position, event_index). For current state, retain the greatest committed source position per key.

Outbox vs raw row CDC¶

Raw CDC exposes storage-shaped events: a business action may update five tables. An outbox row written in the same OLTP transaction publishes one intentional business event. Use raw CDC for replication and analytical state; prefer an outbox for stable domain events consumed by independent services.

Reconciliation¶

CDC is incomplete without proof:

• Source count/checksum by key range versus sink current state.
• Maximum source position applied per partition.
• Delete count and tombstone age.
• Snapshot progress and streaming lag measured separately.
• Quarantine count for incompatible schema.

Periodically repair from a bounded source snapshot. A replay procedure that has never been tested is not recovery.

How it fails¶

• WAL retention fills source disk while the connector is down.
• A replication slot is dropped and recreated while the stored offset still names an older position — see the recovery contract. Silent, unrecoverable gap; connector reports healthy.
• Snapshot row overwrites a newer streamed update.
• Primary-key update leaves the old key alive.
• DDL reaches Kafka before a consumer understands it.
• Repartitioning changes per-key order during migration.
• A sink uses ingestion timestamp for last-write-wins.

Check your understanding¶

An orders snapshot runs for six hours. Order 42 changes from PAID to SHIPPED during hour two. The snapshot row reaches the sink after the live update. Specify the fields and merge predicate that keep SHIPPED, and the metrics that prove no key range was skipped.