DevOps y observabilidad

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.

Por JaviPublicado 28 min de lecturaAvanzado

En este artículo

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.

  1. Take a test dataset: every version of every order the source will ever hold during the test.
  2. Run the transformation once, as a full load, over all of it. That result is the oracle.
  3. Run the same data incrementally: in batches, with reruns, late rows, replays and failures.
  4. 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
  1. Final business state
  2. AssertionEquals the full-load oracle
Many incremental paths, one expected state. However the same data is split, repeated, delayed or replayed, a deterministic incremental load must end in the state a single full load produces. Each path is one test.

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
    1. Read window
    2. Merge target
    3. Before the watermarkCrash
    4. Rerun reads the same windowMerge repeats; result unchanged
  • Watermark firstDangerous
    1. Read window
    2. Claims success earlyAdvance watermark
    3. Before the mergeCrash
    4. Rerun skips the windowRows lost; no error anywhere
The same crash, two designs. Each column is one run that crashes at the marked step. Watermark last: the rerun reads the same window and the idempotent merge makes it harmless. Watermark first: the rerun skips a window that was never written.

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 + ignored equals 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.

Consulta SQL: Incremental load with a version guardT-SQL
-- 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;
T-SQL34 líneasVista de solo lectura. No está conectada a ninguna base de datos.

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

  1. Code change
  2. Unit testsWindow, dedup, merge, rules
  3. Incremental invariantsOracle, partition, replay
  4. Deploy to Test
  5. Integration loadReal Delta or SQL target
  6. Replay testSame window twice
  7. Quality checks
  8. Promote
Incremental-load tests in a release. Unit tests and invariants run on every pull request with local Spark; integration and replay tests run on real storage in the test environment; promotion waits for all of them.

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

Sources

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.

Etiquetas

  • Testing
  • Cargas incrementales
  • MERGE
  • PySpark
  • Pipelines de datos