Change Data Capture (CDC) Explained: How Systems Track Every Database Write

Quick Answer

Change Data Capture (CDC) is a technique for turning every insert, update, and delete made to a database into a stream of events that other systems can consume, instead of those systems repeatedly querying the database to check what changed. Most production CDC tools do this by reading the database's own write-ahead log or binlog, the same log the database already keeps for crash recovery.

The Problem CDC Solves

Say your main application database has a users table, and you need to keep three other systems in sync with it: a search index (so users can be found by name), an analytics warehouse (so the business team can run reports), and a cache (so profile lookups are fast). All three need to know whenever a user row changes.

The naive approach is polling: every few seconds, each downstream system runs a query like SELECT * FROM users WHERE updated_at > last_checked_time. This works at small scale but has real problems. It adds continuous read load to your primary database proportional to the number of downstream consumers. It can miss rapid updates to the same row between poll intervals (you only see the latest state, not every intermediate change). And it requires every table to have a reliable updated_at column, which falls apart for deletes — a deleted row simply isn't there to poll for.

Change Data Capture flips this around. Instead of downstream systems asking the database 'what changed,' the database (or a tool watching it) pushes out a stream of 'this exact row changed, here is the before and after' events as they happen. Downstream systems subscribe to that stream instead of hammering the primary database with queries.

This matters enough that it has become a standard piece of infrastructure in most data engineering stacks — not a niche technique. Debezium, the most widely used open-source CDC platform, describes itself plainly: it is a distributed platform for Change Data Capture that transforms changes in your databases, such as inserts, updates, and deletes, into streaming events.

It's worth being specific about the scale this matters at. A single downstream consumer polling once every few seconds might sound harmless, but a production system with a search index, an analytics pipeline, a cache layer, and two or three other services all independently polling the same source tables adds up to a meaningful, continuous tax on the primary database's capacity — capacity that would otherwise go toward serving actual user traffic. That tax grows linearly with every new consumer you add, which is exactly the kind of cost that CDC is designed to remove, by moving the 'who needs to know about this change' fan-out off the source database entirely and onto a message stream built for fan-out.

Query-Based Polling vs Log-Based CDC

There are two fundamentally different ways to implement CDC, and almost every real system uses the second one.

Query-based CDC is the polling approach described above: periodically query the table for rows that changed, using a timestamp column or a version/sequence column. It is simple to build but has every weakness just described — missed intermediate states, no clean way to detect deletes, and continuous load on the source database.

Log-based CDC instead reads the database's write-ahead log (WAL) or binary log (binlog) — the same append-only log every serious database already maintains internally for crash recovery and replication. Every write to the database, no matter how it happened, gets recorded in this log before it's considered durable. A log-based CDC tool tails this log the same way a replica database would, decodes the entries into structured change events, and emits them — without ever running a single SELECT against the live tables.

This is a much stronger approach: it captures every single change in the exact order they committed, including deletes (which show up as explicit delete log entries), and it adds essentially zero extra read load to the source database, since reading a log is a fundamentally different (and much cheaper) operation than running application queries. The trade-off is that log formats are database-specific and often require elevated permissions to read — which is exactly why purpose-built CDC tools exist rather than everyone writing their own log parser.

Verified specifics: PostgreSQL CDC tools read from a logical replication slot using output plugins, MySQL CDC reads the binlog directly, MongoDB CDC tails the oplog on the primary replica set member, Oracle CDC reads the redo log via LogMiner, and SQL Server CDC reads from dedicated CDC tables that are themselves backed by the transaction log. Different database, different log format, same underlying idea.

A Working CDC Demo With SQLite Triggers

A real log-based CDC tool reads binary WAL/binlog bytes that aren't practical to reproduce in a short demo, but the exact same event shape can be shown with SQLite triggers, which fire automatically on every INSERT, UPDATE, and DELETE and write a structured before/after record to a separate change-log table:

-- The CDC change-log table: this plays the role of a binlog/WAL stream.
-- A real CDC tool (Debezium, etc.) tails the database's actual
-- write-ahead log; here, triggers make the same *shape* of event explicit.
CREATE TABLE cdc_log (
    lsn INTEGER PRIMARY KEY AUTOINCREMENT,
    op TEXT,
    table_name TEXT,
    before_json TEXT,
    after_json TEXT,
    ts REAL
);

CREATE TRIGGER users_au AFTER UPDATE ON users
BEGIN
    INSERT INTO cdc_log (op, table_name, before_json, after_json, ts)
    VALUES ('UPDATE', 'users',
        json_object('id', OLD.id, 'name', OLD.name, 'plan', OLD.plan),
        json_object('id', NEW.id, 'name', NEW.name, 'plan', NEW.plan),
        strftime('%s','now'));
END;

With that trigger wired up, here is what actually happened after inserting two users, updating one, and deleting the other, read straight out of the resulting cdc_log table, in commit order:

=== What a CDC consumer would read off the log ===

[LSN 1] INSERT
  after:  {"id":1,"name":"Rohit","plan":"free","updated_at":"2026-01-01"}

[LSN 2] INSERT
  after:  {"id":2,"name":"Aisha","plan":"free","updated_at":"2026-01-01"}

[LSN 3] UPDATE
  before: {"id":1,"name":"Rohit","plan":"free","updated_at":"2026-01-01"}
  after:  {"id":1,"name":"Rohit","plan":"pro","updated_at":"2026-02-01"}

[LSN 4] DELETE
  before: {"id":2,"name":"Aisha","plan":"free","updated_at":"2026-01-01"}

=== Reconstructing current state purely from the change log (no query on 'users') ===
{
  "1": {"id": 1, "name": "Rohit", "plan": "pro", "updated_at": "2026-02-01"}
}

