Full reload beats the delta you can't trust

Incremental sync is the default everyone reaches for. Pull only what changed since last time: less data, faster runs, lower cost. The instinct is right often enough that it's become reflexive - reflexive enough that when this pipeline went through a design review, the review opened by flagging its full reload as the number-one flaw to fix. It wasn't. Incremental sync rests on a premise that a lot of source systems quietly fail to meet - that you can actually find out what changed.

The premise most APIs don't honor

The standard incremental pattern is the watermark. Keep the last-modified timestamp or highest key you've seen; each run pulls everything past it and advances the mark. It's the shape Azure Data Factory documents as its delta-load solution, it's dbt's incremental materialization, it's every "modified since" parameter on every REST API. And its blind spot is right there in the definition: a watermark finds rows that were created or updated. A row that was deleted at the source doesn't get a fresh timestamp. It doesn't get anything. It's simply absent from a feed you only ever read additions to.

A trustworthy change feed has to report three things: what was created, what was modified, and what was deleted. The first two are common. The third is rare, because it requires the source to do real work - keep tombstone records, expose a deletions endpoint, or publish its transaction log as change data capture. The sync-tool vendors are candid about this. Fivetran captures deletes where it can actually observe them - log-based database replication - and surfaces them as a soft-delete marker column rather than trusting a source's query surface. Airbyte's docs say it plainly: incremental modes cannot detect deletions, and when a source won't report them, the recommendation is a periodic refresh - a full re-pull - to make the destination mirror reality again.

Sync incrementally against a feed with no deletion signal and your copy drifts. Deletions never propagate. Your database slowly fills with rows the source no longer believes in. And the drift is invisible - every individual run looks successful, because nothing errored. You don't discover the problem until someone asks why a number is too high and the answer is "we've been counting ghosts for three months."

The pipeline that made it concrete

The system in question syncs a vendor market-intelligence API into Azure SQL, feeding a Power BI report that refreshes on a known schedule. The API's constraints are the whole story: paged at 100 records per request, hard-limited to three requests per minute per token (HTTP 429 past that), no trustworthy "changed since" filter, and no deletion signal of any kind. The sync runs as a chain of Azure Functions - a starter truncates the target tables and loads the first chunk, then each link processes up to 15 pages, paces itself twenty-odd seconds between calls to respect the rate limit, stops at a nine-minute buffer to fit inside the ten-minute function timeout, and triggers the next link over HTTP. Twice a month, at 3 a.m., it reloads everything.

The design review flagged that truncate-and-reload as Gap #1. My pushback was one sentence: the API provides no valid delta - no reliable "changed since," no deletion signal, no update signal. That sentence ended the argument. Against a source like that, an incremental upsert would accumulate stale and deleted rows forever, invisibly, run after green run. The full reload wasn't the naive choice; it was the correct one - for the same reason dbt's guidance calls incremental models "the easiest place to accidentally break idempotence." A stateless rebuild has no cursor to corrupt and no drift to accumulate.

One real cost remains, and it's better named than hidden: during the reload window the live table is empty or partial. The classical mitigation is to load into staging tables and swap so consumers only ever see a complete generation; we scoped that as a separate enhancement and, in the meantime, kept the downstream semantic-model refresh scheduled away from the sync window. (What happens when a long-running verification pass overlaps the next reload anyway is its own post - the generation token.)

If you can't trust the source to tell you what was deleted, you can't trust a delta. Reload the whole thing, and spend your cleverness on proving the reload was complete.

The effort doesn't vanish - it moves

Choosing full reload doesn't make the engineering easier; it relocates it. With a delta, the hard part is computing the right diff. With a reload, the hard part is proving the load actually landed everything - because a half-finished reload doesn't leave you with stale data, it leaves you with missing data, which is worse. A reload interrupted at page 40 of 100 isn't visibly broken. It's a table that queries fine, joins fine, and reports 40% of reality to every dashboard downstream.

So completeness verification becomes the real design problem. And it has a trap that cost real time to see clearly.

