ERP Data Migration: Best Practices and Common Pitfalls

Data migration is where ERP implementations succeed or fail at the most fundamental level. You can design a perfect system architecture, configure the software flawlessly, and train users comprehensively — and then have the implementation collapse because the data that feeds it is wrong.

The term "data migration" sounds technical and operational. In practice, it is an organization-wide exercise in confronting the accumulated data quality debt of years of operation. Duplicate customer records, inconsistent product codes, unreconciled inventory counts, missing supplier information, and legacy data structures that do not map cleanly to modern ERP data models — these problems do not disappear because a new system has been installed. They migrate, and they cause problems in the new system that are sometimes more expensive to fix than they were in the old one.

This guide covers the complete data migration lifecycle with specific, actionable guidance for each phase and honest descriptions of the pitfalls that most ERP migrations encounter.

Key Takeaways

• Data migration is typically the most underestimated phase of ERP implementation (budget 3–5x initial estimates)
• Begin data quality assessment six to twelve weeks before implementation kickoff
• Never migrate bad data — clean it first, migrate clean data second
• The "just migrate everything and clean it later" approach reliably fails
• Establish data ownership before migration: who is accountable for each data entity's quality?
• Build validation rules before migration, not during
• Plan for two to three migration test runs before the cutover migration
• Historical data and opening balances are separate problems with different solutions

---

The Four Types of Data in an ERP Migration

Not all data in an ERP migration is created equal. Understanding the four types and their distinct migration challenges is the first step to planning a successful migration.

Type 1: Master data

Master data is the foundation of every transaction: customer records, supplier records, products (items), chart of accounts, employees, and warehouse/location hierarchies. Master data quality directly determines transaction data quality — if a customer record is duplicated, every transaction associated with that customer will compound the duplication problem.

Master data migration is typically the most labor-intensive phase because it requires business decisions about every data quality issue encountered. Should duplicate customer records be merged? If so, which one is the authoritative record? How should products with inconsistent units of measure be standardized?

Type 2: Opening balances

Opening balances represent the financial state of the business at the point of go-live: accounts receivable (who owes you money), accounts payable (who you owe money), inventory values (what stock you hold and at what cost), and the general ledger account balances. Opening balances must be accurate to the penny — a $0.01 discrepancy in accounts receivable will cause reconciliation issues for months.

Opening balances are validated against the legacy system's closing state and must reconcile precisely before go-live can proceed. This validation often reveals discrepancies between the legacy system and the actual business state (invoices that were recorded but never sent, inventory that was consumed but not recorded, payments received but not applied).

Type 3: Transaction history

Transaction history is the record of what has happened: purchase orders, sales invoices, inventory movements, HR records, etc. Unlike opening balances, transaction history does not need to be perfectly accurate to enable operations — it needs to be accurate enough for reporting, auditing, and reference purposes.

For most implementations, transaction history from the last two to three years is sufficient. Older history can typically be archived in the legacy system for reference rather than migrated to the new ERP.

Type 4: Configuration data

Configuration data is not business data but rather the settings that make the system behave correctly: payment terms, tax rates, pricing rules, workflow configurations, user roles, and so on. While this is technically part of "migration," it is more accurately described as configuration replication — recreating the legacy system's business rules in the new ERP's configuration model.

---

Phase 1: Data Discovery and Assessment

The data discovery phase should begin six to twelve weeks before implementation kickoff — not after the implementation is already underway. Discovering serious data quality issues during implementation, rather than before it, is the most common cause of ERP timeline overrun.

The data inventory:

Catalog every data entity that exists in the legacy system(s):

• What entities exist (customers, suppliers, products, employees, etc.)
• Where each entity lives (which system or systems)
• How many records exist for each entity
• What format the data is in (relational database, flat files, spreadsheets)
• Who is the business owner responsible for each entity's quality

The data quality assessment:

For each major entity, run a quantitative quality assessment covering:

• Completeness (what percentage of records have all required fields populated?)
• Uniqueness (what percentage of records are duplicates or near-duplicates?)
• Consistency (are the same values represented consistently across records and across systems?)
• Accuracy (spot-check a sample of records against the ground truth — physical inventory, actual supplier invoices, actual customer correspondence)

Typical quality findings in mid-market ERP migrations:

• 8–20% of customer records are duplicates or near-duplicates
• 15–35% of product records have missing or inconsistent unit-of-measure data
• 10–25% of inventory records have balances that do not match the most recent physical count
• Legacy G/L accounts that have not been used in years and should be retired
• Employee records with inconsistent naming conventions, missing fields, or outdated role information

The migration risk assessment:

Based on the data quality assessment, classify each data entity by migration risk:

• Low risk: clean data, clear mapping to new ERP structure, standard format
• Medium risk: some quality issues, requires cleaning but manageable
• High risk: significant quality issues, ambiguous structure, or complex mapping requirements

