Packvane

Reconciling a Parallel Run: Row Counts, Checksums, and the Rows That Move

A parallel run is the only cutover evidence anyone should accept: the old estate and the new one load the same day's data into two places, and you compare them. It is also the step most often cut for time, on the reasoning that the tests passed. The tests passed on test data.

Here is the harness we use. It is deliberately simple, because a reconciliation you cannot explain to a finance director at 07:00 is not evidence.

Layer 1: row counts

Cheap, fast, and catches the large failures — a filter that changed meaning, a join that became an inner join, a truncate that did not run.

SELECT 'legacy' AS side, COUNT(*) AS rows FROM legacy.dbo.fact_orders
WHERE load_date = @d
UNION ALL
SELECT 'new', COUNT(*) FROM target.dbo.fact_orders
WHERE load_date = @d;

Run it per table per load date, write the result into a results table, and never compare by eye. The output you want is a row per table per day with both counts and a difference — something a person can scan in ten seconds and a pipeline can fail on.

Layer 2: column checksums

Row counts agree far more often than the data does. Rounding, collation, trailing whitespace, a datetime that became datetime2, and a Derived Column expression that was rewritten "equivalently" all keep the count identical.

Aggregate per column instead, and split by type:

SELECT
  COUNT(*)                                        AS row_count,
  SUM(CONVERT(DECIMAL(38,6), order_total))        AS sum_order_total,
  SUM(CONVERT(BIGINT, quantity))                  AS sum_quantity,
  CHECKSUM_AGG(CHECKSUM(customer_key))            AS ck_customer_key,
  SUM(CASE WHEN status IS NULL THEN 1 ELSE 0 END) AS null_status
FROM <side>.dbo.fact_orders
WHERE load_date = @d;

Three rules make this work in practice:

  • Sum the money and the quantities explicitly, cast to a wide type. A difference in a total is the one thing the business will care about, and you want it in its own column rather than buried in a hash.
  • CHECKSUM_AGG is order-independent but weak — it will miss some differences, including some transpositions. It is a smoke detector, not a proof. Use it on keys and low-cardinality columns; use real sums on the numbers that matter.
  • Count the NULLs per column. A change in NULL handling is the most common silent difference in a rewritten data flow, and a NULL count finds it instantly.

For a small number of high-value tables, go further and hash the whole row on both sides into a comparison table, then diff the hashes. It costs more and it tells you which rows differ, which is what you need once a checksum disagrees.

Layer 3: the sample

Pick twenty rows at random plus the ten largest by value, and look at them. Not because the query missed something, but because it is the step that catches the difference nobody thought to reconcile — a column that is right but in the wrong unit, a date that is right but in the wrong time zone.

The three differences that are not bugs

Every parallel run turns up differences that are expected. Naming them in advance is what keeps the reconciliation from being argued about:

  1. Non-deterministic ordering. Surrogate keys from an IDENTITY column will not match between the two sides, and neither will anything derived from them. Reconcile on the business key, never on the surrogate, and exclude the surrogate from row hashes.
  2. Timing. The two estates read the source at different moments. If the source is live, late-arriving rows land on one side and not the other. Fix the read window explicitly — both sides reconcile the same load_date, and both take their snapshot from the same point — or accept a known tolerance and record it.
  3. Deliberate corrections. Sometimes the rewrite fixes a bug: the old package double-counted returns, or dropped rows with an empty region code. Those differences must be signed off individually and then excluded by name from the reconciliation, or they will mask the next real problem. Write them down. An unexplained difference and an approved difference look identical in a diff.

How long to run it

Long enough to cover the cycles the data has. A month-end close is a different code path from a Tuesday; so is a quarter-end, a holiday weekend, and whatever your business calls its peak. Two weeks that include a month-end beats six weeks that do not.

Stop when you have a run of clean days that spans every path, and when every remaining difference is on the approved list. Then cut over — and keep the old estate readable, though not running, for one more cycle.

The part people skip

Automate the comparison into the pipeline itself, so that a failed reconciliation is a failed run rather than a spreadsheet somebody forgot to open. A reconciliation that depends on a person remembering is not a control.

We build this harness as part of lift-and-shift and rewrite work, and we will not sign off a cutover without one. If you are planning one, talk to an engineer before the cutover date is set.