◐ ai-teacher

🔀 Pipelines & Orchestration · Chapter 5

Incremental loads: watermarks and CDC

Copying the whole rides table every night works until it takes nine hours. Copying only what changed works until you discover what 'changed' fails to see.

Study this lesson free in the app →

The basics

An incremental load copies only the rows that are new or changed since the last run, instead of the whole table. The two main ways to find them: a watermark (remember the highest updated_at you've loaded, ask for rows above it) and CDC — change data capture (read the database's own change log).

The problem it solves: Meghalaya Cabs' rides table has 40 crore rows; 3 lakh change per day. A full copy every night reads 40 crore rows from production to find 3 lakh changes — slow, expensive and hard on the database. Incremental reads ~0.1% of that.

The mental model: a bank statement. A full load is re-printing your whole passbook every day. A watermark is "show me entries after the last one I ticked". CDC is getting an SMS for every transaction — including the reversals that a "new entries" filter would never show.

Words you must know 5

Watermark (high-water mark)
the boundary value you've loaded up to
Upsert / MERGE
insert new keys, update existing ones, in one statement
Hard delete
the row is physically removed; nothing is left to query
Lookback / overlap
deliberately re-reading a little before the watermark
Log-based CDC
reading the WAL/binlog for every insert, update, delete

Explained

SELECT * FROM rides WHERE updated_at > :last_run looks like a complete incremental pipeline.

It silently misses rows from long transactions, every hard delete, any update that forgot to touch updated_at, and — if you're unlucky — rows sharing a timestamp at the boundary. This lesson is about those four holes.

The library returns shelf

A librarian updates the catalogue each evening by checking the returns shelf: "everything stamped after 5 pm yesterday". Fast — he doesn't walk every aisle.

But a book borrowed at 4:55 pm and stamped late because the counter was busy gets a 4:55 stamp and is never seen. A book that was thrown away never reaches the returns shelf at all. And a book someone re-shelved without stamping is invisible.

So he does two things: checks a little before 5 pm each time (the overlap), and once a month walks every aisle (the full reconciliation). Or he installs a gate counter that logs every book in or out — that's CDC.

The patterns, precisely

Full load — truncate and reload. Simple and catches everything, including deletes. Right for small tables (< a few million rows) and dimension tables.

Watermark (query-based CDC) — WHERE updated_at > :low AND updated_at <= :high, then MERGE on the key. Needs a reliable updated_at maintained on every write (ideally by a DB trigger or default). Variants: monotonically increasing id (catches inserts only, never updates).

Log-based CDC — Debezium / DMS / Fivetran read Postgres WAL (logical replication) or MySQL binlog: every insert, update and delete, in commit order, with before/after images. Starts with an initial snapshot, then streams. Lowest source load, captures deletes, but more moving parts (see st-004).

Trigger-based CDC — DB triggers write changes to an audit table. Captures everything but adds write overhead to the source.

Whichever you choose, the target write must be idempotent — MERGE by key, keeping the latest version.

What each approach sees

                        full load   watermark    log-based CDC
────────────────────────────────────────────────────────────────
new rows                   ✓           ✓              ✓
updates                    ✓      ✓ if updated_at     ✓
                                   is maintained
hard deletes               ✓           ✗              ✓
intermediate states        ✗           ✗              ✓ (every change)
late-committing txns       ✓      needs overlap       ✓ (commit order)
source load             heavy        light       lightest (reads log)
moving parts             few          few          connector, Kafka,
                                                    schema history

Notice full load is the most correct — it's just too expensive at 40 crore rows.

The 0.4% that went missing

April. Meghalaya Cabs' finance team finds fact_rides has 0.4% fewer completed rides than the ops database for the same days.

The watermark load ran every 15 minutes with updated_at > :last_watermark, and set the watermark to max(updated_at) of the rows it fetched.

Root cause: the fare-adjustment job runs a long transaction that sets updated_at = now() at its start (09:58) but commits at 10:04. The 10:00 load ran at 10:00, didn't see uncommitted rows, and moved the watermark to 10:00:12. When the rows became visible at 10:04, their updated_at was 09:58 — below the watermark. Never loaded.

