Change Data Capture (CDC)

1The core idea#

CDC turns every insert, update, and delete into a stream of events other systems can consume. That is the whole thing.

• Your database already knows what changed.
• CDC is about getting that knowledge out, in order, without hammering the database and without missing anything.

The mental model I give juniors. Your primary DB is the source of truth, but everyone wants the same data shaped differently:

• A search index wants it tokenized for full-text.
• A cache wants it hot.
• A warehouse wants it cheap and columnar.
• A fraud service wants it the instant a row changes.

The naive fix is to make the app write to all five places. That is where it falls apart:

• Your write path is now a distributed transaction across five systems.
• When the third one times out, you are left guessing what actually happened.
• Five dependencies at 99.9 percent availability each put your write path at roughly 99.5 percent, about 3.6 hours of downtime a month you did not have before.

CDC flips this.

• The application writes to one place: the database.
• Everyone else subscribes to the stream of changes.
• The database becomes the single writer, and the change log becomes the contract.
• This is the "outbox" idea taken to its logical end: the log of what happened is the integration layer.

2How it works#

There are two families of CDC, and the difference matters a lot in interviews.

Query-based CDC polls the database.

• You add an updated_at timestamp (or a monotonic version column) to each table.
• A job runs every few seconds asking "give me everything where updated_at > last_seen."
• Upside: simple to build, works on any database, no special permissions.
• Downside: it cannot see deletes (the row is gone, so your query cannot find it).
• Downside: it adds query load to the primary.
• Downside: two rows changing in the same millisecond can slip past, depending on your boundary logic.
• Verdict: the duct-tape option.

Log-based CDC reads the transaction log directly.

• A connector pretends to be a replica and asks the DB to stream its WAL or binlog.
• It decodes each change into a structured event: table, operation, before-image, after-image, transaction ID, log position.
• It sees deletes, captures the exact order the database committed in, and puts almost no extra query load on the primary.
• This is what you want for anything serious. Debezium is the open-source tool most teams reach for.

Here is the shape of a typical log-based pipeline.

How the connector stays correct.

• It writes one message per row change into a Kafka topic (usually one topic per table).
• It tracks its position in the log (the WAL offset or binlog coordinate).
• It commits that offset only after the change is safely in Kafka.
• If it crashes, it restarts from the last committed offset and replays.
• That replay is exactly why CDC is at-least-once by default, not exactly-once. More on that in pitfalls.

The snapshot is the mechanic that trips people up.

• Turn on CDC for an existing table of ten million rows and the log only has recent changes, not history.
• So the connector takes an initial snapshot: a consistent read of the whole table.
• Then it switches to streaming from the log position captured at snapshot time.
• Getting that handoff right, no gaps and no duplicates, is the hard part of every CDC tool.

3A worked example with real numbers#

Let me make this concrete with a system I have actually built: keeping an Elasticsearch order-search index in sync with a Postgres orders table.

The numbers.

• The orders table holds 50 million rows and takes 400 GB of disk.
• At peak the app commits 2,000 order writes per second (inserts plus updates plus deletes).
• Each row change decodes to a CDC event of roughly 1.5 KB once you include before-image and after-image.
• So the stream runs at about 2,000 x 1.5 KB = 3 MB per second, or around 260 GB per day of change events through Kafka.

Sizing the pieces.

• Kafka topic. One topic, keyed by order_id, 12 partitions. At 3 MB/s a single partition on commodity hardware eats this without sweating, but 12 lets me run up to 12 parallel indexer consumers and gives headroom for a 5x sale-day spike. Retention set to 7 days, so 7 x 260 GB is about 1.8 TB on the brokers. That buys me a full week to replay if an indexer goes bad on a Friday.
• Initial snapshot. 50 million rows is too big for a blocking snapshot. At chunked reads of 10,000 rows per batch and roughly 20,000 rows/second off a read replica, the backfill takes about 50M / 20,000, which is around 42 minutes, running alongside the live stream via incremental snapshotting so the primary never locks.
• Lag budget. End to end, commit on Postgres to visible in Elasticsearch, I target under 2 seconds at p99. In steady state it sits around 300 to 500 ms. During the snapshot or a sale spike it can climb to 10 to 30 seconds, and I alert if it crosses 60 seconds.
• Indexer throughput. Each consumer batches 500 events and bulk-writes to Elasticsearch. At 2,000 events/second I need the fleet to clear 2,000/second steady, so 4 consumers each doing 500/second gives me 2x headroom.

4Trade-offs#

The decisions you actually have to defend:

Raw rows versus domain events is the one I argue about most in design reviews.

• Log-based CDC naturally emits raw table changes: "column status on row 42 went from 1 to 2."
• Downstream consumers now have to know that status 2 means "shipped."
• You have leaked your internal schema across service boundaries.
• The day someone renames a column in a migration, every consumer breaks.
• The cleaner answer for cross-team contracts is the transactional outbox: the app writes a well-formed domain event ("OrderShipped") into an outbox table in the same transaction as the business change, and CDC captures that table.
• You get the atomicity of CDC with the stable contract of events.
• My rule: outbox whenever the consumer is another team, raw CDC when it is my own search index or warehouse and I control both ends.

The second trade-off worth internalizing: CDC buys you eventual consistency, not consistency.

