Distributed systems
Change data capture: interview questions and how to answer them
CDC turns a database’s own change log into a stream of events, so other systems can follow every row change without the application publishing them.
Written and reviewed by Sahil Srivastav
What it actually is
Change data capture reads the database’s replication log — the write-ahead log in PostgreSQL, the binlog in MySQL — and emits a record for every insert, update and delete. Downstream consumers build search indexes, caches, analytics tables or other services from that stream.
The reason the log is the right source is that it is the same thing the database uses for its own replication, so it already contains every committed change in commit order, including ones no application code knew about: a manual fix in psql, a migration, a bulk import. A trigger-based or polling approach sees only what it was built to see.
This also makes CDC fundamentally different from publishing events from application code. The application cannot atomically write a row and publish a message — that is the dual-write problem — whereas CDC derives the stream from the commit itself, so an event exists if and only if the transaction committed. That property is what makes it attractive and is worth stating precisely in an interview.
Why it matters in production
Because the common alternative, polling a table for rows modified since a timestamp, is wrong in ways that are easy to miss. It cannot see deletes at all, since the row is gone. It misses intermediate states when a row changes twice between polls. And it has a boundary problem: a transaction that commits after your query started but with an earlier timestamp is skipped forever, which is the subtle variant that corrupts data slowly.
And because CDC introduces a whole class of correctness problems of its own, which is exactly what interviews probe. The stream is at-least-once, so a consumer that is not idempotent will double-apply. Ordering is only guaranteed within a partition. And the projection must handle deletes and replays as first-class paths rather than exceptional ones — the failure mode is a search index that confidently returns documents that no longer exist.
How it works
The log already has what you need
The WAL or binlog records every committed change in order, because the database needs that for crash recovery and replication. CDC attaches as a replication consumer, so it sees committed data only — no uncommitted or rolled-back changes — and it sees them in commit order.
Why triggers and polling fall short
Triggers write to an outbox table inside the same transaction, which works but adds write overhead to every transaction and must be maintained per table. Polling cannot observe deletes, misses intermediate states, and has the commit-timestamp boundary problem where a late-committing transaction with an earlier timestamp is skipped permanently.
Deletes must be modelled explicitly
A delete event carries the key and, depending on configuration, the prior row image. A projection that only handles upserts will leave the deleted document in the index forever — and this is the single most common CDC bug, because the happy path and every test with only inserts and updates passes.
At-least-once delivery and the replay path
A consumer that crashes after applying a batch but before committing its cursor will reprocess that batch on restart. So application of each event must be idempotent — an upsert keyed on the primary key rather than an insert, a delete that tolerates the row being absent — and the cursor must advance only with committed work.
Ordering is per-key, not global
Streams are partitioned for throughput, and ordering holds within a partition only. Partitioning by primary key keeps all changes to one row in order, which is what most projections actually need. Any logic requiring a global order across different rows cannot rely on the stream and needs a different design.
Schema changes propagate too
A column added, renamed or dropped appears in the stream, and consumers must tolerate both shapes during the rollout. This is why CDC pipelines carry a schema registry or an explicit compatibility policy — it is the part that breaks during an otherwise routine migration.
Implementing it
Partition by primary key so per-row ordering is preserved, and design every consumer to be correct under reordering across different keys.
Make application idempotent by construction: upsert on the key rather than insert, and treat a delete of an already-absent row as success rather than an error.
Advance the cursor only after the work is durably applied. A cursor committed before the write is how data silently goes missing on restart.
Test the three paths that are usually missing: a delete, a replayed batch, and a rolled-back transaction. If the pipeline is only exercised with inserts and updates, these are exactly the bugs that reach production.
-- Polling cannot see this delete, and will never know the row existed.
DELETE FROM documents WHERE id = 42;
-- Idempotent projection: safe to apply the same event twice.
INSERT INTO search_index (doc_id, title, body, version)
VALUES ($1, $2, $3, $4)
ON CONFLICT (doc_id) DO UPDATE
SET title = EXCLUDED.title,
body = EXCLUDED.body,
version = EXCLUDED.version
WHERE search_index.version < EXCLUDED.version; -- ignore stale replays
-- Deletes are a first-class path, not an exception.
DELETE FROM search_index WHERE doc_id = $1;Interview questions and how to answer them
Why use the transaction log instead of polling a table?
Three reasons. The log contains deletes, which polling cannot observe because the row is gone. It contains every intermediate state rather than just the latest value at poll time. And it has no boundary problem — polling by timestamp permanently skips a transaction that commits late with an earlier timestamp. The log is also what the database already uses for replication, so it is complete by construction.
Your search index keeps returning deleted documents. Why?
The projection handles inserts and updates as upserts and has no delete path, so the tombstone event is ignored. It is the most common CDC bug precisely because every test using only inserts and updates passes. Deletion has to be modelled as a first-class state transition in the consumer.
The consumer crashes mid-batch. What must be true for correctness?
That applying an event twice is harmless, and that the cursor advances only after the work is durable. On restart the batch replays from the last committed cursor, so each application must be an upsert keyed on the row identity rather than an insert, and deletes must tolerate an already-absent row. Cursor-before-write is how records silently vanish.
How does CDC relate to the transactional outbox?
They solve the same dual-write problem from opposite directions. The outbox has the application write an intent row in the same transaction, and a worker publishes it — explicit, under your control, requires code in every write path. CDC derives events from the log, so it needs no application change and captures writes from anywhere, at the cost of coupling consumers to your table shapes rather than to a designed event contract.
What ordering guarantees can you rely on?
Per-partition only. Partitioning by primary key gives you ordered changes for any single row, which is what most projections need. Changes to different rows can arrive in any relative order, so any logic depending on cross-row ordering is unsafe and needs either a single partition — giving up throughput — or a design that does not require it.
Answers that lose the round
- Polling a modified-at column and never handling deletes
- Assuming the stream is exactly-once, so the consumer is not idempotent
- Expecting global ordering when ordering holds only within a partition
- Advancing the cursor before the downstream write is durable
- No handling for schema changes flowing through the stream
- Treating replay as an exceptional path rather than a normal one
- Publishing events from application code instead, and hitting the dual-write problem
Practise change data capture in a real repository
Gronex ships this exact failure as a runnable repository: a projection that misses deletes, exposes rolled-back changes, and duplicates documents when a batch replays. The tests check the invariant at the database boundary, so a fix that only handles the happy path does not pass.
FAQ
Does CDC add load to the source database?
Modest and mostly unavoidable — it consumes the replication stream, which the database produces anyway. The real risk is a stalled consumer: PostgreSQL retains WAL for an inactive replication slot, and a slot left behind can fill the disk and take the primary down. Monitor slot lag as seriously as replication lag.
Can consumers see uncommitted data?
No — logical decoding emits changes at commit, so rolled-back transactions never appear. This is a genuine advantage over trigger-based approaches that fire before the outcome is known, and it is worth stating because it is a correctness property rather than a performance one.
What happens during a schema migration?
The change flows through the stream and consumers see both shapes during the rollout. Consumers must tolerate the old and new forms, which in practice means additive changes only, deployed consumer-first. A rename is a drop plus an add from the consumer's perspective, which is why renames are discouraged in CDC-fed tables.
Is CDC a good way to integrate microservices?
It is effective and it has a coupling cost worth naming: consumers end up depending on your table structure rather than on a published event contract, so your schema becomes a public interface. Many teams use CDC to feed an outbox-style event stream specifically to keep that boundary explicit.