Change Data Capture (CDC) Explained: Streaming Database Changes Reliably

Your production database is the source of truth for orders, users and inventory. Many other systems need that data: the warehouse, the search index, the cache, the CRM. Copying the whole table every night is slow and stale. Querying "rows updated since last time" misses deletes and hammers the database. Change data capture (CDC) solves this by streaming every insert, update and delete as it happens.

#Key takeaways

• CDC captures row-level changes (inserts, updates, deletes) from a database and delivers them to other systems.
• Log-based CDC reads the database's transaction log (the PostgreSQL WAL, the MySQL binlog). It's low-impact, captures deletes, and preserves order.
• CDC powers near-real-time analytics, search indexing, cache invalidation, microservice data sync and migrations.
• The outbox pattern is the reliable way to publish domain events alongside database writes.
• Plan for initial snapshots, schema changes, deletes, ordering and idempotent consumers.

#Approaches to CDC

#Query-based (polling)

Periodically query for changed rows:

sqlSELECT * FROM orders WHERE updated_at > :last_sync_time;

• ✅ Simple, and works with any database.
• ❌ Misses hard deletes (the row is gone).
• ❌ Misses intermediate changes between polls.
• ❌ Depends on a reliable updated_at column that every write path updates.
• ❌ Adds query load; frequent polling on big tables is expensive.

#Trigger-based

Database triggers write changes into an audit table.

• ✅ Captures deletes and every change.
• ❌ Adds overhead to every write inside the transaction; triggers are easy to forget and hard to maintain.

#Log-based (the standard)

Databases already record every change in a transaction log for durability and replication. Log-based CDC reads that log:

textApplication ──writes──▶ PostgreSQL ──WAL──▶ CDC connector ──▶ Kafka / stream ──▶ consumers
                                              (e.g. Debezium)                     warehouse
                                                                                  search index
                                                                                  cache

• ✅ Captures all inserts, updates and deletes, in commit order.
• ✅ Low overhead: no extra queries against tables.
• ✅ Includes transaction metadata and often before/after row images.
• ❌ Requires database configuration (logical replication, binlog format) and permissions.
• ❌ More moving parts to operate.

Open-source connectors like Debezium implement log-based CDC for PostgreSQL, MySQL, SQL Server, MongoDB and others, typically publishing change events to Kafka (Apache Kafka explained). Many managed ingestion services and cloud databases offer CDC as a feature too.

#What a change event looks like

json{
  "op": "u",
  "source": {"table": "orders", "lsn": 983472, "ts_ms": 1790000000000},
  "before": {"id": 1042, "status": "paid",    "total_cents": 4999},
  "after":  {"id": 1042, "status": "shipped", "total_cents": 4999}
}

op is c (create), u (update), d (delete) or r (snapshot read). Consumers apply these to their own stores.

#Use cases

1. Near-real-time analytics. Stream changes into a warehouse or lakehouse and merge them into tables continuously, instead of nightly full dumps (data warehouse vs lake vs lakehouse).

2. Search indexes. Keep a search engine in sync with product or content tables, without dual writes from the application.

3. Cache invalidation. Evict or refresh cache entries when the underlying rows change (caching strategies).

4. Microservice data sharing. Let other services build read models from a service's data without querying its database directly.

5. Zero-downtime migrations. Replicate from an old database to a new one continuously, then cut over (monolith vs microservices covers the strangler pattern).

6. Audit trails. A complete history of how records changed.

7. AI and RAG freshness. Re-embed and re-index documents when they change, so AI answers reflect current data (RAG explained).

#The outbox pattern

Publishing events from application code has a classic problem, the dual write:

pythondb.save(order)              # succeeds
kafka.publish(OrderPlaced)  # fails → downstream never hears about the order

Or the reverse: the event is published, but the database transaction rolls back.

The transactional outbox fixes this. In the same transaction as the business change, insert an event row into an outbox table. CDC reads the outbox table and publishes the events:

sqlBEGIN;
  INSERT INTO orders (...) VALUES (...);
  INSERT INTO outbox (aggregate_id, event_type, payload)
    VALUES ('1042', 'OrderPlaced', '{"order_id": 1042, ...}');
COMMIT;

Either both are committed or neither is. Events are well-designed domain events, not raw table changes, which decouples consumers from your internal schema. This is a foundation of reliable event-driven architecture.

#Pitfalls and how to handle them

#Initial snapshot

CDC captures changes from now on, but consumers also need the existing data. Connectors typically take a consistent snapshot first, then switch to streaming from the corresponding log position. For huge tables, use incremental or chunked snapshots.

#Schema changes

Adding a column is usually fine; renaming or dropping columns can break consumers. Use a schema registry with compatibility rules, and coordinate breaking changes as versioned migrations. Data contracts help (data quality guide).

#Deletes

Downstream systems must handle delete events. Analytics tables might soft-delete (mark is_deleted) to preserve history; search indexes should remove the document. Also watch for tombstone messages in compacted Kafka topics.

#Ordering and duplicates

Ordering is guaranteed per key or partition, not globally. Consumers may see duplicates after failures. Make consumers idempotent: upsert by primary key, and ignore events older than the version already applied (using the log sequence number).

#Log retention and replication slots

If a CDC connector stops, the database keeps log segments until the connector catches up. In PostgreSQL, an abandoned replication slot can fill the disk. Monitor slot lag and alert on it.

#Large transactions and backfills

A migration that updates 50 million rows produces 50 million change events. Coordinate bulk operations with downstream consumers, or throttle them.

#Choosing a CDC approach

#FAQ

#Does CDC slow down my production database?

Log-based CDC has low overhead because it reads logs the database already writes. It does require enabling logical replication or the right binlog format, and log retention must be monitored so it doesn't consume excessive disk.

#What is Debezium?

Debezium is an open-source platform of CDC connectors that read database transaction logs and emit change events, typically to Kafka. It supports several popular databases.

#Is CDC the same as database replication?

They're related. Both read change logs, but replication copies data to another instance of the same database, while CDC exposes changes as events that heterogeneous systems (warehouses, search engines, services) can consume.

#Can CDC replace batch ETL?

For many ingestion needs, yes. It's fresher and captures deletes. Batch processing still makes sense for heavy transformations, historical recomputation and sources without accessible logs. See batch vs stream processing.