Actual live table state (for comparison):
{
  "1": {"id": 1, "name": "Rohit", "plan": "pro", "updated_at": "2026-02-01"}
}

Reconstructed state matches live table: True

The last part of that output is the important proof: state was reconstructed for the users table purely by replaying the four logged events in order (apply INSERT, apply INSERT, apply UPDATE, apply DELETE) — without running a single query against the live users table — and it matched the actual live table state exactly. That is the core CDC guarantee in miniature: a consumer that only ever reads the change stream ends up with the same picture of the data as a consumer querying the source directly, just slightly delayed and without touching the source database at all.

What a Real CDC Event Looks Like

Debezium-style CDC events follow a consistent shape regardless of which database produced them. Each event carries four key parts: before (the full row state immediately before the change, null for inserts), after (the full row state immediately after the change, null for deletes), op (the operation type — create, update, delete, or a snapshot read), and source metadata (which connector produced it, which database and table, and a timestamp/position in the log).

This mirrors exactly what the SQLite trigger demo produced above — before_json, after_json, op, and an lsn (log sequence number) standing in for the source position. That's not a coincidence; it's the natural shape for representing 'what changed' regardless of which database or tool you're using, which is why CDC events translate so cleanly onto a message queue like Kafka.

In production, these events usually land on Kafka topics (one topic per source table is a common convention), and the CDC tool itself runs as a connector inside a Kafka Connect worker — a JVM process dedicated to running these long-lived, continuously-tailing connectors. Downstream consumers (a search indexer, a data warehouse loader, a cache invalidator) subscribe to the relevant topics and apply each event as it arrives, maintaining their own copy of the data derived entirely from the event stream.

Ordering matters a lot here: because events are consumed in the same order they were committed in the source log, a consumer that processes events strictly in order will never apply an UPDATE before the INSERT that created the row, or see a DELETE out of sequence. Losing that ordering guarantee — say, by processing events from multiple partitions without care — is one of the most common ways CDC pipelines produce subtly wrong downstream data.

This event-shape consistency is also why CDC pairs so naturally with stream processing frameworks. Because every event, regardless of source table, carries the same before/after/op/source structure, a single piece of consumer code can often handle many different tables generically — route by table name in the source metadata, apply the op-specific logic, and read whatever fields it needs out of before or after — rather than needing bespoke integration code written separately for every table. That uniformity is a big part of why CDC scales well as the number of tables and consumers grows, compared to hand-rolled polling logic that tends to end up written slightly differently for every table it touches.

What CDC Is Actually Used For

A few concrete use cases cover most of why teams reach for CDC:

• Keeping a search index in sync. Elasticsearch or similar search systems need to reflect database changes, but you don't want your application code responsible for remembering to also update the search index on every write path. A CDC consumer does this centrally and can't be forgotten by a future code change.
• Feeding a data warehouse or lakehouse. Analytics systems need near-real-time copies of operational data without querying production databases directly, which would compete with live application traffic for resources.
• Cache invalidation. Instead of application code manually invalidating cache keys on every write (easy to miss a code path), a CDC consumer watching the relevant tables can invalidate affected cache entries reliably.
• Decomposing a monolith into microservices. CDC lets a new service consume change events from an existing monolith's database as a migration strategy, without the monolith needing to know the new service exists — often called the 'outbox' or 'strangler' pattern when done deliberately.
• Audit trails and compliance. A complete, ordered history of every change to sensitive tables, generated automatically rather than relying on application code to log everything correctly.
• Replicating across heterogeneous databases. If your primary database is PostgreSQL but you need the same data queryable in a different engine entirely (for a reporting tool that only speaks a certain dialect, say), CDC gives you a change stream you can translate and replay into the target system, without the two databases ever needing to talk to each other directly.
• Materialized views and denormalized read models. Applications that need a pre-joined, denormalized view of data for fast reads (common in CQRS-style architectures) can build that view incrementally by consuming change events from the normalized source tables, rather than recomputing the whole view on every change.

What ties all of these together is that CDC turns 'keep system B in sync with system A' from an application-code responsibility (easy to get wrong, easy to forget on one write path) into an infrastructure-level guarantee that works the same way regardless of how many different code paths write to the source database.

Common Pitfalls

CDC pipelines fail in a handful of recurring ways worth knowing before you build on one:

Schema changes break consumers silently. If a column is dropped or renamed on the source table, every downstream consumer expecting that field in the event payload can break at once. Schema registries (common in Kafka-based CDC setups) exist specifically to catch this at publish time rather than at every consumer independently.

At-least-once delivery means duplicates are normal, not a bug. Most CDC pipelines guarantee each change is delivered at least once, not exactly once — a connector restart after a crash can replay a few already-delivered events. Consumers need to be idempotent (applying the same event twice should be harmless), typically by keying off the row's primary key and the event's log position rather than blindly appending.

Initial snapshot vs streaming changeover. A CDC tool needs to establish a starting point — usually a full snapshot of existing data — before it can start streaming live changes from the log. Getting the handoff between 'snapshot done' and 'streaming starts' wrong is a classic source of either duplicate or missing rows right at pipeline startup.

Log retention limits. If a CDC connector goes down for long enough that the source database has already recycled (purged) the WAL/binlog segments it needed, it can't resume from where it left off and may need a full re-snapshot. Production CDC setups size log retention deliberately around expected connector downtime.

Ordering across partitions gets tricky at scale. When change events for a high-volume table get spread across multiple Kafka partitions for throughput, events for two different rows can be processed out of relative order by different consumer instances — usually fine, since most consumers only care about per-row ordering, but a real trap if a consumer's logic secretly depends on cross-row ordering (for example, assuming a parent record's insert event is always fully processed before a dependent child record's event, when they land on different partitions and get consumed by different threads).

Frequently Asked Questions