Change Data Capture (CDC)

Definition: A technique for detecting and capturing row-level changes (inserts, updates, deletes) in a source database as they happen, by reading the database’s own transaction log rather than re-scanning the whole table on a schedule. Instead of a batch job periodically asking “what changed since I last checked,” a CDC pipeline treats every committed write as a discrete, ordered event that downstream systems can consume independently and in near real-time. It underlies most modern real-time data replication, and it’s the reason a search index, cache, or data warehouse can stay in sync with a production database without ever running a heavy full-table query against it.

Architecture

Transaction log as the source of truth — every relational database already records each committed write to a durable, ordered log before applying it to disk — Postgres calls this the write-ahead log (WAL), MySQL the binlog, Oracle the redo log — and CDC piggybacks on that existing mechanism rather than inventing a new one
Log-based CDC — a connector tails the transaction log directly, parsing its binary or logical format into structured change events; this is the dominant modern approach because it adds negligible load to the source and captures every change, including deletes
Trigger-based CDC — database triggers fire on insert, update, and delete, writing a copy of the changed row into a separate shadow table that a downstream job reads; reliable, but adds write overhead to every transaction on the source table
Query-based (polling) CDC — a scheduled job periodically queries for rows whose updated_at or version column changed since the last run; simplest to build, but structurally blind to deletes and adds recurring query load
Connectors — purpose-built software such as Debezium, AWS DMS, or Fivetran’s HVR sits between the source database and everything downstream, translating a database-specific log format into a standardized change-event schema
Change events as structured records — each event commonly carries an operation type (create, update, delete, or snapshot-read), the before-and-after row state, a source timestamp, and a log position or offset for resuming after a restart
Publishing to a broker — connectors commonly publish change events to Apache Kafka or another Message Queue, decoupling the source database from however many downstream consumers subscribe to the resulting stream
The initial-snapshot problem — a connector starting today only sees changes from this point forward, so most implementations first take a consistent snapshot of existing table contents, then switch to streaming log changes from the exact log position the snapshot was taken at, so no row is missed or duplicated

Why It Matters

Keeps downstream systems fresh without hammering the source — replaces a heavy periodic full-table query with a lightweight stream of only what actually changed, cutting both source load and end-to-end staleness from hours to seconds
Captures deletes correctly — a polling query only sees rows that still exist; CDC captures a delete as its own explicit event the moment it’s committed, something query-based approaches structurally cannot do
Decouples source systems from consumers — once changes land on a broker like Apache Kafka, any number of downstream systems — a warehouse, a cache, a search index, another microservice — can subscribe independently, without each one querying the source database directly
Enables real-time architectures — CDC is a backbone of modern Event-Driven Architecture, turning a database’s internal state changes into first-class events the rest of the system can react to
Preserves ordering and completeness — reading the log rather than periodic snapshots preserves the exact sequence of writes, including intermediate states a polling interval would silently skip over

Under the Hood: Reading the Transaction Log

A connector’s core job is reading a stream of bytes it doesn’t own, fast enough to keep up, without ever getting in the source database’s way. Roughly, the process runs like this:

Register as a replica, not a client — log-based connectors typically attach using the database’s native replication protocol (Postgres logical replication, MySQL’s binlog protocol), the same mechanism real standby replicas use, so the connector looks like just another follower rather than added query load
Read sequentially, not randomly — the transaction log is append-only and read forward from a bookmark, so tailing it costs the database little beyond the overhead of streaming bytes it was already writing anyway
Track a resumable position — every event carries a log sequence number (LSN) or binlog coordinate; the connector periodically checkpoints its position so a restart resumes exactly where it left off instead of re-reading everything or skipping changes
Decode the log’s native format — WAL and binlog entries are compact, database-specific binary formats; the connector’s decoding logic translates them into a portable structured representation, commonly JSON or Avro, meaning roughly “table X, row Y, operation Z, old values, new values”
Emit one event per row change — a single multi-row UPDATE statement becomes multiple discrete change events, one per affected row, each independently consumable downstream
Preserve transaction boundaries — many connectors group events by their originating transaction, so a consumer can tell which changes committed together atomically instead of treating every row change as fully independent

Comparison: Log-Based CDC vs Trigger-Based CDC vs Polling

