Test incremental loads

A full load is either right or obviously wrong. An incremental load is wrong quietly — a late row here, an update missed there — and the gap grows for months before anyone sees it. Here is how it goes wrong, and tests at three levels of effort to catch it.

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, on the last N days:

• Form view
• 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:

• Form view
• 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:

• Form view
• 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 · Differences between systems
• Contains · Sets match — the reference.
• Find problems with set is empty — the duplicate and watermark checks.