ERP Data Migration: Where Projects Quietly Fail
The most underestimated line in any ERP project. What actually goes wrong, the validation step teams skip, and why moving all your history is the wrong default.
Data migration is the line item everyone underestimates and nobody wants to own. It is also where an implementation stops being late and starts being wrong.
The failure is rarely dramatic. Records move, the system goes live, and a year later somebody looking for a job from four years ago finds it incomplete. By then the old system is gone or read-only, the migration team has moved on, and the gap is permanent.
This is written for whoever has been handed the migration workstream and is realising it is larger than the plan suggested.
What actually goes wrong
1. The old data was never as clean as anyone claimed. Every legacy system holds the same archaeology: the same customer entered four ways, part numbers reused after a renumbering, a field repurposed in 2019 for something unrelated to its name, and records created by people who left. None of this is visible until you try to map it.
2. Mapping decisions get made by whoever is available. When two systems model something differently, somebody chooses. If that person is a consultant working from a spreadsheet rather than someone who knows why the field exists, the choice gets made on structure rather than meaning, and the meaning is what mattered.
3. Nobody validates against the source. Records get loaded, counts look plausible, everyone moves on. Validation means comparing totals, counts, and specific records against the old system deliberately. Skipping it is the single most common cause of a migration that appears successful and is not.
4. History is treated as lower priority than master data. Customers and parts get attention because they are needed on day one. Closed work orders, old quotes, and superseded revisions get a lighter pass, because nobody needs them this week. They are also exactly what an auditor or a warranty claim will ask for.
5. Attachments and documents get left behind. Drawings, certs, signed travelers, inspection photos. They live outside the tables, they are large, and they are frequently deferred to a phase two that does not happen. In a regulated shop this is the most expensive omission on the list.
The validation step, properly
Validation is not a spot check the day before cutover. Three passes, all cheap relative to what they prevent.
Counts. Row counts per entity, old versus new, with every difference explained rather than tolerated. A gap of 3% is not rounding, it is a rule that dropped records nobody has looked at.
Totals. Financial and quantity sums where they exist. If open AR does not tie out, the mapping is wrong somewhere specific and this is how you find where.
Traces. Pick real records, ideally awkward ones, and follow them end to end in both systems. The job with the change order and the partial shipment tells you more than a thousand clean rows.
Have someone who knows the business do the traces. A technically perfect migration can still be semantically wrong, and only a person who knows what the data meant will catch it.
The default that costs the most: moving everything
The instinct is to bring all history across, because leaving anything behind feels like loss. It is usually the wrong call, and it is the decision that inflates migration cost more than any other.
A better pattern is to move what the new system needs to operate, keep the rest accessible, and be deliberate about the line. Open orders, active customers, current parts and revisions, and inventory have to move. Ten years of closed work orders usually do not need to live in the new system to remain retrievable.
| Strategy | Migration cost | Day-one risk | Old records still answerable |
|---|---|---|---|
| Move everything | Highest | High, more to go wrong | Yes, in the new system |
| Move active, archive the rest | Moderate | Lower | Yes, via the archive |
| Move active, keep old system read-only | Lowest | Lowest | Yes, while the licence lasts |
| Move active, discard history | Low | Low now, high later | No |
Row three is attractive and carries a trap worth naming: it depends on a licence you intend to stop paying. Read-only access is a legitimate strategy only with a defined end date and a plan for what happens then, otherwise it is row four on a delay.
The part vendors do not volunteer
Ask your outgoing vendor for an export early, before you give notice. What they will provide, in what format, and how long it takes are all things you want to know while you still have leverage. Some exports arrive as a documented schema. Others arrive as a pile of CSVs with no relationships, which is technically compliance and practically a different project.
Attachments are the specific thing to ask about, because they are often stored separately and are often not included by default in what a vendor calls a full export.
Questions we get
How long does migration take? It scales with how bad the old data is, not with how much there is. A clean million-row system is faster than a messy hundred-thousand-row one. Anyone quoting a duration before looking at your data is quoting a hope.
Should we clean the data first or migrate then clean? Clean what blocks the mapping, migrate, then clean the rest in the new system where the tooling is better and the problems are visible. Cleaning everything first is how migrations spend six months before moving a single record.
Who should own this on our side? Someone who knows what the data means, not only who administers it. The most valuable person in a migration is usually the one who can say why a field was used that way in 2019.
What about running both systems in parallel? Do it where you can. It is the cheapest insurance available and cutting it short to save a month of licence fees is a common and expensive instinct. Parallel operation is the step that turns a cutover from an event into a decision.
How does this affect our audit position? Directly, if you are certified. Traceability and document control both depend on history being retrievable, so the archive decision is a compliance decision as much as a cost one. The AS9100 requirements cover what has to remain answerable.
What about the data nobody can explain? Every legacy system has some, and the honest handling is to isolate it rather than guess. Move it to an archive, document that nobody could account for it, and move on. Guessing at meaning during a migration bakes a wrong assumption into the new system permanently, which is worse than an acknowledged gap.
Should we cut over during a shutdown week? It is tempting and it quietly removes your ability to react. A quiet period is a reasonable window only if the people who understand the data are actually available, and during a shutdown they usually are not. A normal week with everyone reachable beats a quiet week with nobody.
Is migration cheaper on a custom build? Not inherently. The difference is that the target schema can be shaped around your data rather than the reverse, which removes a category of mapping compromise. The archaeology in the old system is the same either way.
What is next
Before committing to any implementation date, do a small extract from the old system and look at it properly. An afternoon with real records tells you more about the true scope than any vendor assessment, and it usually reprices the project honestly rather than optimistically.
If you are still deciding where you are migrating to, the comparison of building, buying, and partner-built covers the paths, and the five failure modes cover what happens after the data lands.