# Change Data Capture: Moving Only What Changed

> Query-based and log-based CDC compared — what each catches, what each misses, and how to apply changes downstream without duplicates.

- Source: https://akshaythakare.com/notes/etl-elt/change-data-capture
- Topic: ETL / ELT
- Level: intermediate
- Reading time: 4 min
- Tags: cdc, etl, streaming, debezium, interview
- Updated: 2026-10-11

## 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:

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

| Strength | Weakness |
| --- | --- |
| Works with any database, no extra infrastructure | Misses hard deletes entirely |
| Easy to reason about and backfill | Depends on `updated_at` being set reliably on every change |
| | Intermediate changes between runs are lost |

> **Warning: Clock and transaction gaps**
>
> A transaction that started before your watermark but committed after it can carry an `updated_at` older than the watermark and be skipped. Use a small overlap window and deduplicate, or prefer log-based CDC for critical tables.

## 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:

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

```mermaid
flowchart TB
  accTitle: Log-based CDC flow
  accDescr: The database transaction log is read by a CDC connector, published to a stream, then applied to the lake or warehouse.
  DB[(Source DB)] -- WAL / binlog --> C[CDC connector]
  C --> K[Event stream]
  K --> L[Lake / warehouse MERGE]
```

| Strength | Weakness |
| --- | --- |
| Captures deletes and every intermediate change | More moving parts: connector, stream, offsets |
| Low load on the source | Needs log retention and replication permissions |
| Ordered per key, near real time | Schema changes must be handled deliberately |

## Applying changes downstream

Changes must be applied idempotently, in order per key:

```sql
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.