How to Plan and Test a Data Migration
Moving data is the easy part of a data migration. Scripts that read from one system and write to another are ordinary work, and by the second attempt they usually run clean.
What goes wrong is everything around the move. A field that meant one thing in 2019 and something else after a process change. A report that no longer reconciles because two source records collapsed into one. Permissions that let the wrong department see costs. These are found late, often by the person least equipped to absorb the surprise, and the cost of fixing them rises sharply once the old system is switched off.
This is a guide to the two documents that prevent most of that: the migration plan and the test plan. It assumes you have already done the discovery work of inventorying the system and its dependencies. If you have not, our legacy application modernization checklist covers that phase.
What a data migration plan needs to contain
A plan that fits on a few pages and is actually read beats a thorough one that nobody opens. Six things belong in it.
The scope line
State explicitly what is moving and what is not. Every migration gets cheaper and safer as scope shrinks, and the decision to leave something behind is much harder to make once the work is underway.
Common exclusions worth naming: records past their retention requirement, closed accounts with no activity in several years, attachments over a certain age, and features of the old system that nobody used. Write down what you are excluding and get agreement on it in writing. "We assumed that was coming across" is a conversation you want to have before the cutover, not after.
The field map
Every field in the source, its destination, and what happens to it in between. This document is tedious to produce and it is the single most useful artifact of the whole project. It is where you discover that the old system has three fields for phone number and the new one has two, or that a status field carries eleven values of which four are no longer used.
Include the fields you are dropping, marked as dropped. An explicit decision to not migrate something is information. A field that silently disappears looks like a defect.
The transformation rules
Anywhere the data changes shape, write the rule down in plain language before writing it in code. Date formats, currency and units, name splitting or joining, status value remapping, deduplication logic, and default values for fields the source never had.
Deduplication deserves particular attention because it is where migrations quietly lose information. If two customer records are merging, decide which one wins on each conflicting field, and decide what happens to the transaction history attached to the loser. Whatever you choose, it needs to be a stated rule rather than a per-record judgment call, because per-record judgment does not scale past a few hundred records and cannot be tested.
The source of truth during the transition
For any period where both systems exist, name which one is authoritative and for which data. This is the single most common source of confusion during a migration, and it is entirely preventable with one sentence per data type.
If users are entering records in the old system after the extract runs, you need a plan for those records: a delta load, a freeze period, or manual re-entry. Pick one and communicate it, because the default outcome is that a week of new records disappears and nobody notices for a month.
The cutover and rollback plan
The sequence of steps on the day, in order, with an owner and an estimated duration for each. Who runs the final extract, who confirms the load, who verifies the checks, who tells users the new system is live.
The rollback path matters more than most teams admit. Define what "we are going back" means concretely, how long you have to make that call, and who makes it. A rollback plan that exists only as an intention is not a rollback plan.
Who signs off
Name the person who confirms the data is correct, and name them per area rather than in general. Finance signs off on financial records. Operations signs off on jobs or orders. Whoever owns the customer relationship signs off on customer data. A single project sponsor signing off on everything is a sign that nobody has really checked.
Profile the source data before you write the plan
Writing a migration plan against assumptions about the data produces a plan that fails on contact with the data. Profile it first, and expect the profiling to change your plan.
Look at record counts per table, and per year, so you can see when the system was actually used. Look at the range of values in every field you are mapping, especially the ones you assume are constrained. Look for nulls in fields the new system will require, because that combination is a hard failure at load time and is much better discovered now.
Look for the fields that were repurposed. Nearly every system that has run for a decade has at least one field whose name no longer describes its contents. Look for orphans, which are child records pointing at parents that no longer exist. Look for duplicates, using whatever the business considers a duplicate rather than whatever the database considers one.
Two numbers are worth producing early and sharing widely: how many records will migrate, and how many have a problem that needs a decision. The second number is what determines the real timeline, and it is almost always larger than anyone expects at kickoff.
Testing is the part plans underspecify
Most migration plans allocate plenty of detail to the load and a single line to verification. Invert that. The load either runs or errors, and you find out immediately. Correctness is what stays hidden.
Run it more than once
Write the migration so it can be run repeatedly against a clean target. Test runs are not a sign that something is wrong, they are the mechanism by which it gets right. Three to five runs before the production move is normal.
Each run should be reproducible from a script rather than assembled by hand, because a migration you cannot repeat is a migration you cannot fix under time pressure on cutover day.
Reconcile counts and totals
For every table, compare source and destination record counts, and account for every difference. Differences are expected where you excluded records or merged duplicates. Each one should map to a rule in the plan. A difference you cannot explain is a defect, even a small one, because it means a rule is behaving differently than you documented.
Then do the same for the numbers the business cares about. Total open balance. Count of active customers. Sum of the current quarter. These are the figures someone will check on day one, and they are the fastest way to find a transformation rule that is subtly wrong.
Check relationships, not just records
Records can all arrive and still be wrong if they are attached to the wrong parents. Verify that each child record landed under the correct parent, that counts of children per parent match the source, and that nothing became an orphan in transit.
This is where merges cause the most damage. When two records become one, everything attached to both needs to end up on the survivor, and that is worth testing explicitly rather than assuming.
Test permissions after the load
Permissions are usually configured against an empty system and validated against a full one, which means they are often validated only after go-live by someone seeing data they should not.
Log in as a real user from each role and confirm what they can see, edit, and export. Pay attention to cost, margin, salary, and any personal data. Do this on migrated data rather than test data, because permission rules often key off field values that only exist once real records are loaded.
Rebuild the reports and compare
Reports are the acceptance test that leadership will actually run. Take the reports people depend on, produce them from both systems for the same period, and compare them line by line.
Where numbers differ, resolve the difference before cutover, and write down the explanation even when the new number is the correct one. Reporting continuity is one of the first things stakeholders check, and "the new system says something different and we know why" is a survivable conversation in a way that "we are looking into it" is not.
Put real users on real records
The last test is the one that finds what the others cannot. Give the people who use the system every day a set of migrated records and ask them to do their normal work.
They will spot things no reconciliation catches: a record that is technically correct and practically useless, a status that migrated to the wrong equivalent, a note field that lost its line breaks and is now unreadable. Budget time to act on what they find, because discovering issues you then ship anyway is worse than not asking.
Cutover
By cutover day the migration should be boring. Run the final extract, run the load, run the reconciliation checks you have already run several times, have the sign-off owners confirm their areas, and open the new system.
Keep the old system available in read-only mode for a defined period. Users will want to check something against it, and read-only access is the cheapest way to build confidence in the new system. Set a date for switching it off and communicate that date, because a legacy system left running indefinitely tends to keep getting used.
The first two weeks after
Watch for records that users edit immediately after opening them, since that pattern usually indicates something migrated in a form people do not trust. Keep a visible list of reported data issues with owners and status, because visibility is what stops a handful of problems from becoming a general belief that the data is bad.
Run the key reconciliation numbers again at the end of the first week and the first month. Slow divergence points at an integration or an automation behaving differently than it did in the old system, and that is much easier to trace at two weeks than at six.
Getting help with a migration
Jaybird Technologies plans and runs data migrations for business applications, including moves between platforms, consolidations of several systems into one, and migrations into or between environments on platforms such as Quickbase. We handle the field mapping, the transformation rules, and the reconciliation work, and we build the migration so it can be run and rerun until the numbers are right.
Request a consultation if you have a migration coming up and want a second set of eyes on the plan.