--- title: "Test incremental loads" description: "Why incremental loads are worth testing, how they go wrong — late rows, missed updates, deletes, time zones, re-runs — and three levels of test: detect, detailed, balanced; contains for permanent staging; thresholds for systems that never stand still" url: "https://docs.justcat.it/how-to-guides/test-patterns/test-incremental-loads/" --- # Test incremental loads ## How it goes wrong An incremental load picks rows by a watermark — `ModifiedAt > last run` — or by a change log, and merges them into the target. The failure modes are the same everywhere: * **Late-arriving rows** — a row whose `ModifiedAt` is *older* than the watermark when it finally lands in the source (replication lag, a batch stamped with its business date, a transaction that committed after the load read) — skipped forever. * **Missed updates** — the source changes a row without touching `ModifiedAt`; a column added to the source that the merge does not map. * **Deletes** — the source deletes, the target never hears; or the reverse, a soft-delete flag the load ignores. * **Time zones and precision** — a watermark stored in local time compared with a UTC column, or a `DATETIME` rounded to 3 ms, loses or duplicates the rows on the edge. * **Re-runs** — a load that ran twice inserted twice; a load that failed halfway left a watermark it did not earn. * **Clock skew and "too alive" systems** — the source is written every second; whatever you compare, the last minutes differ because they are in flight. Every one of these is invisible in the load's log, which says *succeeded*. They are visible to a test that looks at the data on both sides. ## Three levels ### Detect — counts per day over a window Cheap enough to run after every load. Rung 2 of [Compare data across systems](https://docs.justcat.it/how-to-guides/test-patterns/compare-data-across-systems/ "Compare data across systems"), on the last N days: **Properties** Name : Orders per day match the source, last 14 days Suite : Incremental load First data source : ERP First query : ```sql SELECT CAST(OrderDate AS DATE) AS D, COUNT(*) AS Orders, SUM(Amount) AS Amount FROM dbo.Orders WHERE OrderDate >= DATEADD(DAY, -14, CAST(GETDATE() AS DATE)) AND ModifiedAt < DATEADD(MINUTE, -15, SYSUTCDATETIME()) -- see "systems that never stand still" GROUP BY CAST(OrderDate AS DATE) ORDER BY D ``` Second data source : DWH Second query : ```sql SELECT OrderDate, COUNT(*), SUM(Amount) FROM fact.Orders WHERE OrderDate >= DATEADD(DAY, -14, CAST(GETDATE() AS DATE)) GROUP BY OrderDate ORDER BY OrderDate ``` Expectation : sets match Key : 1 Tolerance : 0.01 Maximum errors logged : 14 **YAML** ```yaml Tests: - Name: Orders per day match the source, last 14 days Suite: Incremental load First data source: ERP First query: | SELECT CAST(OrderDate AS DATE) AS D, COUNT(*) AS Orders, SUM(Amount) AS Amount FROM dbo.Orders WHERE OrderDate >= DATEADD(DAY, -14, CAST(GETDATE() AS DATE)) AND ModifiedAt < DATEADD(MINUTE, -15, SYSUTCDATETIME()) -- see "systems that never stand still" GROUP BY CAST(OrderDate AS DATE) ORDER BY D Second data source: DWH Second query: | SELECT OrderDate, COUNT(*), SUM(Amount) FROM fact.Orders WHERE OrderDate >= DATEADD(DAY, -14, CAST(GETDATE() AS DATE)) GROUP BY OrderDate ORDER BY OrderDate Expectation: sets match Key: 1 Tolerance: 0.01 Maximum errors logged: 14 ``` A late row shows up as a day that differs by one; a missed update as a day whose sum differs. Fourteen days because late rows arrive late — make the window longer than your longest replication delay, and keep one such test with a long window (90 days, monthly grain) for the slow leaks. ### Detailed — the rows, on a window Rung 3 or 4 on the same window, with a key, so the message names the rows: **Properties** Name : Orders of the last 3 days equal the source Suite : Incremental load First data source : ERP First query : ```sql SELECT OrderId, CustomerId, Amount, CAST(ModifiedAt AS DATETIME2(0)) FROM dbo.Orders WHERE ModifiedAt >= DATEADD(DAY, -3, SYSUTCDATETIME()) AND ModifiedAt < DATEADD(MINUTE, -15, SYSUTCDATETIME()) ORDER BY OrderId ``` Second data source : DWH Second query : ```sql SELECT SourceOrderId, CustomerId, Amount, CAST(SourceModifiedAt AS DATETIME2(0)) FROM fact.Orders WHERE SourceModifiedAt >= DATEADD(DAY, -3, SYSUTCDATETIME()) AND SourceModifiedAt < DATEADD(MINUTE, -15, SYSUTCDATETIME()) ORDER BY SourceOrderId ``` Expectation : sets match Key : 1 Maximum errors logged : 50 **YAML** ```yaml - Name: Orders of the last 3 days equal the source Suite: Incremental load First data source: ERP First query: | SELECT OrderId, CustomerId, Amount, CAST(ModifiedAt AS DATETIME2(0)) FROM dbo.Orders WHERE ModifiedAt >= DATEADD(DAY, -3, SYSUTCDATETIME()) AND ModifiedAt < DATEADD(MINUTE, -15, SYSUTCDATETIME()) ORDER BY OrderId Second data source: DWH Second query: | SELECT SourceOrderId, CustomerId, Amount, CAST(SourceModifiedAt AS DATETIME2(0)) FROM fact.Orders WHERE SourceModifiedAt >= DATEADD(DAY, -3, SYSUTCDATETIME()) AND SourceModifiedAt < DATEADD(MINUTE, -15, SYSUTCDATETIME()) ORDER BY SourceOrderId Expectation: sets match Key: 1 Maximum errors logged: 50 ``` Both windows are cut with the **same** predicate on the **same** column semantics — the target keeps the source's `ModifiedAt` for exactly this reason. A source without a reliable `ModifiedAt` is the first finding of this test; there is no incremental load to trust without one. ### Balanced — detect always, detailed on a short window, full once in a while Detect after every load (cheap), detailed on the last day or three (affordable), and the full-row comparison of the whole table on a weekend or before a release. The full one is the only test that finds a row missed a year ago; run it rarely, but run it. ## Deletes, and permanent staging Rows the source deleted are rows `sets match` reports as *extra* on the target — the test for deletes is the comparison itself, on a window that covers the retention of deletes. When the target is **meant** to keep them — a permanent staging area, a history table — compare with `contains`: the target is the superset, the source the subset, and the test proves the source is fully in the target without complaining about what the target keeps: **Properties** Name : Every source order is in staging Suite : Incremental load First data source : DWH First query : SELECT OrderId, Amount FROM stage.Orders WHERE LoadedAt >= DATEADD(DAY, -7, SYSUTCDATETIME()) ORDER BY OrderId Second data source : ERP Second query : SELECT OrderId, Amount FROM dbo.Orders WHERE ModifiedAt >= DATEADD(DAY, -7, SYSUTCDATETIME()) ORDER BY OrderId Expectation : contains Key : OrderId Maximum errors logged : 50 **YAML** ```yaml - Name: Every source order is in staging Suite: Incremental load First data source: DWH First query: SELECT OrderId, Amount FROM stage.Orders WHERE LoadedAt >= DATEADD(DAY, -7, SYSUTCDATETIME()) ORDER BY OrderId Second data source: ERP Second query: SELECT OrderId, Amount FROM dbo.Orders WHERE ModifiedAt >= DATEADD(DAY, -7, SYSUTCDATETIME()) ORDER BY OrderId Expectation: contains Key: OrderId Maximum errors logged: 50 ``` Set the key — with `contains` it is what makes the message list more than the first missing row. And for the history table, the *opposite* question is a `set is empty`: "no row in staging with a source id that never existed in the source" catches the load that invents rows. ## Systems that never stand still When the source is written continuously, the newest minutes differ on every run and the test is red for no reason. Cut both sides at the same moment in the past — `ModifiedAt < DATEADD(MINUTE, -15, SYSUTCDATETIME())` on both queries — with a margin larger than the load's own latency. Use UTC on both sides, or convert; a threshold in local time on one side and UTC on the other is the time-zone failure mode, reproduced in the test. ## Two more tests that belong here * **No duplicates from re-runs** — `set is empty` on `SELECT SourceOrderId FROM fact.Orders GROUP BY SourceOrderId HAVING COUNT(*) > 1`. * **The watermark moved, and not too far** — `set is empty` on the load's control table: `SELECT LastWatermark FROM etl.Watermarks WHERE Job = 'Orders' HAVING MAX(LastWatermark) < DATEADD(HOUR, -6, SYSUTCDATETIME()) OR MAX(LastWatermark) > SYSUTCDATETIME()`. ## Related * [Compare data across systems](https://docs.justcat.it/how-to-guides/test-patterns/compare-data-across-systems/ "Compare data across systems") · [Differences between systems](https://docs.justcat.it/how-to-guides/test-patterns/differences-between-systems/ "Differences between systems") * [Contains](https://docs.justcat.it/reference/tests/expectations/contains/ "Contains") · [Sets match](https://docs.justcat.it/reference/tests/expectations/sets-match/ "Sets match") — the reference. * [Find problems with set is empty](https://docs.justcat.it/how-to-guides/test-patterns/find-problems-with-set-is-empty/ "Find problems with set is empty") — the duplicate and watermark checks.