Data migration: reconcile before you switch

Do not switch to the new system until a reconciliation report shows that it holds the same data as the old one. This article covers how to get to that report and what to do with it.

A migration is a project, not a script

The script is the small part. It copies records from one place to another. The work is everything around it: learning what the old data really contains, deciding what each field becomes, proving the result and planning the switch.

Old systems carry years of history. Rules changed, staff found workarounds and fields were reused for things their names do not suggest. A script written from the schema alone will copy all of that into the new system, or fail on it during the cut-over.

So give the migration its own plan, its own owner and its own budget line. If a proposal shows it as a single task at the end, ask what it includes.

Profile the source data before you map it

Profiling means querying the old data to learn what is really there, not what the documentation says. Do it before anyone writes a mapping, so the surprises come at the start. For every table or entity, find out:

• How many records exist, and how many are active, closed or deleted.
• Which fields are empty, and how often. A field the new system requires may be blank in many old records.
• The longest value in each text field, and the range of each number and date field.
• Every distinct value in each coded field, including misspellings and codes nobody remembers.
• Duplicates and orphans: the same customer entered twice, or a record whose parent no longer exists.
• Dates that cannot be true, such as a birth date in the future.

Every finding needs a decision from the business, not from the developer. Merge the duplicates or carry them over. Fix the blanks at source or supply a default. Write each decision down.

Write the mapping document

The mapping document lists every field in the new system and says where its value comes from. For each field: source, target, type and length, conversion rule, and what happens when the source value is missing or malformed. List the fields you chose not to migrate, and why.

The people who own the data review and sign it. It then becomes the specification for the migration scripts, and the reference for anyone who later asks why a value looks the way it does.

Rehearse against a copy

Run the migration against a copy of production data, into a test instance of the new system. Run it in full, more than once, and time it. The duration tells you whether the switch fits in a night or needs a weekend.

Each dry run produces a reconciliation report and a list of rejects. Fix the script or the source data, then run again from an empty target. Repeat until the run is clean.

Rule of thumb: the cut-over should be the most boring run of all. Same script, same steps, same people as the last clean rehearsal.

What the reconciliation report contains

The report compares the old system with the new one. The finance lead should be able to read it, not only the developer. At a minimum it holds:

• Record counts per table or entity, on both sides, with the difference.
• Control totals on money and quantities, on both sides: open balances, invoice amounts and stock on hand.
• Checksums per table or entity over the key fields. They catch values that changed while the counts stayed the same.
• A list of rejected records, each with its identifier and the reason.
• A sample of records checked field by field by someone who knows the data.
• The date, the script version and the name of the person who ran it.

For money, the acceptable difference is usually none. A difference of one cent means a rule somewhere is wrong, and the same rule may be wrong by far more elsewhere. For counts, the rejects list must explain every gap.

Warning: agree the acceptable difference before the first dry run. A tolerance set after you see the numbers is an excuse.

Plan the cut-over and the way back

The cut-over plan is a timed runbook with a name against every step:

1. Freeze changes in the old system and tell users when.
2. Back up both systems and confirm that the backups restore.
3. Run the migration.
4. Produce the reconciliation report.
5. Review it against the agreed tolerance and decide go or no-go.
6. Open the new system to users, or roll back.

The rollback plan says what happens after a no-go, and the latest time you can still call one. Usually it is simple: reopen the old system, which nobody has touched. It gets hard once users have entered data in the new system, so set the decision point before that.

After the switch, keep the old system in read-only mode for an agreed period. Staff can look up history and check a disputed figure, but nobody can create a second version of the truth. Set the end date in advance, then archive the data and switch the system off.

It matters who signs the reconciliation. It should be the business owner of the data, often the head of finance or operations, not the team that wrote the script. File the signed report with the project record.

A checklist before you switch

Stack9 runs migrations this way. We write a field-level mapping document, run the migration scripts against a copy first and produce a reconciliation report with record counts and totals on both sides before anything is switched over.

Whoever runs yours, confirm these before the switch:

1. The source data is profiled and every finding has a written decision.
2. The data owners have signed the mapping document.
3. At least two full dry runs against a copy have finished clean.
4. The report shows counts, control totals and checksums on both sides, and lists every reject with a reason.
5. The acceptable difference was agreed in advance. For money it is zero.
6. The runbook names a person for every step, and the rollback has been rehearsed.
7. A named business owner signs the reconciliation, and the old system stays read-only afterwards.