The Data Migration Checklist: Plan, Execute, Validate

Data migrations fail in a distinctive way. They rarely fail loudly on the night. They fail three weeks later, when someone notices that a category of customer has the wrong address, or that a year of history is missing for one region, or that every timestamp is out by an hour for records created before a particular date.

Nobody was careless. The problem is that a migration touches everything at once, the old system's quirks are undocumented, and the only complete specification of the data is the data itself. This guide is the sequence that keeps migrations boring — profiling, mapping, dry runs, cutover and reconciliation — with attention to the parts teams skip.

• How to profile source data before committing to a plan
• Building a mapping that survives contact with reality
• Why dry runs are the single highest-value activity
• Choosing between big-bang and phased cutover
• Reconciliation that actually proves correctness
• What to do when something goes wrong mid-cutover

1. Why migrations go wrong
2. Scope and success criteria
3. Profiling the source
4. The data quality decision
5. Building the mapping
6. The hard cases
7. Choosing a cutover strategy
8. Building the pipeline
9. Dry runs
10. Performance and the migration window
11. The cutover runbook
12. Reconciliation and validation
13. Rollback, and when to use it
14. After the cutover
15. Decommissioning the source
16. Twelve mistakes
17. A worked example: a customer platform migration
18. The checklist
19. Frequently asked questions

1. Why migrations go wrong

Five causes account for most failures, and none is a technical limitation.

The source is misunderstood. The schema says one thing; twelve years of application changes, manual corrections and abandoned features say another. A field documented as a country code contains free text in eight percent of rows because a form was unvalidated for two years.

Scope grows quietly. A migration becomes a redesign, then a cleanup, then a consolidation. Each addition is reasonable; together they turn a two-week task into a two-quarter programme with no clear finish line.

Validation is superficial. Row counts match, so it worked. Row counts matching proves almost nothing — every row could be present and half the fields wrong.

The window is underestimated. The extract takes six hours in testing on a quiet database and eleven on a production one under load, and now the business is opening with the migration half done.

Nobody owns the decisions. A migration surfaces dozens of judgement calls about data that has no single obviously right answer. Without a named decision-maker, each one stalls or is made inconsistently by whoever hit it.

2. Scope and success criteria

Write these down before anything else, and get them agreed by whoever will judge the outcome.

What moves. Which entities, which date ranges, which statuses. Be explicit about exclusions — closed accounts older than seven years, test records, soft-deleted rows — because unstated exclusions become disputes on cutover night.

What does not move. Equally important and usually unwritten. Historical audit logs staying in an archive. Attachments moving separately. A legacy module being retired rather than migrated.

Acceptable loss. A hard conversation worth having early. Some source data will not map cleanly. Deciding in advance that, for instance, free-text notes will be concatenated into a single field rather than parsed prevents a week of argument later.

Success criteria, measurable. Not "the data is correct" but specific tests: every active customer present with matching identifiers; total outstanding balance matching to the penny; every order linked to a valid customer; no record with a creation date after its modification date.

The freeze. When does the source stop accepting changes, and who enforces it? Every migration that skips this discovers records created during the window that exist in neither system.

3. Profiling the source

The most undervalued phase. You cannot map what you do not understand, and the schema is not the data.

For every field that matters, establish:

• Fill rate. How often is it populated? A field that is null in ninety percent of rows may not be worth migrating at all.
• Distinct values. A status field with six documented values and nineteen actual ones is a discovery worth making now.
• Format variation. Phone numbers in five formats. Dates as strings in three. Country as name, code and abbreviation, mixed.
• Range and outliers. Dates in 1900 and 2099. Negative quantities. Amounts with more decimal places than the currency has.
• Referential integrity. How many child rows point at a parent that no longer exists? In an old system, the answer is rarely zero.
• Duplicates. By whatever definition the business uses, which is usually not the primary key.
• Encoding. Mixed encodings in one column are common in long-lived systems and produce corruption that survives every count-based check.

Profile the whole dataset, not a sample. The rows that break a migration are by definition rare, and sampling is designed to miss them. Profiling is cheap; missing a category of bad data is not.

