Skip to main content
CodeSparring

High-Water-Mark Incremental Extraction and Safe Backfill

Level 5: Level 5: Advanced & Company-Specific SQL for DE Interviewshard40 mindbt-orchestrationhigh-water-markincremental loadwatermark state tablepartition-overwrite backfilllate-arriving lookbackidempotency

Load only rows newer than a stored watermark, catch late arrivals with a partition-overwrite backfill, and stay idempotent per partition.

Full refresh, watermark, or CDC: pick the loader

Reloading a whole table every run is safe but slow and expensive. Three loaders trade freshness against cost:

  • Full refresh. Rebuild the target from all of source every run. Simple and always correct, but it does not scale.
  • High-water-mark incremental. Keep the last-processed value (a watermark) in a small state table, and each run pull only source rows newer than it. Cheap, and the subject of this lesson.
  • CDC. Consume a change stream of inserts, updates, and deletes (the previous lesson).

The incremental loop

Store one watermark in a state table. Each run:

  1. Read the watermark: SELECT watermark FROM state.
  2. Pull the new slice: WHERE updated_at > watermark. Strict > keeps a row sitting exactly on the boundary from reloading every run. That is an efficiency choice, not a correctness one: because step 3 upserts on the business key, either > or >= stays idempotent, exactly as Level 4 noted.
  3. Upsert the slice into the target, keyed on the business key.
  4. Advance the watermark to the newest value you just loaded.

Late arrivals and the backfill

Strict > has a cost: a row that arrives late with an updated_at below the current watermark is never picked up. The fix is a rolling lookback, reprocessing the last few partitions each run so late rows are caught. You met this rolling-lookback idea in Level 4's idempotent-merge lesson, where re-scanning the last few days stayed safe only because the upsert underneath was idempotent; the same pairing holds here. The mechanism is partition-overwrite: DELETE a bounded load_date range, then INSERT ... SELECT the same range from source. Because you delete before you insert, re-running does not duplicate, which is the whole point.

When you need to correct history rather than catch late rows, the two production answers share that shape:

  • Partition-overwrite: delete and reinsert a bounded date range.
  • Staging plus atomic swap: build a corrected copy in a staging table, validate it, then swap it in inside a transaction, which commits the swap all at once or not at all, so readers never see a half-built table.

The invariant across all of it is idempotency per partition: running the same load twice leaves the same rows. That is what makes a retried or backfilled run safe.

Interview nuance: the number one incremental bug is a non-idempotent append (an INSERT with no delete or upsert), which doubles rows on any retry. If your loader cannot survive being run twice, it is wrong. Lead with "delete-then-insert the partition, or upsert on the key".

In the warehouse this differs. This maps to dbt is_incremental() models, BigQuery and Hive INSERT OVERWRITE PARTITION, Delta replaceWhere, and Snowflake CREATE OR REPLACE TABLE ... SWAP WITH plus Streams offsets. Airflow passes the data interval as the partition to overwrite, and catchup=True triggers the backfill runs.

Sample data for this example
CREATE TABLE source (id INTEGER, updated_at TEXT, amount INTEGER);
INSERT INTO source VALUES
  (2, '2026-03-02 10:00', 200), (3, '2026-03-03 10:00', 300), (4, '2026-03-02 09:00', 250);
CREATE TABLE state (id INTEGER PRIMARY KEY, watermark TEXT);
INSERT INTO state VALUES (1, '2026-03-02 10:00');
Worked example (SQL)
-- Strict > picks up only id 3. Id 4 arrived late (below the watermark) and needs the backfill.
SELECT id, updated_at, amount,
  CASE WHEN updated_at > (SELECT watermark FROM state WHERE id = 1)
       THEN 'loaded (newer than watermark)'
       ELSE 'skipped (needs backfill lookback)' END AS incremental_decision
FROM source
ORDER BY updated_at;

Apply

Your turn

The task this lesson builds to.

Write a script that incrementally loads only source rows newer than the stored watermark, upserts them, advances the watermark, and then backfills the recent partition so a late-arriving row is not missed, over source(id, load_date, updated_at, amount), target(id, load_date, updated_at, amount) keyed on id, and a one-row state(id, watermark).

Load rows whose updated_at is strictly greater than the watermark, upsert on id, set the watermark to the newest updated_at now in target, then partition-overwrite the '2026-03-02' load_date (delete that partition from target, reinsert it from source) to catch a late row that landed below the watermark.

3 hints and 3 automated checks are waiting in the workspace.

Practice

Make it stick

A second problem on the same idea, so it survives past today.

Write a script that loads a corrected partition into target with partition-overwrite so that re-running it leaves the target unchanged, over source(id, load_date, amount) and a target(id, load_date, amount) pre-seeded with stale values for load_date '2026-03-05'.

Delete the '2026-03-05' partition from target, then reinsert it from source for that same date. A naive append would violate the key or double rows on a retry; delete-then-insert stays idempotent.

3 hints and 2 automated checks are waiting in the workspace.