ivinco
Change Data Capture (CDC): How It Works and Breaks

Change Data Capture (CDC): How It Works and Breaks

Ivinco Team··Updated

Change data capture (CDC) is a way to record every insert, update and delete in a database and pass those changes to other systems as they happen, instead of re-copying whole tables on a schedule. The most common method reads the database's own transaction log (the PostgreSQL WAL, the MySQL binlog, the SQL Server transaction log) and turns each committed change into an event that a warehouse, a search index or another service can apply.

On a whiteboard, CDC looks simple. Read the log, stream the events, replay them into the warehouse. Real-time replication without nightly ETL.

In production it fails in ways a proof of concept never shows. A typical failure: someone runs an ALTER TABLE on a multi-terabyte table. The CDC connector hits a schema change it can't resolve, falls back to a full resync, and spends the next day replaying the whole table while lag climbs into the millions of events. Meanwhile the PostgreSQL replication slot holds on to WAL, disk usage on the primary climbs, and the DBA gets paged because writes are about to stop.

This is the Schema Change Tax: the cost of change data capture at scale, paid in emergency engineering hours every time a source schema evolves. It is easy to underestimate because it never appears in a demo.

CDC is not a simple streaming pattern. It is a distributed system with latency-sensitive state and an operational surface that rivals a stream processor. "Read the log, stream the rows" is the mental model that produces that failure.

How Change Data Capture Works

Log-based CDC systems read the database's native replication log (PostgreSQL WAL, MySQL binlog, Oracle redo log, SQL Server transaction log) and convert each committed transaction into a stream of row-level change events. An insert produces a create event with the new row. An update produces an event with before and after row images. A delete produces a delete event with the old row.

The downstream consumer is typically Kafka, a warehouse like Snowflake or BigQuery, or a lakehouse table format like Iceberg or Delta. The consumer replays the stream to maintain a near-real-time replica of the source.

The four CDC methods

Log reading is one of four common ways to capture changes:

  1. Timestamp queries. A scheduled query pulls rows whose updated_at column is newer than the last run. Simple, but it misses hard deletes and adds query load to the source.
  2. Table diffs. Compare a fresh copy of the table with the previous one. Catches everything, at the cost of reading the full table every time.
  3. Triggers. Database triggers write every change to a shadow table. Deletes are captured, but every write on the source gets slower.
  4. Log-based CDC. Read the transaction log. Low impact on the source, captures inserts, updates and deletes in commit order, and needs access to the log plus enough log retention for the consumer to keep up.

At scale, log-based CDC is the default, and the rest of this post is about it.

  • Debezium on Kafka Connect. The de facto open-source standard. A Kafka Connect worker pool runs connector instances for MySQL, PostgreSQL, Oracle, SQL Server, MongoDB and others. Events land in Kafka topics per table. Consumers then write those topics to a warehouse or lakehouse. This is the pattern Debezium documents and the most common production shape.
  • Flink CDC. Flink CDC embeds change capture directly in a Flink job, skipping Kafka as the transport layer. Sources include MySQL, PostgreSQL, Oracle, SQL Server, TiDB, MongoDB, Vitess, Db2 and OceanBase. The project describes "sub-second end-to-end latency in real-time binlog synchronization scenarios." The trade-off is that Flink CDC inherits Flink's full operational model.
  • Managed CDC services. Estuary Flow, AWS zero-ETL integrations and Airbyte (for simpler cases) remove the Debezium and Kafka Connect operational burden, at the cost of usage-based pricing and vendor-specific feature gaps.

Who Actually Needs CDC

The short list of use cases where CDC pays for its operational cost:

  1. Real-time data warehouse replication at high change volume. When nightly batch loads can't keep up with the source change rate, or when analytical latency SLAs require sub-hour freshness on specific tables.
  2. Event-driven architectures built on database state. Downstream services that react to every order, every user update, every payment. These consumers fundamentally need the stream.
  3. Audit logs and compliance. Regulated industries that need to capture every change for forensics, with immutable records.
  4. Multi-region replication with transformation. When the destination is a different database engine, a different cloud, or a warehouse schema that doesn't match the source.
  5. Feeding streaming analytics. Flink or Spark Streaming jobs computing real-time aggregations on transactional data.

The use cases where CDC is usually wrong:

  • Moving 10 tables into a warehouse for daily reporting. Use a managed zero-ETL integration or scheduled extracts. Running Kafka Connect for this is infrastructure theater.
  • Low-change-rate operational tables. A 500-row customers table that updates twice a day does not warrant a replication slot; an incremental query every few minutes is simpler and just as fresh for the business consumer.
  • Small teams with competing priorities. CDC has enough operational complexity that a small team running it alongside other priorities can spend more hours on replication incidents than on data value.

The Schema Change Tax