Gaps in a key are sparsity, not loss

The intuitive way to check for missing rows is to look at the ID column: take the minimum and the maximum, and flag every integer in between that isn't present.

-- The confidently wrong completeness check
SELECT s.value AS missing_id
FROM GENERATE_SERIES(
       (SELECT MIN(record_id) FROM dbo.Target),
       (SELECT MAX(record_id) FROM dbo.Target)) AS s
WHERE NOT EXISTS (SELECT 1 FROM dbo.Target t WHERE t.record_id = s.value);

It is wrong, and confidently wrong. We ran exactly this analysis against a local load: 1,000 rows, IDs running from 4 to 1402 - and 399 "missing" integers, with holes starting as low as 6, 8, and 10. Every one of them was a number the source had simply never issued. The low-range holes were the diagnostic clincher: this loader walks IDs in ascending order, so an interrupted run is missing its tail - it cannot produce holes at 6 and 8 in a table whose load reached 1402. That's not damage; that's the source's own key sparsity. Acting on the check would have launched a back-fill chasing 399 records that never existed - at three requests a minute, more than two hours of API budget per run, spent politely asking for ghosts.

Key sparsity isn't an API quirk, either - it's the normal condition of natural keys. SQL Server's own documentation states that IDENTITY doesn't guarantee consecutive values: rolled-back inserts and server restarts consume numbers that are never reissued. If your own database won't promise contiguous keys, an API you don't control certainly isn't promising them.

Absent integers in a key range are not evidence of loss. They're evidence that keys aren't contiguous, which they almost never are. The only sound completeness check is against ground truth from the source itself: the count the API reports, or the actual set of IDs it returns - never a synthetic range you reconstructed from the endpoints.

What verification looks like when it's evidence-first

The design we landed on orders the checks from cheapest evidence to most expensive:

• Probe the source's own total first. One rate-limited request for the record count the API itself reports. If loaded equals expected, write a durable Complete record and stop - zero further API spend on the happy path.
• Repair the tail before anything fancy. Because the loader walks IDs ascending, an interrupted load is missing its tail. Re-fetch from the highest loaded ID upward and upsert until the source runs dry. Cheap, and provably complete for cursor-mode loads.
• Census only on real disagreement. If the counts still disagree, crawl the source's actual ID set page by page and diff it against what's loaded - the expensive path, bounded per run and resumed by self-chaining, because at three requests a minute a full census doesn't fit in one function execution. The bounds are concrete: a back-fill of ~500 records fits comfortably in a run; 3,000 would take roughly 13 minutes of API time, past the function timeout.
• Never delete on suspicion. Rows present locally but absent from a census get recorded as a mismatch, not deleted. The one thing this source cannot tell you is that a record was deleted - a verifier shouldn't fabricate the very signal the API doesn't have.

None of it is clever, and that's the point: every corrective action is anchored to something the source actually said, never to a pattern inferred from our own copy.

When to keep the delta

None of this means incremental sync is wrong. When the source genuinely has a reliable change feed - real modification timestamps and real deletion signals: tombstones, a deletions endpoint, log-based CDC - incremental is the better design, and reloading everything is waste. At scale it's the only design; nobody truncate-reloads a hundred-million-row table twice a month. This pipeline could afford correctness-by-reload precisely because the dataset is thousands of rows - about an hour of paced calls - not millions.

The decision rule is just narrower than the reflex: use a delta when the source can be trusted to report all three kinds of change, and full-reload-plus-verification when it can't. And if the table is too big to reload and the source won't report deletions, that isn't a pipeline problem to out-engineer - it's a data-contract problem to escalate, because you're being asked to mirror something that refuses to be mirrored.

The short version

Don't sync incrementally against a feed that can't tell you about deletions - you'll drift, silently, and look fine the whole time. Reload the full set instead, and move your engineering effort to proving the reload was complete. When you verify, check against the source's own count or ID set, never against a filled-in integer range: a missing number is almost always a number that was never issued, not a row you lost.