High-risk entities need extended timelines, dedicated business owner involvement, and potentially specialized data cleaning tools or scripts.

---

Phase 2: Data Cleaning

Data cleaning is where the real work — and the real value — of data migration happens. The goal is to bring each data entity to a quality level that is suitable for migration to the new ERP.

Never migrate bad data. The temptation to migrate data now and clean it later is strong, especially when the cleaning is taking longer than planned. Resist it. Bad data in a new ERP is worse than bad data in a legacy system because:

• The new ERP's validation rules will flag errors that the legacy system ignored, creating immediate operational issues
• Users who encounter data quality problems in the new ERP blame the new system, not the underlying data quality issues
• Cleaning data after migration requires understanding the new ERP's data model, which is harder than cleaning data in the familiar legacy structure

Data cleaning process:

For each high- and medium-risk entity, apply a structured cleaning process:

1. Extract the data from the legacy system to a staging environment
2. Run automated validation rules to identify all quality issues
3. Prioritize issues by volume and impact (100 duplicate customer records > 5 missing supplier postal codes)
4. Resolve issues with business owner input (what is the authoritative record when two customer records conflict?)
5. Document decisions and resolutions for audit trail
6. Re-run validation rules to confirm all issues are resolved
7. Get business owner sign-off on the cleaned data before migration

Tools for data cleaning:

ECOSIRE uses custom Python scripts for automated validation and transformation, combined with Excel or Google Sheets for business owner review and approval of individual record decisions. For very large datasets, dedicated ETL tools (Pentaho, Talend, or cloud-native equivalents) provide better performance and audit logging.

---

Phase 3: Data Mapping and Transformation

Data mapping defines how data from the legacy system structure maps to the new ERP's data structure. This is a technical exercise with significant business input requirements.

Structural mapping challenges:

Legacy systems often have data structures that do not map cleanly to modern ERP structures. Common challenges:

• Chart of accounts restructuring: Legacy chart of accounts may have hundreds of accounts that need to be consolidated into a cleaner structure in the new ERP. Each consolidation decision requires finance team input.
• Product hierarchy changes: Legacy product catalogs often have ad hoc hierarchies that need to be restructured into the ERP's category/subcategory model. Each product needs to be mapped to its new category.
• Multi-currency reconfiguration: If the business has grown to operate in multiple currencies since the legacy system was implemented, the currency configuration in the new ERP needs to reflect the current state, not the historical one.

Transformation rules:

Beyond structural mapping, data often needs to be transformed before it fits the new system's format requirements. Transformation rules should be documented before the migration runs and reviewed by the business owner. Common transformations:

• Name standardization (all customers in First Last format, all suppliers with registered legal name)
• Phone number format normalization (all numbers in E.164 international format)
• Address validation and standardization (postcode validation, country code standardization)
• Unit of measure conversion (historical records in legacy units converted to ERP standard units)
• Status code mapping (legacy system status codes mapped to ERP status values)

The data mapping document:

Produce a formal data mapping document that shows, for every field in the legacy system, the corresponding field in the new ERP, any transformation applied, and the business rule governing the mapping. This document is essential for:

• Validating the migration script against intended behavior
• Resolving discrepancies discovered during testing
• Supporting audit queries after go-live

---

Phase 4: Migration Development and Testing

With cleaning complete and mapping documented, the migration scripts can be developed and tested.

Migration script development:

Migration scripts extract cleaned data from the staging environment, apply transformations according to the mapping document, and load the transformed data into the new ERP. ECOSIRE develops migration scripts in Python, using the Odoo XML-RPC API for loading. The scripts include:

• Pre-migration validation (confirm the data in the staging environment matches the approved cleaned dataset)
• Batch processing with error logging (record any records that fail to load for manual review)
• Post-migration validation (confirm that the loaded data matches expected counts and values)
• Rollback capability (if a migration run produces unexpected results, the ability to reset the ERP to its pre-migration state)

Test migration runs:

Plan for two to three test migration runs before the cutover migration:

• Test run 1 (week X): Validate the migration script's basic functionality and identify any transformation errors
• Test run 2 (week X+2): Validate against a more complete and cleaner dataset; produce the first full validation report
• Test run 3 / dress rehearsal (week X+4): Full end-to-end migration run against the final cleaned dataset, timed, with the cutover process executed exactly as it will be on go-live day

Each test run should produce a validation report comparing the migrated data against the expected counts, values, and referential integrity constraints. Discrepancies must be resolved before the next test run.

---

Phase 5: Opening Balance Reconciliation

Opening balance reconciliation is separate from master data and transaction history migration because it requires point-in-time financial accuracy rather than just data completeness.

The reconciliation process:

On the agreed cutover date, extract the closing balances from the legacy system for every financial entity: accounts receivable aging, accounts payable aging, inventory values by location, G/L account balances. These become the opening balances in the new ERP.

