Data Migration Planning: Dry Runs, Reconciliation and Sign-Off

The sequence that makes a migration safe: profile, map, dry run at full volume, reconcile against an agreed tolerance, and get the data owner's signature before the cutover window opens.

Data migration is the process of moving data from a source system to a target system in a form the target can use, verified by reconciliation against agreed tolerances and formally accepted by a named data owner. On most IT go-lives it is the single largest source of cutover risk, because it is the one activity that cannot be meaningfully rehearsed at small scale.

The reason migrations fail late is almost always the same: the data was profiled optimistically, the dry runs were done on a subset, and the first full-volume load happened in the cutover window.

The sequence

1. Profile the source data first

Before mapping anything, find out what is actually there: record counts, null rates on mandatory fields, duplicate keys, orphan records, values outside the reference data, free-text fields holding structured data because someone had nowhere else to put it.

Profiling is the cheapest activity in the whole migration and the one most often skipped. Every hour spent here removes several from the reconciliation arguments later.

2. Agree what is not migrating

Migration scope is a business decision, not a technical one. How many years of history? Closed records? Inactive customers? Attachments?

Every record excluded reduces risk, duration and cost. Get the decision made and written down early, because it will be challenged during UAT when somebody cannot find a 2019 order — and the answer needs to be a documented decision rather than an apology.

3. Map, then clean at source

Field-level mapping with transformation rules, exceptions and defaults. Where data must be cleaned, clean it in the source system wherever possible. Cleaning in the migration layer means the next dry run reintroduces the same problems, and it means the source system remains wrong for anyone still using it.

4. Dry runs — at full volume

At least two, and the last one at full production volume. Subset dry runs prove the mapping works; they prove nothing about duration, and duration is what determines whether the cutover window is long enough.

Record for each run: load duration, error counts by type, reconciliation result, and what changed since the previous run. The trend across runs matters as much as the final number — errors that are not falling between runs indicate a mapping problem rather than a data problem.

5. Reconciliation, against a tolerance agreed in advance

Reconciliation compares source and target and states whether the difference is acceptable. Three levels are usually needed:

• Counts — records in, records out, records rejected, and the rejected ones explained
• Financial and quantitative totals — balances, values, quantities, summed both sides
• Sample-level checks — individual records compared field by field, including deliberately awkward ones

The tolerance is a business decision and must be agreed before the dry run produces a number. “Within 0.1% variance on total value, zero variance on record count” is a criterion. A tolerance negotiated after the result is known is not a control.

6. Sign-off

A named data owner accepts the migrated data against the reconciliation evidence, on a date. Not the project manager, not the technical lead — the person accountable for the data in the business.

This is normally a go-live criterion, and it is the artefact you will be asked for six months later when somebody questions a figure. Keep the evidence with the signature.

When the numbers do not match

They will not, on the first run. What matters is the response.

Categorise every difference before fixing anything: expected exclusions (in scope of the agreed rules), data quality rejections (bad source data), mapping defects (your fault), and unexplained. The last category is the only one that should stop a go-live decision, and it is the one that gets quietly merged into the third when time is short.

Keep the categorisation visible in the reconciliation record. A migration accepted with 400 unexplained differences is a decision someone made; it should be a documented one.

Migration in the cutover window

By the time the window opens, the load should be a rehearsed, timed activity with a known duration and a known error rate. Specific things to have settled in advance:

• The load's place on the cutover timeline, with its measured duration
• What happens if it overruns — this is often what triggers the rollback decision
• Who runs reconciliation during the window, and how long that takes
• The delta load approach for records created after the extract, if there is one
• Who signs off, at what time, and what happens if they are unreachable

A migration that has been dry-run twice at full volume turns the cutover window into an execution task. One that has not is where the point of no return arrives while people are still arguing about a variance.