DevOps et observabilité
Testing Incremental Loads and Idempotency: Reruns, Late Data, Replay and Recovery
How to prove an incremental load produces the correct final state when it reruns, replays data, receives late or out-of-order records, or recovers from a partial failure: guarantees, a full-load oracle, watermark and MERGE tests, invariants, metamorphic tests and a tested PySpark suite.
Dans cet article
An incremental load that ran successfully last night has proven one thing: that a single run, with last night’s data, in last night’s order, produced a result nobody complained about. It hasn’t proven that a rerun is harmless, that a record committed a few seconds late will ever arrive, that an older update can’t overwrite a newer one, or that the table can be rebuilt after an incident. This guide answers one question:
How do I know an incremental pipeline will still produce the correct final state when I rerun it, replay data, receive late records, or recover after a failure?
Three principles:
- An incremental load is not proven correct because one run succeeds.
- Idempotency means that repeating the same logical input does not corrupt the final state.
- Recovery behaviour must be tested before an incident. During the incident, you can only use what you already proved.
This isn’t an introduction to incremental loading. Incremental Loads in Microsoft Fabric explains how to build one (bounded watermark windows, lookback, guarded MERGE, deletes, reconciliation) and the incremental load patterns cheat sheet is the quick reference. This guide is the other half: how to prove the design delivers what it promises.
The guarantees to test
An incremental design makes promises. Each one is a test, and most designs make all nine:
| Guarantee | The promise | Tested by |
|---|---|---|
| Completeness | Every intended source record eventually appears | Oracle comparison; late-arrival and boundary tests |
| No unintended duplication | Reprocessing doesn’t create duplicate business rows | Same batch twice; duplicate input; replay |
| Correct updates | Changed source records update the target | Insert and update tests; MERGE cases |
| Safe replay | Previously processed input can be processed again | Same batch twice; replay window; recovery rebuild |
| Late-arriving correctness | Records arriving after their batch are handled by policy | Late-arrival test, with and without lookback |
| Correct state advancement | Watermarks and checkpoints move only after success | Partial-failure tests; quality hold |
| Restartability | A failed run can restart safely | Crash before and after the merge, then rerun |
| Determinism | The same logical input gives the same final state | Batch-partition and input-order invariants; tie tests |
| Observability | Each run explains what it did | Run records: counts, windows, status |
The rest of this guide turns each row into a test, using one small scenario.
The scenario
Orders, with these source fields:
| Field | Meaning |
|---|---|
order_id |
Business key |
customer_id |
Who placed it |
updated_at |
When this version was written at the source (the watermark column) |
version |
A per-order version from the source (a row version, CDC sequence or similar) |
status, amount |
Business content |
is_deleted |
The source deleted the order |
The target holds the latest valid state per order. The state is a watermark: the upper bound of the last window that was fully processed. Each run reads the half-open window watermark − lookback < updated_at <= upper_bound, with the upper bound captured from the source clock, the convention the incremental-loads article uses.
The full-load oracle
The most useful single technique in this guide:
For a deterministic workload, the final incremental result should match the trusted full-load result.
- Take a test dataset: every version of every order the source will ever hold during the test.
- Run the transformation once, as a full load, over all of it. That result is the oracle.
- Run the same data incrementally: in batches, with reruns, late rows, replays and failures.
- Compare the final incremental state with the oracle.
The oracle doesn’t need to be clever; it applies the same rules (latest version wins, invalid rows rejected) to everything at once, so it can’t have incremental bugs. Any difference between the two is either an incremental bug or a deliberate, documented difference (for example, a late row your policy chose to ignore). The tests below use it throughout.
- One batch
- Three daily batches
- Day 2 replayed
- Rebuilt from raw
- Final business state
- AssertionEquals the full-load oracle
The model under test
The tests run against a compact PySpark model of the load. Every function is DataFrame-in, DataFrame-out; a small Store object stands in for what survives a crash (target table, watermark, quarantine, run log). It mirrors a Delta MERGE plus a watermark table closely enough to test the guarantees, and it runs on a laptop.
# src/incremental/orders.py
"""A unit-testable model of an incremental orders load.
Pure DataFrame logic plus a tiny in-memory "durable store" (target, watermark, quarantine, run log).
It mirrors a Delta MERGE + watermark table closely enough to test the guarantees; it is not Delta.
"""
from dataclasses import dataclass, field
from datetime import datetime, timedelta
from pyspark.sql import DataFrame, Window
from pyspark.sql import functions as F
KEY = "order_id"
BEGINNING = datetime(2000, 1, 1) # not 1900: naive datetimes before 1970 fail to convert on Windows
def extract_window(source: DataFrame, after: datetime, up_to: datetime) -> DataFrame:
"""Half-open window: after < updated_at <= up_to. Consecutive windows meet exactly."""
return source.where((F.col("updated_at") > F.lit(after)) & (F.col("updated_at") <= F.lit(up_to)))
def latest_per_key(batch: DataFrame) -> DataFrame:
"""One row per order: the highest source version. The same version with different content is a conflict."""
content = F.struct("customer_id", "status", "amount", "is_deleted", "updated_at")
conflicts = (
batch.groupBy(KEY, "version").agg(F.countDistinct(content).alias("variants"))
.where(F.col("variants") > 1).limit(1).count()
)
if conflicts:
raise ValueError("conflicting rows share an order_id and version")
newest_first = Window.partitionBy(KEY).orderBy(F.col("version").desc())
return batch.withColumn("_r", F.row_number().over(newest_first)).where("_r = 1").drop("_r")
def split_valid(changes: DataFrame):
"""Critical rule: a live order needs a customer and a non-negative amount. Deletes are always valid."""
valid_row = F.col("is_deleted") | (
F.col("customer_id").isNotNull() & F.coalesce(F.col("amount") >= 0, F.lit(False))
)
return changes.where(valid_row), changes.where(~valid_row)
def apply_changes(target: DataFrame, changes: DataFrame):
"""MERGE-equivalent final state, guarded by version: an older or equal version never overwrites.
Deletes are soft: the row stays, with is_deleted = true, so a late older update can't resurrect it.
"""
versions = changes.join(target.select(KEY, F.col("version").alias("_current")), KEY, "left")
stats = versions.agg(
F.sum(F.col("_current").isNull().cast("int")).alias("inserted"),
F.sum((F.col("version") > F.col("_current")).cast("int")).alias("updated"),
F.sum((F.col("version") <= F.col("_current")).cast("int")).alias("ignored"),
).first()
candidates = target.withColumn("_existing", F.lit(0)).unionByName(changes.withColumn("_existing", F.lit(1)))
newest = Window.partitionBy(KEY).orderBy(F.col("version").desc(), F.col("_existing").asc())
merged = candidates.withColumn("_r", F.row_number().over(newest)).where("_r = 1").drop("_r", "_existing")
return merged, {k: int(stats[k] or 0) for k in ("inserted", "updated", "ignored")}
class InjectedFailure(RuntimeError):
"""Raised by tests to simulate a crash between two steps."""
@dataclass
class Store:
"""What survives a crash: the target table, the watermark, the quarantine and the run log."""
target: DataFrame
watermark: datetime = BEGINNING
quarantine: set = field(default_factory=set)
runs: list = field(default_factory=list)
def run_incremental(store: Store, source: DataFrame, upper_bound: datetime, run_id: str,
lookback: timedelta = timedelta(0), fail_after: str | None = None,
max_reject_ratio: float = 0.2) -> dict:
"""Extract → deduplicate → validate → merge → quarantine → watermark. The watermark moves last."""
run = {"run_id": run_id, "previous_watermark": store.watermark, "proposed_watermark": upper_bound,
"status": "started"}
store.runs.append(run)
window = extract_window(source, store.watermark - lookback, upper_bound)
run["source_rows"] = window.count()
changes = latest_per_key(window)
run["duplicates"] = run["source_rows"] - changes.count()
valid, rejected = split_valid(changes)
run["accepted"], run["rejected"] = valid.count(), rejected.count()
if fail_after == "extract":
raise InjectedFailure("crash before merge")
evaluated = run["accepted"] + run["rejected"]
if evaluated and run["rejected"] / evaluated > max_reject_ratio:
run["status"] = "held" # nothing written, watermark unchanged
return run
merged, stats = apply_changes(store.target, valid)
store.target = merged.localCheckpoint() # the durable write
run.update(stats)
if fail_after == "merge":
raise InjectedFailure("crash after merge, before the watermark")
store.quarantine |= {(r[KEY], r["version"]) for r in rejected.select(KEY, "version").collect()}
store.watermark = upper_bound # progress is recorded only now
run["status"] = "succeeded"
return run
def full_load(spark, source: DataFrame, schema) -> DataFrame:
"""The oracle: the same rules applied to all the data at once."""
valid, _ = split_valid(latest_per_key(source))
merged, _ = apply_changes(spark.createDataFrame([], schema), valid)
return merged
Two choices make the model honest about what it is. fail_after lets a test crash the run at a precise point, which a real pipeline can’t do on demand. localCheckpoint() materialises the merged target, standing in for the durable write: what the store holds after a crash is exactly what a real table would hold. What it doesn’t model is Delta’s transaction log, concurrent writers or a real MERGE statement; those belong to the integration tests later in the guide.
The tests use fixed timestamps (at(9, 10) is 2026-11-10 09:10), an order(...) helper that builds one source row, and a state(store) helper that returns the business state as sorted tuples, so comparisons don’t depend on row order.
A normal incremental run
Batch 1 holds orders 1, 2 and 3; batch 2 adds a new version of order 2 and a new order 4.
BATCH_1 = [order(1, 1, at(9, 10)), order(2, 1, at(9, 20)), order(3, 1, at(9, 30))]
BATCH_2 = BATCH_1 + [order(2, 2, at(10, 15), status="REFUNDED", amount="0.00"), order(4, 1, at(10, 30))]
def test_insert_and_update(spark):
store = empty_store(spark)
run_incremental(store, source(spark, BATCH_1), at(10), "r1")
run = run_incremental(store, source(spark, BATCH_2), at(11), "r2")
assert (run["inserted"], run["updated"]) == (1, 1)
rows = {r.order_id: r for r in store.target.collect()}
assert len(rows) == 4 # four business rows, no duplicates
assert (rows[2].status, rows[2].version) == ("REFUNDED", 2)
assert store.watermark == at(11)
assert state(store) == oracle(spark, BATCH_2)
BATCH_2 is what the source holds at 11:00, including the rows already loaded; the window decides what the run reads. Every assertion maps to a guarantee: the counts (observability), four business rows (no duplication), order 2’s newer state (correct updates), the watermark (state advancement), and the oracle (completeness and correctness at once).
Same batch twice
The canonical idempotency test. Rewind the watermark and run batch 2 again:
def test_same_batch_twice_leaves_the_same_business_state(spark):
store = empty_store(spark)
run_incremental(store, source(spark, BATCH_1), at(10), "r1")
run_incremental(store, source(spark, BATCH_2), at(11), "r2")
once = state(store)
store.watermark = at(10) # replay batch 2: same logical input
rerun = run_incremental(store, source(spark, BATCH_2), at(11), "r2-rerun")
assert state(store) == once # business-state idempotency
assert (rerun["inserted"], rerun["updated"], rerun["ignored"]) == (0, 0, 2)
assert [r["run_id"] for r in store.runs] == ["r1", "r2", "r2-rerun"] # execution records stay distinct
assert store.watermark == at(11)
Two kinds of idempotency are being tested, and they are related but not identical:
- Execution idempotency: the pipeline can run again safely. The rerun completes, records itself as a separate run with its own ID, and doesn’t trip over the previous run’s records.
- Business-state idempotency: the final target data is unchanged. Same rows, same versions, no duplicates, and the merge reports zero inserts and zero updates.
A pipeline can have the first without the second (it reruns happily and appends every row again) or the second without the first (the data would be fine, but the run fails because its log entry already exists). Test both. The side effects count too: the quarantine is a set keyed by order and version in this model, so a rerun can’t add the same rejected row twice; a real quarantine table needs the same key.
Duplicate keys in one batch
The source sends the same order twice in one window: a retry at the source, a CDC stream that emits two versions, or an identical row duplicated by a file resend. There’s no universal right answer; there is a chosen policy, and the tests encode it:
| Policy | When it fits | What the test asserts |
|---|---|---|
| Highest source version wins | The source has a version or sequence | The highest version survives |
| Latest event wins | Only a timestamp exists | The latest timestamp survives, with a defined tie-break |
| Reject the batch | Duplicates mean the extract is broken | The run fails or holds |
| Quarantine | Duplicates are rare and need review | Both rows quarantined, with a reason |
| Deterministic tie-break | Two versions share an ordering value | The same row survives every run |
This model uses highest version wins, and treats the same version with different content as a conflict it refuses to guess about:
def test_duplicate_key_in_a_batch_keeps_the_highest_version(spark):
rows = [order(5, 1, at(9)), order(5, 2, at(9, 5), status="CANCELLED"), order(5, 1, at(9))]
store = empty_store(spark)
run = run_incremental(store, source(spark, rows), at(10), "r1")
assert run["duplicates"] == 2
assert [(r.order_id, r.status) for r in store.target.collect()] == [(5, "CANCELLED")]
def test_conflicting_rows_with_the_same_version_are_rejected(spark):
rows = [order(6, 1, at(9), amount="10.00"), order(6, 1, at(9), amount="99.00")]
with pytest.raises(ValueError, match="conflicting"):
latest_per_key(source(spark, rows))
Late-arriving data
The watermark is 12:00. A legitimate record committed after the 12:00 run had read the source, but its updated_at is 11:55: a long transaction, a lagging replica, a slow clock. A strict watermark never sees it:
LATE = order(7, 1, at(11, 55)) # committed after the 12:00 run had read
def late_scenario(spark, lookback):
store = empty_store(spark)
on_time = [order(8, 1, at(11, 52))] # inside a 10-minute lookback
run_incremental(store, source(spark, on_time), at(12), "r1") # watermark 12:00
run_incremental(store, source(spark, on_time + [LATE]), at(13), "r2", lookback=lookback)
return store, on_time + [LATE]
def test_strict_watermark_loses_a_late_row(spark):
store, all_rows = late_scenario(spark, lookback=timedelta(0))
assert 7 not in {r.order_id for r in store.target.collect()}
assert state(store) != oracle(spark, all_rows) # the incremental result diverges from the oracle
def test_lookback_recovers_the_late_row(spark):
store, all_rows = late_scenario(spark, lookback=timedelta(minutes=10))
assert state(store) == oracle(spark, all_rows)
assert store.runs[-1]["source_rows"] == 2 # 11:55 re-read window includes order 8 again
assert store.runs[-1]["ignored"] == 1 # ...and the re-read is harmless
The first test documents the failure; keep it. It’s the clearest explanation in the codebase of why the lookback exists, and it fails if someone “simplifies” the scenario away. The second shows the trade-off: a lookback re-reads data that was already processed (order 8 comes back), which is only safe because the merge is idempotent. Lookback and idempotency are a pair: adding one without testing the other turns a late-data fix into a duplication bug.
Strategies to test, depending on the source:
- Overlap or lookback window: as above. Test with a late row inside the lookback (recovered) and outside it (documented as lost, or caught by reconciliation).
- CDC or source sequence: the source’s log position or sequence number replaces the timestamp; late commits get later positions, so they can’t be skipped. Test that the checkpoint is the position, not a timestamp derived from it.
- Replay range: periodically reprocess a wider window (the last day, the last week). Test it like any replay.
- Source version: the version decides what wins, whatever the arrival time. The next sections test it.
Watermark boundaries
A row exactly at the watermark must belong to exactly one window. With the half-open convention after < updated_at <= up_to, it belongs to the window that ends at that instant:
def test_each_boundary_row_is_read_exactly_once(spark):
rows = [order(11, 1, at(11, 59)), order(12, 1, at(12)), order(13, 1, at(12, 1))]
first = extract_window(source(spark, rows), BEGINNING, at(12))
second = extract_window(source(spark, rows), at(12), at(13))
assert sorted(r.order_id for r in first.collect()) == [11, 12] # exactly at 12:00 belongs here
assert sorted(r.order_id for r in second.collect()) == [13] # ...and not here
| Comparison | Lower bound | Upper bound | Effect |
|---|---|---|---|
| Half-open (correct) | > |
<= |
Each instant belongs to exactly one window |
| Both inclusive | >= |
<= |
The boundary row is read twice: repeated processing, harmless only if idempotent |
| Both exclusive | > |
< |
The boundary row is never read: silent loss |
| Closed-open | >= |
< |
Also correct, if every window uses it |
Use fixed timestamps for boundary tests, and include a row exactly at the boundary, one just below and one just above. Timestamps with lower precision at the source (seconds, or a datetime column rounded to 3.33 ms in SQL Server) make “exactly at the boundary” more common than intuition suggests.
The watermark moves last
Progress state must not claim success before the durable business result exists.
- Watermark lastSafe
- Read window
- Merge target
- Before the watermarkCrash
- Rerun reads the same windowMerge repeats; result unchanged
- Watermark firstDangerous
- Read window
- Claims success earlyAdvance watermark
- Before the mergeCrash
- Rerun skips the windowRows lost; no error anywhere
The partial-failure tests crash the model at two points and rerun:
def test_crash_after_merge_then_rerun(spark):
store = empty_store(spark)
run_incremental(store, source(spark, BATCH_1), at(10), "r1")
with pytest.raises(InjectedFailure):
run_incremental(store, source(spark, BATCH_2), at(11), "r2", fail_after="merge")
assert store.watermark == at(10) # progress not claimed
run_incremental(store, source(spark, BATCH_2), at(11), "r2-rerun")
assert state(store) == oracle(spark, BATCH_2) # the merge repeated safely
assert store.watermark == at(11)
def test_crash_before_merge_leaves_the_target_untouched(spark):
store = empty_store(spark)
run_incremental(store, source(spark, BATCH_1), at(10), "r1")
before = state(store)
with pytest.raises(InjectedFailure):
run_incremental(store, source(spark, BATCH_2), at(11), "r2", fail_after="extract")
assert (state(store), store.watermark) == (before, at(10))
Recovery differs by crash point. After the merge, before the watermark: the target already holds the batch, the state says it doesn’t, and the rerun’s merge is a no-op for those rows (that’s the case idempotency exists for). Before the merge: nothing durable changed; the rerun is simply the first real attempt. A third case, a multi-table load that merged one table but not the next, behaves like the first only if every table’s write is idempotent; rerun the whole logical batch, not just the table that failed.
When data and state live in the same transactional engine, a stronger option exists: commit both in one transaction (SQL Server can; see the SQL section). In a Lakehouse, a Delta commit is atomic per table, and the watermark usually lives in another table, so “watermark last, idempotent merge” is the design to test. The Fabric pipeline testing guide shows the same test at pipeline level.
Out-of-order versions and equal timestamps
Arrival order is not necessarily business order.
Version 3 of an order arrives and is loaded; then version 2 shows up late. A merge that applies “whatever arrived last” overwrites newer state with older:
def test_an_older_version_never_overwrites_a_newer_one(spark):
store = empty_store(spark)
v3 = order(9, 3, at(10, 50), status="SHIPPED")
run_incremental(store, source(spark, [v3]), at(11), "r1")
v2_late = order(9, 2, at(11, 10), status="PAID") # arrives later, but is older business state
run = run_incremental(store, source(spark, [v3, v2_late]), at(12), "r2")
assert run["ignored"] == 1
assert [(r.version, r.status) for r in store.target.collect()] == [(3, "SHIPPED")]
def test_equal_timestamps_are_ordered_by_version(spark):
rows = [order(10, 2, at(9), status="SHIPPED"), order(10, 1, at(9), status="PAID")]
assert [r.status for r in latest_per_key(source(spark, rows)).collect()] == ["SHIPPED"]
Note what v2_late does: its updated_at is later than v3’s, because it was written late, yet it’s older business state. That’s why the guard compares versions, not timestamps. When two updates share a timestamp, the timestamp can’t order them at all, so something else must: a source sequence, a transaction ID, an event ID or a version number. If the source provides none of these, the tie-break is a business decision; write it down and test that it gives the same answer every run. In SQL, this is the WHEN MATCHED AND source.version > target.version condition; in the incremental-loads article, it’s the guarded MERGE.
MERGE and upsert: the cases to test
Whatever engine runs the merge, its behaviour reduces to a short list of cases. Each needs a test:
| Case | Expected behaviour (this model) | Covered by |
|---|---|---|
| Insert | New key → new row | test_insert_and_update |
| Update | Newer version → replaces | test_insert_and_update |
| Unchanged record | Same version again → no change, counted as ignored | test_same_batch_twice…, test_merge_is_idempotent_at_unit_level |
| Duplicate source key | Highest version wins before the merge | test_duplicate_key_in_a_batch… |
| Target-only row | Untouched (an incremental batch says nothing about missing keys) | Every multi-batch test keeps earlier orders |
| Null key | Invalid; never reaches the merge | Rejected by validation (customer_id) in this model; test your key rule the same way |
| Older source version | Ignored | test_an_older_version_never_overwrites… |
| Newer source version | Replaces | test_insert_and_update |
The unit-level idempotency test runs the merge function twice on its own result:
def test_merge_is_idempotent_at_unit_level(spark):
target = full_load(spark, source(spark, BATCH_2), ORDERS)
again, stats = apply_changes(target, latest_per_key(source(spark, BATCH_2)))
assertDataFrameEqual(again, target)
assert stats == {"inserted": 0, "updated": 0, "ignored": 4}
Delta Lake documents that a MERGE can fail when several source rows match and try to update the same target row, because the update is ambiguous (Delta Lake: upsert into a table using merge), which is one more reason the deduplication step comes first and has its own tests.
Deletes
Deletion is a policy, not a default, and each policy needs its own tests:
| Policy | Meaning | Test |
|---|---|---|
| Hard delete | The row is removed | The key is gone; a late older update must not re-insert it |
| Soft delete | The row stays, flagged | The flag is set; a late older update must not clear it |
| Tombstone | A delete record is kept as the latest version | Later versions win; older ones don’t |
| Ignore source deletes | The target keeps history | A delete changes nothing, deliberately |
| SCD / history | Each change is a new row | Delete closes the current row’s validity |
Don’t assume every incremental load propagates deletes: a watermark extract never sees a hard-deleted row at all, because there’s no row left to have a new updated_at. That needs CDC, a soft-delete column at the source, or a periodic snapshot comparison. This model uses soft deletes, and the test is the dangerous case, a late older update after the delete:
def test_soft_delete_is_not_resurrected_by_an_older_update(spark):
store = empty_store(spark)
deleted = order(14, 3, at(10, 40), deleted=True)
run_incremental(store, source(spark, [order(14, 1, at(9)), deleted]), at(11), "r1")
run_incremental(store, source(spark, [deleted, order(14, 2, at(11, 20))]), at(12), "r2")
assert [(r.version, r.is_deleted) for r in store.target.collect()] == [(3, True)]
Empty batch
def test_empty_batch_succeeds_without_touching_the_target(spark):
store = empty_store(spark)
run_incremental(store, source(spark, BATCH_1), at(10), "r1")
before = state(store)
run = run_incremental(store, source(spark, BATCH_1), at(11), "r2") # nothing new since 10:00
assert (run["status"], run["source_rows"]) == ("succeeded", 0)
assert state(store) == before # no accidental truncation
assert store.watermark == at(11) # the window was fully read
assert len(store.runs) == 2 # evidence exists for the empty run
The watermark behaviour on an empty window is a design decision. This model advances it to the upper bound, because the window was fully read; a design that takes the new watermark from MAX(updated_at) of the extracted rows has nothing to take, and must keep the old value. Either is fine. Not deciding is the bug: a MAX over zero rows is NULL, and a NULL watermark makes the next run read everything. The test pins down the choice, and the “no accidental truncation” assertion catches the overwrite-mode write that replaces a table with zero rows.
Data-quality failure: technically complete, logically not
Technical completion must not automatically advance logical progress.
A batch can load without a single error and still fail a critical rule. Two policies, both legitimate, and the test must match the one you chose:
- Hold: don’t publish, don’t advance state. The next run reads the same window again, after the source is fixed.
- Publish with a warning: for non-critical rules, publish the valid rows, quarantine the rest, advance state, alert the owner.
This model quarantines invalid rows and holds the whole batch when more than 20% are invalid:
def test_quality_failure_holds_the_batch_and_the_watermark(spark):
store = empty_store(spark)
bad = [order(15, 1, at(9), amount="-5.00"), order(16, 1, at(9, 5), amount=None), order(17, 1, at(9, 10))]
run = run_incremental(store, source(spark, bad), at(10), "r1")
assert run["status"] == "held"
assert store.target.count() == 0 and store.watermark == BEGINNING # completed technically, not logically
Data Quality Checks for Data Pipelines covers the rule framework: row rules versus dataset checks, quarantine with reason codes, and the maximum quarantine ratio used here.
Schema changes during an incremental load
A schema change mid-stream is one of the few events that can break an incremental load between two runs that were each correct. Classify the change and test the designed response:
| Change | Typical response | Test |
|---|---|---|
| Additive (new nullable column) | Accept; evolve the target intentionally, or ignore the column | The next run succeeds; the column is present (or deliberately absent) |
| Type change | Block, unless the widening is safe and declared | The run fails before writing, with the column named |
| Removed field | Block | The run fails before writing |
| Renamed field | Block: it’s a removal plus an addition | The run fails before writing; nothing is silently nulled |
The dangerous outcome is a run that “succeeds” by filling a renamed column with nulls from then on. Test it by feeding the next batch with the changed schema and asserting the response. Schema and data-contract testing is the next guide in this pillar.
Reconciliation
Reconciliation proves that every row the run evaluated went somewhere. Useful checks, none of which is “source count equals target count”:
- Changes accounted for:
inserted + updated + ignoredequals the rows sent to the merge. - Evaluated = accepted + rejected: nothing vanished between validation and the merge.
- Business-key count: after a run, the distinct keys in the target equal the keys before plus the run’s inserts.
- Amounts: the sum of a measure over the window’s accepted rows matches the change in the target, where the semantics allow.
def test_accepted_plus_rejected_equals_evaluated(spark):
rows = [order(i, 1, at(9, i)) for i in range(20, 30)] + [order(30, 1, at(9, 30), amount="-1.00")]
store = empty_store(spark)
run = run_incremental(store, source(spark, rows), at(10), "r1")
assert run["accepted"] + run["rejected"] == run["source_rows"] - run["duplicates"]
assert run["inserted"] == run["accepted"]
assert store.quarantine == {(30, 1)}
The flagship explains why exact equality is sometimes the wrong rule: filters, deduplication, late rows and aggregation all make counts differ for legitimate reasons.
Invariants: properties, not examples
An example test checks one input. An invariant is a property that must hold for every input, which makes it much harder for a bug to slip between examples. The useful ones for incremental loads:
- Business-key uniqueness: the target never has two rows for one key.
- Replay independence: the final state doesn’t depend on whether a window was processed once or several times.
- Watermark monotonicity: in normal runs, the watermark never moves backwards.
- Conservation: accepted + rejected = evaluated; inserted + updated + ignored = merged input.
- No growth on rerun: a rerun never increases the business-key count.
def test_watermark_never_moves_backwards_in_normal_runs(spark):
store = empty_store(spark)
run_days(spark, store, [8, 9, 10])
marks = [r["proposed_watermark"] for r in store.runs if r["status"] == "succeeded"]
assert len(marks) == 3 and all(r["source_rows"] > 0 for r in store.runs) # not vacuously true
assert marks == sorted(marks)
Assert invariants inside example tests too (several tests above check key uniqueness and conservation in passing), and in the production checks: they are the same rules, run on real data.
Metamorphic tests
A metamorphic test changes the shape of the input without changing its meaning, and asserts that the result doesn’t change. No expected output needs to be written by hand: the oracle is the other run. Three are especially useful for incremental loads.
Batch-partition invariant: processing the rows as one batch or as several must give the same final state. This catches any hidden dependence on batch boundaries.
HISTORY = {
8: [order(40, 1, at(9, day=8)), order(41, 1, at(10, day=8))],
9: [order(40, 2, at(9, day=9), status="SHIPPED"), order(42, 1, at(11, day=9)), order(41, 2, at(12, day=9), deleted=True)],
10: [order(40, 3, at(9, day=10), status="DELIVERED"), order(43, 1, at(10, day=10)), order(42, 2, at(11, day=10), amount="80.00")],
}
ALL_ROWS = [row for day in sorted(HISTORY) for row in HISTORY[day]]
def run_days(spark, store, days):
"""One run per day; each run sees everything the source holds by then and reads its own window."""
for day in days:
visible = [row for d in HISTORY if d <= day for row in HISTORY[d]]
run_incremental(store, source(spark, visible), at(23, 59, day=day), f"day-{day}")
def test_batch_partition_invariant(spark):
one_batch = empty_store(spark)
run_incremental(one_batch, source(spark, ALL_ROWS), at(23, 59), "all")
three_batches = empty_store(spark)
run_days(spark, three_batches, [8, 9, 10])
assert state(one_batch) == state(three_batches) == oracle(spark, ALL_ROWS)
Replay invariant: input plus the same input again gives the same final state. That’s the same-batch-twice test, generalised to a window in the middle of history:
def test_replaying_day_two_does_not_regress_newer_state(spark):
store = empty_store(spark)
run_days(spark, store, [8, 9, 10])
expected = state(store)
store.watermark = at(0, day=9) # rewind to the start of day 2 and replay it
run_incremental(store, source(spark, HISTORY[8] + HISTORY[9]), at(23, 59, day=9), "replay-day-2")
assert state(store) == expected # day 3's newer versions survive
assert store.target.count() == len({row[0] for row in ALL_ROWS})
Replaying day 2 after day 3 is where a merge without a version guard fails: day 2’s versions of orders 40 and 42 would overwrite day 3’s.
One side effect to notice: the test rewinds the watermark to replay, so afterwards it points at the end of day 2, and the next normal run re-reads day 3. That’s harmless here, because the re-read is idempotent, but it’s a design choice worth making explicitly. Many teams run replays as a separate job with its own window parameters, so the main watermark is never moved backwards.
Order invariant: shuffling the input rows must not change the result, if the business semantics allow it:
def test_input_order_does_not_change_the_result(spark):
shuffled = ALL_ROWS[:]
random.Random(7).shuffle(shuffled)
assert oracle(spark, shuffled) == oracle(spark, ALL_ROWS)
When these properties don’t hold, and shouldn’t:
- Batch partition breaks legitimately for aggregates computed per batch (a “rows received this run” measure) and for SCD Type 2 history, where splitting changes into more batches can create more intermediate versions. Compare only the current state, or design the test around the history rule.
- Replay breaks for append-only targets by design; test the downstream deduplication instead.
- Order breaks when arrival order is the business order and nothing else orders the data (no version, no sequence). That’s a design smell worth finding: the order test fails, and the fix is a version.
Recovery replay
The disaster-recovery version of the same idea: the target is lost, and it’s rebuilt by replaying retained raw batches.
def test_recovery_rebuild_from_retained_batches(spark):
rebuilt = empty_store(spark) # the target was lost; raw batches were retained
for day in sorted(HISTORY):
rebuilt.watermark = BEGINNING # replay each retained batch from scratch
run_incremental(rebuilt, source(spark, HISTORY[day]), at(23, 59, day=day), f"rebuild-{day}")
assert state(rebuilt) == oracle(spark, ALL_ROWS)
This is a test of three things at once: that raw input is retained (you can’t replay what you didn’t keep), that the version logic holds when history is replayed in bulk, and that nothing is silently lost on the way. Recovery and Disaster Recovery Architecture for Microsoft Fabric covers the recovery design; this test is the cheap, frequent version of a recovery drill, and belongs in the regular suite.
Mutation checks: do the tests catch real bugs?
A suite that has never failed hasn’t proven it can. Three deliberate bugs, each in a copy of the module, each run against the full suite:
| Mutation | What it simulates | Tests that failed |
|---|---|---|
Upper bound < instead of <= |
Rows exactly at the boundary are never read | test_each_boundary_row_is_read_exactly_once |
| Version guard removed (newest arrival wins) | An older update overwrites a newer one | test_an_older_version_never_overwrites_a_newer_one, test_soft_delete_is_not_resurrected_by_an_older_update, test_replaying_day_two_does_not_regress_newer_state |
| Watermark advanced before the merge | Progress claimed before the data exists | test_crash_after_merge_then_rerun |
Every mutation was caught by at least one test that names the guarantee it breaks. That’s the bar: if a plausible bug can be introduced without a test failing, a test is missing.
The first row deserves a second look: only the dedicated boundary test caught it. None of the other datasets happens to have a row at exactly a run’s upper bound, so every oracle comparison still passed. Boundary bugs need boundary data; an oracle can only find differences the input can produce.
The oracle also caught a bug in the tests themselves while I was writing them. The multi-day helper first used the history’s keys (1, 2, 3) as calendar days, so every daily run read an empty window. The oracle comparisons failed immediately. The watermark-monotonicity test passed: three empty runs have perfectly ordered watermarks. That’s why it now also asserts that each run read rows. An invariant can be vacuously true; an oracle comparison can’t.
The same pattern in T-SQL
In SQL Server, the same load is a staging table, a deduplication with ROW_NUMBER, a version-guarded upsert and a watermark table. These scripts were not executed for this guide.
-- One row per order: the highest source version in the window
WITH ranked AS (
SELECT s.*,
ROW_NUMBER() OVER (PARTITION BY s.order_id ORDER BY s.version DESC) AS rn
FROM stg.orders AS s
WHERE s.updated_at > @window_start
AND s.updated_at <= @window_end
)
SELECT order_id, customer_id, updated_at, version, status, amount, is_deleted
INTO #changes
FROM ranked
WHERE rn = 1;
BEGIN TRANSACTION;
-- Update only when the incoming version is newer: older or equal versions are no-ops
UPDATE t
SET t.customer_id = c.customer_id, t.updated_at = c.updated_at, t.version = c.version,
t.status = c.status, t.amount = c.amount, t.is_deleted = c.is_deleted
FROM dbo.orders AS t
JOIN #changes AS c ON c.order_id = t.order_id
WHERE c.version > t.version;
INSERT INTO dbo.orders (order_id, customer_id, updated_at, version, status, amount, is_deleted)
SELECT c.order_id, c.customer_id, c.updated_at, c.version, c.status, c.amount, c.is_deleted
FROM #changes AS c
WHERE NOT EXISTS (SELECT 1 FROM dbo.orders AS t WHERE t.order_id = c.order_id);
-- Same transaction: the watermark can't move without the data, or the data without it
UPDATE etl.watermark
SET watermark_value = @window_end, last_successful_run_id = @run_id, updated_at = SYSUTCDATETIME()
WHERE pipeline_name = 'orders_incremental';
COMMIT TRANSACTION;The WHERE c.version > t.version predicate is the version guard; drop it and the out-of-order test fails. The validation step (rejecting rows without a customer, quarantining them) sits between the deduplication and the transaction, as in the PySpark model. Run the same test cases against a disposable database: insert, update, unchanged, older version, duplicate in staging, empty window, and a crash simulated by an error raised before COMMIT, after which the watermark must be unchanged. A single MERGE statement with WHEN MATCHED AND source.version > target.version is an equivalent alternative.
Incremental tests in CI/CD
- Code change
- Unit testsWindow, dedup, merge, rules
- Incremental invariantsOracle, partition, replay
- Deploy to Test
- Integration loadReal Delta or SQL target
- Replay testSame window twice
- Quality checks
- Promote
Block on any failure of: same batch twice, late-arrival recovery, older-version protection, watermark-boundary completeness, partial-failure recovery, or the oracle comparison. These are correctness failures with no “minor” version. A held batch in the integration test with clean test data also blocks: the rules or the data are wrong. Investigate, policy-based: runtime of the integration load against its baseline.
The integration tests repeat the most important cases against the real target: a real Delta MERGE (or the SQL transaction) on a test table, run twice with the same batch, with a crash simulated between the merge and the watermark update. They are slower and need infrastructure, which is why the unit-level model above carries most of the case coverage. In Fabric, run them as pipeline tests, as in Testing Microsoft Fabric Pipelines and Notebooks.
Production canary
After deployment, validate with a small controlled batch, never by replaying production history:
- a canary order (a known test key in a source that allows it, or a dedicated canary source) flows through, and its expected state appears;
- the watermark advanced, once, to the expected upper bound;
- no duplicate business keys exist after the run;
- the execution log has the run, with counts that add up;
- the quality results for the run are recorded and pass.
Nothing in a production canary should rewind a watermark, replay a window or write test rows into tables consumers read. Those tests belong in Test, where the replay test already ran.
Execution evidence
Every run should record enough to explain it later, and enough for the tests above to assert on:
| Field | Why |
|---|---|
run_id |
Correlates the run’s writes, quality results and log |
source_interval |
The window actually read (start, end, lookback) |
previous_watermark, proposed_watermark |
What state moved from and to; whether it moved |
source_rows, duplicates |
What was read, and how much was collapsed |
inserted, updated, ignored, rejected |
Conservation checks |
status, quality_status |
Succeeded, held, failed; and why |
start_time, end_time |
Duration baselines |
version |
The commit or release that ran |
The model’s run dictionary is a minimal version of this record. Designing an Execution Log for Microsoft Fabric Pipelines and Notebooks covers the production design, including how reruns are recorded.
How the change-detection choice affects testing
The incremental-loads article explains how to choose a change-detection strategy. Each choice moves where the testing effort goes:
| Strategy | What to test hardest |
|---|---|
| Timestamp watermark | Boundaries, late rows (lookback), equal timestamps, clock source |
| CDC / log position | Checkpoint is the position; resumption after a gap; deletes |
| Source sequence or version | Out-of-order arrival; the version guard; conflicts at the same version |
| Replay window | Idempotency over the whole window; no regression of newer state |
Anti-patterns
| Anti-pattern | Why it fails |
|---|---|
| Watermark advances before writes finish | A crash loses the window silently |
| Timestamp-only ordering with ties | Ties resolve differently on different runs |
| Assuming retries imply idempotency | A retry is a rerun; without idempotency it duplicates |
| No duplicate-input test | The first source retry doubles a day’s revenue |
| No late-arrival test | Late rows are lost without a single error |
| Only testing the insert path | Updates, deletes and older versions carry the bugs |
| Full refresh used to hide broken incremental logic | The incremental path stays broken; recovery depends on it |
| Replay impossible | No raw retention or no idempotency: recovery can’t happen |
| State stored only inside the orchestrator | It can’t be inspected, tested or restored |
| No raw retention | Nothing to replay after an incident |
| Comparing only row counts | Counts match while versions are wrong |
| Tests that depend on the current time | Pass today, fail at midnight |
| No reconciliation | Rows vanish between steps unnoticed |
| Retrying bad data forever | Burns capacity; the window never advances |
Before calling an incremental pipeline safe
- Initial load tested
- Inserts tested
- Updates tested
- Same batch twice tested
- Duplicate source keys tested
- Late data tested
- Watermark boundaries tested
- Out-of-order updates tested
- Empty batch tested
- Partial failure tested
- Replay tested
- Quality failure tested
- Reconciliation tested
- Execution state recorded
- Recovery replay tested
What to read next
- Incremental Loads in Microsoft Fabric: how to build the load these tests prove.
- Testing Microsoft Fabric Pipelines and Notebooks: running the same tests as Fabric pipeline tests.
- Data Quality Checks for Data Pipelines: the rules that decide whether a batch is published or held.
- Recovery and Disaster Recovery Architecture for Microsoft Fabric: replay and rebuild at platform scale.
- PySpark DataFrame Unit Testing with pytest: the fixtures and equality helpers these tests use.
- Incremental load patterns cheat sheet: the patterns at a glance.
Sources
- PySpark: Testing PySpark,
assertDataFrameEqual,DataFrame.localCheckpoint - Delta Lake: Upsert into a table using merge (merge semantics, multiple source rows matching one target row)
- pytest: Fixtures, Parametrizing tests
Last verified: November 2026. The PySpark model and its 22 tests were executed with PySpark 3.5.9, pytest 9.2.0, Python 3.12.10 and Java 21, with three mutation checks; the T-SQL and Delta integration examples were not executed. The guarantees, oracle pattern, invariants, blocking guidance and checklist are Javi Builds guidance.
Ce contenu vous a-t-il été utile ?
Que pourrions-nous améliorer ?
Merci — cela m’aide à améliorer Javi Builds.
Continuer à apprendre
- Aide-mémoireLire et écrire des tables DeltaLire et écrire des tables Delta avec PySpark à l’aide de modèles sûrs d’ajout (append), d’écrasement (overwrite), de merge, d’évolution du schéma, de partitionnement et de maintenance pour les pipelines de données en production.Ouvrir l’aide-mémoire
- RessourceContenu en anglaisFabric Notebook Performance ChecklistA practical pre-run, read, transform, write and orchestration checklist for faster and more reliable Microsoft Fabric notebooks.Voir la ressource
Que devrais-je créer ensuite ?
Je développe Javi Builds autour des problèmes concrets rencontrés par les data engineers. Dites-moi quel sujet vous aimeriez voir ensuite.
Restez au courant
Recevez uniquement les nouvelles de Javi Builds qui vous intéressent.
Consultez votre boîte de réception
Je vous ai envoyé un lien de confirmation. Votre abonnement sera actif après confirmation.