Produce a written profile document. It is the input to every subsequent decision and the reference when someone asks why a rule exists.

4. The data quality decision

Profiling will reveal problems. Now decide, explicitly and per problem, what to do about each.

The trap is fixing in transit by default, because it feels efficient. It buries business decisions inside transformation logic where nobody will find them, and it means the migration cannot be re-run against a corrected source without re-deriving the same guesses.

A useful discipline: any transformation that changes meaning rather than format needs a named owner who agreed to it. Reformatting a date is format. Deciding that a blank country means the home market is meaning, and someone should own that.

5. Building the mapping

The mapping document is the specification. Field by field, from source to target:

• Source location and target location
• The transformation, stated precisely enough to be implemented unambiguously
• What happens when the source is null or invalid
• Whether the target field is required
• Any lookup or reference data involved
• Who agreed the rule, for anything non-obvious

Target fields with no source are as important as mapped ones. Every one needs a decision: a default, a derivation, or acknowledged emptiness. A required target field with no source is a blocker that must be resolved before the pipeline is built, not discovered during it.

Reference data is where mappings get long. Status codes, categories, currencies, country lists, product types. Each needs an explicit source-to-target table, including what happens to source values with no target equivalent — which there will be, because the old system accumulated categories nobody remembers creating.

Keep the mapping in something reviewable and versioned. It will change dozens of times, and knowing what changed and why is what makes the third dry run informative rather than confusing.

6. The hard cases

Six patterns that consume disproportionate effort. Recognising them early is most of the battle.

Identity and deduplication. Does the target need new identifiers, or can source ones carry over? Carrying them over is far simpler and constrains the target's design. And if records must be merged, the merge rules — which record wins per field, what happens to both sets of children — are a substantial piece of work in their own right.

Hierarchies and self-references. Parents must exist before children, which means ordering matters, and cycles in the source data will hang a naive loader. Check for cycles during profiling.

Many-to-many relationships where the source used a delimited string in a column. Parsing is straightforward; the values that do not match anything are the work.

Dates, times and zones. The most reliable source of late-discovered errors. Establish what the source actually stored — local time with no zone is depressingly common — and what the target expects. If the source changed convention partway through its life, and it may well have, the transformation depends on the record's age.

Free text. Notes fields accumulate structured information that nobody put in structured fields. Extracting it is tempting and unreliable. Migrate it verbatim and extract afterwards if it turns out to matter.

Files and attachments. Usually a separate pipeline with separate failure modes. Verify by checksum, not by count, and expect a percentage of references pointing at files that no longer exist.

7. Choosing a cutover strategy

The honest default is big bang if the window allows it. It is by far the simplest, and simplicity is what makes migrations succeed. The alternatives exist because sometimes the window does not allow it, and every one of them costs substantially more in complexity.

The decisive question is: how long can the business be without the system? Get a real answer from someone who can commit to it. A weekend is generous; four hours on a Sunday morning is a serious constraint that changes the design.

8. Building the pipeline

Whatever tooling you use, certain properties matter more than the technology choice.

Re-runnable. You will run it many times. Running it twice must not produce duplicates, and it must be able to start from clean without manual cleanup.

Resumable. A pipeline that fails at eighty percent and must restart from zero turns a six-hour window into an impossible one. Checkpoint at meaningful boundaries.

Observable. Progress, throughput, error counts, and which record failed. Nothing is worse at three in the morning than a process that has been running for an hour with no output.

Failure-tolerant with a policy. Decide in advance: does a single bad record abort the run or get quarantined? Usually quarantine, with a threshold that aborts if too many accumulate — because a thousand failures means a systemic problem, not a thousand bad records.

Separated stages. Extract, transform, load as distinct steps with the intermediate output retained. When something is wrong you can inspect what came out of each stage instead of guessing.

Logged decisions. Every defaulted value, every unmapped code, every quarantined row, recorded with the reason. This log is what you use to answer "why is this field empty" for the next six months.

9. Dry runs

The highest-value activity in the entire project, and the one most often cut when the schedule slips.

