Reconciling a Load: Counting Rows Is the Weakest Possible Check

A load is finished, the counts match, and the pipeline is declared correct. The counts matching is the weakest evidence available, and it is the evidence most pipelines use.

Two tables with the same row count can differ in every row. One missing row and one duplicated row cancel exactly. A transformation that drops rows in one range and duplicates them in another leaves the total untouched. A filter that excludes a customer’s rows entirely is invisible if another customer’s rows are counted twice.

Reconciliation is the practice of establishing, after the fact, that the sink contains what the source contains. Its methods form a ladder, and each rung costs more than the one below while catching strictly more.

Why the comparison needs a position

Any comparison between a source and a sink is a comparison at an instant, and the source does not stop changing. If the sink is at position $P$ and the source has moved to $P’$, the source’s current contents include changes the sink has not yet received, and the difference is expected rather than a defect.

A sound comparison therefore freezes both sides at the same point. In practice this means recording the sink’s applied position, meaning the maximum source position present in the sink rather than the position the consumer has read, as distinguished in offset commit ordering — and comparing the sink against the source as of that position.

For a source whose history is queryable as of a position, that is a snapshot read at the position. For a source that only has a current state, it is impossible to reconstruct exactly, and the comparison has to accept a tolerance: the sink’s contents plus the changes in the source’s log after $P$ should equal the source’s current contents. Applying those changes to a copy of the sink is a small replay, and it is often cheaper than it sounds when the log volume since the last check is small.

The distinction between read position and applied position is where reconciliation most often goes wrong. A consumer that has read to $P’$ but committed to $P$ reports a lag of zero on its read metric while the sink is behind, and a comparison against the source at $P’$ finds spurious differences.

Rung one: counts, and when they are enough

A COUNT(*) on both sides, restricted to the same key range and the same position, is cheap: a scan of an index, or a scan of a covering index, rather than a scan of the table. It catches gross failures: a connector that stopped, a range that was never loaded, a filter that excluded everything.

It catches nothing subtle. The specific case it cannot catch is a compensating error, and compensating errors are common in exactly the failures reconciliation is meant to detect. A deduplication bug that drops one row per batch and an upsert bug that duplicates one row per batch produce a stable count and a corrupt table.

Counts are also expensive in the wrong dimension when the sink is partitioned or distributed: a COUNT(*) over a large table is a full scan of an index on every partition, and its cost grows with the table rather than with the change volume. For a table that is checked daily and changes by a fraction of a percent per day, the count reads a thousand times more data than it needs to.

The incremental form fixes that: count per key range or per partition, and compare the counts per range rather than in total. A compensating error across two ranges then shows as a difference in each, which converts the cheap check into a useful one at no extra cost.

Rung two: key sets

Comparing the set of keys catches every case where a row is missing or duplicated, because the key set’s size and membership are both checked. The comparison is a set difference in both directions: keys in the source and not in the sink, and keys in the sink and not in the source.

The second direction is the one that finds rows the source deleted and the sink retained, which a count cannot distinguish from a row the source inserted and the sink lost.

Executing the difference requires both key sets to be enumerable in a comparable order, which for large tables means either streaming both sides in key order and merging, or hashing each side and comparing hashes per range. The streaming merge is exact and costs one sequential pass over each side; the hash comparison is cheaper to transport but introduces collisions, and the collision probability depends on the hash width. A 64-bit hash over a billion keys has a birthday collision probability near 3 percent, which is too high to treat as negligible; a 128-bit hash makes it negligible.

The practical form is per-range: for each split or partition, compute a hash of the sorted key set, and compare the hashes. A mismatched range is then investigated by streaming that range’s keys. This localises the work to the ranges that differ, which is what makes the check affordable on a table that is mostly correct.

Rung three: values

Keys matching says the right rows exist. It says nothing about their contents, and a connector that maps a timestamp with the wrong timezone, or a decimal with the wrong scale, produces matching keys and wrong values.