Fix: an overlap window longer than the longest transaction, a safety lag on the upper bound, idempotent MERGE — and a nightly count reconciliation that would have caught it in a day instead of a month.

At scale

Bounded windows beat stored state for re-runs. If the window is derived from the Airflow data interval (updated_at in [data_interval_start - lookback, data_interval_end)), re-running a past interval reproduces it exactly. A stored watermark only moves forward; to re-run last Tuesday you must hand-edit state.

Deletes at scale. Soft deletes (is_deleted, deleted_at) make deletes visible to watermarks. Otherwise: CDC, or a weekly anti-join of all source keys vs target keys (cheap if you only pull the key column).

Applying CDC to the lake. Change events are MERGEd into a Delta/Iceberg/Hudi table, ordered by log position (LSN / binlog offset), not by wall-clock time. Batch them (every few minutes) — a MERGE per event creates thousands of tiny files.

Index the watermark column. updated_at without an index makes every incremental query a full scan — you've rebuilt the full load with extra steps.

Gotchas

Bound both ends, overlap the low end, lag the high end, MERGE the result, advance last.

Watermarks can't see deletes. When deletes or every intermediate state matter — CDC.

Words to know

Incremental loadLoading only rows that are new or changed since the previous load.
WatermarkThe stored boundary value (e.g. max updated_at) up to which data has been loaded.
Lookback windowRe-reading a period before the watermark to catch late-committed rows.
MERGE / upsertA single statement that inserts new keys and updates existing ones.
Hard deletePhysically removing a row, leaving nothing for a query-based load to detect.
Soft deleteMarking a row deleted with a flag or timestamp instead of removing it.
Log-based CDCCapturing changes by reading the database's write-ahead log or binlog.

In two minutes

Test yourself

1. Which change can a watermark on updated_at never detect?

  1. An insert
  2. An update that sets updated_at
  3. A hard DELETE of a row
  4. A row updated twice
Show the answer

A hard DELETE of a row — A deleted row no longer exists to be selected. You need soft deletes, CDC or a key reconciliation.

2. Why freeze the upper bound of the window at the start of the run, slightly behind now()?

  1. So in-flight transactions can commit and the window is fixed and reproducible
  2. To make the query faster
  3. Because databases reject now() in WHERE
  4. To load future rows
Show the answer

So in-flight transactions can commit and the window is fixed and reproducible — An open-ended > low window changes while you read it, and transactions about to commit with earlier timestamps would fall behind your next watermark.

3. When should the watermark be saved?

  1. Before the query runs
  2. Right after the query returns
  3. Whenever the DAG starts
  4. After the target write has committed successfully
Show the answer

After the target write has committed successfully — Saving it first means a failed write loses that window forever. Saving it last means a crash just re-reads the window — safe with MERGE.

4. A pipeline uses WHERE ride_id > :last_max_id on an auto-increment key. Ride fares are corrected after completion. What happens to corrections?

  1. They're loaded on the next run
  2. They're never loaded — id watermarks only see new rows
  3. They're loaded twice
  4. They cause a primary key error
Show the answer

They're never loaded — id watermarks only see new rows — An update doesn't change the id, so the row stays below the watermark.

5. Your window overlaps the previous one by 10 minutes. What makes the overlap harmless?

  1. Writing with MERGE on the key (after deduping to the latest version)
  2. Using INSERT only
  3. Sorting by updated_at
  4. Running the job less often
Show the answer

Writing with MERGE on the key (after deduping to the latest version) — Re-loaded rows overwrite themselves with identical values. With plain INSERT the overlap creates duplicates.

6. Meghalaya Cabs needs every status transition of a ride (requested → accepted → arrived → completed) for analytics, plus deletes. Which approach?

  1. Watermark on updated_at every hour
  2. Daily full load
  3. Watermark on ride_id
  4. Log-based CDC from the WAL
Show the answer

Log-based CDC from the WAL — Watermark loads see only the state at query time, so intermediate states between runs are lost. CDC emits every change in commit order, including deletes.

There are 9 questions, 3 puzzles and 5 recall cards for this lesson in the app — the recall cards come back on later days, just before you would forget.

Learn it so it stays. Read it, test yourself, then recall it on two different days. The app schedules that for you, free.

Start this lesson →