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

A client came to us mid-project. Their ERP migration had been running for eight months against a six-week estimate, and the previous implementation partner had been replaced. The new system was built. The data was not in it. The failure had nothing to do with Odoo, nothing to do with the implementation team's technical skill, and nothing to do with how the migration scripts were written.

The source system had eleven years of records. About 40% of the supplier master had duplicate entries — different spellings, different address formats, some active, some inactive, some that existed only because someone made a typo in 2017 and the correction was never applied to the original record. The chart of accounts had GL codes that referenced cost centers that no longer existed. Inventory records had negative quantities. Nobody had noticed because the old system had been generating reports that manually excluded these records for so long that excluding them had become the default.

The audit that nobody wants to pay for

Every data migration needs a pre-migration data audit. Not a quick look — a systematic profiling of every entity you are moving: row counts, null rates, duplicate rates, referential integrity violations, value distributions on key fields.

We run this in the first two weeks of every ERP engagement now, 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. We have had clients 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. We have never had a client who did the audit regret it. We have had several who skipped it and wished they hadn't.

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 handles maybe 60% of duplicates in a supplier master if the data is reasonably structured. The other 40% requires 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 2–5x more complex than the initial 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 failure rate is usually below 0.5% of records, which is close enough to handle manually 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 — typically the last 24 months for reporting purposes, 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. We have seen clients extend their go-live timeline by two months trying to migrate seven years of history that nobody will ever query. Migrate what matters. Archive the rest.