How it works
The database remains the source of truth. When a transaction inserts, updates, or deletes rows, CDC captures those committed changes and publishes records containing identifiers, operation types, and source positions; row contents depend on database and connector configuration.
Log-based CDC reads the database's transaction log, such as a write-ahead log or binary log. Trigger-based capture and polling are alternatives, but they have different costs: triggers add work to writes, while simple timestamp polling can miss deletes and intermediate changes.
For an existing dataset, coordinate an initial snapshot with a retained log position so changes during the copy are not lost. The connector then streams changes from the appropriate position, using a snapshot protocol that handles overlap without letting older snapshot rows overwrite newer updates.
A connector can publish changes to a durable message log, and independent consumers update downstream stores. For example, a change to post 42 becomes an index update keyed by post ID, while a delete removes that post from search.
Consumers checkpoint progress after applying changes and must tolerate replay after crashes. Preserve ordering where it matters, commonly per entity, and use idempotent writes or comparable source versions so duplicates and older events cannot overwrite newer state.
Monitor capture lag, consumer lag, and retained-log capacity. If a connector falls behind beyond available source history, recovery may require a new snapshot; schema changes, deletes, and source failover also need explicit handling.
A worked example
Keeping a post search index fresh
Suppose a posts database receives 2,000 changes per second, and search should reflect edits within five seconds under normal operation. Capture committed changes from the database log and publish them to a durable stream partitioned by post ID. An indexer applies updates and deletes, using a comparable source version to reject older changes. If it crashes after indexing post 42 but before checkpointing, replaying the update is harmless. To build a new index, coordinate a snapshot with retained change history and replay through a known watermark before switching traffic. Monitor capture and indexing lag separately. Search results can still be stale, so enforce current visibility rules against authoritative permission state before returning private posts.
Trade-offs to say out loud
- Reliable propagation, but asynchronous visibility
- CDC removes the application's database-to-broker dual write, but downstream stores still lag behind committed database state.
- Lower write-path coupling, but more operational work
- Log-based CDC avoids synchronous calls to downstream systems but requires connector management, database permissions, checkpoints, and retention monitoring.
- Replay supports recovery, but duplicates remain possible
- A crash between applying a change and saving progress can replay it, so end-to-end correctness still requires retry-safe consumers.
- Parallel processing, but limited ordering
- Partitioning increases throughput, but preserving per-key order does not preserve atomic visibility of a transaction across multiple downstream records.
- Automatic capture, but storage-schema coupling
- Raw row changes expose table structure rather than stable business events, so schema evolution can break consumers unless contracts are managed.
- Rebuildable projections, but costly bootstrap and catch-up
- Snapshots and replay consume database, network, and sink capacity, and extended outages can exhaust retained change history.
When to reach for it
- A transactional database owns the data, while a search index or materialized view must stay fresh without synchronous dual writes.
- You need to migrate a live database by bulk-copying existing data and continuously applying changes before cutover.
- Multiple consumers need committed changes independently, such as an indexing pipeline and an analytics pipeline.
- A database transaction must reliably hand work to a queue, and CDC can publish records from a transactional outbox.
How to bring it up in the interview
- Start with the consistency boundary: the database commit is authoritative, while downstream updates are asynchronous; state the acceptable freshness target.
- Draw the complete path from database log to connector, durable stream, and consumer, then explain where each component records progress.
- Walk through a consumer crash after applying an update but before checkpointing; interviewers will probe duplicates, version checks, and ordering.
- Explain bootstrap and recovery, not just steady-state streaming: coordinate the snapshot with log positions, retain enough history, and plan for resnapshotting.
- Distinguish row changes from business events: use an outbox when consumers need an explicit event such as OrderPlaced rather than an interpretation of table updates.
Common mistakes
- Claiming CDC makes the source database and downstream search index strongly consistent or provides exactly-once side effects automatically.
- Copying a snapshot and only then starting capture, leaving a gap where concurrent writes can be lost.
- Ignoring deletes or discarding deletion markers too early, allowing delayed updates or snapshot rows to resurrect removed data.
- Assuming one database transaction stays atomic after its changes are split across stream partitions and downstream writes.
- Treating raw CDC as a permanent business-event history without considering retention, schema changes, or missing business intent.
Where it comes up
Interview questions where change data capture is part of a strong answer, and why.
- Design Facebook post searchCDC keeps the search index aligned with post edits and deletions while requiring replay-safe indexing and protection against resurrecting deleted posts.
- Design a large data migration to the cloudCDC captures writes during the bulk copy and supports catch-up to a verified cutover position.
- Design Dropbox or Google DriveCapturing committed metadata changes is central to building a reliable sync change feed without losing updates between scans.
- Design a restaurant review app like YelpCapturing review inserts, edits, and deletions supports reliable propagation to search indexes and rating projections.
- Design Facebook MarketplaceCDC provides a reliable path from committed listing changes to the search index without application-level dual writes.
Questions people ask
What is the difference between CDC and polling?
CDC captures database changes; log reading, triggers, and polling are possible implementation approaches. Log-based CDC can capture committed changes, including deletes, without repeatedly scanning application tables. Simple updated_at polling usually observes current row state rather than every transition and needs extra handling for deletes and checkpoint boundaries.
What is the difference between CDC and the outbox pattern?
CDC transports committed database changes, while the outbox pattern writes an explicit event alongside application state in one database transaction. They work together: a CDC connector can capture outbox inserts and publish them to a broker. This preserves business intent without an unsafe application-level dual write.
Does CDC guarantee exactly-once delivery?
Not by itself; guarantees depend on the connector, transport, and destination. A practical design assumes changes can be replayed and makes downstream application idempotent or version-aware. Arbitrary external side effects still need their own duplicate-prevention strategy.