The ERP implementation wasn't what worried us most.
The data was.
Fifteen years of customers, suppliers, products, units of measure, addresses, payment terms, tax codes, warehouses, categories, and relationships had accumulated across multiple systems.
On paper, the ERP data migration looked straightforward:
Legacy ERP
↓
Extract
↓
Transform
↓
Load
↓
New ERP
The first test migration proved otherwise.
Records loaded successfully and still produced incorrect business behavior.
That became the most important lesson of the project:
A successful import is not the same thing as a successful migration.
Here's what actually broke.
The First Mistake: Treating Migration as a Copy Job
Our earliest thinking was essentially:
Legacy Customer → New Customer
Legacy Product → New Product
Legacy Supplier → New Supplier
But fifteen years of ERP data isn't just information. It contains fifteen years of business decisions, exceptions, workarounds, and technical debt.
The source systems had accumulated old naming conventions, retired products, duplicate suppliers, incomplete addresses, obsolete categories, unused fields, invalid codes, and manual corrections.
If we copied everything exactly, we would reproduce those problems in the new ERP.
So the migration became:
Extract
↓
Profile
↓
Clean
↓
Normalize
↓
Deduplicate
↓
Transform
↓
Validate
↓
Load
↓
Reconcile
We quickly realized that ERP data migration is far more than moving records between databases. It requires profiling source data, cleaning inconsistencies, documenting transformation rules, validating relationships, and planning cutover long before production. A structured ERP data migration strategy with validation and cutover planning helps prevent technical success from turning into business failure.
The additional work wasn't migration overhead.
It was the migration.
Customer IDs Weren't Actually Unique
The legacy system had customer numbers such as:
CUST-10021
CUST-10022
CUST-10023
Great.
Until we discovered:
CUST-10021 Acme Manufacturing
ACME001 Acme Manufacturing Ltd.
10021 ACME MFG
Three IDs.
Potentially one customer.
The old environment could tolerate this because different teams had learned which record to use.
The new ERP couldn't safely inherit that assumption.
We created an identity crosswalk:
SOURCE_ID MASTER_ID
CUST-10021 → CUSTOMER-00482
ACME001 → CUSTOMER-00482
10021 → CUSTOMER-00482
That crosswalk later became critical for orders, invoices, contacts, addresses, and historical references.
Duplicate Records Were Rarely Exact Duplicates
We initially expected duplicate detection to be easy.
Something like:
SELECT email, COUNT(*)
FROM customers
GROUP BY email
HAVING COUNT(*) > 1;
Useful?
Yes.
Enough?
Not remotely.
Consider:
ABC Industrial Pvt Ltd
ABC Industrial Private Limited
A.B.C. Industrial
ABC Industries
Maybe duplicates.
Maybe related companies.
Maybe completely different legal entities.
Supplier matching was even riskier because incorrectly combining suppliers could affect tax IDs, bank details, payment terms, purchase history, and legal relationships.
Instead of:
Match found
↓
Automatically merge
we used confidence levels:
HIGH CONFIDENCE
→ Auto-resolve where rules permit
MEDIUM CONFIDENCE
→ Review
LOW CONFIDENCE
→ Keep separate
Deduplication became a business-data decision rather than a string-matching exercise.
"Inactive" Didn't Mean "Safe to Delete"
We found thousands of inactive customers and products.
The obvious question was:
Why migrate them?
Then we looked at the relationships.
An inactive customer might still appear on historical invoices, credit notes, tax reports, service records, warranty claims, or audit records.
A discontinued product might still be required to interpret a ten-year-old invoice.
So we stopped thinking only in terms of:
ACTIVE
INACTIVE
and started using:
Operationally active
Historically required
Compliance required
Archive only
Safe to exclude
That distinction reduced unnecessary migration without destroying historical meaning.
Product Master Data Was Worse Than Customer Data
Customers were messy.
Products were worse.
A single item could involve:
SKU
Description
Category
Brand
UOM
Purchase UOM
Sales UOM
Tax classification
Cost
Price
Warehouse rules
Supplier relationship
Barcode
Serial/lot tracking
Every one of those fields could contain fifteen years of assumptions.
Units of Measure Broke Quietly
Suppose the legacy system stored:
Purchase UOM = CASE
Sales UOM = EACH
1 CASE = 24 EACH
The product imported.
The supplier imported.
The price imported.
But if the conversion became:
1 CASE = 12 EACH
the ERP could still accept the record.
Now purchasing, inventory, costing, and sales could all become wrong without producing a technical import error.
That changed how we classified failures:
Technical failure
→ Record cannot load
Semantic failure
→ Record loads but means the wrong thing
Semantic failures were often more dangerous.
Product Codes Had Changed Over Time
One product might have been known as:
2009 → P-1045
2014 → SKU-1045
2019 → PROD-8821
2024 → FG-8821
Were those four products?
Or four identifiers for the same product?
If we migrated them independently, inventory and reporting would fragment.
So we created lineage:
Legacy SKU A ─┐
Legacy SKU B ─┤
Legacy SKU C ─┼──→ Canonical Product
Legacy SKU D ─┘
Historical transactions could remain traceable without requiring four active product masters.
Free-Text Fields Became a Migration Problem
Legacy systems often accumulate fields where users can enter almost anything.
For example, Payment Terms contained:
NET30
Net 30
30 Days
30days
N30
Net-30
Thirty Days
To a person, these mostly mean the same thing.
To the new ERP, they may be seven separate values.
So we normalized them:
NET30
Net 30
30 Days
30days
N30
Net-30
Thirty Days
↓
NET_30
The same problem appeared in countries, states, currencies, categories, brands, tax codes, shipping methods, and payment methods.
This was where a staging layer became invaluable.
We Stopped Migrating Directly Into the ERP
Our first architecture was:
Legacy Database
↓
Transformation Script
↓
New ERP
That made troubleshooting unnecessarily difficult.
We changed it to:
Legacy Systems
↓
Extract
↓
┌───────────────────┐
│ Migration Staging │
└─────────┬─────────┘
↓
Profile
↓
Clean
↓
Normalize
↓
Transform
↓
Validate
↓
New ERP
The staging database gave us somewhere to inspect the data before it became production ERP data.
Now we could ask:
SELECT payment_term, COUNT(*)
FROM staged_customers
GROUP BY payment_term;
or:
SELECT uom, COUNT(*)
FROM staged_products
GROUP BY uom;
Unexpected values became visible before loading.
This is one reason a structured ERP data migration strategy matters. A useful migration plan defines source profiling, field mappings, transformation rules, cleansing, staging, validation, reconciliation, acceptance criteria, and cutover rather than treating ETL as the whole project.
Null Didn't Always Mean Missing
Suppose:
Credit Limit = NULL
What does that mean?
It might mean:
Credit limit was never configured.
Or:
Customer has unlimited credit.
Or:
Legacy application ignored this field.
Those are completely different business meanings.
The same issue appeared with:
Lead Time = 0
Discount = 0
Tax Rate = NULL
Minimum Order = 0
Inactive Date = NULL
Migration rules therefore needed to define meaning, not simply datatype conversion.
Dates Exposed Years of Inconsistency
We found dates represented as:
2024-01-08
08/01/2024
01/08/2024
8-Jan-24
NULL
0000-00-00
Parsing was only the first problem.
Interpretation was harder.
Is:
08/01/2024
January 8?
Or August 1?
The answer depended on which source application created the record.
We therefore kept source context alongside transformed values:
source_system
source_record_id
original_value
normalized_value
transformation_rule
That made transformations explainable and easier to audit.
Addresses Looked Simple Until We Standardized Them
One source might contain:
123 Industrial Rd.
Mumbai
Maharashtra
400001
India
Another:
123 Industrial Road,
Mumbai - 400001,
MH
IND
The target ERP wanted structured fields:
Address Line 1
Address Line 2
City
State
Postal Code
Country
Parsing years of inconsistent addresses reliably was not trivial.
During migration, we deliberately separated:
Original Address
from:
Normalized Address
That gave reviewers a way to detect destructive transformations.
Referential Integrity Broke Before the Records Did
Loading a product successfully wasn't enough.
It might reference:
Category CAT-007
that no longer existed.
A supplier could reference:
Payment Term PT-19
that had been retired.
A customer could reference a salesperson who left eight years ago.
These were orphan relationships.
We started validating them before load:
Customer
↓
Valid payment term?
Product
↓
Valid category?
Supplier
↓
Valid currency?
Warehouse Item
↓
Valid warehouse?
Migration order then became dependency-driven:
Reference Data
↓
Master Data
↓
Opening Balances
↓
Open Transactions
↓
Selected History
Master Data Ownership Became Bigger Than Migration
During cleanup, we kept hearing:
Who decides whether this value is correct?
IT couldn't answer everything.
Developers could determine whether "Net 30" could technically map to NET_30.
Finance needed to determine whether it should.
The same applied across domains:
Product classification → Operations
Supplier terms → Procurement
Customer hierarchy → Sales
Tax treatment → Finance/Tax
Inventory units → Warehouse/Operations
We eventually assigned a business owner to each critical data domain.
That accelerated decisions because exceptions finally had someone responsible for resolving them.
We Built an Exception Queue Instead of Hiding Errors
Early migration scripts tried to fix unexpected values automatically.
That was risky.
We changed the philosophy.
If a transformation wasn't sufficiently confident, it generated an exception:
Record: SUP-18842
Field: payment_terms
Source: "45/60 depending"
Expected: controlled code
Status: REVIEW_REQUIRED
Exceptions were categorized:
Missing mapping
Invalid reference
Possible duplicate
Malformed value
Unknown code
Conflicting identity
Business decision required
Now migration quality became measurable:
12,840 exceptions
↓
4,200
↓
712
↓
84
↓
0 blocking exceptions
That's far more useful than saying:
"The migration is 95% complete."
Transformations Needed to Be Deterministic
If:
"Net Thirty"
became:
NET_30
during test migration three, it needed to produce exactly the same result during production migration.
Transformation logic therefore lived in mapping tables, version-controlled scripts, and documented business rules rather than undocumented spreadsheet edits.
The principle was simple:
Same source
+
Same transformation rules
=
Same target
Without deterministic transformations, every rehearsal becomes a different migration.
Excel Was Useful Until It Became the Migration Engine
Spreadsheets were excellent for reviewing mappings, business sign-off, small exception lists, and sample records.
They were much less reliable for managing large-scale transformations.
Problems included:
Accidental formatting
Lost leading zeros
Date conversion
Manual edits
Formula mistakes
Version confusion
For example:
00001234
quietly becoming:
1234
can break an external identifier.
We still used spreadsheets.
We just stopped making them the authoritative transformation engine.
Migration Testing Needed Realistic Data
A test migration with:
100 customers
100 products
20 suppliers
worked beautifully.
Production-scale data exposed different problems.
Large volumes surfaced performance bottlenecks, duplicate patterns, unexpected nulls, encoding problems, API constraints, long-running validation, and much larger exception sets.
Our useful rehearsals eventually needed representative volume and complexity, not only representative records.
A migration test had to answer two questions:
Is the transformation correct?
Can we execute it at production scale?
Both matter.
Record Counts Were Necessary but Not Sufficient
Suppose:
Source Customers: 150,000
Target Customers: 150,000
Looks perfect.
But imagine 10,000 customers have the wrong payment terms.
The count still matches.
So reconciliation expanded beyond counts.
We compared:
Record counts
Distinct identifiers
Status distributions
Category distributions
Currency distributions
Null rates
Financial totals
Relationship counts
Exception counts
For critical fields, we sampled actual values too.
Migration validation needed to prove not only that the data arrived, but that it still meant the right thing.
The Best Validation Was Running the Business
Eventually, SQL checks weren't enough.
A customer master could look correct in the database and still fail when someone tried to create an order.
So UAT included real workflows:
Create Sales Order
↓
Allocate Inventory
↓
Ship Product
↓
Generate Invoice
↓
Post Accounting Entry
and:
Create Purchase Order
↓
Receive Goods
↓
Match Supplier Invoice
↓
Post Payment
If migrated master data couldn't support those processes, it wasn't ready.
Cutover Changed the Problem Again
Migration rehearsals happened while the legacy ERP continued changing.
Production cutover couldn't work that way indefinitely.
At some point we needed:
Legacy ERP
↓
FREEZE
↓
Final Extract
↓
Transform
↓
Load
↓
Reconcile
↓
Release New ERP
Without a freeze or controlled delta strategy, the source and target immediately begin diverging.
A successful cutover depends on much more than loading data. Governance, reconciliation, user readiness, rollback planning, and production validation all need to work together. That's why mature ERP implementation services for enterprise rollouts treat migration as one workstream within the broader implementation lifecycle rather than an isolated technical task.
We Didn't Migrate All 15 Years
This was one of the most valuable scope decisions.
The original request was:
"Move everything."
Eventually, we separated the data:
MASTER DATA
Current + historically required
OPEN TRANSACTIONS
Required operationally
RECENT HISTORY
Useful inside ERP
OLD HISTORY
Archive / reporting platform
The new ERP didn't need to become a museum for every obsolete record ever created.
Reducing unnecessary history lowered transformation effort, validation effort, storage requirements, cutover duration, and the overall failure surface.
The important question wasn't:
How many years can we migrate?
It was:
Which data actually needs to exist inside the live ERP?
The Migration Architecture We Ended Up Trusting
Our final ERP data migration architecture looked closer to:
Legacy ERP
│
▼
Extract
│
▼
┌─────────────┐
│ Raw Staging │
└──────┬──────┘
│
▼
Profiling
│
▼
Normalization
│
▼
Deduplication
│
▼
Transformation
│
▼
┌──────────────┐
│ Validation │
└──────┬───────┘
│
┌─────┴─────┐
▼ ▼
Valid Exception
Records Queue
│ │
│ Business Review
│ │
└─────┬─────┘
▼
Load
│
▼
New ERP
│
▼
Reconciliation
│
▼
Business Process UAT
The staging and exception layers were not unnecessary complexity.
They were what made the migration explainable, testable, and repeatable.
The Checklist We'd Use Next Time
- Profile source data before finalizing mappings.
- Decide what should be migrated, archived, and retired.
- Define canonical IDs for customers, suppliers, and products.
- Create explicit deduplication rules.
- Preserve source-to-target ID crosswalks.
- Normalize controlled values before loading.
- Validate units of measure and conversion factors.
- Identify orphan relationships.
- Define what
NULL, zero, and blank mean by field. - Assign business owners to every critical master-data domain.
- Route uncertain transformations to an exception queue.
- Keep transformation rules deterministic and version-controlled.
- Separate raw, transformed, and target data.
- Test with realistic data volumes.
- Reconcile more than record counts.
- Validate real end-to-end business processes.
- Decide deliberately how much history belongs in the live ERP.
- Rehearse the complete migration more than once.
- Define the cut-off and delta strategy.
- Measure migration duration before production cutover.
- Define rollback conditions.
- Obtain business sign-off on critical master data.
What Actually Broke
Looking back, the biggest failures weren't exotic technical problems.
They were ordinary data problems that had accumulated quietly for years:
One customer had three identities.
One product had four SKUs.
"Net 30" had seven spellings.
NULL had multiple meanings.
Inactive records still had important history.
Old categories were still referenced.
Units of measure looked valid but meant different things.
External relationships depended on legacy identifiers.
The legacy ERP had learned to live with those inconsistencies because employees understood the workarounds.
The new ERP didn't.
That's why ERP data migration isn't fundamentally an import problem.
It is a meaning-preservation problem.
The goal isn't:
Move 15 years of data.
The goal is:
Understand 15 years of data
↓
Preserve what still matters
↓
Correct what is wrong
↓
Retire what is obsolete
↓
Make the result trustworthy
If the target ERP contains every legacy record but users don't trust its customer, supplier, product, inventory, and financial data, the migration has failed.
A smaller, cleaner, reconciled dataset that the business trusts is worth far more than a perfect copy of fifteen years of accumulated mistakes.
