How to Migrate ERP Data Without Losing a Row (or a Job)

Data migration is where ERP projects go to die. The blueprint, the demo, the user training — all of that goes well. Then someone runs a trial migration on Friday afternoon, and on Monday the customer balance report is missing 3% of the receivables. Now you have a politics problem, not just a technical one.

Here is how we keep data migrations boring.

Principle 1 — The migration is a build, not a script

A one-shot SQL script gets you a single dirty load. A pipeline gets you sandbox loads, dry runs, dress rehearsals, and a real go-live — with the same code every time. Build the pipeline like a product.

Principle 2 — Reconciliation is part of the deliverable

A migration is not "done" because rows landed in the target. It is done when balances match the source. Currency totals, customer balances, inventory on-hand by warehouse, GL by period — all must reconcile to the legacy system before sign-off.

If you cannot reconcile, you have not migrated. You have copied bytes.

The 5-step pipeline

Step 1 — Profile

Before you write a single transform, profile every source table:

• Row counts per table, per partition
• Null rates per column
• Cardinality of categorical columns
• Date range of every date column
• Duplicate detection (same key, multiple rows)
• "Soft-deleted" rows (deleted flag, status code, etc.)

This step is boring and skippable, which is why most failed migrations skip it. Don't.

Step 2 — Transform

Map every source field to a target field. For each, document:

• Source location
• Type conversion (varchar → number, date format change, currency conversion)
• Default value when source is null
• Validation rule (must be in [X, Y, Z], must match regex, must reference an existing parent row)

Keep transforms idempotent. Running the transform twice on the same input must produce the same output.

Step 3 — Load

Loading order matters because foreign keys. Load chart-of-accounts before journal entries. Customers before invoices. Items before stock balances. We default to a 6-tier load order:

1. Reference data (currencies, countries, units of measure)
2. Master data (chart of accounts, customers, suppliers, items)
3. Opening balances (GL, AP, AR, inventory)
4. Transactional data (invoices, payments, stock moves)
5. Derived data (allocations, valuations)
6. Cutover-window data (last 24-48h of legacy activity)

Tier 6 is the cutover window. We freeze the legacy system Friday evening, run tier 6, and open the new system Saturday morning.

Step 4 — Reconcile

For every loaded module, run a reconciliation report:

• GL. Trial balance from new system vs. legacy, by account, by period. Tolerance: 0.01 of base currency.
• AR. Customer balances. New system total per customer = legacy total per customer. Tolerance: 0.
• AP. Same as AR, for suppliers.
• Inventory. On-hand quantity per item per warehouse. Tolerance: 0.
• Sales / purchase volumes. Last 90 days, per month, both systems.

Any variance gets a named owner and a written explanation. "We don't know" is not an acceptable resolution. The biggest project killers I've seen all started with a 0.3% variance someone decided was "good enough." It never is.

Step 5 — Cutover

The cutover plan is a minute-by-minute spreadsheet. Real example:

Friday 18:00 — Legacy system closes for end users
Friday 18:30 — Final tier 6 export from legacy
Friday 19:00 — Run tier 6 migration into target
Friday 21:00 — Full reconciliation report
Friday 22:00 — Go / no-go call
Saturday 06:00 — Sanity checks
Saturday 08:00 — Open new system for users

Every cutover ships with a rollback script — sometimes "drop the schema and re-import the legacy snapshot," sometimes "switch DNS back to the old server." If your cutover plan does not include a rollback path, the cutover is not ready.

Common migration mistakes

Mistake: Trying to clean data during migration. Two changes at once is one change too many. Migrate the dirty data as-is. Clean it in the target system, where rollback is easier.

Mistake: Excluding "old" data. Tax auditors in Egypt and KSA reach back 5+ years. Migrating only "last 3 years" creates a future audit problem.

Mistake: Treating reconciliation as a final check. Reconcile after every dry run. Catching a transform bug at the third dry-run is cheap. Catching it 4 hours into go-live is career-changing.

Mistake: One-person migrations. The person who built the pipeline cannot be the only one who knows it. Pair-program at minimum.

Our reference pipeline

Our internal migration toolkit is Python (pandas + sqlalchemy) for the transform stage, Oracle/Postgres for staging, and a custom reconciliation runner that produces a side-by-side PDF report. We've used it for migrations from Oracle Forms 6i, FoxPro, Tally, Excel ledgers, and a half-dozen custom Access databases.

If you have a migration coming up and want a sanity check on your pipeline design, reach out. We'll review the plan free of charge — most migrations fail on architecture, not effort.