ERP Data Migration: What Actually Goes Wrong (And Why)

Take a migration that has run for eight months against a six-week estimate. The new system is built. The data is not in it. The delay usually has nothing to do with the ERP or with how well the migration scripts were written.

Look at the source system instead. It holds eleven years of records, and the supplier master is full of duplicates: different spellings, different address formats, some active and some not, and some that exist only because someone made a typo in 2017 and never fixed the original. The chart of accounts has GL codes pointing at cost centers that no longer exist. Inventory records show negative quantities. Nobody noticed, because the old system's reports have excluded these records for so long that excluding them became the default.

The audit that nobody wants to pay for

Every data migration needs a pre-migration data audit. That means systematic profiling of every entity you are moving: row counts, null rates, duplicate rates, broken references and the spread of values in key fields.

We run this in the first two weeks of an ERP engagement, before any build work starts. The output is a data quality report that tells the client exactly what they have: how many supplier records, how many duplicates, which fields are empty that should not be, which relationships are broken. People often refuse to believe the numbers. "That can't be right, we maintain those records carefully." It is almost always right.

The audit takes about two weeks for a mid-sized company. It costs time and money. Skipping it costs more time and more money six months later, when the migration is failing.

Data cleaning is the project, not a task within it

After the audit, you know what you have. Then someone has to fix it. This is the part that project plans almost never account for correctly.

Automated deduplication catches the obvious duplicates in a supplier master if the data is reasonably structured. The rest need human judgment. Is "Al-Noor Trading Co." the same entity as "Alnoor Trading"? Is the second address a branch office or a data entry error? These decisions cannot be automated. They require someone from the finance or procurement team who knows the supplier relationships to sit down and make calls. That person has a day job. Getting their time is harder than writing the migration scripts.

Budget for data cleaning as its own workstream, with its own timeline, its own owner from the client side, and its own sign-off criteria. "Data cleaning: 1 week" in a project plan is how migrations fail.

The "just export and import" assumption

The most common mental model of a data migration is: export from old system, transform to new format, import to new system. For small companies with recent data and clean records, this is sometimes true. For companies that have been running an ERP for more than five years, it is almost never that simple.

Source systems accumulate workarounds. A company running Odoo 10 might have a custom module that stores payment terms in a field that has a different meaning in Odoo 17. Their purchase orders might reference a document type that was retired in a system upgrade but never cleaned up. Their inventory might include items that were moved to a different module in a previous implementation and exist in two places simultaneously.

ETL transformation logic for a real ERP migration is usually far more complex than the first estimate suggests. The only way to know how complex it is before you start is to examine the source data in detail, not the source system documentation. Documentation describes what the system was designed to do. The data shows what actually happened in it over years of use.

Run the migration three times before you run it for real

The migration script that works on a sample of 500 records will fail on the full dataset. There will be edge cases in records 4,000–6,000 that are not represented in your sample. You need to run the full migration against a staging environment, inspect the results, find the failures, fix the transformation logic, and run it again.

We typically run a full migration three times before the live cutover: once at week four to find the obvious failures, once at week eight after data cleaning to find what's left, and once in the week before go-live against the most recent data snapshot. Each run surfaces new edge cases. By the third run, the failures left should be few enough to fix by hand at cutover.

The test migrations also give you a realistic timing estimate for the live cutover. Knowing that a full migration of your company's data takes eight hours means you plan your cutover window accordingly. Going into a Friday-night cutover not knowing how long it takes is how people end up in the office Sunday morning.

What to migrate and what to leave behind

Not everything needs to come across. This is a decision most clients resist because it feels like data loss, but migrating everything uncritically is how you bring twelve years of bad data into a clean new system.

A reasonable approach: migrate open transactions (open purchase orders, unpaid invoices, current inventory) fully. Migrate master data (suppliers, customers, products, chart of accounts) after cleaning. Migrate historical transactions selectively, often the last 24 months for reporting, with older history kept accessible in the old system or an archive for the statutory retention period.

The old system does not need to go away on go-live day. Running it in read-only mode for 12–18 months for historical lookups costs almost nothing and eliminates a significant source of user anxiety about the migration. Projects lose months trying to migrate years of history that nobody will ever query. Migrate what matters and archive the rest.