Change Data Capture (CDC)

How to copy every database change to other systems by reading the database's own log: snapshot then stream, event shape, ordering by key, idempotent sinks, deletes, schema changes and slot monitoring.

Overview

Change data capture (CDC) means turning every insert, update and delete in a database into an event that other systems can consume. A search index, a cache, a data warehouse, an analytics stream or another service can then stay in step with the source database without the application writing to each of them.

The naive way is for the application to write to the database and then to every other system. That is a dual write, and it drifts out of step under crashes and concurrent requests. CDC avoids it by reading the database's own change log: the write-ahead log (WAL) in PostgreSQL, the binary log (binlog) in MySQL. The database already writes that log for crash recovery and replication, and its replication interface hands the changes out in commit order. A CDC connector such as Debezium reads it and publishes each committed change, usually to Kafka.

Reading the log is the easy part. The interview is about the rest: how a new consumer gets the existing rows, what an event looks like, which order is promised, how a sink handles duplicates and deletes, what happens when the schema changes, and why a stalled connector can take down the source database.

What you will be able to do

  • Explain why dual writes fail and what CDC reads instead.
  • Describe snapshot then stream, and why the snapshot must line up with a log position.
  • Compare log-based CDC with polling an updated_at column.
  • Design an idempotent sink that survives duplicates and late events, in SQL.
  • Monitor a CDC pipeline and explain replication slot growth.

Key concepts

  • Dual-write problem
  • Logical decoding
  • Replication slot
  • MySQL binlog (ROW format)
  • Snapshot then stream
  • Change event envelope
  • Ordering per key
  • Idempotent sink
  • Tombstones
  • Slot lag and WAL retention

Interview questions

  • Why not just have the application write to the database and then to the search index?
  • How does log-based CDC work on PostgreSQL?
  • How does a new consumer get the existing data and then the changes, without gaps?
  • Why not poll the table on updated_at instead?
  • What ordering does a CDC pipeline guarantee?
  • Design a sink that stays correct with duplicates and replays.
  • Your CDC connector has been down for six hours. What is happening to the source database?
  • When would you use the Outbox pattern instead of CDC on the business tables?

Sign up free to read the full topic

More System Design topics

Start free on Cariara · Coding problems · System design studio