Approach	Source DB Impact	Latency	Captures Deletes?	Operational Complexity
Log-Based CDC	Minimal — reads a log the database already writes	Seconds or less	Yes — deletes are explicit log entries	Moderate to high — connector infra, log retention tuning
Trigger-Based CDC	Moderate — every write also fires a trigger and writes a shadow row	Seconds	Yes — triggers fire on DELETE too	Moderate — triggers to maintain, tightly coupled to schema
Polling (Query-Based)	High — repeated full or filtered table scans	Minutes, tied to poll interval	No — deleted rows simply vanish from results	Low — just a scheduled query

Common Pitfalls

Schema changes breaking downstream consumers — adding, renaming, or dropping a source column changes the shape of every subsequent change event, and a consumer expecting the old schema can fail or silently misinterpret fields
Getting the initial snapshot wrong — forgetting that a new pipeline needs a snapshot of existing data, not just future changes, means the downstream system starts out missing everything that existed before the pipeline turned on
Connector lag under high write volume — a burst of writes can make the connector fall behind, and lag tends to compound if the broker or downstream consumers also struggle to keep pace
Assuming exactly-once delivery — most CDC pipelines are at-least-once, meaning a restart or rebalance can redeliver an already-processed event; consumers need to be idempotent rather than assume single delivery
Forgetting to handle deletes explicitly — it’s easy to build transformation logic that only handles inserts and updates, silently ignoring delete events until a downstream table quietly accumulates rows the source no longer has
Underestimating log retention requirements — if a connector falls far enough behind that the source recycles WAL or binlog segments before they’re read, the connector loses its position and may need a full re-snapshot
Treating CDC as a substitute for data validation — CDC faithfully replicates whatever the source contains, bad data included; it moves changes accurately, it doesn’t clean them (see Data Quality and Validation)

Worked Example

A single UPDATE on an orders table shows every hop a change makes, from source write to a downstream system reflecting it:

An application runs UPDATE orders SET status = 'shipped' WHERE id = 4821
Postgres commits the write and appends it to the WAL before returning success to the application
Debezium, tailing the WAL as a logical replication client, reads the new WAL entry
Debezium decodes it into a structured event containing the before-image (status: 'processing') and after-image (status: 'shipped')
Debezium publishes that event to a Kafka topic, e.g. orders.public.orders
A downstream consumer, say a search-index updater, reads the event and applies the same change to its own copy of order 4821

Step	Location	What Happens
1	Application	Executes the `UPDATE` statement against order 4821
2	Source database	Commits the write, appends an entry to the WAL
3	CDC connector	Tails the WAL, reads the new entry
4	CDC connector	Decodes the entry into a structured event with before/after state
5	Message broker	Receives and stores the event on a topic and partition
6	Downstream consumer	Reads the event, applies the same status change to its own copy

The whole hop, from committed write to a downstream system reflecting status: 'shipped', commonly completes in well under a second on a healthy pipeline — the delay is dominated by network and broker throughput, not by any polling interval.

Real-World Use

Real-time data warehouse sync — streaming every source change into Snowflake or another warehouse keeps analytics dashboards minutes-fresh instead of waiting on an overnight batch load
Cache invalidation — a change event triggers an immediate cache eviction or update, avoiding the stale-cache window a periodic refresh job would otherwise leave open
Event-driven microservice integration — a change to an orders table becomes an event other services react to, without those services querying the orders database directly or coupling to its schema (see Event-Driven Architecture)
Incremental data lake ingestion — feeding a data lake a continuous stream of row-level changes instead of repeated full-table reloads, cutting both compute cost and the lag before new data is available for analysis
Zero-downtime database migrations — CDC can stream ongoing changes from an old database to a new one during a cutover, keeping both in sync until traffic switches over with minimal downtime

Best Practices

