Data Migration Checklist: Don't Lose Data

I've spent a good part of twenty years cleaning up migrations someone else already declared finished. It's a project that looks done long before it actually is: the records appear in the new system, everyone celebrates, and three weeks later someone notices half the historical notes are missing or the dates are a month out. By then it's not a migration problem, it's an archaeology problem — and a painful one. Here's the checklist I work through to stop that happening.

Before you move anything

1. Profile the source data first. You can't migrate data you don't understand. Count the records, check the formats, find the blanks, spot the duplicates. Surprises found now are cheap; surprises found after go-live are not.

2. Agree what "done" looks like. Define the success criteria up front: which fields must come across, what counts as a complete record, and how you'll prove it worked. A migration without an agreed definition of done never really ends.

3. Build a field-level mapping document. Every source field should map explicitly to a target field, with the transformation rules written down. This is the single most valuable artefact of the whole project — and the one most often skipped.

During the move

4. Transform deliberately, not on the fly. Date formats, phone numbers, picklist values, currencies — these rarely match between systems. Decide the rules in advance and apply them consistently, rather than fixing things ad hoc as they break.

5. Handle the awkward cases on purpose. Records with missing required fields, duplicates, and "orphaned" data with no obvious home will exist. Decide what happens to them before the migration, not in a panic during it.

6. Run a trial migration. Always do a full dress rehearsal into a test environment first. The first run always teaches you something the planning didn't.

The goal of a migration isn't to move data. It's to move data you can prove is complete and correct.

After the move — the part people skip

7. Reconcile, don't eyeball. Compare source and target systematically: row counts, totals, spot-checks of high-value records, field-by-field validation. "It looks fine" is not validation.

8. Produce a discrepancy report. Document every check you ran and every exception found, with a severity rating. This is your sign-off evidence — and your protection if a question comes up later.

9. Keep the source data accessible. Don't decommission the old system the moment the new one is live. Keep a verified export until you're genuinely confident nothing was lost.

Do it yourself, or use data migration services?

The checklist above is genuinely runnable in-house for a straightforward move — one system to one system, a few thousand records, fields that map cleanly. Plenty of businesses do exactly that and it goes fine.

It stops being a DIY job at fairly predictable points:

• Several sources into one destination. The complexity isn't linear; it's in the reconciling — deciding which system wins when two disagree about the same customer.
• The fields don't map. When the old system stored something the new one has no place for, that's a decision about the business, not a technical step, and it needs someone who will surface it rather than silently drop the data.
• You can't afford downtime. Migrating a live system without a gap requires a rehearsed cutover and a tested rollback, which is a different exercise to a weekend copy.
• The data is regulated. Health, financial or personal data carries obligations that survive the migration, including what happens to the copy left behind on the old system.
• Nobody owns it. The most common failure isn't technical — it's that the migration was somebody's third priority and the validation step got skipped under deadline pressure.

A migration strategy is mostly about sequence and reversibility: what moves first, what gets validated before anything is switched off, and how you get back if it goes wrong. If those three questions have clear written answers, the tooling matters much less than people assume.

The one thing worth insisting on, whoever runs it: do not decommission the old system on go-live day. Keep it readable, and keep it long enough that a quiet month-end problem still has somewhere to be checked against.

Data cleaning services: the step that decides the outcome

Most of what gets called a migration problem is really a data quality problem that the migration exposed. The same customer entered four ways, phone numbers stored as text with spaces in random places, dates as free text, "unique" keys that turn out to be anything but, a "notes" field holding information that should have been three separate fields. A lot of work has usually gone into these systems over the years. That doesn't stop them being, underneath, completely cactus.

None of that is visible while everyone stays in the old system, because the humans using it quietly compensate — they know the data lives all over the joint and work around it without thinking. Move it somewhere that won't compensate and it surfaces all at once.

Data cleaning is the work of resolving that before the move: de-duplicating, standardising formats, filling or explicitly marking gaps, and deciding what to do with records that can't be salvaged. It's worth doing deliberately for one reason above all — a migration is the cheapest opportunity you will ever get to clean this up. Everything is already being touched, mapped and validated. Doing it later means a second project with none of that momentum.

