How to Migrate Data Between Business Systems
The old system has been running since before anyone currently on staff was hired. It holds fifteen years of customers, jobs, notes and a few things nobody can explain. Someone has finally approved the budget for a replacement, and now a quieter question arrives. How do we get everything across without losing the parts we did not know we needed?
That question deserves more respect than it usually gets. Buying the new system is the easy part. Moving the data is where projects stall, because the data carries years of habits, shortcuts and half-finished clean ups. This guide walks through a migration in the order it should happen, with the checks that catch problems while they are still cheap.
Start by deciding what "done" means
Before touching a file, write three sentences.
- What the new system must be able to answer on day one that the old one answered.
- What history you will bring, and what you will leave behind in a read-only archive.
- Who signs off that the numbers are right.
That last point is the one people skip. A migration without a named person who accepts the result is a migration that never quite ends. In a small company this is usually the owner or the finance lead, and they need to be involved from the profiling stage, not only at the end.
Moving every record is rarely the right goal. Closed jobs from twelve years ago may be better kept in a searchable archive than loaded into a system where they will clutter every report. A common compromise is to migrate open items in full, the last two or three years of transactional history in summary or in full, and everything older into an archive you can still search.
Profile the old data before you promise anything
Profiling means measuring what is actually in the data rather than what the documentation says should be there. It is a few hours of work that changes the whole plan.
For each table or sheet you intend to move, collect these facts.
- Row counts, and the count of distinct values in each key column.
- How many values are empty in each field, and how many are placeholders such as "N/A", "TBD" or "000-000-0000".
- The range of dates, with attention to impossible ones such as a birth date in 1900 or an order in the future.
- The list of distinct values in any field that is meant to be a category, such as status or customer type. Fifty spellings of the same status are common.
- Which fields hold free text where people have stuffed structured information, such as a phone number in the notes field.
- Which fields are used and which have not been touched in years.
A spreadsheet pivot table is enough for a small dataset. For larger ones, a short query in whatever database holds the data will do. The output is a profile sheet, one row per field, with columns for fill rate, distinct values, odd findings and a decision.
Here is the kind of thing profiling finds. In a hypothetical customer table of 6,200 rows, 410 have no email, 93 have an email that is clearly someone else's, 1,100 appear to be duplicates of other rows, and the "State" field has 71 distinct values including "PR", "P.R.", "Puerto Rico" and "Pr". None of that is shocking. All of it will damage a migration if discovered mid-load.
Map every field, and write the rules down
A mapping document says where each old field goes in the new system and what changes along the way. Use one row per target field.
| Target field | Source | Transformation | Required | Notes |
|---|---|---|---|---|
| Customer name | CUST_NM | Trim spaces, fix capitalization | Yes | Keep original in a legacy field |
| Phone | PHONE1 | Strip symbols, store in standard format | No | Reject placeholders |
| State | ST | Map to a standard code list | Yes | Unknown values go to review |
| Status | STAT | Lookup table from old to new | Yes | Three old values merge into one |
| Legacy ID | CUST_ID | Copy unchanged | Yes | Needed for reconciliation |
Three habits make this document worth the effort.
Keep the old identifier. Store the legacy ID in the new system in its own field, permanently. It is how you trace any record back, how you reconcile, and how you answer the question that will come up six months later about where a strange record came from.
Build lookup tables for categories. If the old system has statuses of "Open", "OPEN", "open" and "O", a table that maps each to one new value is both the transformation and the documentation.
Decide on a policy for bad data. Each problem needs one of four answers. Fix it automatically, flag it for a person to review, load it with a default, or leave it out and log it. Choose per field and write it down.
Also note how the new system handles required fields and formats. Many systems refuse records that fail validation, and others accept anything and let the problem surface later. Know which kind you have bought.
Deduplicate with matching rules, not guesses
Duplicates are the most common surprise. The same customer exists as "Maria Santos", "Santos, Maria" and "M. Santos", with the same phone but different emails.
Matching works best in layers.
- Exact match on a strong key. A tax ID, a verified email, a license number. Anything that is unique by nature.
- Normalized match. Lowercase, strip punctuation and spaces, standardize abbreviations, then compare. "123 Calle Luna, Apt. 4" and "123 calle luna apt 4" now agree.
- Fuzzy match with human review. Names or addresses that are close but not identical go to a short review list, with the two records side by side.
Never merge on a weak signal alone. Two people can share a name, a household or a phone. The cost of wrongly merging two customers, which scrambles their histories and can expose one person's data to another, is higher than the cost of leaving a duplicate for later.
When you do merge, you need a survivorship rule, which is a decision about which record's value wins when the two disagree. A reasonable default is that the most recently updated non-empty value wins, except for a few fields such as legal name where the finance team's record wins. Write the rule. Keep the discarded record's legacy ID attached to the survivor so nothing becomes untraceable.
Run trial loads until they are boring
A trial load is a full run of the migration into a test copy of the new system. The aim is to run it several times, each time smoother than the last.
A sensible rhythm looks like this.
- Trial one uses a small sample of a few hundred records chosen on purpose, including the ugliest ones. Its job is to break things.
- Trial two runs the full dataset. It finds volume problems, such as timeouts, rate limits on the new system's interface, and runs that take eleven hours instead of the two you expected.
- Trial three and beyond repeat the full run with the fixes in place and include user acceptance checks, where the people who will use the system look up customers they know and confirm that the data looks right to them.
Make every load repeatable by script or saved tool configuration, so that a run is a button press and not a day of manual steps. If a migration depends on someone's memory of the right clicks, it will go wrong on the night.
Make loads idempotent where you can. That means that running the same load twice yields the same result rather than duplicate records. The usual technique is to use the legacy ID as a unique key and update the record if it already exists instead of creating a new one. It also lets you rerun a failed batch without cleaning up first.
Time every run. The duration of the final trial tells you how long the real cutover window must be.
Reconcile with numbers that someone can check
Reconciliation is how you prove the data arrived. It has two levels.
Counts and totals. For every entity, compare the number of records in the old system, the number you intended to move, the number loaded, and the number rejected. These should reconcile exactly, with each missing record explained. Then compare financial totals, such as open balances, inventory value and year to date revenue, between old and new. Illustrative example. If the old system shows 6,200 customers, the plan excludes 800 inactive ones, the merge removes 1,100 duplicates down to 380 survivors, and the rejects number 12, then the expected load is 6,200 minus 800 minus 720 minus 12, which is 4,668. If the new system shows 4,668, good. If it shows 4,671, you have three records to explain.
Spot checks by humans. Pick thirty records at random and ten chosen by risk, such as your largest customers and the oldest open jobs. A person who knows the business compares each against the source, field by field. Numbers can match while meaning is wrong, for example when every status has been mapped to a plausible but incorrect value.
Keep the reconciliation reports. They are your evidence if a question arises later, and in regulated fields they may be part of your records.
Plan the cutover like a small operation
Cutover is the moment the new system becomes the source of truth. Treat it as an event with a script.
- Choose the window. Pick a low activity period. In Puerto Rico, avoid the days around a forecast storm, and avoid the end of a month or a quarter when finance needs the old system most.
- Freeze the old system. Stop new entries, or move to read-only. Announce it ahead of time and say what staff should do for the few hours of the freeze, whether that is paper, a shared note, or simply waiting.
- Run the final extract and load. Use the script you have rehearsed, and time it.
- Reconcile immediately. Counts, totals and the spot checks, with the sign-off person present.
- Capture the delta. Anything entered in the old system after the extract began has to be moved. Keep this small by keeping the freeze tight.
- Switch connections. Repoint integrations, forms and email links to the new system. This is where forgotten dependencies appear, so go through your list of connections one at a time.
- Go or no go. Decide against criteria written beforehand.
Some teams run the old and new systems in parallel for a period, entering everything twice. It is the safest method and also the most tiring. It works for a few weeks on a narrow process, and becomes unsustainable beyond that. A lighter version is to keep the old system read-only for ninety days so that staff can look things up while they build trust in the new one.
Write the rollback plan before you need it
A rollback plan answers a single question. If we decide at hour six that this is not working, how do we return to a state where the business can operate?
A usable plan has these pieces.
- A full, verified backup of the old system taken immediately before the freeze, with proof that it restores.
- The decision point, meaning the time after which going back costs more than pushing forward, and who has authority to call it.
- A list of what to undo, such as redirected integrations and changed DNS or email settings.
- A way to reconcile any transactions that happened in the new system before the rollback, if there are any.
Most migrations do not roll back. Writing the plan still pays for itself, because it forces you to find out whether your backup actually restores, and it lets the team commit to the cutover without the dread of having no exit.
After go live
The first two weeks decide whether people trust the new system.
- Staff a visible support channel, and answer quickly.
- Keep a list of data issues found by users, and fix the causes, not just the symptoms.
- Run the reconciliation reports again at the end of week one and the end of month one.
- Archive the old system, with its documentation and the mapping document, and note the date you will decommission it. Do not turn it off before the first month end close.
- Remove the temporary permissions and accounts you created for the migration.
A checklist for Monday morning
- Write what done means and name the person who signs off.
- Profile one table, perhaps customers, and see what you learn.
- Start the mapping document with five rows.
- Find out how the new system accepts data, whether by file import, interface or vendor tool, and what its limits are.
- Ask the vendor what happens to your data if you leave, including format and fees.
If you are weighing a move and would like another opinion on the plan, Ozcorp InfraOps works with small teams on this kind of project, and InfraOps Base starts at $500 per month with vendor and license charges billed separately. The method above works with or without us. Profile first, rehearse often, and keep the door back open until you are sure.
Discuss your infrastructure requirements
Review the current service offer or schedule a conversation about the scope you need.
Stay in touch
Follow Ozcorp
Leave your details for occasional notes about new articles and Ozcorp updates.