Data Migration

The most consistently underestimated activity in ERP implementation, and the most common cause of a delayed go-live. Budget 15–25% of total project effort for it.

The golden rule

You migrate master data and open items. You do not migrate history.

Data	Migrate?
Active customers, vendors, materials, employees	✅ Yes, cleansed
Chart of accounts, cost centres, org structure	✅ Yes
BOMs, routings, price conditions	✅ Yes
Open sales orders, purchase orders	✅ Yes
Open AR and AP items	✅ Yes
Current stock quantities and values	✅ Yes
Fixed asset register with accumulated depreciation	✅ Yes
GL opening balances as at cutover	✅ Yes
Closed/posted invoices from prior years	❌ No
Historical goods movements	❌ No
Completed production orders	❌ No
Ten years of journal entries	❌ No

Where history goes instead: the legacy system kept read-only for the statutory retention period, or extracted to a data warehouse / archive with a documented retrieval process.

Business users always ask for full history. The counter-argument: migrating posted documents means recreating the exact accounting entries, in the right periods, under a different chart of accounts and a different data model. It doesn’t reconcile, it takes months, and it delivers a system whose historical data can’t be trusted anyway. Give them a reporting archive instead.

The process

PROFILE     What data exists? How much? How bad is it?
SCOPE       What migrates, what doesn't — signed off
CLEANSE     Fix it in the SOURCE system, with business owners
MAP         Legacy field → new field, with transformation rules
EXTRACT     Pull from legacy into a staging area
TRANSFORM   Apply mappings, defaults, and derivations
VALIDATE    Business rules, referential integrity, completeness
LOAD        Into the target, via APIs or sanctioned load tools
RECONCILE   Prove counts and values match
SIGN OFF   Business owner accepts the loaded data

Repeat 5–9 at least four times: two test loads, a dress rehearsal, and the real cutover.

Data profiling — do this in week one

-- Volume
SELECT COUNT(*) FROM customers;                                     -- 47,882

-- How many are actually active?
SELECT COUNT(*) FROM customers
 WHERE id IN (SELECT DISTINCT customer_id FROM orders
               WHERE order_date > CURRENT_DATE - 730);              -- 6,104  ← the real number

-- Quality
SELECT COUNT(*) FROM customers WHERE tax_id IS NULL;                -- 18,220
SELECT COUNT(*) FROM customers WHERE email NOT LIKE '%@%';          --  3,447
SELECT COUNT(*) FROM customers WHERE country_code NOT IN
       (SELECT code FROM valid_countries);                          --    892

-- Duplicates
SELECT normalised_name, COUNT(*) FROM customers
 GROUP BY normalised_name HAVING COUNT(*) > 1;                      --  2,113 groups

Profiling almost always produces the same finding: the customer thinks they have 47,000 customers; they have 6,000 real ones and 41,000 records of accumulated debris. Migrating only the active ones cuts effort dramatically and improves the new system’s data quality on day one.

Present the profiling results to the business early. It’s the evidence that turns “migrate everything” into a rational scoping conversation.

Cleansing

Cleanse in the source system, not in the migration script.

Why: the business owns the data and must own the corrections; corrections made in a script are lost on the next extract; and the legacy system stays usable during the transition.

Cleansing activities:
  - Deduplicate customers, vendors, materials
  - Fill mandatory fields
  - Standardise: country codes, UoM, addresses, phone formats
  - Deactivate records with no activity in N years
  - Correct invalid references
  - Resolve inconsistent naming conventions
  - Validate tax IDs, IBANs, postcodes

Assign a data owner per object. Not IT — the business. The Master Data Manager owns customers; Purchasing owns vendors; Engineering owns BOMs. Publish a weekly data quality scorecard from month one. Progress is visible and public, which is what makes it happen.

Mapping

Object: Customer
Legacy: CUSTMAST                    Target: business_partner + roles

Legacy field   Type      Target field           Transformation
─────────────────────────────────────────────────────────────────────
CUST_NO        CHAR(8)   external_id            Keep for cross-reference
                         partner_id             New internal number range
CUST_NAME      CHAR(40)  name1                  Trim, title case
CUST_NAME2     CHAR(40)  name2                  Trim, may be null
ADDR1..ADDR4   CHAR(40)  street, street2, city  Parse — see rule DM-C-014
ZIP            CHAR(10)  postal_code            Validate against country format
CTRY           CHAR(3)   country_code           Map: legacy 3-char → ISO 3166-1 alpha-2
                                                Mapping table DM-MAP-COUNTRY
CURR           CHAR(3)   currency_code          Direct; validate against ISO 4217
TERMS          CHAR(2)   payment_term_code      Mapping table DM-MAP-TERMS (14 values)
CREDIT_LIM     NUM(11,2) credit_limit           If null → 0; if > 99,999,999 → flag
SLSMAN         CHAR(3)   sales_rep_id           Map via employee migration; if not
                                                found → default 'UNASSIGNED' + flag
TAX_ID         CHAR(20)  tax_registration       Strip spaces/hyphens; validate by country
(none)         —         account_group          Derive: if CTRY='BD' → 'DOMS' else 'EXPT'
(none)         —         reconciliation_account  Constant '113000'
STATUS         CHAR(1)   blocked_flag           'A'→false, 'I'→true, 'D'→exclude entirely

Every field needs an explicit rule, including the ones that come from nowhere. The “(none)” rows — derived and constant values — are where most migration defects hide, because nobody reviews a field that has no source.

The staging area

