Incremental Load Is Not One Thing

“We’ll load it incrementally” is one of those phrases that ends a meeting and starts a bug.

It sounds like a decision. It isn’t. Incremental load is not a technique — it’s a family of techniques, and they fail in completely different ways. Saying you’ll “do incremental” is like saying you’ll “handle concurrency.” Which part? With what guarantee? The word hides the actual choice, and the actual choice is where correctness lives.

Here is the version I wish someone had handed me earlier: the patterns, what each one assumes about your source, and the one rule that decides between them.

The rule

Pick the weakest method that still guarantees correctness for how your source actually behaves.

Weakest, not fanciest. Every step up in sophistication buys you a capability and hands you a new failure surface. Change-data-capture is more powerful than a timestamp watermark, and it’s also one more system that can silently fall behind. The goal is not to load data cleverly. The goal is to load it correctly and never think about it again. Cleverness you have to babysit is a downgrade.

So the job is to find the floor — the simplest pattern whose assumptions your source actually satisfies — and stop there.

The patterns, weakest to strongest

1. Full reload. Throw the target away, load everything. Not incremental at all, and worth naming first because it’s often the correct answer for small reference data and you talked yourself out of it for no reason. No watermark to corrupt, no state to drift. Boring, and that’s exactly why it’s often the right call.

2. Append-only. New rows arrive, old rows never change. Insert the new ones. This is the happy path — event logs, immutable transactions — and if your source really is append-only, do not reach for anything heavier.

3. Timestamp watermark. Keep the max modified_at you’ve seen; next run, pull everything newer. The workhorse of enterprise ETL, and the one with the sharpest hidden edge (below).

4. High-water-mark ID. Same idea, but on a monotonic integer key instead of a timestamp. Good when the source hands out ascending IDs and you trust them more than its clock.

5. MERGE / upsert. Rows change in place, so match on a business key and update-or-insert. You’ve stopped assuming the past is frozen. Now you need a reliable key, and you need to mean it.

6. Rolling-window overwrite. Don’t trust the source to tell you what changed — just re-pull the last N days every run and overwrite that window. The “I don’t trust anyone, including myself” pattern. Wasteful, and wonderfully robust against late-arriving corrections.

7. Snapshot diff (key + row hash). No usable change-marker at all? Hash each row, compare against last load’s hashes, act on the difference. Expensive, but it manufactures change-detection out of nothing.

8. Change-data-capture. Read the source’s own transaction log. The most complete and the most machinery — a separate moving part with its own lag, its own outages, its own on-call. Powerful when you genuinely need every change including deletes; overkill when you don’t.

Read that list as a ladder. Start at the bottom rung your source can stand on. Most teams reach three rungs too high because the fancy option sounds like the professional one.

The watermark bug that will find you

Pattern 3 is the most common, so its failure mode deserves its own section, because it is quiet and it is real.

A timestamp watermark says: give me every row where modified_at > @last_seen. Looks airtight. It isn’t, for two reasons.

The tie bug: a run stops mid-cluster, the saved watermark equals the shared timestamp, and the rows it never reached are excluded forever by >.

Ties. If a batch of rows shares the exact same modified_at — same millisecond, because they were written by one transaction — and your run happens to stop partway through that group, the next run asks for > @last_seen and skips the rest. You just dropped rows and nothing errored. The fix is to make the watermark a pair, (timestamp, id), and page on both:

WHERE modified_at > @last_ts
   OR (modified_at = @last_ts AND id > @last_id)
ORDER BY modified_at, id      -- page in the same order you compare

Moving targets. If you read up to “now” while the source is still writing “now,” you’ll grab a half-finished slice and re-grab the rest next time — or miss it. Freeze a cutoff at the start of the run and never chase past it:

WHERE modified_at > @last_ts AND modified_at <= @run_cutoff

Both bugs come from the same wrong belief: that a watermark is a feature. It isn’t. A watermark is a bookmark. If the bookmark lies, you either miss data or duplicate it — and a timestamp watermark quietly assumes your source can tell time, and tell it honestly. Plenty of sources can’t. Clocks skew, backfills stamp yesterday’s date, a nightly job rewrites modified_at on rows it didn’t actually change. The watermark is only as trustworthy as the column under it.

When there’s no key and no clock

The genuinely hard case: files land on a schedule, no business key, no reliable change column. Before you reach for fuzzy matching (don’t), answer one question — are these new events, or periodic snapshots?

• New events → append-only, plus ingestion metadata so you can dedupe if a file gets delivered twice (a file registry keyed on name + hash makes re-delivery idempotent).
• Periodic snapshots → rolling-window overwrite. Treat each file as the truth for its time bucket and replace that bucket wholesale. Don’t try to diff it row by row; just re-own the window.

If you truly need row identity and the source won’t give you one, synthesize a key from a content hash of the stable columns — and then go ask whoever owns the source to add a real key upstream, because you’re now maintaining a workaround forever.

The columns that make it survivable

Whatever pattern you land on, a few columns turn “it loaded” into “I can explain what loaded”:

• ingest_ts, source_system, batch_id on every row — so any row can be traced back to the run that produced it.
• row_hash — cheap change-detection and cheap corruption-detection, for free.
• For history: SCD2’s effective_from / effective_to / is_current.

And a small control table per feed — call it what you like — holding the current watermark, the last run_cutoff, the last batch_id, and a late_arrival_window. It’s plumbing, pure and simple. It is also the difference between “the pipeline is behaving” and staring at row counts trying to reconstruct what it did last night. The layers nobody demos are the ones that decide whether the system can be trusted.

Full reloads without amnesia

One more, because it bites people: sometimes you need to wipe and reload a table that participates in incremental logic. Prefer DELETE over drop-and-recreate — you keep the schema, the grants, and every downstream view or shortcut that points at it, instead of rebuilding them from a DDL script that drifted months ago.

But watch the control table. If your reload also clears the watermark and file registry, the pipeline now believes nothing has ever loaded — which is exactly what you want for a true from-scratch reload, and a disaster if the source files it needs to re-read are already gone. Clear the memory only when you’re certain the history it forgets can be rebuilt from what’s still on disk.

Nothing here is exotic; that’s what makes it dependable. “Incremental load” collapses eight honest, unremarkable decisions into one hand-wave, and the bugs live in the collapse. Name the pattern, check that your source actually satisfies its assumptions, pick the weakest rung that holds — and then the phrase can go back to ending meetings instead of starting bugs.

Companion code: ramwise-examples/incremental-load-patterns — the tie bug reproduced on SQLite, and the composite-watermark fix.