Data Migration: How to Plan, Test, and Verify the Move
A data migration is finished not when the copying stops, but when you can show that the new system holds everything it should, in a form your team can actually work with. Getting there takes six steps: decide what moves and what stays behind, map every field and rule, clean the data before you move it, rehearse the whole migration on a copy more than once, verify the result with counts, totals and checks by the people who use the data, and plan the switch – including a way back. The copying itself is usually the easiest part.
Why migrations go wrong
Most problems are not technical failures but surprises in the data:
- The data is messier than anyone thought. Duplicate customers, phone numbers in five formats, notes typed into fields meant for numbers, records created under rules that changed years ago.
- The two systems describe things differently. Google Cloud's migration guidance notes that "sometimes data types between the source and target databases are not equivalent" – a timestamp in one system may end up as plain text in another.
- Rules live only in the old code. Status "3" may mean "paid but not shipped", and nobody ever wrote it down.
- The switch is planned as one big moment, with no rehearsal and no way back.
Small differences are enough. Outside any migration, we saw this in the auto-data.net app: an Italian user found it stuck in English after the language code changed from "it" to "it,it-IT,it-CH". The same language to a person, no match to software; the fix was normalising the codes in the API. Every such case needs an explicit rule.
Step 1: Decide what moves – and what doesn't
Start with an inventory of every place data lives today: the main database, spreadsheets kept "on the side", uploaded files and attachments, user accounts and permissions, history and logs. Then decide category by category.
Not everything has to move. Google's guidance treats filtering as a normal part of a migration – for example, cancelled orders might not be transferred at all. For personal data there is also a legal angle: the European Commission's GDPR guidance says personal data must be stored "for the shortest time possible" and that organisations should set time limits to erase or review it, while respecting legal obligations to keep some records for a fixed period, such as tax rules. A migration is the natural moment to apply those limits.
| Data | Typical decision |
|---|---|
| Active customers, open orders, current balances | Move, and verify most carefully |
| Closed orders and old documents | Move, or keep in a read-only archive – check legal retention periods first |
| Attachments and uploaded files | Move, and check that every link to a record still works |
| Users and permissions | Recreate deliberately rather than copy blindly |
| Test records, abandoned drafts, data nobody needs | Usually leave behind |
A migration is also a good moment to check who can see and change what.
Step 2: Map every field and every rule
The core document of any migration is the mapping: for each field in the old system, where it goes in the new one, how it is converted, and who agreed to that. Typical decisions:
- how to split a single "name" field into first and last name, and what to do with company names;
- how to convert dates, currencies, units and statuses;
- which record wins when two customers are duplicates;
- what happens to empty or invalid values – a default, a flag for review, or rejection;
- how text is encoded – older systems sometimes store Cyrillic or other non-Latin text in older encodings rather than UTF-8, and it must arrive readable.
Daily users know rules no document mentions, so walk through real records with them. For custom-built systems, part of the job is reading the code for hidden rules – see our article on improving, rebuilding or replacing legacy software.
Step 3: Clean before you move
Cleaning is cheaper before the move, while the old system and the people who know it are still at hand. Merge duplicates, fix obviously wrong values, fill in mandatory fields the new system will require. The Commission's guidance also reminds organisations that personal data must be accurate and kept up to date, and that inaccurate data should be corrected or deleted. Agree in advance what "clean enough" means and who in the business decides – otherwise cleaning never ends.
Step 4: Rehearse on a copy, more than once
Run the full migration on a copy of real data, check the result, fix the scripts and run it again. Microsoft's go-live checklist for business systems asks teams to "test data migration several times before cutover", to address data corruption and duplication, to make sure the migration fits in the time window available for the switch, and to get the business to sign off. Its cutover guidance adds that the final rehearsal should be as close as possible to the real one: the same data, tools, people, steps and timing.
Google adds two points. Testing at the data level "does not replace testing on the client level", so people should work with the migrated data in the new system – find a customer, issue an invoice, run last month's report. And avoid changing the schema, data values or migration process mid-migration. Time each rehearsal so you know how long the real switch will take.
Step 5: Verify – how to prove nothing was lost
No single check is enough. Combine several:
| Check | What it catches |
|---|---|
| Record counts per type (customers, orders, invoices, products) | Missing or duplicated records |
| Totals: invoice amounts, balances, stock per warehouse | Lost lines, rounding and currency errors |
| Field-by-field comparison of a sample chosen by users | Wrong mapping |
| Edge cases: oldest record, longest name, special characters, empty fields | Conversion and encoding problems |
| Relationships: every order has a customer, every line an invoice | Broken links between records |
| Everyday tasks and reports compared with the old system | Missing business rules |
For large databases, tools can compare automatically. AWS Database Migration Service, for example, can compare each row in the source with its corresponding row in the target and report mismatches. Its documentation also shows the limits: tables need a primary key or unique index, rows that keep changing cannot be validated, and if applications write to the target during validation, discrepancies might not be reported accurately. So freeze changes while you verify, and treat automatic checks as a complement to checks by people who know the business.
Step 6: Plan the switch and the way back
Write a cutover plan. Microsoft's guidance lists what it should contain: the order and timing of each task, an owner and a backup owner, instructions, verification and sign-off steps, and a rollback plan. Agree in advance who makes the final go/no-go decision and on what criteria.
- Choose a quiet time. Avoid month-end closing, peak season and holidays.
- Freeze changes. Stop data entry in the old system during the final run, or capture changes made during the move.
- Back up both sides. Google recommends backing up the target before users get access, so its initial state can be recovered.
- Decide on the way back. Google's guidance is blunt: "It's critical to decide whether to implement and test fallback processes or understand the consequences of not doing so." For most small and mid-sized businesses this means keeping the old system intact and read-only until the new one has proved itself.
Websites: addresses are data too
When a website moves to a new system, its URLs are part of the migration. Every old address that has links or search traffic needs a permanent redirect to its new equivalent. When we split our own site from seoandweb.eu/bg/ and /en/ into seoandweb.bg and seoandweb.eu, that meant about 45 permanent (301) redirects plus hreflang links between the language versions. More in how to redesign a site without losing Google rankings.
After the switch
- Log every issue reported in the first weeks and fix the cause, not only the record.
- Before you finally switch off the old system, take a final backup – Google notes it may also be needed for compliance – and make sure you know how to read it.
If you are moving from spreadsheets or an old system to new software, we can help map the data, write and rehearse the migration and set up checks your team can trust. See our web-based software development service, read when a business has outgrown spreadsheets, or contact us to describe what needs to move.
Sources
- Database migration: Concepts and principles (Part 1) – Google Cloud
- Database migration: Concepts and principles (Part 2) – Google Cloud
- AWS DMS data validation – AWS Documentation
- Use the go-live checklist to make sure your solution is ready – Microsoft Learn
- Transition to new solutions successfully with the cutover process – Microsoft Learn
- How long can data be kept and is it necessary to update it? – European Commission