Retry and Checkpoint Strategy for Microsoft Fabric Incremental Loads

Incremental loads fail. A source times out, a transient network error hits, a capacity throttles mid-run. The pipeline stops halfway, and now a human or an automatic retry re-runs it. What happens next depends on a design decision most pipelines make implicitly and get wrong: how they track what has already been loaded.

There are two failure modes. If the pipeline advanced its "last loaded" marker before the data was actually committed, the retry starts after the data that never landed — and those rows are skipped forever, a silent gap. If instead it re-reads the same window but appends rather than merges, the retry loads the first half a second time — a duplicate that inflates every downstream total.

Both are invisible at the time. The pipeline eventually succeeds, the run goes green, and the gap or the duplicate is discovered weeks later when a number does not reconcile. Getting retry and checkpointing right is what makes a failed run a non-event instead of a data-quality incident.

The foundation is an explicit watermark — a small control table that records, per source and table, the high-water mark of what has been successfully loaded: a timestamp, a change-tracking version, or a sequence number. The incremental load reads "everything since the watermark", processes it, and updates the watermark only when done.

The control table is deliberately separate from the data and deliberately explicit. Inferring progress from "the max date already in the target" is fragile — it breaks the moment late or out-of-order data arrives — whereas a watermark you own and update under your own control is auditable and recoverable. You can see exactly where every table stands and reset it deliberately if you need to reload a window.

A good control table also records the status and timing of each run, so a failure is visible as a run that started and did not complete, with the watermark still at its pre-run value. That record is what lets a retry know precisely where to resume.

The single most important property is idempotency: running the load twice on the same window must leave the target identical to running it once. Achieve it with a Delta Lake MERGE keyed on the business key — insert new rows, update changed ones, and leave a re-loaded row reconciled rather than duplicated. With a MERGE, reprocessing the same window after a failure is harmless by construction.

This is what lets you be generous with retries. If re-running a window cannot create duplicates, then on any failure the safe action is simply to reprocess from the watermark — no manual cleanup, no "did the first half commit?" investigation. The MERGE absorbs the overlap. Appending instead of merging removes that safety entirely and makes every retry a risk.

Idempotency also protects against the subtler case where a source re-sends data or a run is triggered twice. A MERGE-based load treats a duplicate delivery as a no-op update; an append-based load treats it as new rows. On an ERP or distribution feed where re-sends happen, that distinction is the difference between a stable total and a drifting one.

The ordering is the rule that ties it together: commit the data first, advance the watermark second, and never the other way round. If the batch commits to the Delta table and the pipeline fails before updating the watermark, the retry reprocesses the same window — harmless, because the load is idempotent. If the watermark advances first and the commit then fails, the retry skips the uncommitted window — a permanent gap.

So the watermark update is the last step, and ideally it happens only on confirmed success of the data commit. Treating the data write and the watermark update as a sequence where the watermark strictly follows the commit means the worst case is reprocessing, which idempotency makes safe — rather than skipping, which nothing makes safe.

This is a small discipline with a large consequence. Most "silent gap" incidents trace back to a watermark that moved before the data landed. Reverse the order and the gap becomes impossible; the retry simply does the window again.

With idempotency and correct watermark ordering in place, automatic retries become safe and worthwhile. Configure bounded retries with exponential backoff for the transient failures — timeouts, throttling, brief network drops — that resolve themselves on a second attempt a moment later. Fabric Data Factory activities support retry counts and intervals natively; the point is that a retry cannot corrupt data, so letting the pipeline self-heal on transient faults is pure upside.

Bound the retries, though. A pipeline that retries forever hides a real failure — a changed source schema, revoked credentials, a source genuinely down. After a small number of attempts, stop and alert, so a persistent failure surfaces to a human the same day rather than looping silently. The alert should carry which table, which watermark, and the error, so the on-call engineer resumes from a known point.

The combination — self-healing on transient faults, loud failure on persistent ones — is what makes an incremental estate low-maintenance. Transient blips never reach a human; real problems always do.

The whole strategy reduces to one testable property: re-running any load must leave the lakehouse in the same state as a single clean run. Get there with an explicit watermark table, an idempotent MERGE on the business key, the watermark advanced only after the data commits, bounded retries with backoff for transient faults, and alerting for persistent ones. Then a mid-run failure is a non-event — the retry reprocesses the window and reconciles.

The way to verify it is to actually do it: kill a load halfway, re-run it, and reconcile the result against a clean run. If they match, the strategy holds. If re-running produces duplicates or a gap, one of the four pieces is missing — and it is far better to find that in a test than in a month-end total that will not tie out.