When a source schema changes (ADD COLUMN, ALTER COLUMN TYPE, RENAME COLUMN, DROP COLUMN), the CDC connector has to reconcile the change. If the change is backward-compatible and the connector supports incremental snapshots, some systems handle it gracefully. If it's not, the connector typically needs a full resync.

Full resync on a large table is where the operational crisis lives:

  • A multi-terabyte table has to be read from the source, serialized through the connector, published to Kafka (or a stream equivalent), and replayed by every downstream consumer.
  • During the resync, the replication slot keeps accumulating WAL. If WAL retention fills disk faster than the resync can drain it, the source database runs out of disk and stops accepting writes.
  • Consumers see lag grow. Downstream analytical queries against the warehouse return partial data. Some systems route that partial data to the dashboard anyway.
  • Recovery takes hours for small tables and can take days for multi-terabyte ones, depending on network throughput and downstream write parallelism.

Managed zero-ETL integrations hide the problem rather than remove it. Each source documents its own limitations, and schema changes are where they bite.

Mitigation patterns that work:

  • Schema change coordination. Source-side DDL goes through a review that includes the CDC pipeline owner. No schema changes without a migration plan for the CDC layer.
  • Set snapshot.mode deliberately. The default initial snapshots only when no offsets exist, so restarts just resume streaming. no_data never snapshots; Debezium says to use it only when all data of interest is still in the WAL.
  • Per-table topic fanout with selective resync. When one table's schema changes, you only want to resync that table's topic, not every table the connector manages. This requires clean per-table topic naming and ownership.
  • Incremental snapshots. Debezium can snapshot a subset of tables without stopping the connector, and the Debezium 3.0 release added snapshot isolation level options for PostgreSQL.

The Replication Slot Problem

PostgreSQL CDC has a class of problems around replication slots that is worth calling out.

A PostgreSQL logical replication slot is a server-side object that tracks how far a subscriber has consumed the WAL. The slot holds a reference to the oldest WAL that the subscriber still needs. If the subscriber stops consuming (the Kafka Connect worker dies, a network partition, a connector bug), the slot stays at its current position, and PostgreSQL cannot remove any WAL newer than it.

Picture a Debezium connector that dies on a Friday night. By Monday morning the primary has kept a whole weekend of WAL it couldn't clean up. Disk is nearly full, WAL archive jobs are failing, and writes to the primary are about to block.

Operational discipline that prevents this:

  • Alert on slot lag. Compare confirmed_flush_lsn in pg_replication_slots with pg_current_wal_lsn() and alert when lag passes a threshold you choose, for example 1 GB or an hour.
  • Alert on slot age. A growing age(catalog_xmin) on a logical slot means the slot is stuck.
  • Cap retained WAL. On PostgreSQL 13 and later, max_slot_wal_keep_size invalidates a slot before it can fill the disk. You lose CDC continuity for that slot, which is better than losing the primary.
  • Give WAL its own volume. That keeps WAL growth from filling the data volume.
  • Automate slot cleanup. If a slot is clearly abandoned (no consumer for a day or more), drop it. On PostgreSQL 18 and later, idle_replication_slot_timeout invalidates slots that stay inactive longer than the timeout.

MySQL binlog-based CDC has its own failure modes, but the slot problem is PostgreSQL-specific. Oracle redo logs have a different model again. If you run CDC across multiple source engines, each needs its own operational playbook.

The Primary Key Requirement

CDC needs a key to match each change event to a row downstream.

In PostgreSQL, a table in a publication needs a replica identity, by default its primary key, to replicate updates and deletes. The PostgreSQL documentation is blunt about the consequence: for a table with no primary key and no other replica identity, UPDATE and DELETE statements fail on the source once the table is in a publication that replicates them. REPLICA IDENTITY FULL works as a fallback, at the cost of using the whole row as the key.

Downstream, events without a reliable key break upserts. Destinations produce duplicates, and row-level ordering becomes unreliable.

Legacy databases often have tables with surrogate keys that aren't declared as primary keys, or composite keys that aren't declared unique. CDC adoption typically starts with auditing every source table and declaring the missing keys.

The "Real-Time" Expectation Gap

CDC is marketed on sub-second latency. Production latency is usually higher, and the sources of delay are structural.

End-to-end CDC latency is the sum of:

  • Source commit to WAL visibility. PostgreSQL commits data and writes WAL; the WAL becomes visible to logical decoding with a small delay.
  • WAL read to connector. Network round-trip, connector parsing.
  • Event serialization. Avro or JSON encoding, Kafka producer batching, Schema Registry compatibility checks.
  • Kafka commit acknowledgment. Replication factor and the acks setting determine how long this takes.
  • Consumer read latency. Consumer polling interval, consumer group rebalances.
  • Destination write latency. Warehouse staging, transformation, commit.

A Debezium connector doing MySQL binlog replication into Kafka, into a Spark job, into Iceberg is not a sub-second pipeline. Expect seconds when every hop is healthy, and minutes or more when one component falls behind.

