Change Data Capture: Moving Only What Changed

The problem

Re-copying a whole source table every run is simple but grows with table size, loads the source database and hides when things changed. Change data capture (CDC) moves only inserts, updates and deletes since the last run.

Approach 1 — query-based (incremental extract)

Select rows whose change column moved past a stored watermark:

SELECT *
FROM orders
WHERE updated_at > :last_watermark
  AND updated_at <= :run_started_at;   -- fixed upper bound for a repeatable run

Approach 2 — log-based

Read the database's own change log (PostgreSQL WAL via logical replication, MySQL binlog, SQL Server CDC tables). Tools such as Debezium turn each committed change into an event:

{ "op": "u", "before": { "id": 42, "status": "PENDING" }, "after": { "id": 42, "status": "PAID" }, "ts_ms": 1760000000000 }

Applying changes downstream

Changes must be applied idempotently, in order per key:

MERGE INTO dim_orders t
USING (
  SELECT * FROM staged_changes
  QUALIFY ROW_NUMBER() OVER (PARTITION BY id ORDER BY change_seq DESC) = 1   -- latest change per key
) s
ON t.id = s.id
WHEN MATCHED AND s.op = 'd' THEN DELETE
WHEN MATCHED THEN UPDATE SET status = s.status, updated_at = s.updated_at
WHEN NOT MATCHED AND s.op <> 'd' THEN INSERT (id, status, updated_at) VALUES (s.id, s.status, s.updated_at);

Order by a sequence from the log (LSN or offset), not by wall-clock time, so two changes in the same millisecond still apply correctly.

Choosing

Use query-based extracts for small or append-mostly tables where deletes don't matter. Use log-based CDC when you need deletes, history of every change, low source impact or low latency.