Reconcile the extracted balances against:

• The most recent bank statements (for cash accounts)
• The most recent accounts receivable aging report confirmed with AR staff
• The most recent accounts payable aging report confirmed with AP staff
• The most recent physical inventory count adjusted for movements since the count date

Any discrepancy between the extracted balance and the ground truth must be resolved before go-live. Carry the discrepancy into the new system and it will cause reconciliation issues for months.

The "forever open" invoice problem:

In most legacy accounting systems, there exist a number of invoices that are technically open (not fully paid) but are functionally dead — disputed, uncollectable, or administratively abandoned. These "forever open" invoices should not be migrated as open AR. They should be written off or marked as uncollectable before migration. The finance team needs to make explicit decisions about each one.

---

Phase 6: Cutover Planning and Execution

The cutover is the moment when the legacy system stops and the new ERP starts. Planning the cutover sequence precisely prevents chaos on go-live day.

The cutover plan:

Document each step of the cutover process with:

• Who performs the step
• Estimated duration of the step
• Dependencies (what must be complete before this step begins)
• Validation check (how you confirm the step is complete and correct)
• Rollback action (what to do if this step fails)

A typical cutover for a 100-person company takes 8–16 hours. Cutover is typically scheduled over a weekend to minimize business disruption.

Cutover sequence:

1. Legacy system freeze: no new transactions allowed
2. Final data extract from legacy system
3. Final data validation and cleaning (any issues discovered since the last test run)
4. Migration script execution for master data
5. Opening balance migration and validation
6. Transaction history migration (if migrating recent history)
7. Full validation report review and sign-off by finance
8. System configuration validation (all settings correct)
9. New ERP opened for business

---

Common Pitfalls and How to Avoid Them

Pitfall 1: Migrating everything

The impulse to migrate all historical data is understandable but often counterproductive. Ten years of transaction history from a legacy system takes weeks to migrate, validate, and load — and most of it will never be queried. Define a migration horizon (typically two to three years) and archive older history in the legacy system rather than migrating it.

Pitfall 2: Assuming the legacy system data is the source of truth

Legacy system data is frequently wrong. If you treat it as authoritative, you migrate the errors into the new system. Physical counts, bank statements, and supplier statements are better sources of truth for opening balances than legacy system reports.

Pitfall 3: No test environment

Migrating directly to production without testing in a sandbox environment is high-risk. Always maintain a test environment that mirrors production for the migration testing phase.

Pitfall 4: Insufficient business owner involvement

Data cleaning and mapping decisions require business judgment that technical teams do not have. Insufficient business owner involvement leads to technical teams making business decisions that are wrong, which creates data quality issues that surface as operational problems after go-live.

Pitfall 5: No rollback plan

Every go-live should have an explicit rollback plan: if the new system fails to work correctly in the first 24–48 hours, what is the process to revert to the legacy system? Having this plan ready reduces the pressure to make hasty decisions in a crisis.

---

Frequently Asked Questions

How long should we plan for data migration in an ERP implementation?

For a mid-market company (100–300 employees, 2–5 data source systems), plan six to ten weeks for data migration as a dedicated phase, beginning after configuration is complete enough to establish the target data structure. Additionally, allocate four to six weeks before implementation kickoff for data quality assessment and initial cleaning. The total data migration investment is typically ten to sixteen weeks from first assessment to cutover.

Should we clean data before or during the implementation?

Before, always. Data cleaning that happens in parallel with implementation uses the same business owner resources that the implementation team needs for configuration validation and testing. It also delays the migration timeline because cleaning depends on business decisions that take time to gather. Starting data cleaning six to twelve weeks before implementation kickoff is the most effective approach.

Can ECOSIRE migrate data from any legacy ERP?

ECOSIRE has built migration tooling for SAP Business One, QuickBooks (Desktop and Online), Sage 50 and Sage 100, Microsoft Dynamics (GP, NAV, BC), and several industry-specific platforms. For other systems, the migration approach is designed based on the available export format — SQL database, XML, CSV exports, or API access. No legacy system has been encountered that could not be migrated, though the effort varies significantly by source system.

What is the typical cost of data migration in an ERP implementation?

For ECOSIRE implementations, data migration typically represents 15–25% of the total implementation cost. For a $150,000 implementation, data migration cost is $22,000–$37,000. The range depends primarily on data quality (lower quality = more cleaning cost) and volume (more records = more script development and validation time). Data quality issues discovered after migration starts can expand this cost significantly, which is why the pre-implementation assessment investment is so valuable.

---

Next Steps

If you are planning an ERP implementation and want help assessing your data quality before making timeline and budget commitments, ECOSIRE offers a data readiness assessment that evaluates your key data entities, identifies quality issues, and provides realistic estimates for the migration phase.

Learn more about ECOSIRE's Odoo migration practice at /services/odoo/migration.