Idempotent Loads: Upsert & MERGE

Idempotent loads: run it twice, get the same table

Pipelines fail and get re-run. A backfill reprocesses last week. The same daily file lands twice. If your loader is a blind INSERT, every re-run duplicates rows, the cardinal data-engineering sin. A loader is idempotent when running it N times leaves the target in the same state as running it once. The test is blunt, and it's exactly what the Level 4 grader does: run the script twice, assert the row count is identical.

The tool is an upsert: insert the row if its key is new, otherwise update the existing row. SQLite (and Postgres) spell it INSERT … ON CONFLICT:

INSERT INTO dim_customer (email, name, city)
SELECT email, name, city FROM stg_customer
WHERE true
ON CONFLICT(email) DO UPDATE SET
  name = excluded.name,
  city = excluded.city;

• ON CONFLICT(email) names the unique key that defines "same row." (email must have a UNIQUE / PRIMARY KEY constraint.)
• DO UPDATE SET … = excluded.<col> overwrites with the incoming values; excluded is the row that would have been inserted.
• Run it twice: the first run inserts, the second run hits the conflict and updates to the same values: zero new rows. Idempotent.

Use DO NOTHING instead of DO UPDATE when you only want inserts-if-absent and never want to touch existing rows.

Incremental loads with a high-water mark

For large sources you don't reprocess everything; you load only rows newer than the last successful load, tracked as a high-water mark:

INSERT INTO fact_events (event_id, payload, event_ts)
SELECT event_id, payload, event_ts
FROM stg_events
WHERE event_ts > (SELECT COALESCE(MAX(event_ts), '1970-01-01') FROM fact_events)
ON CONFLICT(event_id) DO NOTHING;

The WHERE event_ts > MAX(...) skips already-loaded rows; the ON CONFLICT DO NOTHING is the safety net for overlap at the boundary. Together they're both efficient and idempotent. (Whether you use > or >= at the exact boundary barely matters when ON CONFLICT absorbs any re-seen key, but a strict cutoff of either kind has a subtler failure mode, covered next.)

Late-arriving data: why a strict high-water mark silently drops rows

A high-water mark of MAX(event_ts) quietly assumes events arrive in event-time order: that once you've recorded 2026-03-02, nothing older will ever appear again. Real sources violate that constantly: a mobile client was offline, an upstream queue retried, a partner's nightly file ran a day late. The event happened at event_ts = 2026-02-28, but it only lands in your source after the load that already advanced the high-water to 2026-03-02. A strict WHERE event_ts > high_water (or even >=) filters that row out on every future run: it is silently dropped, and no error is ever raised.

The fix is a lookback (a "safety lag"): reprocess a trailing window instead of a hard cutoff.

INSERT INTO fact_events (event_id, payload, event_ts)
SELECT event_id, payload, event_ts
FROM stg_events
WHERE event_ts >= date(
        (SELECT COALESCE(MAX(event_ts), '1970-01-01') FROM fact_events),
        '-3 days'                      -- reprocess the last 3 days on every run
      )
ON CONFLICT(event_id) DO UPDATE SET payload = excluded.payload;

You deliberately re-scan the last N days each run. Rows you've already loaded hit ON CONFLICT and update in place (harmless, precisely because the load is idempotent) while a genuinely late row inside the window finally gets inserted. The lookback trades a little repeated work for the guarantee that nothing older-than-cutoff is lost. Size N to your worst realistic lateness (an hour, a day, a week): too small and stragglers still slip through, too large and every run reprocesses more than it needs.

In the warehouse: production incremental models never trust a bare MAX. dbt's incremental strategy exposes a lookback config for exactly this, and teams write an explicit WHERE event_ts >= (SELECT MAX(event_ts) FROM {{ this }}) - INTERVAL 'N days'. Snowflake/BigQuery streaming and Spark structured streaming call the same idea a watermark with allowed lateness. The lookback only works because the merge underneath is idempotent; the two techniques are a pair.

Common pitfalls

• No unique constraint = no conflict = duplicates. ON CONFLICT(email) only fires if email is actually declared UNIQUE / PRIMARY KEY. Without the constraint, the insert just appends. Idempotency is enforced by the schema, not the query.
• INSERT … SELECT … ON CONFLICT parse quirk: SQLite needs a WHERE (use WHERE true if you have no real filter) before ON CONFLICT when the source is a SELECT, to disambiguate the grammar. A bare INSERT … SELECT … ON CONFLICT … can fail to parse.
• Upserting the wrong key. Conflict on a non-business column (e.g. a surrogate key) never matches the natural duplicate, so re-runs still duplicate. Conflict on the natural / business key.

In the warehouse: Snowflake / BigQuery / SQL Server use MERGE INTO target USING source ON <key> WHEN MATCHED THEN UPDATE WHEN NOT MATCHED THEN INSERT. SQLite / Postgres use INSERT … ON CONFLICT. Same idea, different keyword, and interviewers expect you to name both.

Recap: an idempotent loader survives re-runs without duplicating rows; achieve it with INSERT … ON CONFLICT(<business key>) DO UPDATE / DO NOTHING backed by a real UNIQUE constraint, optionally scoped by a high-water-mark WHERE with a lookback window so late-arriving rows aren't silently dropped, and prove it with the "run twice, same count" test (warehouses spell the same operation MERGE).

Execution mode: you write a multi-statement script. It runs against a fresh seeded SQLite DB, then hidden assertion queries check the row count and the merged values, and the grader re-runs your whole script to confirm the row count doesn't move.