Data Migration From Legacy Systems: How to Plan It Without Losing a Record

Prices are in US dollars. Euro and pound figures in parentheses are approximate, rounded conversions. Quotes are issued in the currency of your contract.

Data migration is the part of a modernization program that cannot be partly right. A new system with a few rough screens is a new system. A new system with 2 percent of the invoices missing is an incident, a finance investigation and, in regulated industries, a reportable event. Yet migration is routinely treated as a task at the end of the build, done once, on the weekend of the cutover, by whoever is available. This post lays out the plan that experienced teams follow instead: six phases, three rehearsals, named sign-offs, and a decision in advance about what to do with data that is wrong in the old system.

What are the phases of a data migration?

Across a modernization program, migration is 10 to 20 percent of the budget. On a $200,000 to $600,000 (€175,000 to €520,000, £150,000 to £445,000) core platform rebuild that is $20,000 to $120,000 (€17,000 to €105,000, £15,000 to £90,000), most of it in cleansing and rehearsals. Teams that quote it as a line item worth a weekend are the ones that lose records. Budgeting a multi-year program shows where it sits in the whole.

Why is the three-rehearsal rule non-negotiable?

The first full run of a migration always fails. A field is longer than the target allows, a date format from 2009 is different, a lookup table has a code that no longer exists. That is the point of the first run: to find those things in a copy, not in production. The second run proves the fixes and produces a realistic timing. The third run is the dress rehearsal, executed from the runbook by the people who will do the cutover, at the time of day the cutover will happen. If the third run needs a fix, there is a fourth. The live cutover is the first run that has nothing new in it.

Each rehearsal ends in reconciliation: for every entity, the count in the source, the count in the target, the count rejected, and for anything with money in it, the totals. Customers, open orders, invoice balances, stock quantities. A migration is correct when those numbers match or every difference is explained in writing.

Who has to sign off, and on what?

• Data owners sign the mapping. The head of finance signs how invoices map. The head of operations signs how orders map. Engineers propose, owners decide.
• Data owners also sign each rehearsal's reconciliation. They look at the counts and the totals and at a sample of their own records in the new system, and they put their name on it.
• The program sponsor signs the go or no-go on cutover day, against criteria written weeks earlier: reconciliation within tolerance, runbook completed within the window, rollback tested.
• Compliance or legal sign retention decisions: what is migrated, what is archived, what is deleted, and for how long the old system remains readable. In regulated industries this is not optional.

Sign-offs are not ceremony. They are the mechanism that makes the business look at its own data before it is too late to fix.

What do you do with data that is wrong in the old system?

Profiling always finds it. Thousands of customers with no country. Two product codes for the same product. Orders from 2014 referencing a warehouse that closed. The migration cannot fix the business's data, but it has to decide what to do with it, and the decision belongs to the data owner, not the engineer. There are four options, and the mapping document should name one for every category of bad data found:

1. Fix at source, before migration, by the business. Best for data that is still in use and has an obvious correct value.
2. Transform in flight, with a rule: default missing countries from the postal code, merge known duplicate codes. Best for systematic problems with a systematic answer.
3. Migrate as is and flag, so the new system holds the record with a marker for later cleanup. Best for history nobody uses but nobody can delete.
4. Do not migrate. Archive it readable and leave it out of the new system. Best for closed records past their retention need. This is where migrations get smaller and cheaper, and it is the option most often forgotten.

When do two systems have to run side by side?

Incremental modernization, the kind described in modernization without the big bang, often means the old and the new system are both live for months, each owning part of the data. That is workable if three things are true: one system is the system of record for each data type, the synchronization between them is one-directional wherever possible, and a reconciliation report runs every day and someone reads it. The integration post covers the system-of-record decision. Two-way synchronization of the same records is the thing to avoid at almost any cost. Where the business insists on it, budget for conflict rules, and expect to spend time on the conflicts.

What does the cutover weekend actually look like?

1. Freeze. The old system goes read-only at a published time. The business has been told for weeks.
2. Extract and load from the runbook, with timings checked against the rehearsal.
3. Reconcile. Counts and totals, against the signed tolerances.
4. Smoke test. Named users execute named transactions in the new system: raise an order, post an invoice, find a customer.
5. Go or no-go. The sponsor decides against the written criteria. Rollback means the old system comes out of read-only. It has been tested.
6. Hypercare. Two to four weeks of daily reconciliation and fast fixes, with the old system still readable for lookups.

How 7L approaches data migration

7L International is a custom software development and digital transformation firm with hubs in New York, London and Athens that has supported more than one hundred organizations, many of them through migrations from systems that had run the business for a decade or more. We profile first, because the documentation is never right, and we put a reconciliation report in front of the data owners after every rehearsal. We prefer to shrink migrations by archiving what does not need to move, and we run old and new side by side only with a clear system of record and one-way sync. The enterprise software development guide shows how migration fits in a wider program. If you are planning to move off a system and are worried about the data, tell us what is in it, and we will tell you what the migration plan should contain.