Advanced: Change Data Capture (CDC)

Your data lives in a database, but that is rarely the only place it needs to be. The same orders also belong in a search index so users can find them, in a cache so the hot ones are fast, in a data warehouse so analysts can query them, and perhaps in two or three downstream services that react to them. The naive way to keep all of these in sync — have the application write to each one — is exactly the dual-write problem from the Distributed Transactions lesson: separate writes that can half-fail and silently diverge. Change Data Capture (CDC) is the clean way out, and it comes up constantly once a design has more than one data store.

In the interview: the moment your design says “and then we also update the search index / cache / warehouse,” do not write “the service updates all of them.” That is a dual write waiting to drift. Say instead: “I’ll capture changes from the database with CDC and fan them out through Kafka, so every derived store stays in sync with the source of truth.” That single sentence signals you have built real data pipelines.

The problem: one source of truth, many copies

Suppose orders lives in PostgreSQL. You also need:

• Elasticsearch so customers can full-text search their orders,
• Redis caching the most-read orders,
• a data warehouse (Snowflake/BigQuery) for analytics,
• a fulfilment service that ships them.

If the Order service writes to Postgres and then also calls Elasticsearch, Redis, the warehouse loader, and the fulfilment API, you have five writes that are not atomic. A crash — or just a failed network call — after the Postgres commit leaves the index stale, the cache wrong, and fulfilment unaware. Worse, this logic is now smeared across your application code, and every new consumer means touching the writer again.

The insight behind CDC is that the database already records every change it makes, in order, durably — in its write-ahead log (Postgres WAL, MySQL binlog, Mongo oplog). That log is how the database itself implements durability and replication. If we can read it, we have a perfect, ordered, complete feed of everything that happened — and we never have to ask the application to dual-write again.

How log-based CDC works

A CDC connector (Debezium is the standard open-source choice) connects to the database as if it were a replica and tails the commit log. Every committed INSERT, UPDATE, and DELETE becomes a structured change event — typically carrying the table, the operation, the primary key, and the before/after row images — which it publishes to a log like Kafka, one topic per table. Downstream, each consumer reads the stream and applies the changes to its own store: the indexer upserts documents, the cache invalidates keys, the warehouse loader appends rows.

// A Debezium-style change event for an UPDATE
{
  "op": "u",                       // c=create, u=update, d=delete
  "source": { "table": "orders", "lsn": 894621 },
  "before": { "id": 42, "status": "PENDING" },
  "after":  { "id": 42, "status": "PAID" }
}

Three properties make this powerful:

• Completeness. Because it reads the commit log, it captures every change, including deletes — the thing naive polling almost always misses.
• Low overhead. It piggybacks on a log the database writes anyway; there is no extra query load on your tables and no application code in the write path.
• Decoupling. The producer (the database) knows nothing about its consumers. Adding a new downstream store is just another consumer on the stream — no change to the writer.

CDC vs the outbox pattern vs polling

Three approaches solve “get changes out of the database,” and interviewers like to hear the trade-offs:

The outbox pattern and CDC are close cousins, and real systems often combine them: the application writes a clean, purpose-built event into an outbox table in the same transaction as the business change (so the two are atomic), and then a CDC connector tails the outbox table and publishes those events. You get the outbox’s hand-authored payloads and CDC’s reliable, no-dual-write delivery. Use plain log-based CDC when you want every change to a table with no app changes (syncing a search index); use outbox-plus-CDC when downstream services need meaningful domain events rather than raw row diffs.

Guarantees and gotchas

CDC is eventually consistent, and getting it right means respecting a handful of properties:

• Ordering is per key, not global. Changes to the same row arrive in commit order (so PENDING → PAID never flips), but changes across different rows may interleave. Partition the Kafka topic by primary key so all events for one entity land on one partition and stay ordered.
• Delivery is at-least-once, so consumers must be idempotent. A connector restart can re-emit the last few events. Apply changes idempotently — an upsert keyed by primary key, or a dedup on the log offset / event id — exactly as in the idempotency discussion of the transactions lesson. An upsert of the after image is naturally idempotent, which is why most CDC consumers use it.
• Snapshot then stream. When you first attach CDC to an existing table, the log only has recent changes. The connector takes an initial snapshot of the whole table, then switches to streaming the log from the snapshot’s position — so consumers get the full current state and then every subsequent change, with no gap.
• Schema changes. When the source table’s schema evolves, the event shape changes too. A schema registry and backward-compatible consumers keep the pipeline from breaking on an ALTER TABLE.
• Deletes and tombstones. A delete event carries the key with a null after; consumers must honour it (remove the document, evict the cache key), which is precisely what polling cannot do.

This is really a stream-processing pipeline whose source happens to be a database log, so everything you know about Kafka partitions, consumer groups, and replay applies directly — including the ability to rebuild a derived store from scratch by replaying the topic.

Where it shows up

CDC is the quiet workhorse behind a lot of designs:

• Search indexing. Keep Elasticsearch in sync with the primary database without the writer ever calling Elasticsearch.
• Cache invalidation. Evict or refresh cache entries the instant the underlying row changes, instead of relying on TTLs alone.
• Microservice data propagation. Let one service’s database changes flow to others as events, avoiding synchronous cross-service reads and shared databases.
• Analytics / ETL. Continuously replicate operational data into a warehouse or data lake in near-real-time, replacing nightly batch dumps.
• Audit logs and history. The change stream is an ordered record of everything that happened — persist it and you have an audit trail for free.

In an interview, naming CDC for any of these — “I’ll keep the read model in sync via CDC rather than dual-writing” — is a concise way to show you have moved data between systems correctly before.

Key Takeaways

1. CDC streams every row change out of a database. A connector tails the write-ahead log / binlog and turns each committed insert, update, and delete into an event, usually published to Kafka.

2. It solves the dual-write problem. Instead of the application writing to the database and the index and the cache (which can half-fail and drift), every derived store is driven from the one authoritative change stream.

3. It is complete, low-overhead, and decoupled. It captures deletes (which polling misses), reads a log the database already maintains, and lets you add new consumers without touching the writer.

4. Outbox and CDC combine well. Write clean domain events to an outbox table in the same transaction as the business change, then let CDC tail that table — purpose-built payloads with reliable, no-dual-write delivery.

5. Respect the guarantees. Order per key (partition by primary key), make consumers idempotent because delivery is at-least-once, snapshot before streaming, and plan for schema evolution.

6. It is a stream-processing pipeline. Everything about Kafka partitions, consumer groups, and replay applies — including rebuilding a derived store from scratch by replaying the change log.