Monitor connector lag as a first-class metric — track the gap between the latest log position and the connector’s current read position, and alert on it the same way you’d alert on any other pipeline SLA
Plan for schema evolution explicitly — use a schema registry and a compatibility policy so a source-side migration doesn’t silently break every downstream consumer
Treat deletes as first-class events, not afterthoughts — design downstream transformation logic to handle the delete case from day one, rather than patching it in after data quietly goes stale
Size the initial snapshot deliberately for large tables — a naive snapshot of a multi-billion-row table can take hours and load the source heavily; chunked or parallelized snapshotting strategies exist specifically for this
Make downstream consumers idempotent — since most CDC pipelines deliver at-least-once, design consumers so processing the same event twice produces the same end state as processing it once
Right-size log retention — configure WAL or binlog retention with enough buffer for realistic connector downtime (deploys, restarts, incident response) so a routine outage doesn’t force a full re-snapshot

FAQ

Does CDC replace ETL entirely? No — CDC is usually the extraction mechanism feeding into an ETL vs ELT pipeline; transformation and loading still happen downstream, CDC just supplies the “what changed” stream instead of a batch extract.

Is CDC the same as database replication? Closely related but not identical — native replication typically keeps a full standby copy of a database, while CDC repurposes the same underlying log to emit discrete, consumable change events to arbitrary downstream systems, not just another database instance.

Can CDC work without changing the source database’s schema? Yes, for log-based CDC — since it reads a log the database already produces, it requires no triggers, extra columns, or schema changes at all, which is a big part of its appeal over trigger-based approaches.

What happens if the CDC connector goes down? A well-configured connector resumes from its last checkpointed log position once restarted, replaying only what it missed — the risk is the log being recycled before the connector comes back, which is why retention sizing matters.

Does CDC guarantee events arrive in order? Generally yes per source table or row, but a broker with multiple partitions can reorder events across partitions unless the pipeline explicitly keys events, commonly by primary key, to preserve per-row ordering.

Is CDC only useful for huge, high-volume systems? No — even a modest application benefits any time near-real-time sync, delete-awareness, or reduced source load matters more than the operational complexity of running a connector.

History

Origins in database replication technology — reading a transaction log to propagate changes predates the term “CDC” by decades, rooted in native database replication and standby-server tooling built for high availability, not analytics
The term entered mainstream data-warehousing vocabulary in the 1990s and 2000s alongside early ETL tooling, initially describing any technique — including trigger-based and timestamp-based approaches — for identifying rows changed since the last load
Debezium’s 2016 open-source release popularized log-based CDC specifically, shipping connectors for Postgres, MySQL, MongoDB, and other databases on top of Kafka Connect, turning what had been proprietary, costly tooling into a freely available standard
Tight pairing with Kafka via Kafka Connect — Debezium ships as a set of Kafka Connect source connectors, which cemented Kafka as the default broker for CDC pipelines and made “Debezium plus Kafka” close to a default architecture choice
Cloud-managed CDC services emerged through the 2010s and 2020s — AWS DMS, Google Datastream, Fivetran, and similar offerings packaged log-based CDC as a hosted service, lowering the operational barrier that had kept it in the hands of large, dedicated data teams
Adoption for real-time ELT pipelines — as cloud warehouses grew cheap and fast enough to transform data after loading it (see ETL vs ELT), CDC became the natural extraction layer feeding that pattern continuously instead of on a nightly batch schedule

Common Interview Questions

“How does CDC differ from polling a table on a schedule?” — expect an answer centered on reading the transaction log versus re-querying, and the resulting differences in source load, latency, and delete-visibility
“How would you handle a schema change on a table being captured by CDC?” — expect discussion of schema registries, compatibility modes, and coordinating source migrations with downstream consumer updates
“What happens to in-flight events if a CDC connector crashes?” — expect an explanation of checkpointed log positions, resuming from the last committed offset, and at-least-once delivery implications
“Why can’t polling reliably detect deletes?” — expect the observation that a deleted row simply stops appearing in query results, leaving no record a deletion happened at all, unless the table uses soft deletes
“Walk through what happens end-to-end when a row is updated in a CDC-enabled database” — expect a trace from the application write through the transaction log, connector, broker, and downstream consumer

Example

Debezium reads a Postgres database’s write-ahead log and streams every row change into Kafka, keeping a search index updated within seconds of a database write. On day one, it first takes a consistent snapshot of the existing products table — say, two million rows — before switching to streaming mode, so the search index starts out complete rather than empty. From that point on, every insert, update, and delete on products shows up in the index within roughly a second of being committed, with no polling query ever touching the source database.

Referenced by

Dig deeper

Spot an error in this note?