Incremental load

What is an incremental load?

An incremental load moves only the rows that are new or changed since the last successful run, instead of reading the whole source every time. A nightly job that syncs millions of invoice lines does not need to reread every year of history when only yesterday moved.

It is one of the most common patterns in any data pipeline that feeds a data warehouse. Full loads are simpler and stay the right choice for small tables. Incremental loads win when the source is large, when you refresh several times a day, or when the source system cannot take a heavy query on every run.

The idea is simple. The hard part is knowing exactly what changed, and making the run safe to repeat.

How the pipeline knows what changed

Every incremental load rests on a change-detection strategy, and each one has a blind spot worth knowing before you trust it.

High-water mark on a modified-at column. The source carries a column such as updated_at, and the pipeline stores the highest value it has processed as a watermark. The next run asks only for rows above that mark. It is cheap and needs no special source support, but it has two failure modes. It misses hard deletes, because a row that is gone from the source is simply absent from the result, not flagged. And it breaks on clock skew and late-arriving rows: if a transaction commits after the read but carries an earlier timestamp, the watermark can move past it and the row is never seen again.

Change data capture. Here the source's own log of inserts, updates and deletes drives the load. Because deletes appear in that log, change data capture catches the one thing a modified-at column cannot. It needs access to the log, so not every source offers it.

Full refresh on a trailing window. Reload a bounded recent window on every run, say the last two or three days, and accept a full reload of that slice. This is the pragmatic guard against late edits: anything that changed inside the window is re-read whatever its timestamp says. You still need another mechanism for changes older than the window.

Vendor tools package these ideas. Power BI's incremental refresh partitions a table by date and re-processes only recent partitions, with an optional detect-data-changes column that skips a partition when its maximum modified-at has not moved.

A worked example: the row that gets missed

Take a sync that pulls orders where updated_at is greater than the stored watermark. The job runs at 02:00 and reads everything committed by that moment. The newest row it sees was updated at 01:59:58, so it saves the watermark as 01:59:58.

Order 5012 was saved at 01:59:55, but its database transaction did not commit until 02:00:03, three seconds after the read snapshot closed. The 02:00 run never saw it. The 03:00 run asks for rows where updated_at > '01:59:58', and 5012 carries 01:59:55, which is below the mark. That order is now invisible to every future run until someone reloads the table by hand.

The usual fixes are the trailing window above, or lagging the watermark by a safety margin so recent rows get a second look.

Why an incremental load must be idempotent

Runs fail halfway, queues redeliver, and someone always reruns yesterday's job. If replaying the same batch changes the result, incremental loads quietly corrupt the warehouse. Idempotence is the property that makes a rerun safe: process the same rows once or five times and the table lands in the same state.

Two habits get you there. Write with an upsert keyed on a stable business key, so a replayed order updates its existing row instead of inserting a duplicate. On Delta Lake that is the MERGE INTO operation, matching on the key to update and inserting only genuinely new rows. Second, advance the watermark only after the run succeeds, never before, so a crash mid-run does not skip the rows it failed to write.

The same mechanism handles updates for free. If order 5012 is cancelled today, its updated_at changes, the next run pulls it again, and the upsert overwrites the existing row rather than adding a second one.

What to watch out for with incremental loads

• Deletes need their own plan. A modified-at strategy never sees a hard delete. Use change data capture, soft deletes, or a periodic reconciliation that compares source and target keys.

• Changed logic means a full reload. When you rewrite the transformation, rows already loaded still hold the old output. Tools like dbt expose a full-refresh run for exactly this, rebuilding the table from scratch.

• Keep a reconciliation habit. Even a healthy incremental load drifts over time. A periodic full reload or backfill, or a row-count check against the source, catches the rows that slipped through.