For sub-second latency, the architectural choice is Flink CDC writing directly to the destination (bypassing Kafka) or an in-database replication feature (PostgreSQL logical replication to PostgreSQL, for example). Every hop you add adds latency.

When CDC Is Not the Answer

Three scenarios where teams pick CDC and later regret it:

  1. The source is a SaaS app, not a database. Some apps publish their own change streams (Salesforce has Change Data Capture events), but most give you API pagination and webhooks, which have different guarantees. Reverse-engineering CDC from API polling is a different problem.
  2. The requirement is "I need fresh data in the warehouse." That is an ETL latency question, not a CDC question. If daily batch meets your freshness SLA, keep daily batch. If hourly works, use hourly. CDC is the right answer only when the latency budget is below what batch can deliver.
  3. The business doesn't act on sub-second changes. Most analytical dashboards are checked every few minutes or hours. CDC into a warehouse behind a dashboard that refreshes hourly is infrastructure that serves no business consumer.

See our streaming vs. batch post for the longer treatment of the Freshness Gap: the distance between the architecture's capability and what the business actually consumes.

There is no systematic public data on failure rates across Debezium, Flink CDC and managed services. The Debezium issue tracker is the best public source and shows recurring reports of a growing WAL backlog, retries on an invalidated slot and streaming stalls. These are reports from the people who hit problems, not systematic sampling; the true failure-rate picture lives inside vendor support channels.

What is clear: CDC is operationally harder than batch ingestion at the same scale, and its failure modes are less familiar to most teams. Newer releases have removed some pain points. The category's operational surface has not shrunk.

CDC works when schemas don't change. Schemas change.


Need a CDC pipeline that survives schema changes, or help with a Debezium cluster stuck on slot lag? Our data engineering work starts with a 30-minute call with a senior engineer about your sources and what fails today.

Frequently Asked Questions

What is change data capture (CDC) and how does it work?

Change data capture records every insert, update and delete in a database and streams those changes to other systems. Log-based CDC reads the database's replication log (PostgreSQL WAL, MySQL binlog, SQL Server transaction log) and turns each committed transaction into row-level change events. Consumers such as Kafka, warehouses and lakehouses apply those events to keep a near-real-time copy of the source.

What is the difference between ETL and CDC?

ETL extracts data in batches on a schedule, transforms it and loads it into a target. CDC captures each change as it happens and streams it. The two are not rivals: CDC often replaces the extract step of an ETL or ELT pipeline, swapping full or incremental batch extracts for a continuous change stream.

What is the difference between change tracking and change data capture in SQL Server?

Both track inserts, updates and deletes. Change data capture reads the transaction log asynchronously and stores the changed data and its history in change tables. Change tracking records synchronously that a row changed, but not the data or its history, so applications read the current row from the table itself. It uses far less storage.

What is the difference between Salesforce Change Data Capture and platform events?

Salesforce Change Data Capture publishes an event automatically whenever a record is created, updated, deleted or undeleted, and you can't publish those events yourself. Platform events are custom messages: you define their fields and publish them from Apex, Flow or an API call. Use CDC to keep an external store in sync with Salesforce records, and platform events for integration messages of your own.

Debezium runs on Kafka Connect and produces events into Kafka topics for downstream consumers to read. Flink CDC embeds change capture directly into a Flink job, skipping Kafka as a transport layer. Debezium is the more common production pattern because Kafka acts as a durable buffer. Flink CDC gives lower latency at the cost of inheriting Flink's full operational model.

Why do CDC pipelines fail on schema changes?

When a source schema changes (ALTER TABLE, ADD COLUMN and so on), the CDC connector must reconcile the downstream schema. Changes that aren't backward-compatible typically trigger a full resync. On a large table this can take hours to days, during which replication slots accumulate WAL and downstream data is stale.

How do you monitor Debezium replication slots in PostgreSQL?

Alert on the gap between confirmed_flush_lsn in pg_replication_slots and pg_current_wal_lsn(), and on a growing age(catalog_xmin), which signals a stuck slot. On PostgreSQL 13 and later, set max_slot_wal_keep_size so a stuck slot is invalidated before it fills the disk. If a slot is clearly abandoned, drop it: losing CDC continuity is better than losing the primary database.

Do I need Kafka for CDC?

No. Kafka is the most common transport for Debezium, but Debezium Server streams change events straight to Amazon Kinesis, Google Cloud Pub/Sub, Apache Pulsar, Redis streams, NATS and other systems without Kafka. Flink CDC reads changes inside a Flink job, and managed services hide the transport entirely. Kafka earns its place when several consumers need the same durable stream.

Is AWS zero-ETL a replacement for Debezium?

For moving transactional data from Aurora, RDS or DynamoDB into Redshift, often yes: AWS zero-ETL replicates the changes without a pipeline you run. It does not replace Debezium for custom downstream processing, fan-out to several destinations, or event-driven architectures that need Kafka-style durability.