Data migration when moving to new software: why we count records three times

When an organisation moves to a new system, it mostly talks about the new system's features. Screens, reports, the mobile app. How the data in the old system will be moved usually gets left until last and is waved through with "we'll do a transfer".

That is where projects really get stuck. The code works and the screens are ready, but some of the customer records come through missing, and nobody knows how many or why.

In this post I explain what we check before, during and after a migration.

Inventory first

We do not start a migration with the first line of code. We start with a list. Which tables are in the old system, how many records each one holds, what each field means and which ones are no longer used.

The most common surprise on this list is the same information being held in two places. The customer's address sits in both the customer table and the invoice table, and the two do not match. Which one counts as correct is not a technical decision. It is a decision for the organisation. It has to be asked before the migration.

The second surprise is that the old system is an Excel file. In Excel, every field without a rule is a field where someone, one day, typed something different "temporarily". You find rows with "none" in the date column and cells with two numbers in the phone column.

Field mapping

Two systems do not hold data in the same way. The old system may have a single "full name" field while the new one has two separate fields. A city that was free text in the old system may be picked from a list in the new one.

For each field we write a mapping rule: the old field, the new field and the conversion between them. Records that do not fit the rule are not quietly corrected. They go onto a separate list. We review that list one by one together with the organisation.

Silent correction looks tempting, but it is the worst thing you can do in a migration. A faulty record was faulty in the old system. If it is corrected by guesswork during the migration, it becomes a record in the new system that looks right but is wrong. Nobody will ever notice it again.

Counting three times

When the migration is finished, we count records at three points before we say it is complete.

The first is the source. The number of records in the old system that need to be migrated, table by table.

The second is the transfer. The number of records the migration script read, converted and set aside on the separate list.

The third is the target. The number of records actually created in the new system.

The source must equal the records transferred plus the records set aside. The records transferred must equal the target. If these two equations do not hold, the migration is not finished, however small the difference. A difference of a few hundred records means a few hundred customers' histories have been lost.

The numbers matching is not enough on its own. The numbers can match while the content is corrupted. So we open randomly selected records side by side in both systems and compare them field by field. For critical fields such as amounts and dates, we also calculate totals and summary values separately on both sides.

Trial migrations and a rollback plan

We do not do the real migration for the first time on the real day. First we do at least two trial migrations with a copy of the data. In each trial we measure the time, review the records set aside and fix the mapping rules.

Before the real migration, a rollback plan is put in writing. What happens if the migration stops halfway or the numbers do not match, up to what point you can go back to the old system, and who makes that decision. If these questions are asked on migration day, the answers are given in a hurry.

Two systems together for a short time

Where possible, after the new system goes live, the old system also stays open in read-only mode for a short time. If a record looks missing or wrong, you can check the old system and compare.

The end of this period is set from the start. If it is not, the two systems live side by side for months, and the debate about which one is correct starts all over again.

Not migrating is also a decision

Not every old record has to be moved to the new system. Migrating data whose retention period has expired keeps the organisation holding that data for no reason. For personal data, this can also turn into a breach of obligations under KVKK, Turkey's data protection law.

A migration is a good moment for this clean-up. We identify the records whose retention period has expired, leave them out of the migration with the organisation's approval and record the deletion. That way the new system starts clean.

In short

Data migration is not a transfer. It is a small project. A migration done without an inventory, mapping, three-stage counts, trial runs and a rollback plan loses trust on the new system's first day.