Designing a CDC Pipeline: Change Data Capture from Binlog to Downstream

Every data team eventually needs the same thing: the OLTP database's changes, delivered elsewhere — to the search index, the cache, the warehouse, the fraud model. The obvious approach, dual writes ("write to Postgres and publish to Kafka"), is a correctness trap: the two writes aren't atomic, so crashes and races leave the copies permanently diverged, and no retry policy fixes a fork. Change data capture inverts the approach: stop announcing your changes — let the pipeline read the database's own write-ahead log, the one place where the truth is already totally ordered and durable.

The capture layer: tailing the log

Every serious database exposes its replication stream: MySQL binlog, Postgres logical decoding (WAL → row events via a replication slot), MongoDB change streams. A CDC connector (Debezium being the reference design) poses as a replica, decodes row-level events — INSERT/UPDATE/DELETE with before/after images — and publishes them, typically one Kafka topic per table, partitioned by primary key so each row's history stays ordered:

{op: "u", before: {id: 42, status: "pending"},
          after:  {id: 42, status: "paid"},
 source: {lsn: 0x1A2B3C, table: "orders"}, ts_ms: ...}

Two properties fall out for free, and they're the whole reason to do it this way: nothing is missed (the log is the database's own definition of what happened — triggers, bulk updates, and out-of-band scripts included, which application-level events never reliably capture), and order per key is the commit order. The connector tracks its position (LSN/GTID) as a consumer offset; crash → resume from the last recorded position → at-least-once delivery of an idempotent-by-nature stream (row events carry full state, so downstream upserts absorb replays).

The hard part everyone skips: the initial snapshot

A new consumer needs the current table contents plus all changes after — and the table is being written while you copy it. The naive "dump, then stream" loses or duplicates the writes that happened mid-dump unless the handoff is exact. The clean design (DBLog/Debezium's watermark approach): interleave chunked snapshot reads with the live stream, using watermarks to deduplicate — mark position, read chunk, mark again; any streamed event for a key inside the chunk window supersedes the snapshot row. Result: a consistent snapshot without locking the source, resumable per chunk. In an interview, distinguishing "bootstrap" from "tail" — and knowing the bootstrap is the hard 20% — is what separates people who've operated CDC from people who've read about it.

Schema evolution: the operational 80%

ALTER TABLE happens; the pipeline must not corrupt or halt. DDL flows through the same log; the connector maintains an internal schema history and stamps every event with its schema version, while a schema registry enforces compatibility rules downstream (backward-compatible changes — add nullable column — flow through; breaking changes — drop/retype a column — get stopped at the source and force a coordinated migration: new topic, dual-publish window, consumer cutover). The registry is the data-contract enforcement point: without it, one hasty ALTER at 2am becomes silent nulls in the warehouse and a fraud model scoring garbage. Unglamorous, decisive.

Delivery semantics and the transformation tier

Downstream consumers see at-least-once, per-key-ordered events. The standard consumption patterns, each one sentence: cache invalidation — evict/upsert on event (TTL as backstop); search indexing — upsert documents, with a version check (event.lsn > doc.lsn) making replays harmless; warehouse — append raw events, then merge into current-state tables on a schedule (dbt-style), keeping the append log as the audit trail; event-carried state transfer — other services materialize local replicas of the table they keep asking you for. For consumers wanting transactions rather than rows, the log also carries transaction boundaries — buffering a transaction's events until its commit marker restores atomicity that per-row topics lose (and is exactly what feeding a read model demands).

Two sharp edges to name before the interviewer does. Resharding the source: key-partitioned topics preserve per-key order only while the partition count is fixed — plan topic partitioning up front or accept an ordering epoch on split. The outbox alternative: when what you want downstream is a domain event ("OrderPlaced") rather than row diffs, CDC on an outbox table gives you both worlds — the app writes the event in the same transaction as the data, CDC ships it exactly-once-ish; that's the transactional outbox pattern with CDC as its delivery mechanism, and choosing between "row CDC" and "outbox CDC" is a modeling decision, not a technology one.

Interview probe	Answer sketch
Why not triggers writing to a queue table?	Triggers tax every write, are per-table toil, and the queue table becomes its own contention point; log tailing is invisible to the workload
Replication slot fills the disk?	The classic Postgres incident: a stalled consumer pins WAL forever — monitor slot lag, alert, and have a drop-and-resnapshot playbook
Exactly-once into the warehouse?	At-least-once + idempotent merge (dedupe on LSN/PK) — same answer as every exactly-once question, and it's fine
Multi-region source failover?	GTID-based positions survive primary failover; without them, CDC must re-anchor — say "position portability" and you've said the risk

The durable principle: the write-ahead log is the database's own event stream — capture it instead of duplicating it. Dual writes ask two systems to agree; CDC asks one system to remember, and it already does.