• There is always lag between the commit on the primary and the change landing downstream.
• Usually under a second. Under load, or during a snapshot, it can be minutes.
• If a user updates their profile and immediately searches for it, your index may not have caught up.
• Design for that or you will get bug reports that are not bugs.

5Where it is used#

CDC shows up anywhere two data systems need to agree without a two-phase commit between them.

• Search indexing. The classic. Orders live in Postgres, full-text search lives in Elasticsearch. You do not want the order service talking to Elasticsearch on every write, because a search cluster hiccup would then fail an order. CDC streams order changes into the indexer, and the index lags by a second or two. Fine for search.
• Cache invalidation. Instead of the app remembering every cache key to bust on a write, a CDC consumer watches the table and evicts or updates the relevant entries. The database change is the invalidation signal, so you can never forget to bust a key.
• Data warehouse and analytics. Nightly batch ETL is dying. Modern setups stream row changes into Snowflake or BigQuery continuously so analysts see near-real-time data. CDC is the pipe, and this is the most common reason "Big Data" teams adopt it.
• Microservice data sharing. Service A owns the customer table. Service B needs a read-only copy to avoid synchronous calls on its hot path. CDC (ideally via an outbox) feeds B a local materialized view it can query at memory speed.
• Audit and replication. The log already contains before-and-after images with transaction IDs, so CDC gives you an audit trail almost for free, and it powers cross-region replication where native database replication will not reach.

If the same data is needed in more than two places, I start sketching a CDC pipeline.

6Common pitfalls and gotchas#

"Exactly-once" is mostly a lie.

• Delivery is at-least-once: the connector can crash after writing to Kafka but before committing its offset, and on restart it replays.
• So every consumer must be idempotent: upsert by primary key, dedup on an event ID, or key Kafka messages so the same row always lands in the same partition.
• A candidate who claims exactly-once with no idempotency on the consumer is telling me the pipeline will double-process rows in production.

Ordering is per partition, not global.

• Kafka only guarantees order within a partition.
• Key by table name and you serialize the whole table through one partition, losing parallelism.
• Key by primary key and you keep order per row (usually what you actually need) while scaling across partitions.
• But you have now lost cross-row ordering, so a consumer cannot assume it sees order 41 before order 42.
• Know which guarantee your downstream needs before you pick the key.

Schema changes break the pipe.

• Someone adds a column, drops one, or changes a type.
• Log-based connectors have to decode the new log format, and downstream consumers have to handle the new shape.
• Without a schema registry and compatibility rules, a Monday-morning migration becomes a Monday-morning outage.
• Treat the CDC event schema as a public API with versioning.

Snapshots can take out your primary.

• The initial snapshot of a huge table is a long-running read.
• Older tools took locks or ran one giant transaction that blocked vacuum on Postgres and bloated the database.
• Use incremental snapshotting (chunked reads interleaved with the live stream) on large tables, and run the snapshot off a replica if you can.

Deletes and tombstones.

• A delete in CDC often shows up as a change with a null after-image, followed by a tombstone message.
• Consumers that only handle inserts and updates will quietly keep deleted rows forever in the search index.
• I have seen a "ghost results" search bug traced straight to a consumer ignoring tombstones.

7How it shows up in interviews#

Interviewers reach for CDC in any "keep two datastores in sync" question: design a search system, a news feed, an e-commerce catalog, analytics on top of an OLTP database.

• The moment I ask "what happens if the search write fails after the database write succeeds," a weak candidate is stuck.
• That dual-write problem is exactly the door CDC walks through.

Here is the contrast I watch for on the whiteboard.

Weak answer: "The order service writes the order to Postgres and then writes the same order to Elasticsearch in the same request handler. If I want it faster I can put the Elasticsearch write on a background thread."

• It fails the first follow-up: if the Postgres write succeeds and the Elasticsearch write fails, the two stores disagree and nothing reconciles them.
• A background thread makes it worse, because now the failure is silent.
• There is no replay, no ordering guarantee, and order-service availability is now chained to the search cluster's availability.
• This is the dual-write anti-pattern, and the candidate usually cannot say how they would rebuild a corrupted index without re-reading all 50 million rows from the primary.

Strong answer: "I would not dual-write. The service writes only to Postgres. Then log-based CDC, Debezium reading the WAL, streams row changes into a Kafka topic keyed by order_id across 12 partitions. The search indexer is a consumer group that bulk-writes to Elasticsearch. This decouples the write path from the search cluster, so a search outage never fails an order, and it gives me a replayable log. With 7-day Kafka retention, if the indexer falls behind or the index gets corrupted I reset the consumer offset and rebuild, no load on the primary."

• Then, unprompted, they name the costs.
• "This is eventually consistent, so there is 300 to 500 ms of lag in steady state and up to tens of seconds during a snapshot."
• "Consumers must be idempotent because delivery is at-least-once, so I upsert by order_id."
• "Deletes come through as tombstones, which the indexer has to handle or deleted orders linger in search."

The gap between those two answers is the whole interview.

• The weak one treats CDC as a magic sync button.
• The strong one treats it as an ordered, replayable, at-least-once event log with real lag and real schema coupling.
• And it names which of those costs apply to the specific system on the whiteboard.

Next, see the System Design Question walkthrough on designing a real-time search indexing pipeline, where CDC is the backbone of the whole answer.