Comparing values requires a hash over the row’s canonical representation, and the canonicalisation is the hard part. The same logical value has many representations: a timestamp with a timezone renders differently depending on the session’s zone, a decimal renders with or without trailing zeros, a string’s Unicode normalisation form differs, and a float’s shortest representation is not unique.

A hash computed over the source’s text rendering and the sink’s text rendering will differ for values that are equal, producing false positives that make the check useless. The canonicalisation has to be defined explicitly: a fixed date format in a fixed zone, a fixed decimal scale, a fixed normalisation form, a fixed null representation. Once defined, the hash is a reliable comparison and the false positive rate falls to zero.

The hash’s cost is a full read of both sides, which for a large table is the most expensive operation in the pipeline. It is affordable when restricted to the rows that changed since the last check, which requires the sink to know each row’s source position, the version column that backfill also relies on.

Hashing rows in a stable order

A hash over a set of rows is order-dependent unless the rows are sorted or the hash is commutative. Sorting a billion rows is expensive; a commutative combination such as summing per-row hashes is cheap but has a specific weakness: two rows whose hashes sum to the same value as two other rows produce the same total, and addition has no collision resistance beyond the hash’s own width.

The standard construction is a sorted merge: stream both sides in key order, hash each row’s canonical bytes, and combine with a rolling hash that includes the position, so that a row’s contribution depends on where it appears. Then a single missing row changes every subsequent contribution, and the final hash differs.

import hashlib

def range_digest(rows):
    h = hashlib.blake2b(digest_size=16)
    for key, payload in rows:              # rows already in key order
        h.update(key.encode('utf-8'))
        h.update(b'\x00')
        h.update(payload)                  # canonical bytes, fixed encoding
        h.update(b'\x1e')
    return h.hexdigest()

The separator bytes matter: without them, a key of ab followed by payload c hashes identically to a key of a followed by payload bc. Any delimiter that cannot appear in either field fixes that, and the choice of delimiter is a correctness detail rather than a cosmetic one.

The rolling construction makes the digest sensitive to order, which is what catches a row that was written at the wrong position, a plausible symptom of a partition misassignment in a parallel read.

Tolerating the differences that are expected

A reconciliation that reports every difference is a reconciliation nobody reads. Real pipelines have known, expected divergences, and the check has to exclude them without excluding the class they belong to.

A sink that applies a transformation by design differs from the source by the transformation. Comparing raw source values against transformed sink values requires applying the transformation to the source side of the comparison, which means the check shares code with the pipeline, and a bug in the transformation then appears on both sides and cancels. That is the fundamental limit of reconciliation: it can verify transport, not logic, when it reuses the logic under test.

The way out is to compare at a boundary before the transformation, or to compare against an independently computed value. Neither is free, and most pipelines accept that reconciliation verifies that the rows arrived rather than that they are semantically right.

A sink that holds only the latest version of a row and a source that holds history differ by construction. Comparing them requires the source side to be reduced to its latest version at the same position, which is a query the source may not support efficiently.

Frequency and the cost curve

The cost of a full value comparison scales with the table, and the value of the check scales with the change volume since the last check. For a table that changes slowly, daily full comparisons are mostly wasted work; for a table that changes completely every day, a weekly comparison finds the divergence long after it began.

The compromise is a tiered schedule: cheap key-set comparisons frequently, value comparisons over changed ranges frequently, and a full value comparison rarely, as a backstop against drift that the incremental checks cannot see: a row modified in place without a position change, which a capture stream that missed the modification would not have stamped.

The last case is the one that justifies the expensive check. A row whose contents changed without its version advancing is invisible to every incremental comparison, and it arises from a source that writes without updating the version column, from a bulk load that bypasses the application, or from a manual correction applied directly to the sink. The full comparison is the only thing that finds it, which is why it is worth running occasionally even when everything else is green.