A dry run is the full pipeline against a full copy of production data, into a target environment, timed and validated exactly as the real thing will be. Not a sample. Not a subset. The whole thing.

Run at least three. The first will fail — that is its purpose. It surfaces the data you did not profile, the transformations that were wrong, and the performance that does not hold. The second validates the fixes and usually surfaces a second tier of problems. The third should be uneventful, and if it is not, run a fourth.

Each dry run produces:

• Timings per stage, which is your window estimate
• The error and quarantine log, reviewed row by row for patterns
• Full validation results
• A list of mapping corrections

Have real users check real records. Automated validation confirms what you thought to check. A person who knows the business looking at fifty familiar records finds the things nobody thought to check — and those are the errors that survive to production.

Refresh the source copy between runs. A dry run against three-month-old data misses everything that changed since, and what changed is disproportionately likely to be what breaks.

10. Performance and the migration window

Measure, do not estimate. Then plan for worse.

The common surprises: extraction is slower on a production system serving live traffic than on a quiet copy; loading slows as target tables grow and indexes are maintained; and network transfer between environments is frequently the actual bottleneck rather than either end.

The reliable improvements, in rough order of impact:

• Drop indexes and constraints before loading, rebuild after. Frequently the single largest gain, and the rebuild time must be included in the window.
• Bulk load rather than row-by-row. An order of magnitude, routinely.
• Parallelise by independent partition — by region, by date range, by entity — where referential order permits.
• Extract early. Anything that has not changed can be pulled and transformed days ahead, leaving only the delta for the window.

Then add contingency. A plan that exactly fills the window has no room for the one stage that runs slow, and something always does. If the window is eight hours, the plan should complete in five.

11. The cutover runbook

Written in advance, rehearsed in the final dry run, followed on the night. Every step needs a name against it, an expected duration, and a way to confirm it succeeded.

1. Go/no-go. A named decision-maker, criteria agreed in advance, at a fixed time.
2. Freeze the source. Disable access, confirm no writes are occurring. Confirm, do not assume.
3. Final backup of both source and target, verified restorable.
4. Extract. Confirm counts against the source before proceeding.
5. Transform. Review the error log before loading anything.
6. Load. Progress checkpoints so a stall is visible.
7. Rebuild indexes and constraints. Any constraint violation here is a real problem, not a formality.
8. Automated validation. The full suite, results reviewed before proceeding.
9. Manual spot checks. Business users, prepared list of records, twenty minutes.
10. Smoke test the application. Log in, search, open a record, complete a transaction end to end.
11. Go/no-go on release. The second decision point, and the last moment rollback is cheap.
12. Open to users.
13. Monitor at elevated attention for the first hours.

Two practicalities that matter more than they should: agree a single communication channel so status is in one place, and staff the night with people who slept — a team that worked a full day before a midnight cutover makes avoidable mistakes at four in the morning.

12. Reconciliation and validation

Counts are the beginning, not the end. Five layers, each catching what the previous misses.

Completeness. Row counts per entity, source against target, accounting for documented exclusions. Catches wholesale loss.

Financial and quantitative totals. Sum every numeric column that means something — balances, quantities, amounts — and match exactly. This catches truncation, rounding and unit errors that counts cannot.

Referential integrity. Every foreign key resolves. No orphans. No records pointing at excluded parents.

Field-level comparison. For a statistically meaningful sample, compare every field. Automate it, because doing it by hand means doing thirty rows and calling it done. This is the layer that catches the mapping error affecting one field in one segment.

Business rule validation. Assertions that must hold in the target regardless of the source: no order without a customer, no negative stock, no modification date preceding creation, every active account with a valid contact method. These catch problems the source did not have and the migration created.

Automate all five and run them after every dry run. A validation suite that runs in ten minutes gets run every time; one that takes a day gets run once.

13. Rollback, and when to use it

Every runbook needs a rollback plan, and the honest observation is that after users have been let in, rollback is rarely viable — new data now exists only in the target, and reverting loses it.

