Data migration is the part of a system change that everybody underestimates, and it is where a decade of business history quietly goes missing. Not dramatically — nobody notices on the day. It surfaces four months later when somebody needs the correspondence from a job in 2022 and it is not there.

Here is how to do it properly.

Decide what moves before you decide how

The instinct is to move everything. It is almost always wrong.

Migrating fifteen years of records costs several times what migrating three years costs, takes considerably longer and carries every data quality problem you have ever had into a clean new system.

Split your data into three categories:

  • Active. Current customers, open jobs, live contracts. Must migrate, must be perfect.
  • Recent history. Typically two to three years. Migrate, and accept some imperfection.
  • Archive. Everything older. Keep it readable somewhere — an export, a read-only copy of the old system, a document archive — but do not migrate it.

Nearly every business we take through this ends up migrating far less than it intended and being glad of it.

The five stages

1. Scope

What data exists, where it lives and what depends on it. This stage always finds something unexpected: a spreadsheet somebody maintains alongside the system, a field being used for something other than its label, attachments stored outside the database.

Also identify what is not in the system. Business knowledge held in email, in a folder of documents, in somebody's head. That does not migrate automatically and it needs a plan of its own.

2. Cleanse — before you move

The stage most often skipped and the most valuable.

Deduplicate. Standardise formats — dates, phone numbers, postcodes, company names. Close records that should have been closed years ago. Fix the entries where somebody used a text field as a free-for-all.

Do this in the old system, or in an export, before anything moves. Cleaning after migration is much harder because the bad data is now entangled with new records created after go-live.

3. Map

Every field in the source, matched to a field in the destination. Written down, in a spreadsheet, reviewed by somebody who understands the business rather than only the systems.

Three things this stage exposes:

  • Fields with no destination. Decide explicitly: drop it, or add a custom field. Do not leave it undecided, because undecided means lost.
  • Fields being used unofficially. The notes field that holds the delivery instructions everybody relies on.
  • Different assumptions. The old system allowed one contact per company, the new one expects several. That is a business decision, not a technical one.

4. Trial and reconcile

Run the migration into a test environment. Then reconcile properly, which means three checks:

  • Counts. Records of each type, source against destination. Any difference must be explained rather than accepted.
  • Totals. Financial values in particular. If the total invoiced value differs, something is wrong even if the counts match.
  • Samples. Twenty records checked field by field, by somebody who knows what they should say. Include the awkward ones — the customer with three addresses, the job that was cancelled and restarted.

Expect the first trial to fail. That is the purpose of running one.

A migration tool reporting success means the records were written. It says nothing about whether they are right, and those are entirely different claims.

5. Cut over

Freeze changes in the old system, run the final migration, reconcile again, then open the new system.

Keep the old system available in read-only form for at least six months. This is not optional and it is where the money is well spent — it is the answer to every awkward question in the first two quarters, and it costs a fraction of a rushed data recovery.

The decisions to make in advance

Who owns data quality? A named person who decides what is a duplicate and what happens to records that do not fit.

What is the acceptable loss? Perfect migration of fifteen years is not achievable at a sensible cost. Decide what would be genuinely damaging to lose and protect that absolutely.

How long can you be down? This drives whether you can do a clean freeze-and-move or need a phased approach with a period of dual running.

What is the rollback? If the new system is unusable on day three, what happens? An answer that requires the old system still being available is another reason to keep it.

What it costs and how long it takes

For a small business moving one system: four to eight weeks end to end, and £2,000 to £8,000 depending on data volume and how cooperative the systems are.

The technical transfer is frequently a single day. The scoping, cleansing, mapping and reconciliation are where the time goes and compressing them is precisely what causes migrations to fail.

The upside nobody mentions

A migration is the only time a business will genuinely clean its data. The cleansing stage is worth doing on its own merits, and businesses routinely find dormant customers worth contacting, duplicate records that were distorting their reporting and jobs never invoiced.

Our data migration team handles this work for UK businesses, including the reconciliation that proves it worked. Get in touch before you commit to a new system, because the migration scope should influence which system you choose.