Incremental Loads Have a Complexity Budget
Last updated on
4 min read

Incremental Loads Have a Complexity Budget


We reach for incremental loading almost automatically. Why process a million rows when only a few thousand changed?

It’s a reasonable instinct. It can also become a trap. The first implementation is cheap; the tenth edge case is not. A watermark ties at midnight, a late correction falls outside the window, a dimension changes without a fact changing, and a partial publish means the saved checkpoint is now ahead of the data.

By the time all of that is handled, the incremental pipeline can cost more to understand than a full rebuild would cost to run.

I’m not arguing that incremental loading is bad. I’m arguing that it has a complexity budget, and the savings should pay for it.

Compare the work you avoid with the machinery you add

Take a fictional reference dataset of 300,000 rows that is updated once a day. A full refresh reads, validates, and replaces 300,000 rows. A timestamp-based incremental refresh reads fewer changed rows, but adds a persistent checkpoint, change detection, merge keys, delete semantics, and a recovery procedure.

Those extra parts may be completely justified if the full refresh is slow, expensive, or disruptive. But if the complete refresh already finishes comfortably inside the operational window, the incremental path may be optimizing the least important part of the system.

A decision diagram compares full rebuild and incremental processing across runtime, state, failure handling, and source guarantees.

The comparison I want is not simply bytes scanned. It is:

CostFull rebuildIncremental
Routine computeMore repeated workLess work when change rate is low
StateMinimalCheckpoints, keys, often history
Late changesIncluded if the source snapshot is completeMust be detected or covered by a replay window
RecoveryRebuild a consistent targetReconcile partial attempts and checkpoint position
Source requirementsComplete consistent extractReliable change semantics or compensating logic

The table is not a benchmark. It is a map of costs that need to be measured or accepted for the actual workload.

The first hidden bill is proving what changed

If a source guarantees immutable append-only records with a monotonic cursor, incremental processing is pleasantly dull. The cursor is trustworthy and the target is append-only. That is an excellent fit.

Now remove those guarantees. Records can be updated without advancing modified_at; deletions are not surfaced; an old mapping change affects historical interpretations. We can still build incremental logic, but we have to manufacture evidence of change through snapshots, hashes, CDC, or bounded reprocessing.

That work has a cost. More importantly, each mechanism comes with a failure boundary. A 30-day replay window cannot repair a correction effective 18 months ago. A row hash cannot discover a deleted record if the source never exposes the current key set.

For the taxonomy of methods and their source assumptions, see Incremental Load Is Not One Thing. Here I care about the economic decision: whether the added state and reconciliation effort earns its place.

The second bill is recovery

Imagine the extractor finishes, the transform succeeds, and the destination write fails after a partial commit. If the watermark was advanced before the destination became consistent, the next run may skip data permanently. If it wasn’t advanced, the next run must be safe to replay without duplication.

In Microsoft Fabric, the choice can also cross Lakehouse and Warehouse writes, which do not automatically become one transaction. That requires a carefully chosen commit boundary. For a robust incremental pipeline, the checkpoint should represent successfully published source progress, not merely successfully fetched source progress. When publication is split across systems, the definition of success gets harder.

I find it useful to separate two optimizations that are often mixed together:

  1. Incremental extraction: read only what changed in the source.
  2. Incremental publication: update only what changed in the analytical output.

You might need one without the other. A small full source extract can feed a targeted merge. An efficient incremental source feed can also trigger a complete rebuild of a tiny downstream table. There is no rule that both sides must be incremental.

My decision boundary

I would start with a measured full rebuild when the source is reasonably small, the refresh window is forgiving, and the destination can be replaced safely. A successful full-reload baseline is also a valuable reconciliation oracle if we later optimize it.

I’d move to incremental processing when one of three pressures becomes material: source access is costly or restricted, the full rebuild exceeds the refresh window, or the target’s size makes replacing it disruptive. Then I’d choose the weakest change-detection method supported by actual source guarantees.

For mutable analytical facts, a hybrid is often more forgiving: incremental ingestion plus bounded recomputation of affected outputs. It deliberately spends some extra compute to reduce the number of edge cases that require bespoke repair.

The number I’d like every design review to show is not just “rows avoided.” It is how much runtime and capacity we save after paying for retries, replay, checks, and future changes. Sometimes the optimization wins by a mile. Sometimes it costs us six months of intricate logic to save a few minutes a day.

Next in “Microsoft Fabric migration and data correctness” The Rows That Never Reached the Reject Table →