This means the go/no-go before opening to users is the real decision point. Before it, rollback means restoring the target and unfreezing the source, which is unpleasant but clean. After it, rollback means reconciling divergent data, which is worse than fixing forward.

Define the abort criteria in advance, when everyone is calm: validation failures above a threshold, financial totals not matching, a core workflow broken. At two in the morning, with sunk effort and people waiting, the pressure to proceed is enormous, and pre-agreed criteria are what protect the decision.

Also define what does not trigger a rollback: cosmetic issues, a handful of quarantined records, non-critical performance. Fix those forward.

14. After the cutover

The migration is not done when the system opens.

First week: elevated monitoring, a clear route for users to report data problems, and someone assigned to triage them. Expect reports; most will be data that was always wrong and is now visible, which is worth saying out loud because it will otherwise be attributed to the migration.

Quarantined records need working through. They were set aside precisely because no automatic rule was safe, so this is manual and someone must own it. Set a deadline or it will not happen.

Keep the source read-only and available for a defined period — a month at minimum, longer for anything financial. It is the only way to answer questions about what the data used to be.

Write the post-mortem while it is fresh, including what the dry runs caught. That document is what makes the next migration cheaper.

15. Decommissioning the source

The step that gets forgotten, leaving systems running for years because nobody was sure it was safe to stop them.

Before switching anything off: confirm no integrations still read from it, which requires actually checking rather than asking; take a final archive in a format readable without the original system; confirm retention obligations are satisfied by the target or the archive; and get written sign-off from whoever owns the data.

Then stop it, keep the archive, and remove the infrastructure. A migration that leaves the old system running has delivered half its value.

16. Twelve mistakes

1. Profiling a sample. The rows that break migrations are rare by definition.
2. Trusting the schema. It describes intent, not contents.
3. Validating with counts alone. Every row present and half the fields wrong passes.
4. One dry run. The first is for finding problems, not for confidence.
5. Dry runs on stale data. Misses everything recent, which is what breaks.
6. Redesigning during migration. Two hard projects entangled into one impossible one.
7. Burying business decisions in transformation code. Nobody will find them later.
8. No freeze, or an unenforced one. Produces records in neither system.
9. A plan that exactly fills the window. Something always runs slow.
10. No named decision-maker. Judgement calls stall or get made inconsistently.
11. Skipping business user checks. Automated tests only check what you thought of.
12. Leaving the source running indefinitely. Half the value undelivered.

17. A worked example: a customer platform migration

A company moving twelve years of customer, order and support data from an ageing in-house system to a commercial platform. Roughly four hundred thousand customers, six million orders, two million support interactions.

Profiling took three weeks and changed the plan twice. The country field contained ninety-one distinct values for what should have been a list of forty. Eleven percent of customers had no valid contact method — mostly records created by a bulk import in the system's second year. Four percent of orders referenced a customer identifier that did not exist, all from a single eighteen-month period when a delete cascade was broken. And timestamps before a particular date in year six were stored in local time; after it, in UTC. That last discovery alone would have produced a year of subtly wrong dates and no error message.

Quality decisions were made explicitly, with owners. The country values were mapped by hand into the target's list, with seven genuinely ambiguous entries quarantined. Customers with no contact method were migrated as-is and flagged, because deciding they were invalid was a business decision the business declined to make. The orphaned orders were migrated against a synthetic "unknown customer" record rather than excluded, because finance needed the totals to reconcile. Each of these was signed off by a named person, and each is documented in the mapping with the reason.

The strategy was big bang over a weekend, chosen after establishing that support could operate from an exported spreadsheet for forty-eight hours. A phased approach was considered and rejected because orders reference customers and support interactions reference both, so no clean split existed.

Four dry runs. The first failed at forty percent on a memory limit and revealed that the transformation held everything in memory rather than streaming — a design flaw found in week two rather than on cutover night. The second completed in fourteen hours against a nine-hour window, which triggered the index-dropping and parallelisation work that brought it to six. The third surfaced the timestamp problem in validation, because a business user checking familiar records noticed an order timed an hour before the customer called about it. The fourth was uneventful and became the rehearsal.

