Old and new stores both hold 42 million customer records during a coexistence period. The nightly full-row comparison now takes 6 hours 40 minutes against a 4-hour window, and the legacy side serves the export at about 1800 rows per second. Roughly what does a hash-bucket comparison cost instead, and which assumption decides whether it fits?

The assumptions, stated

• 42 million rows on each side, canonical form about 1.7 kB per row, so roughly 70 GB per side.
• The legacy export path sustains about 1,800 rows a second. That is the binding rate: 42,000,000 / 1,800 is about 23,300 seconds, or 6 hours 29 minutes, which matches the observed 6 hours 40 minutes.
• Both engines can compute an aggregate hash inside the database over a range of the primary key.
• The expected number of genuinely differing rows per night is on the order of a few hundred, from in-flight writes and a known lag of a few seconds.

The arithmetic

A hash-bucket comparison replaces the export with a scan. Partition the key space into B buckets, have each side compute one aggregate hash per bucket in SQL, and ship 2B hashes instead of 84 million rows.

• Hashing cost: a sequential scan of a 70 GB table at roughly 400 MB/s is about 3 minutes of I/O, and per-row hashing at roughly 60,000 rows per second per core across 8 cores is about 90 seconds of CPU. Call it 5 minutes a side, versus 6 and a half hours. Two orders of magnitude, because the row never leaves the database and is never serialised across the wire.
• Comparison cost: with B = 4,096, that is 8,192 hashes. Trivial.
• Descent cost: for every bucket whose hashes differ, fetch the rows in that bucket and compare them properly. Each bucket holds about 10,250 rows, so one mismatching bucket costs about 6 seconds of export.

The descent is where the sizing decision lives. If 300 differing rows scatter uniformly across 4,096 buckets, about 290 buckets are dirty, so you export 290 × 10,250 rows — roughly 3 million rows, or 28 minutes. Total: about 40 minutes. It fits.

Which assumption dominates the error

Not the row count, and not the hash rate. It is whether the two engines agree on a canonical row serialisation. Timestamp precision, numeric scale, null versus empty string, character case in identifiers, trailing whitespace and collation all change the hash without changing the meaning. Get one of them wrong and the scheme fails in the most expensive way available: every bucket is dirty, the descent degenerates into the full export you were escaping, and the run takes longer than before while reporting a mismatch rate near 100%. Prove the canonical form on a thousand known-identical rows in production before trusting a single bucket result.

The second assumption is the expected difference count, because bucket sizing is set by it rather than by the row count. Choose roughly ten times as many buckets as expected differing rows, so most buckets stay clean; at 300 differences, 4,096 buckets is right and 64 buckets would be useless.

What the number rules in or out

40 minutes inside a 4-hour window means you can reconcile nightly for the whole coexistence period, and it leaves room to reconcile the high-value entity hourly. 6 hours 40 minutes means you cannot, which is how reconciliation quietly becomes weekly, then monthly, then a report nobody reads. The cost of verification is what decides how long a coexistence period is safe, so it belongs in the design rather than in operations.

When this is the wrong answer

If the two sides have genuinely different schemas and the transformation is lossy, there is no canonical form and hashing compares nothing: compare derived business values instead, and accept a sampled check. If there is a trustworthy modified-at column on both sides, a watermark comparison of only the rows changed since the last run is cheaper than any hashing scheme and should be tried first. And if the entity count is under a million, the full comparison already fits and this is complexity bought for nothing.