A backfill can match row counts and totals while still being wrong

The backfill completed. Row counts match. The USD total matches. Two payment amounts are still wrong.

A count and a control total are useful checks, but different datasets can satisfy both. Here is a small counterexample you can run without a warehouse.

Three candidates, one summary

The expected payment rows contain 100, 200 and 300 minor units. Each row is identified by provider, payment and currency.

Now consider three candidate outputs:

Candidate	Amounts	Defect
Changed values	p1=100, p2=250, p3=250	Two amounts changed and cancel out in the total
Swapped identity	p1=100, p2=200, p4=300	p3 disappeared and an unrelated p4 appeared
Duplicate key	p1=100, p1=200, p3=300	The expected grain is violated

Every candidate has three rows and a USD total of 600. None equals the expected result.

Compare identities before values

The script below checks uniqueness before building a dictionary. Then it reports missing keys, extra keys and changed values on shared keys.

That ordering matters: converting duplicate keys straight into a dictionary would overwrite one row and hide a defect in the input.

Save this as backfill_compare_demo.py and run it with Python. It uses only the standard library.

from collections import defaultdict


def summary(rows):
    totals = defaultdict(int)
    for provider, payment, currency, amount in rows:
        totals[currency] += amount
    return len(rows), dict(totals)


def keyed(rows):
    result = {}
    for provider, payment, currency, amount in rows:
        key = (provider, payment, currency)
        if key in result:
            raise ValueError(f"duplicate key: {key}")
        result[key] = amount
    return result


def compare(expected, candidate):
    left, right = keyed(expected), keyed(candidate)
    return {
        "missing": sorted(left.keys() - right.keys()),
        "extra": sorted(right.keys() - left.keys()),
        "changed": [(key, left[key], right[key])
                    for key in sorted(left.keys() & right.keys())
                    if left[key] != right[key]],
    }


expected = [("A", "p1", "USD", 100), ("A", "p2", "USD", 200),
            ("A", "p3", "USD", 300)]
changed = [("A", "p1", "USD", 100), ("A", "p2", "USD", 250),
           ("A", "p3", "USD", 250)]
swapped = [("A", "p1", "USD", 100), ("A", "p2", "USD", 200),
           ("A", "p4", "USD", 300)]
duplicate = [("A", "p1", "USD", 100), ("A", "p1", "USD", 200),
             ("A", "p3", "USD", 300)]

for candidate in [changed, swapped, duplicate]:
    assert summary(candidate) == summary(expected) == (3, {"USD": 600})
assert compare(expected, changed) == {
    "missing": [], "extra": [],
    "changed": [(("A", "p2", "USD"), 200, 250),
                (("A", "p3", "USD"), 300, 250)],
}
assert compare(expected, swapped) == {
    "missing": [("A", "p3", "USD")],
    "extra": [("A", "p4", "USD")], "changed": [],
}
assert compare(expected, list(reversed(expected))) == {
    "missing": [], "extra": [], "changed": [],
}
try:
    compare(expected, duplicate)
except ValueError:
    pass
else:
    raise AssertionError("duplicate key accepted")
print("all candidate counts and totals:", summary(expected))
print("changed amounts:", compare(expected, changed))
print("swapped identity:", compare(expected, swapped))
print("duplicate rejection and order independence: passed")

The local run produced:

all candidate counts and totals: (3, {'USD': 600})
changed amounts: {'missing': [], 'extra': [], 'changed': [(('A', 'p2', 'USD'), 200, 250), (('A', 'p3', 'USD'), 300, 250)]}
swapped identity: {'missing': [('A', 'p3', 'USD')], 'extra': [('A', 'p4', 'USD')], 'changed': []}
duplicate rejection and order independence: passed

The example also reverses the expected rows and confirms that physical row order does not create a difference. The contract is about keyed payment state, not file ordering.

Choose an expected result that answers the right question

A backfill may intentionally change business results. Comparing it with yesterday’s production table and rejecting every changed value would prevent the correction you were trying to make.

Define the intended source snapshot, transformation rules and output scope first. The expected rows in this experiment are an independently specified fixture. In a real rebuild, the baseline might be a separately calculated canary result or an approved restatement, with expected changes recorded explicitly.

A difference report tells you what changed. It does not decide whether the change was intended. A fully matching output also does not prove correctness if both sides use the same flawed logic.

For that reason, keep at least one small case with expected rows written from the business rule, rather than generating every expectation with the transformation being tested.

Keep the comparison contract narrow

This script compares only an integer amount at the grain (provider, payment, currency). If the published output also includes status, effective date, allocation or classification, decide which of those fields belong in the payload comparison.

Totals are grouped by currency; summing USD and EUR minor units together would be a different error. The fixture happens to use one currency, but the summary function keeps currency separate.

The dictionaries fit in memory and assume well-formed tuples. This is a teaching example, not a distributed comparison engine. For large data, the same categories can be computed with validated unique keys and joins in the warehouse. Nulls, decimals, timestamps and allowed tolerances need explicit equality rules.

Validation comes before publication

I maintain a free fintech data engineering roadmap. The backfill orchestration lesson asks learners to start with a canary period, reserve daily capacity, and define stop conditions.

The atomic publication lesson separates candidate computation from exposing a result to consumers. The mechanism needs to match the platform’s actual guarantees; this Python comparison does not implement a publication switch.

A useful review artifact is a candidate version, its source/rule manifest, the key-level differences, the expected exceptions, and the decision to publish or stop. Keep the prior version available under the agreed rollback plan.

For your next test, add equal and opposite errors in different payment rows. If the only assertion is a matching grand total, that test should show exactly what the gate misses.