Data migration: field mapping, dirty records and reconciliation after the move

The migration was estimated at two days: “copy the tables over”. On migration day it turns out 8% of customers have no phone, 3% of orders have the status “other”, and amounts are stored with tax while the new system expects them without.

Data migration is usually seen as technical work: write a script, copy the tables over, check nothing fell over. But almost every question that comes up during a migration is not technical. What to do with an order in a status the new system does not have? With a customer with no phone number, now that a phone is mandatory? With two records for the same person? The business answers these questions, and the analyst has to ask them — before the migration, not on cutover night.

Mapping: every field with a rule

The core migration document is the correspondence between fields in the old and new systems. It is useful only if each field states not just “where” but “how” — and what to do with a value that does not fit.

Old field        New field         Transformation            If it doesn’t fit
client.phone     customer.phone    to format +44XXXXXXXXXX   migrate empty, flag
order.status     order.status      per status table          “other” → decision below
order.sum        order.total       minus VAT at the rate     no rate → rejects report
                                   on the order date
order.manager    —                 not migrated              —
—                order.channel     fill with “offline”       —

Status table
  new, accepted          → confirmed
  dispatched, in transit → shipped
  completed              → delivered
  cancelled, returned    → cancelled
  other (3% of orders)   → ? process owner decides by 10.11

Note the amount: the old system stored it with tax, the new one without. A difference like that is invisible from field names and is found only if the meaning of every field is checked. It is the same work as the dictionary in the piece on the data model — only for two systems at once.

Dirty records

Any system that has lived for a few years holds records that break its own rules. The new system is usually stricter — and such records will not get in. The kinds are almost always the same:

Kind	Example	Possible decisions
Empty where now mandatory	Customer with no phone	Fill before migrating; migrate flagged; do not migrate
Duplicates	Two records for one customer	Merge by a rule; migrate both and merge later
Value outside the reference list	Status “other”, a misspelt city	Map by hand; add an “undetermined” value
Broken links	An order whose customer was deleted	Migrate with an anonymised customer; do not migrate
Old format	Date as text, amount with spaces	Convert by a rule; reject into the report

Choosing the option is for the data owner, not the script’s author. The analyst’s job is to count such records in advance and bring the decision with numbers: “312 customers without a phone, 290 of them with no orders in three years”.

A default value is the most dangerous decision. A field filled “by default” during migration is indistinguishable from a real one. A year later a report shows 8% of customers in the capital, when in fact their city was simply never recorded. If you do fill it, keep a marker that the value was substituted.

What to migrate at all

The new system does not need all of the past. Migrating the whole history is expensive and drags all the dirt along. Common decisions: migrate only active records and N years of history; leave the closed ones in the old system, read-only; export the archive to files. Which is right depends on who will look into the past and why — reporting, disputes with customers, retention requirements.

Trial runs and reconciliation

A migration cannot be checked once, on cutover day. It needs trial runs on a full copy of the data — two or three, with fixes in between. Each run answers three questions: how many records were migrated, how many rejected and why, and how long it took. The last one decides how long the system will be unavailable during cutover.

How to tell everything was migrated correctly is agreed in advance — before the first run:

What we reconcile	How	Acceptable difference
Counts	Records before, migrated, in the rejects report	Adds up exactly
Money	Order totals by month in the old and new system	Zero, allowing for the tax recalculation
A sample	20 records of different kinds, by eye, field by field	Zero
Behaviour	The key scenario on a migrated record	The scenario passes

Cutover

On migration day three questions remain, and all three are requirements:

The freeze window. When the old system stops accepting changes, and who has been warned.
The delta. What to do with records created in the old system after the last run.
The point of no return. Until when it is possible to go back to the old system, and what then happens to data created in the new one. As the glossary says about rollback, returning the code does not return the data.

The sign a migration was not described as requirements: asked “how will we know everything moved across”, people answer “we’ll see afterwards”.

A ten-minute check

Take the plan for the next migration and look for three things: a mapping of statuses (or other reference lists) covering every value, a decision for each kind of dirty record, and a reconciliation table. Whatever is missing will be decided at night, by someone without the authority to decide it.

What next

The data is migrated, the feature works, the task is closed. But the real test of the spec is only beginning: what the people using the system will say about it, and whether that reaches whoever writes the next task.