Why Row Counts Aren't Enough: Type-Aware Checksum Validation

Every migration tool reports row counts. Counts catch dropped rows; they say nothing about mangled ones. A timestamp shifted by a timezone, a decimal rounded through a float, an emoji flattened by an encoding — all invisible to COUNT(*). Real validation means comparing the data itself.

The naive approach fails immediately

Hash each row's string representation and compare? Two engines disagree about the representation of identical values. The same logical row produces different bytes on each side:

• MySQL returns a TIME as a duration object; PostgreSQL returns a time-of-day.
• Oracle pads CHAR columns with spaces — and treats '' as NULL.
• A float32 read back through float64 grows noise digits: -3.14 → -3.140000104904175.
• A MySQL SET is 'a,b'; its PostgreSQL TEXT[] counterpart is {a,b}.
• SQLite stores your datetime as an ISO string, because SQLite stores everything as whatever it wants.

Canonicalization: one logical value, one hash

DBShifts's validator maps every value to a canonical form before hashing, guided by the column's type: durations and time-of-day collapse to one form, CHAR padding is stripped, single precision floats compare at float32's actual precision, date-at-midnight equals date, ISO strings parse back into timestamps, arrays compare as parsed structures regardless of serialization. Engine quirks get pair-scoped rules — '' ≡ NULL only when Oracle is involved, never between engines that distinguish them.

Order-independent, full-table

Source and target return rows in different physical orders, so per-row hashes are combined with a commutative accumulator — order can't matter. And because the accumulator is constant-memory, the full-table mode verifies every row, not a sample, without holding tables in RAM.

validate cert_types
  ✓ row counts     5 = 5
  ✓ checksum       order-independent, 24 columns canonicalized
  ✓ fk integrity   0 orphans
  ✓ null counts    match per column

Drift is not corruption

One more distinction that saves 3 a.m. panics: if the source keeps serving writes after the snapshot, source and target legitimately diverge. The validator classifies mismatches — rows missing on the target that were inserted after the cut are drift, expected on a live source; value mismatches in rows both sides share are corruption, never expected. The report tells you which one you're looking at.