What Is CDC? Change Data Capture and Log-Based CDC Explained

PostgreSQL implements logical decoding, which decodes the write-ahead log, a record of storage-level changes, into a stream of row changes. It requires wal_level = logical, which the documentation says increases WAL volume, especially with many tables on REPLICA IDENTITY FULL and heavy updates and deletes. A consumer reads through a replication slot: a stream that persists independently of the connection, is crash-safe and emits each change once in normal operation.

MySQL's binary log records data changes for replication and point-in-time recovery. Row capture needs binlog_format = ROW, the default in MySQL 8.4, and the default binlog_row_image = FULL logs every column of the before and after image. A delete logs only the before image, an insert only the after image and an update both.

SQL Server takes a different route: CDC is enabled per database with sys.sp_cdc_enable_db and per table with sys.sp_cdc_enable_table, and a SQL Server Agent capture job reads the transaction log into change tables that mirror the source columns. By default the job processes up to 1,000 transactions per scan cycle with a 5 second wait, and a cleanup job keeps 4,320 minutes, or 3 days, of change rows. The Microsoft documentation notes a built-in latency between a commit and its row appearing in the change table.

Debezium, the most widely used open-source log-based connector, publishes each change as an event whose value holds before, after, source and op, plus ts_ms. The op field is c for a create, u for an update, d for a delete and r for a row read during a snapshot. before is null on a create, and after is null on a delete because the row no longer exists.

The source block names the source table by database and schema, and records the log position, which is the field to use for ordering and deduplication. After a delete event, Debezium emits a tombstone: the same key with an empty value, which lets a compacted Kafka topic drop the key's history.

Whether before is filled depends on the source. On PostgreSQL, REPLICA IDENTITY DEFAULT gives the previous primary key values on updates and deletes, FULL gives the previous values of every column, and NOTHING gives none. A table with no primary key and the default setting produces only create events. If a downstream job needs to know what a value was, set the identity to FULL or index that table on the columns it needs, and accept the extra WAL.

The log holds recent changes, not history, because databases recycle old log segments. A new pipeline therefore starts with a snapshot, a consistent read of the existing rows, then switches to streaming from the log position recorded at the start of the snapshot. Debezium tags snapshot rows with op = r, and its snapshot.mode setting decides when to snapshot: initial is the default and snapshots only when no offsets are stored, when_needed also re-snapshots if the recorded log position is gone from the server, and no_data skips the snapshot and streams only.

A full snapshot of a large table can run for hours, and changes keep arriving the whole time. Netflix's DBLog framework, described in a 2020 paper by Andreakis and Papapanagiotou, avoids blocking the log: it reads the table in chunks and uses watermarks to interleave those chunk reads with log events, so log processing does not stall and no locks are taken. The same mechanism lets an engineer re-snapshot one table later, for a backfill, without stopping the stream.

Plan for duplicates. The PostgreSQL documentation says a slot's position is persisted only at checkpoint, so after a crash it can return to an earlier LSN and send recent changes again, and that logical decoding clients are responsible for avoiding ill effects from handling the same message more than once. Debezium's FAQ puts it the same way: in normal operation each event is seen once, and after a failure a consuming application may see an event at least once.

The fix is an idempotent apply. Key every event by the source primary key, carry the log position, and merge into the target so that replaying an event leaves the same result. An idempotent pipeline applies an event only if its position is newer than the one stored for that key. Ordering holds per key as long as one key always goes to one partition, which is why CDC topics are partitioned by primary key.

The most common incident is a stalled consumer. PostgreSQL replication slots know nothing about their consumers and keep required WAL and catalog rows even when nothing is connected, so a dead connector fills the disk, and the documentation warns that in extreme cases the database can shut down to prevent transaction ID wraparound. Alert on slot lag in bytes, and drop slots that are no longer needed. SQL Server behaves alike: with CDC enabled, the log truncation point will not advance until the capture process has gathered the changes, even under simple recovery.

Schema changes come second. A connector decoding a log has to survive an added or dropped column. SQL Server keeps a fixed column structure in the change table, ignores new columns until you create a second capture instance, and supplies nulls for dropped ones. Decide before launch how added columns reach the warehouse and who is told when the contract changes.

Third is the hidden cost on the source: wal_level = logical raises WAL volume, and large transactions arrive as one burst when they commit. Permissions and failover need planning too. Treat the connector like a production service with its own lag and error alerts.

A change stream is a log of events, not a copy of the table, so something has to apply it. The usual pattern lands raw events in an append-only table, then runs a scheduled MERGE that keeps the newest event per primary key and upserts it into the target table; rows whose latest event is d are deleted instead.

If an analyst needs to know which address a customer had when an order shipped, the same events feed a slowly changing dimension type 2 table: each update closes the open row and opens a new one, and the event timestamp supplies the validity dates. Event-by-event capture is what makes this possible, which a periodic snapshot or a poll cannot offer.

CDC comes up in pipeline design rounds whenever a prompt says to replicate an operational database into a warehouse with low latency and no load on production. A strong answer names log-based capture and says why polling loses intermediate states and deletes, then covers the first snapshot and ordering per key, and explains how merges stay idempotent and what happens when the consumer falls behind.

Practice that design under time on data pipeline interview questions and data pipeline practice problems. For questions asked about CDC specifically, see CDC pipeline interview questions, and for how the round is run, the system design round guide.