staging_customer_raw        exact copy of the extract, untouched
staging_customer_transformed after mapping
staging_customer_validated   with validation results per record
staging_customer_load        load-ready
migration_error              record key, rule violated, severity, message
migration_mapping_country    lookup tables, versioned
migration_run                run id, timestamp, counts, status

Keep the raw extract untouched. When a number is wrong at reconciliation, you need to trace from the loaded record back through transformation to the original source value. Without the raw layer that trace is guesswork.

Validation layers

Technical    Types, lengths, formats, encoding
Referential  Every foreign key resolves in the target
Business     Mandatory fields, valid combinations, value ranges
Duplicate    No duplicates within the load or against existing data
Cross-object A sales order references a customer that will exist
Reconciliation Counts and control totals

Validate before loading, not during. A load that fails at record 40,000 of 60,000 leaves partially migrated data and a difficult recovery. Validate the whole file first, fix, then load clean.

Loading

Method	Notes
Vendor load tools (SAP LSMW/LTMC/Migration Cockpit, D365 DMF, NetSuite CSV import, Odoo import)	Preferred. Uses standard validation and business logic
APIs / BAPIs	Full business logic; slower; good for complex objects
Direct table insert	Almost never. Skips validation, derived fields, and dependent records. It will corrupt your data
Recording/scripting the UI	Slow, brittle, but occasionally the only route

Load order matters — dependencies are strict:

 Org structure and configuration
 Chart of accounts, cost centres, profit centres
 Currencies and exchange rates
 Business partners (customers, vendors)
 Materials (general → plant → sales → valuation views)
 Prices and condition records
 BOMs, then routings, then production versions
 Fixed assets (master, then values)
 Open purchase orders
Open sales orders
Stock quantities and values
Open AR / AP items
GL opening balances (the balancing figure)
Employees, then time and payroll data

Get this order wrong and loads fail on missing references. The GL opening balance load comes last because it must reconcile against everything already loaded.

Opening balances

The most scrutinised part of the migration.

Legacy trial balance at cutover:
  Assets       12,480,000
  Liabilities  (7,220,000)
  Equity       (5,260,000)
  ──────────────────────
  Balance               0

Loaded via:
  AR subledger open items    →  must equal legacy AR control account
  AP subledger open items    →  must equal legacy AP control account
  Inventory (qty × value)    →  must equal legacy inventory accounts
  Fixed assets (APC + acc.dep) → must equal legacy asset accounts
  All other GL accounts      →  loaded directly as balances

  Then: new trial balance must equal legacy trial balance, exactly.

Exactly. Not “within rounding”. A difference of 3.47 means something is wrong, and it will grow. Finance will not sign off, and they are right not to.

The standard technique: load subledger detail (which produces GL postings automatically via reconciliation accounts), then load remaining GL balances against a data migration clearing account, which must end at zero. A non-zero clearing account balance is your error, quantified.

Reconciliation

Every object needs a reconciliation report:

Object: Customers
  Source records                 47,882
  Excluded (inactive)           (41,778)   ← per agreed scope rule
  Excluded (duplicates merged)     (231)
  In scope                        5,873
  Loaded successfully             5,847
  Failed                             26   ← listed with reasons
  Reconciliation                  FAIL — 26 records require action

Object: Open AR
  Source open items               3,412
  Source total value         14,882,140.55
  Loaded items                    3,412
  Loaded total value         14,882,140.55
  Difference                          0.00
  Reconciliation                  PASS

Value reconciliation matters more than count reconciliation. Loading 3,412 records with the wrong values passes a count check and fails an audit.

Test loads

Load	Purpose	Data
Load 1	Prove the technical process works	Small subset, dirty
Load 2	Volume and quality assessment	Full extract, partially cleansed
Load 3	Full dress rehearsal, timed	Full, cleansed
Load 4	Cutover rehearsal, exact procedure	Full, final
Production	The real one	Final extract at freeze

Time every load. If the full load takes 26 hours and your cutover window is 18, you find out in rehearsal, not on go-live weekend. This is precisely what rehearsals are for.

Delta handling

Between the final test extract and cutover, the legacy system keeps running:

Option A — Freeze
  Stop transactions in legacy, extract, load, go live
  ✅ Clean. ❌ Business stops for the window

Option B — Delta load
  Full load beforehand, then load only changes during the window
  ✅ Shorter freeze. ❌ Complex, needs reliable change tracking

Option C — Manual re-entry
  Freeze early; users re-enter the few transactions from the gap
  ✅ Simple. ❌ Only viable for low volumes

Most projects use A for master data (frozen a week early) and A or C for transactions.

Common failure modes

Failure	Prevention
Cleansing started too late	Start in month one, with named business owners
History migration attempted	Scope it out early, with the archive alternative agreed
Load takes longer than the window	Time it in rehearsal; parallelise
Opening balances don’t reconcile	Clearing account technique; reconcile every test load
Duplicates created	Deduplicate in source; unique constraints in target
Encoding corrupts non-Latin names	UTF-8 end to end; test with real Bengali/Arabic/Chinese data
Numbers truncated or reformatted	Treat identifiers as strings throughout; never open a CSV in Excel
Dates in the wrong format	ISO 8601 in every intermediate file
Missing derived fields	Review the “(none)” rows in the mapping specifically
No rollback plan	Take a database backup before the production load

Developer checklist

That last one matters more than people expect. For years afterwards someone will ask “what happened to customer 00048821 from the old system?” The cross-reference table answers it in seconds. Keep it forever.