Validation caught two things automated checks alone would not have. The financial total matched to the penny on the third run, which was reassuring and turned out to be because two errors cancelled — a small number of refunds were being loaded as positive and an equal value of adjustments was being dropped. Field-level comparison across a ten-thousand-row sample found it. The second was a support user noticing that interaction threads had lost their ordering, because the target sorted by a field the migration had populated identically for every row in a batch.

Cutover took five hours and forty minutes against a nine-hour window. One stage ran slower than in the dry run — extraction, because production had traffic the copy did not — which the contingency absorbed exactly as intended. Go/no-go was called at hour seven after validation and spot checks, and users were let in on Sunday afternoon.

The first week produced thirty-one reported issues, of which twenty-three were pre-existing data problems now visible in a system that displayed them more prominently. Six were genuine migration defects, all in reference data mappings for rarely used categories, all fixable with an update. Two were quarantined records that a business user needed to resolve by hand.

What made it work was not the tooling, which was unremarkable. It was three weeks of profiling before anyone wrote a transformation, four full dry runs on refreshed data, business users looking at records they recognised, and a named person empowered to decide what happened to bad data. Every one of those is the thing that gets cut when the schedule slips, and cutting any of them is what turns a five-hour cutover into a three-week recovery.

18. The checklist

19. Frequently asked questions

How long should a migration take?

Far longer in planning than in execution. A rough shape for a substantial migration is that profiling and mapping take a third of the effort, building the pipeline takes a quarter, dry runs and validation take the rest, and the cutover itself is a few hours. Teams that compress the first two thirds discover why they exist during the last third.

Can we clean the data during the migration?

Formatting, yes — that is unavoidable. Meaning, only with explicit decisions and named owners, because a transformation that quietly reinterprets data is a business decision hidden in code. Where the source will stay live for a period, fixing in the source is usually better: the correction has independent value and the migration stays simpler.

How much validation is enough?

All five layers — completeness, totals, referential integrity, field-level sampling and business rules — automated and run after every dry run. The one that gets skipped is field-level comparison, because it is the most work to build, and it is the one that catches the errors totals and counts cannot see.

What if we cannot take any downtime?

Then you need continuous change capture and a short final switch, which is substantially more complex and should be chosen deliberately rather than by default. Before committing, push hard on what the real constraint is — many "no downtime" requirements turn out to be "no downtime during business hours", which a weekend window satisfies.

Should we migrate all the history?

Ask what it is for. History that is genuinely queried belongs in the target. History retained for compliance can frequently live in an archive that satisfies the obligation at a fraction of the complexity. Migrating twelve years because nobody wanted to decide is a common and expensive default.

What is the single highest-value activity?

Full dry runs against refreshed production data, with business users checking records they recognise. It is where the problems that would have been production incidents get found, and it is the first thing cut when the schedule slips — which is why so many migrations fail in ways that were entirely discoverable in advance.

How do we handle data that has no target field?

Decide explicitly rather than defaulting. Options are extending the target, concatenating into a notes field, storing it in a supplementary table, archiving it outside the system, or agreeing to lose it. All are defensible; silently dropping it is not, because someone will ask for it in eight months.

When is it safe to switch off the source?

After a defined period of the target running without data-related incidents — a month at minimum, considerably longer for financial or regulated data — with all integrations confirmed migrated, an archive taken in a format readable without the original system, retention obligations verified, and written sign-off from the data owner.

Key takeaways

• Profile the whole dataset. The schema describes intent; the data describes reality.
• Make quality decisions explicit, with named owners, outside the transformation code.
• Dry run at least three times on refreshed data — the highest-value activity in the project.
• Validate in five layers. Counts prove almost nothing on their own.
• The go/no-go before opening to users is the real decision point. After it, fix forward.
• Plan to finish well inside the window. Something always runs slower than it did in rehearsal.

A migration that goes well is indistinguishable from one that was easy, which is why the discipline is undervalued. The work that makes it boring — profiling, mapping, rehearsing, validating — happens weeks before the night, and it is entirely the reason the night is uneventful.