DE Change Data Capture

Traditional incremental loading depends on source tables having reliable timestamp or ID columns to detect changes. Many production databases lack these markers. Worse, standard queries cannot detect when a row is deleted — a deleted record leaves no trace that an incremental extract can find. Change Data Capture (CDC) solves both problems by reading changes directly from the database's internal transaction log.

What Is Change Data Capture

Change Data Capture is a technique that monitors a database's transaction log and captures every insert, update, and delete as a structured event the moment it occurs. Instead of periodically querying the database to find what changed, CDC watches the log that the database already maintains for its own internal use and turns those log entries into a stream of change events.

The Security Camera Analogy

Periodically checking a warehouse for inventory changes is like sending an auditor to count shelves every morning. Changes that happened and were reversed overnight go undetected. A security camera recording every entry and exit captures every change in real time — who came in, what they took, what they added, and when they left. CDC is the security camera for a database: it records every change as it happens, nothing is missed, and deletions are captured alongside inserts and updates.

How CDC Works

The Database Transaction Log

Every major relational database maintains an internal transaction log (also called the Write-Ahead Log or WAL). This log records every change made to every table — inserts, updates, deletes, and schema changes — in chronological order. The database uses this log for crash recovery and replication. CDC tools read this same log and publish the change events to a downstream system.

Database Transaction Log (simplified):
+-----+--------+------------+---------+-----------------------------+
| LSN | Table  | Operation  | Row Key | Data                        |
+-----+--------+------------+---------+-----------------------------+
| 101 | orders | INSERT     | ORD500  | {amt: 2500, status: pending}|
| 102 | orders | UPDATE     | ORD499  | {status: completed}         |
| 103 | customers| DELETE   | C012    | {name: Old Customer}        |
| 104 | orders | INSERT     | ORD501  | {amt: 750, status: pending} |
+-----+--------+------------+---------+-----------------------------+

CDC reads this log and emits each entry as a structured event.

CDC Event Structure

Each CDC event carries the operation type (insert, update, delete), the table name, a timestamp, and the row data. For updates, both the before-state and after-state of the row appear in the event.

CDC Event (JSON format from Debezium):
{
  "op": "u",                     -- u=update, c=insert, d=delete
  "ts_ms": 1715788200000,        -- event timestamp
  "source": {
    "table": "orders",
    "db": "ecommerce"
  },
  "before": {                    -- row values BEFORE the change
    "order_id": "ORD499",
    "status": "pending",
    "amount": 1200
  },
  "after": {                     -- row values AFTER the change
    "order_id": "ORD499",
    "status": "completed",
    "amount": 1200
  }
}

CDC vs Timestamp-Based Incremental Loading

Capability                    | Timestamp Incremental | CDC
------------------------------|----------------------|------------------
Detect inserts                | Yes                  | Yes
Detect updates                | Yes (if updated_at)  | Yes (always)
Detect deletes                | NO                   | Yes
Source requires special column| Yes (updated_at)     | No
Latency                       | Batch interval       | Near real-time
Impact on source DB           | Query load on source | Minimal (reads log)
Handles rapid updates         | May miss intermediate| Captures all states

Debezium: The Leading CDC Tool

Debezium is the most widely used open-source CDC platform. It connects to the transaction log of MySQL, PostgreSQL, MongoDB, SQL Server, Oracle, and other databases, and publishes change events to Apache Kafka topics in real time. Downstream consumers read from Kafka and apply the changes to destination systems.

Debezium CDC Pipeline:

[MySQL orders table]
    | (transaction log)
    v
[Debezium Connector]
    | (reads WAL continuously)
    v
[Kafka Topic: ecommerce.orders]
    | (stream of insert/update/delete events)
    v
[Consumer Options]:
  - Kafka Connect JDBC Sink --> Data Warehouse
  - Flink or Spark Streaming --> Real-time processing
  - Custom consumer --> Cache invalidation, notifications

Common CDC Use Cases

Real-Time Data Warehouse Sync

Changes in the operational database appear in the data warehouse within seconds of occurring, instead of the next morning's batch load. Customer updates, new orders, and status changes propagate in near real time.

Deletion Propagation

When a user deletes their account, CDC captures the delete event and removes the corresponding record from all downstream systems — data warehouse, cache, search index, and compliance archives — automatically. Without CDC, deleted records persist indefinitely in destinations that only receive inserts and updates.

GDPR Right to Erasure

CDC makes it practical to propagate deletion events to every downstream system when a user exercises their right to data erasure under GDPR. The delete event flows from the source system through Kafka to every destination that holds the user's data.

Cache Invalidation

When a product price changes in the source database, the CDC event triggers immediate cache invalidation in the application layer — ensuring customers see the updated price without waiting for a cache refresh cycle.

Database Requirements for CDC

CDC requires specific database configurations to expose the transaction log. For PostgreSQL, logical replication must be enabled. For MySQL, binary logging with row-based format must be active. For SQL Server, CDC or Change Tracking must be configured on the target tables. These settings are standard in production databases and add minimal overhead to the source system.

Summary

Change Data Capture reads database transaction logs to capture every insert, update, and delete event in real time. Unlike timestamp-based incremental loading, CDC detects deletions and requires no special columns in the source table. Debezium is the dominant open-source CDC tool, publishing events to Kafka for consumption by warehouse loaders, stream processors, and cache systems. CDC enables real-time data synchronization across distributed systems and makes deletion propagation — critical for privacy compliance — tractable at scale.