Post-Cutover Reconciliation and Drift Detection

The switch is followed by a period in which two systems may both hold data that claims to be authoritative. Reconciliation is the process of establishing that they agree, or of finding exactly where they do not. It is not a verification step that can be skipped when the migration is urgent, because the alternative to knowing whether the two agree is not knowing, and a divergence that is discovered later costs more to repair than one discovered immediately.

Agreement is a claim about rows, not about files

The natural verification is a checksum: hash the whole dataset on both sides and compare the digests. It is cheap and it answers the wrong question.

A whole-dataset digest tells whether the two are identical and nothing about where they differ. When it disagrees, which is the case that matters, the result is a single bit of information and the investigation starts from nothing. On a dataset of a terabyte, narrowing down a mismatch by bisection costs a sequence of re-reads whose total volume is comparable to the dataset itself, and each round requires both sides to be quiescent or to be snapshotted consistently.

A per-partition digest is far more useful, and the structure to compute it is a hash tree. Leaves are hashes of individual rows or blocks, and each internal node is the hash of its children’s hashes. Comparing two trees top-down identifies the differing subtrees in time proportional to the depth times the number of differing leaves, rather than proportional to the dataset. This is the structure Merkle described for signature schemes, and it is the same structure used by content-addressed stores and by synchronisation protocols.

The construction requires a defined ordering of the leaves, because the tree is only comparable if both sides build it over the same sequence. Ordering by primary key is the usual choice and it has a hidden dependency: the two systems must order keys identically. A collation difference turns a key-ordered hash tree into a comparison of two different permutations, which reports a mismatch everywhere.

Hashing rows without hashing the encoding

A row hash must be computed over a canonical serialisation, not over the storage format. The same logical row stored in two systems will differ in column order, in null representation, in numeric encoding, and in timestamp precision.

Three rules make a row hash comparable:

Normalise types explicitly. A timestamp with microsecond precision and one with millisecond precision produce different bytes for the same instant. Casting both to a common representation, or truncating both to the coarser precision, is required and it changes what the comparison can detect. Truncating to seconds makes the check blind to sub-second differences, which may be exactly the difference that matters for an ordering-sensitive workload.

Exclude generated and volatile columns. A row’s physical identifier, its insertion timestamp, and any column the destination computes differently will differ for reasons unrelated to the data. Excluding them is not optional; including them makes every row mismatch.

Encode nulls distinctly from empty values. A null string and an empty string are different values in most schemas and identical in many serialisations. A hash that conflates them will accept a migration that silently converted one to the other.

A canonicalisation function that does this is small. The following is Python, using only the standard library:

import hashlib, json, datetime

def canonical(row, columns):
    out = []
    for name in columns:                 # explicit column order, not storage order
        v = row[name]
        if v is None:
            out.append(["n"])            # null is tagged, never conflated with ''
        elif isinstance(v, datetime.datetime):
            # Truncate to the precision the comparison is meant to cover.
            out.append(["t", v.replace(microsecond=0).isoformat()])
        elif isinstance(v, bytes):
            out.append(["b", v.hex()])
        else:
            out.append(["v", v])
    return json.dumps(out, sort_keys=True, separators=(",", ":"))

def row_digest(row, columns):
    return hashlib.sha256(canonical(row, columns).encode()).digest()

The digest is over the canonical form and not over the storage encoding, which is what makes it comparable across systems. The tag on each value (“n”, “t”, “b”, “v”) prevents a null from hashing the same as the string “null”, which is a real collision when the serialiser is not careful.

Sampling tells the truth about rates and lies about presence

Hashing every row is expensive and usually unnecessary. Sampling a fraction of rows and comparing their digests gives an estimate of the mismatch rate, and the estimate is statistically sound if the sample is uniform.

What it cannot do is establish absence. A sample of one row in a thousand that finds no mismatch is consistent with a dataset containing a hundred mismatched rows. The confidence interval depends on the dataset size and the sample size, and the useful statement is “at most this fraction mismatched, with this confidence”, not “the data agrees”. Writing the first statement into the migration record is honest; writing the second is not.

Presence failures are systematically under-sampled for a second reason. A row that failed to copy entirely is missing from the destination and present in the source, and a sample keyed on the destination’s keys never sees it. Comparing counts per partition catches gross omissions and misses a missing row that was compensated by a spurious extra row in the same partition. Comparing key ranges catches both, which is why a per-partition comparison should include a key-range digest as well as a content digest.

Divergence that appears after the switch

The comparison performed at the switch establishes agreement at one instant. Divergence afterwards comes from three sources, and each needs a different detector.

Writes that still reach the old system. A client that has not re-resolved, or a batch job that holds a configured endpoint, keeps writing to the source. The detector is traffic measurement at the source, and the remedy is to make the source read-only at the switch rather than merely deprecated. A source left writable will accumulate changes that must be reconciled or discarded, and the decision of which is not obvious.

Writes that reach neither system. A client whose endpoint is misconfigured fails, and the failure may be silent if the client swallows errors. The detector is a comparison of write volume between the two systems over time, and the remedy is to find the client, which requires knowing which clients exist, which is the inventory problem again.

Writes that reach the destination and are rejected. A schema constraint, a length limit, or a type that the source accepted and the destination does not. These show up as a difference in accepted-write counts and as errors in the destination’s logs. They are the most dangerous of the three, because the client may have received a success from an intermediate layer while the destination rejected the record.

A recurring comparison closes all three. Running the same per-partition digest on a schedule, and alerting on any partition whose digest changes independently on one side, detects divergence within one interval of its occurrence. The cost is a periodic read of the whole dataset, which for a large dataset is a real load and must be scheduled against it.

Reconciliation is a merge, not a comparison

When a mismatch is found, the response is to decide which side is correct for each differing row, and that decision cannot be made from the hashes. It requires the row’s history: when it changed on each side, and what changed it.

Three outcomes are possible for a differing row. The destination is correct and the source’s copy is stale, which is the normal case after a switch. The source is correct and the destination missed an update, which happens when the change stream lagged at the switch. Both are wrong, in the sense that a write landed on each side with different content, which happens during a dual-write interval with a partial failure.

The third case is the one that requires a rule. Where both sides accepted a write to the same key and the writes differ, one of them must be chosen. Choosing by timestamp requires the clocks to be comparable, which they are only if both systems took the time from a synchronised source. Choosing by origin requires knowing which side was authoritative at the moment of the write, which requires the write to carry that information. Neither rule can be applied retroactively to writes that did not record it, and that is the reason the dual-write design should stamp each write with its origin before the cutover rather than after.

The result of a reconciliation pass is a set of applied corrections, each of which should be recorded with the key, the chosen value, and the rule that chose it. That record is the audit trail, and it is also the input to the next question: whether the same class of divergence can recur. A divergence class that recurs after being corrected once indicates that the mechanism producing it is still running, and the correction is treating a symptom. Finding and stopping that mechanism is what allows the source to be decommissioned with the confidence that nothing is still writing to it.