Data migration testing: when the oracle is wrong

In 2026 we moved an audience-research analytics pipeline off a managed data warehouse and onto Postgres. The new pipeline recomputes the same curated tables: counts, ratings, sentiment, rankings. The obvious data migration testing question before cutting over: does it produce the same answers?

The answer turned out to be "no, and that's good news," but it took two versions of the gate to see it.

A gate that cannot fail

The first parity gate counted rows in four anchor tables and passed if all four were non-empty.

That can't establish parity. Matching row counts can prove a table is wrong; they can never prove it's right. The gate was green while one table undercounted a survey answer by a meaningful share of respondents, because nothing ever compared a value to anything.

Data migration validation that compares values

Version two compared column by column and row by row against a frozen mirror of the old warehouse output, and produced a verdict per table.

Two design choices mattered:

• Scope the comparison. The mirror was frozen at a point in time and the new pipeline is live. Titles still collecting data on either side of the freeze will differ for reasons that say nothing about the port, so the comparison only covers data that finished collecting before the freeze.
• Partition, don't allowlist. Some columns rank each item against the whole dataset, and the two datasets differ by construction, so those columns always diverge. Rather than exempting them (which would hide a real bug forever), they're reported in their own bucket. A pass still requires everything to be clean.

The first real run split the tables into clean passes, expected cross-population differences, and a set of genuine divergences.

Then we checked the oracle

Before fixing those divergences, we recomputed a few expected values straight from the cleansed source rows, using deliberately simple queries: count distinct respondents per answer, nothing clever.

The new pipeline matched the recomputed truth on every answer we checked. The frozen mirror didn't. The biggest "divergence" the gate had found was the new pipeline being right and the old warehouse output being stale.

A gate judged against a wrong oracle stays permanently red, and a permanently red gate is one nobody reads. So the gate changed again:

• Pass/fail is judged against ground truth, recomputed from source with simple queries.
• The mirror diff still runs, but it's advisory.

As a bonus, the ground-truth checks work on the most recent data, which the frozen mirror can't speak to at all. That's the data anyone actually cares about.

A related bug: the empty fallback

The same migration surfaced a smaller bug worth its own rule. A data-access layer fell back to a secondary source whenever the primary looked stale. But "stale" was computed from when an item was created, not when its data was last computed, so almost everything was stale. And the fallback was returned without checking whether it had anything in it.

The result: full, correct data replaced by empty results.

const primary = await fetchPrimary(id)
if (isStale(primary)) {
  const fallback = await fetchFallback(id)
  if (fallback.rowCount > 0) return { ...fallback, fallbackReason: 'stale' } 
}
return primary

Never trade a full answer for an empty one. Falling back is resilience only when the fallback is better.

What I'd tell anyone migrating data

1. Your gate should be able to fail. Prove it with a known-bad case.
2. Don't assume the old system is correct. Check it against the source.
3. Report expected differences separately instead of hiding them.

Related: AWS to Azure Government migration: the small things that break · LLM guardrails: assert before you render