Change Data Capture (CDC) is the practice of detecting and propagating row-level changes - inserts, updates, and deletes - from a source database to a downstream system as they happen, instead of reloading whole tables. The hard part isn't reading new rows; it's catching deletes and in-place updates without scanning the entire source every run. The technique you pick decides whether you can do that cheaply.

Log-based vs. query-based

Query-based CDC asks the source for what changed using a column you trust. The two common variants:

• Timestamp - a WHERE updated_at > :last_run filter. Simple, but it misses hard deletes and depends on every write touching the column.
• Trigger - database triggers write change rows into a shadow audit table. Captures deletes, but adds write latency to every source transaction and couples your pipeline to source DDL.

Log-based CDC reads the database's own transaction log - MySQL binlog, PostgreSQL WAL, Oracle redo. The log already records every committed change in order, including deletes, so you get a complete, ordered stream without querying tables or touching the write path.

Why log-based beats batch diffing

Batch diffing - snapshot the source, compare against yesterday's copy - costs a full table scan every run and grows with table size, not change volume. On a 200M-row table where 50k rows changed, you read 200M to find 50k. Log-based reads exactly the 50k. It also captures the order of changes and the deletes that a snapshot comparison can only infer. The cost is operational: you need log access, retention long enough to survive consumer downtime, and handling for schema changes mid-stream.

Debezium + Kafka pattern

The common open-source stack is Debezium (a Kafka Connect source connector) tailing the log and emitting one change event per row mutation to a Kafka topic per table. Each event carries before, after, and an op field (c/u/d/r for create/update/delete/snapshot-read).

{
  "op": "u",
  "before": { "id": 42, "status": "pending" },
  "after":  { "id": 42, "status": "shipped" },
  "source": { "table": "orders", "lsn": 8472913 }
}

Consumers apply these events to a staging area or an operational data store, then merge into the warehouse. Because events can be redelivered, the apply step must be idempotent - see error handling for the patterns that make replay safe.

Frequently asked questions

Does CDC capture deletes? Log-based and trigger-based CDC do, because the log and the trigger both see the delete. Timestamp-based query CDC does not - a deleted row simply stops appearing, with no event to mark it gone.

Why is log-based preferred for high-volume tables? Its cost scales with the number of changes, not table size, and it never queries the source tables or adds load to the write path.

What is an initial snapshot? Before tailing the log, the connector reads the existing table once (op r) so consumers start from a complete picture, then switches to streaming new changes.

How does CDC relate to data quality? A CDC stream can carry malformed or out-of-order events; route suspect rows through the same checks you apply elsewhere, covered in data quality management.