Quick summary
An incremental load skips records because a timestamp watermark records when a row was stamped, not when it became readable. A row that commits after the pipeline has read, but carries an earlier timestamp, falls permanently behind the watermark and is never requested again. The job still reports success.
The fix is to read from an overlap window rather than the bare watermark, write idempotently with MERGE on a business key, derive the watermark from the batch instead of the clock, and reconcile against the source on a schedule.
A fraction of a percent. That is how much data Incremental Loads can silently miss when they rely on timestamp-driven watermarks, even under every row-count alert you would set.
The symptom is always the same. The job runs green, row counts climb, and a business user finds a record in the source that never reached the data warehouse. Nothing errors. Nothing alerts. Those rows are skipped, and skipped for good.
These are the symptoms our team at ScriptsHub Technologies sees most often. Incremental Loads are the default for any table too large to refresh in full, and the pattern looks simple: remember the highest change timestamp loaded, then ask for everything greater. That single piece of state carries an assumption almost no operational database honors.
This guide explains why late-arriving data falls behind a watermark in Azure Data Factory, Synapse and PySpark, and how to close the gap. The scenario below is a composite of our engagement patterns, not a single client.
Why do incremental loads miss records without ever failing?
An incremental load skips records when a row’s change timestamp and the moment it becomes readable are not the same instant. The watermark advances past the stamp before the commit makes the row visible, and no later run asks for it again.
What a watermark actually guarantees
A watermark is the highest value of a change column, typically a last-modified timestamp, that your pipeline has loaded, and the next run filters on anything greater, as Microsoft’s Data Factory delta-loading guidance defines it. The guarantee holds only when a row’s timestamp and its read visibility coincide, which under concurrent writes they rarely do.
Late-arriving data arrives in several shapes. Commit lag is the most common: the change column is stamped when the statement runs, but under read-committed isolation the row becomes visible only at commit, so a read landing between them misses it. Clock skew puts rows from a slow application server below a watermark its faster peers already passed. Timezone drift, where the watermark is UTC and the change column local, skips or replays whole blocks. Backdated corrections write an older business timestamp; hard deletes leave none at all. Swapping in an auto-incrementing ID does not escape this: identity values are allocated before commit, so a higher ID can become visible while a lower one is open.
Why silent skips cost more than loud failures
A loud failure announces itself: the job turns red, you rerun, the data is whole again. A silent skip succeeds and advances the watermark past rows it never read.
Figure 1: A row stamped before a read but committed after it falls permanently behind the advancing watermark.
What does late-arriving data look like in a production pipeline?
In the pattern we see most often, order rows written inside long transactions are stamped seconds before they commit. An hourly incremental load reads mid-transaction, misses them, then advances the watermark past their timestamps.
The composite pipeline our team works from pulls order records from an operational SQL Server database into ADLS Gen2 through Azure Data Factory, then PySpark, then Synapse. A watermark control table drives it: read the high-water mark, select everything stamped later, write the new maximum back. It runs hourly. It has never failed.
An operations analyst then reports orders visible in the source but never in the report: dozens across a month against hundreds of thousands of rows. Pipeline status green, no alert fired.
The root cause sits inside the commit window. Orders were written inside longer transactions, so the change column was stamped on write but the row was visible only on commit. The hourly copy read mid-transaction, missed those rows, then advanced the watermark past stamps still inside the open transaction. Every later run asked for data beyond that point. A smaller share traced to application servers with clocks differing by seconds.
Figure 2: The gap shapes that put rows behind a watermark, and which of them a lookback window can recover.
How do you diagnose records missing from an incremental load?
Run a key-level anti-join between source and target across a bounded window. A watermark gap produces missing rows that are few, scattered, clustered just below a recorded watermark, and never recovered by a rerun.
Pull business keys from the source for a bounded window and see what exists on only one side. The misses correlate with heavy write concurrency, their timestamps clustering just below the watermark for that run. That points at a read-visibility gap, not a transformation bug, and the same anti-join underpins reconciling records across systems.
Wondering whether this is happening to you? That anti-join is the fastest answer, and Pattern 5 has a version to run today. Sizing the window it covers is harder; our pipeline engineering practice does that from measured commit lag.
The cause was never a coding error. Three assumptions combined badly: that the change column signaled completeness when it only records when a row was stamped, that the watermark could advance to the highest value seen rather than one proven closed, and that re-reading cost too much. The last removed the one mechanism that would have made late-arriving data self-healing.
How do you design incremental loads that self-heal?
Treat the watermark as an optimization that decides how much data you read, never as proof that the data is complete. Three principles follow: the watermark is a guess; re-reading must be harmless before it is useful; completeness must be proven outside the loader.
Figure 3: Where the overlap window, the idempotent MERGE and the reconciliation job sit in a corrected pipeline.
Which fixes actually work in ADF and PySpark?
Five patterns do the work: a metadata-driven overlap window, an idempotent MERGE, a batch-derived watermark, commit-ordered change tracking, and scheduled reconciliation.
Pattern 1: Read from an overlap window, and keep the buffer in metadata
Subtract a lookback buffer from the watermark and bound the batch at the top, so every run re-covers a slice the previous run touched. Commit behavior differs by source, so hold the buffer in a control table, not in code.
What the buffer actually buys you. It re-reads a window the previous run covered, so a row committing late is collected by the next incremental load, not skipped. The upper bound keeps the batch deterministic; without it, rows landing mid-read fall into an irreproducible boundary. Holding the lookback in the control table makes widening it a data change, not a redeploy, the principle behind Microsoft’s control-table delta copy template.
Pattern 2: Make the write idempotent with MERGE
An overlap window is only safe when reloading a row cannot duplicate it. Make the write an upsert keyed on the business key, and let the change column decide the winner.
Why convergence matters more than correctness here. Collapsing duplicate keys first, then keying on the business key, makes the load convergent: the same batch applied twice produces the same table, and the timestamp comparison stops an older re-read overwriting a newer. In Synapse, MERGE runs on dedicated SQL pools only; on a lakehouse Delta Lake MERGE converges the same way.
Pattern 3: Derive the watermark from the data, not the clock
Where the watermark comes from matters more than teams expect
Why the clock is the wrong source.A call to
utcnow()belongs to the machine running the pipeline, not the database being read, and latency, retries and clock drift push it ahead of what was loaded. Taking the batch maximum ties the watermark to observed data; writing it only after a successful load means a failed run replays instead of skipping.
Pattern 4: Move to commit-ordered change tracking
Where you control the source database, you can remove the timestamp problem rather than mitigate it. Change tracking assigns versions at commit time, in commit order.
When to use which. Microsoft states that change tracking orders changes by transaction commit time, which makes it reliable under long-running and overlapping transactions, and it reports the deletes no timestamp column can, though applying them needs the MERGE branch above. The snapshot transaction is not optional: without one, more changes can return than the version you just captured, a smaller version of the same race. An idempotent write makes those extra rows harmless. Costs: source-side enablement and retention tuning.
Pattern 5: Reconcile on a schedule, not on suspicion
The overlap window heals rows inside the buffer; a scheduled comparison catches what the incremental load misses.
What the schedule catches that the buffer can’t. Backdated edits, hard deletes, and any outage outlasting the lookback produce late-arriving data outside the overlap window. Comparing a fixed historical range catches it; a schedule proves the fix holds.
Change tracking or timestamps: which incremental load strategy fits?
Match the strategy to how much of the source you control. If you own the database, change tracking removes the failure mode, provided reads run inside a snapshot transaction. If not, a timestamp watermark with an overlap window recovers late-committed rows. Hash comparison covers an unreliable change column; full refresh suits small dimensions.
Not every incremental load needs the full treatment; the comparison below weighs the options.
Table 1: Choosing an incremental load strategy by how much of the source you control.
Figure 4: Illustrative shape of the daily completeness gap before and after an overlap window and scheduled reconciliation. Values are schematic, not measured client results.
What best practices keep incremental loads complete?
Size the lookback from measured commit lag rather than intuition. Sample the delay between change timestamp and read visibility on a busy day, then double it.
Never let an incremental load advance the watermark before the write succeeds; treat the two as one unit. Store watermarks in UTC and compare against a change column converted into it. Alert on the reconciliation delta rather than job status, because green pipelines hide this bug and a reporting layer stakeholders trust depends on the difference. Where hard deletes are permitted, timestamps can never be complete: add change tracking, a soft-delete flag, or an anti-join. Where the change column is unreliable, compare row hashes. The same holds when the landing zone is Delta, alongside the Fabric lakehouse storage decisions.
Watch out. Test in non-production first: enabling change tracking on a live OLTP database and switching a read to snapshot isolation both change server behavior. Size the lookback from your own commit lag, not the thirty minutes here. Widening the lookback window is not a fix on its own. If the load still appends instead of merging, a longer overlap converts missing rows into duplicates: easier to see, no less wrong. Make the write idempotent first, then widen the window.
Key takeaways for data engineering teams
Designing for late-arriving data stops an incremental load drifting below its source. Rows that commit late are collected on the next pass, watermark and lookback become configuration not code, and scheduled reconciliation turns an invisible gap into an alertable number.
The field rule: if a source writes inside transactions, runs on multiple application servers, or allows hard deletes, assume the watermark is lying.
Losing rows you cannot see? Our team audits timestamp-driven pipelines end to end: measuring commit lag, sizing the lookback, converting appends into idempotent merges, and standing up the reconciliation that proves it closed. [[FILL-ENGAGEMENT]] Talk to our data engineering team about a completeness review, or see how we build pipelines that stay auditable.
Frequently asked questions
Q. What is incremental load in ETL?
An incremental load moves only the records created or changed since the last run, rather than the full dataset. It cuts run time, compute and network cost, and keeps heavy read traffic off production source systems.
Q. What is the difference between full load and incremental data loading in ETL?
A full load replaces the entire dataset on every run; an incremental load moves only what changed. Full loads are complete by construction, but their cost scales with table size rather than with change volume.
Q. What is the difference between CDC and incremental load?
CDC is one way to implement an incremental load. A timestamp watermark infers change from a column, while CDC reads the transaction log, so it captures deletes and orders changes by commit.
Q. What are the differences between truncate and load and incremental load?
Truncate and load empties the target and rewrites it, so history and any rows deleted upstream are lost. An incremental load merges only changed rows, preserving history at a fraction of the read cost.
Q. How to handle incremental load in PySpark?
Read a bounded window, derive the new watermark from the batch with F.max rather than the clock, then write with MERGE on a business key. A Spark streaming watermark is a different mechanism.
