Automated cross-system load validation

A nightly framework that compares metrics between a legacy warehouse and its replacement, and reports only the differences that actually matter.

The problem

Running two warehouses in parallel during a migration means someone has to answer “do these agree?” every morning. Doing it by hand does not scale past a couple of entities, and the naive automated version is worse than useless: it fires on every difference, most of which are timing artifacts rather than real defects.

Approach

Three ideas did most of the work.

Windowed cutoff dates, per entity. The two systems do not finish loading at the same time. Comparing everything up to “now” guarantees the newer rows look missing on one side. Each entity gets its own cutoff derived from its actual load watermark, so the comparison only covers the window both systems have finished writing.

Tolerances with gates. Currency columns get a small percentage tolerance, because rounding differences between two engines are real and not worth waking up for. But tolerance alone is dangerous: two totals can land within tolerance while the underlying row counts are wildly different. So every tolerance check is gated on exact-match columns — distinct document counts, quantity sums — that must agree before a tolerance pass is allowed to count as a pass.

Directional status, not boolean. “Mismatch” is not actionable. “Target is behind source by N documents” is. Every difference is reported with a direction and a magnitude so the reader knows which system to go look at.

What was harder than expected

Rendering the report. The output goes out as email, and email clients are not browsers — the layout had to be rebuilt with inline styles, presentational tables, and explicit margin resets on every paragraph before it rendered consistently. Roughly as much iteration went into the report being readable as went into the comparison logic being correct, which in hindsight is the right ratio: a correct result nobody reads has no value.

Outcome

Manual reconciliation dropped from a recurring multi-hour task to reading one email. Load-timing false positives went to zero.