It's also the step to be sceptical about when a quote looks low. Cleaning is where the hours are, and a migration quote that doesn't mention data quality is either assuming your data is perfect or planning to move the mess intact.

What about data migration tools?

There are plenty — platform-specific importers, ETL tools, purpose-built migration products — and the honest position is that the tool is rarely what decides the outcome.

A tool moves records from A to B according to rules you give it. It won't tell you that two systems disagree about which customer record is current, or that a field means something different than its name suggests. Those are judgement calls, and they're the actual work.

Where tools genuinely help: volume too large to handle manually, repeatable runs during rehearsal (which you want, because you'll do it more than once), and a record of what happened. Where they mislead: making it feel finished. A clean import log is not evidence the migration worked — only reconciliation against the source is.

Practical order: settle the mapping, clean the data, then pick whatever tool fits the systems involved. Choosing the tool first tends to shape the migration around the tool's assumptions rather than your business's.

Frequently asked questions

What should be on a data migration checklist?

Nine steps in three phases. Before you move: profile the source data, agree what "done" looks like, and build a field-level mapping document. During: transform deliberately rather than on the fly, handle awkward cases on purpose, and run a trial migration. After: reconcile systematically, produce a discrepancy report, and keep the source data accessible until you're confident nothing was lost.

Why do data migrations fail?

Rarely because the data was hard to move. They fail because nobody validated the result, the mapping lived in someone's head instead of a document, or the timeline didn't allow for the inevitable second run. The technical part is seldom the risk — the discipline is.

How do I check a data migration worked?

Reconcile, don't eyeball. Compare source and target systematically: row counts, totals, spot-checks of high-value records, and field-by-field validation. "It looks fine" is not validation. Then produce a discrepancy report documenting every check you ran and every exception found, with a severity rating — that's your sign-off evidence and your protection if a question comes up later.

Should I run a test migration first?

Always. Do a full dress rehearsal into a test environment before the real thing, because the first run invariably teaches you something the planning didn't. It's the cheapest place to discover that date formats, picklist values or currencies don't map the way you assumed.

What are data cleaning services?

Resolving data quality problems before a move — de-duplicating records, standardising formats like phone numbers and dates, filling or explicitly marking gaps, and deciding what happens to records that can't be salvaged. It matters because the humans using your old system quietly compensate for messy data, and a new system won't. A migration is also the cheapest opportunity you'll get to do it, since everything is already being touched, mapped and validated. A migration quote that never mentions data quality is either assuming your data is perfect or planning to move the mess intact.

Do I need data migration tools?

Usually less than people expect. A tool moves records according to rules you give it; it can't tell you that two systems disagree about which customer record is current, or that a field means something different from its name. Those judgement calls are the real work. Tools genuinely help with large volumes, repeatable rehearsal runs and keeping a record of what happened. What they don't do is prove the migration worked — a clean import log isn't evidence, only reconciliation against the source is. Settle the mapping and clean the data first, then pick the tool.

When can I switch off the old system?

Not the moment the new one goes live. Keep a verified export of the source data until you're genuinely confident nothing was lost. Migrations look finished long before they are — problems often surface weeks later, when someone notices historical notes are missing or dates are a month out, and by then it's painful to fix without the original.

Why migrations go wrong

Most failed migrations don't fail because the data was hard to move. They fail because nobody validated the result, the mapping lived in someone's head, or the timeline didn't allow for the inevitable second run. The technical part is rarely the risk — the discipline is.

This is methodical, unglamorous work — the hard yards — and it's exactly where an independent pair of hands earns its keep: someone whose job is to be sceptical, document everything, and prove the result before you commit. My own habit when something won't reconcile is boring and it works: map the whole structure out, write the scripts, run them, and don't call it done until source and target agree. I've run data migrations across CRMs, finance systems, legacy databases and sensitive records, in health, finance and small business alike — twenty years of it, on systems where a mis-formatted field doesn't just look untidy, it quietly stops the record reaching the next system entirely. The process stays the same regardless: careful, documented, verified. For anything involving personal information, the Australian Privacy Principles (administered by the OAIC) set the baseline for what "handled properly" actually means.

Planning a system change? Email us a description of what you're moving from and to, and we'll map out the safest approach and where the real risks sit.