The Validation Pass: Reconcile the New System Against Your Old One, Field for Field

A migration isn't done when the data loads — it's done when you can prove the new system reproduces the numbers your office already trusts, side by side, in the same period and the same cut.

The riskiest moment in any system migration isn't the import itself. It's the meeting two weeks later when a program director glances at a dashboard, frowns, and says, "That's not the number I reported last year." If you can't immediately explain the gap, the new system loses its credibility on day one — and credibility, once lost, is expensive to rebuild.

The fix is not better luck. It's a deliberate validation pass: a structured reconciliation of the new system against the old one, field for field, before anyone treats the new numbers as authoritative. Done well, it turns "trust us, the data moved" into "here are last year's figures, reproduced exactly."

Why a clean-looking import still can't be trusted

Imports rarely fail loudly. The file loads, the row count looks plausible, the screens populate — and everything appears fine. The failures that matter are quiet: a date format that shifts a paper into the wrong reporting year, an affiliation that didn't map and silently dropped a member's funding, a one-to-many join that double-counts a publication linked to several authors.

So the goal of validation isn't to confirm that data arrived. It's to confirm that the data means the same thing it did before — that a query you've run for years returns the answer it always has. That requires comparing against a baseline you already believe.

Build a "compare against source" view

Start by reproducing the old system's output in a layout your team recognizes. If your prior reports came out as a members-by-program roster, build a view in the new system that mirrors that exact layout — same columns, same grouping, same order.

The cardinal rule is like-for-like. Pull the same window from both systems, segmented the same way. If you're checking publications, don't compare "all publications ever" in the old system against "last fiscal year" in the new one and wonder why they differ.

Match the segmentation precisely on every axis that affects the count:

• Date window — the exact start and end, and the same date type.
• Population — full members only, or members plus associates?
• Scope — members-only projects vs. any project; cancer-relevant vs. all.
• Layout — the same columns and grouping, so a human eye can scan two sheets in parallel.

Treat the first import as a mapping pass, not live data

A first load is best understood as a field-mapping rehearsal, not the real thing. Its job is to prove that each source column lands in the right destination field and that your join keys hold — not to stand up production-ready numbers.

Plan for at least one round-trip: load, compare, find the mapping gaps, correct, reload. And end the pass with an explicit human sign-off — a named person who looks at the side-by-side and says, in writing, "these match, or the differences are explained and acceptable."

When you compare, calibrate your alarm to the size of the gap. Small drift (a record or two) is expected — records keep changing. A large variance (100 expected vs. 120 reported) is a real flag that should stop the sign-off.

When the numbers don't match, trace the cause before blaming the import

The instinct when a count is off is to assume the migration broke something. Sometimes it did. Just as often, the baseline is the thing that's wrong — or rather, the baseline answers a slightly different question than the one you're now asking.

Suspect the remembered number first. A stakeholder who says "we had 120 members" may be recalling a different reporting period, or counting associates the new report excludes, or remembering a figure from before a program realignment.

Common root causes, in rough order of how often they bite:

1. Different reporting period. The remembered figure used a calendar year; your query uses the fiscal year.
2. Different population definition. Full members vs. full-plus-associate.
3. A genuine mapping miss. An affiliation, sponsor name, or date that didn't map.
4. A join-multiplication artifact. A publication linked to several authors counted once per link instead of once per record.
5. A structural CSV fault.

Don't forget to validate the file's structure

A subtle and underrated failure: a stray delimiter or an unescaped comma inside a free-text field can shift every value after it one column to the right, for the rest of that row. The data "loaded" — but a project's total cost is now sitting in the start-date field.

So validate the structure of any CSV before you trust its contents: confirm the column count is consistent across every row, that quoted fields are properly escaped, and that the header line matches the body.

The payoff

A disciplined validation pass does more than de-risk a launch. It produces a shared, written understanding of what every key number means — which period, which population, which scope — and that understanding outlives the migration. The first time leadership asks a hard question about a figure in a submission, you'll have the receipts.