How to Plan a Data Migration That Doesn't Lose Data
Key takeaway
Profile the source data before you design anything; the gap between what the schema allows and what the data actually contains is where migrations fail. Then migrate repeatedly into a test environment, reconcile by record count and control totals, and keep the old system readable after cutover.
Data migration is consistently the most underestimated line in a modernisation budget. The transfer is easy. The archaeology — discovering that a decade of users have been putting delivery instructions in the fax number field — is not.
Step 1 — Profile before you plan
- Count records per entity, and distinct values per key column.
- Find nulls, duplicates, orphaned references, and out-of-range dates.
- Look for overloaded fields — one column carrying two meanings is extremely common in old systems.
- Identify records that are genuinely dead. Migrating twelve years of abandoned accounts costs money and buys nothing.
Step 2 — Map explicitly, and decide on the bad rows
Write a field-level mapping document: source field, target field, transformation, and the rule for records that don't fit. That last column is the one people skip, and it's the one that decides whether a migration night ends at 2am or at 9am. Every source record should end in exactly one of three buckets: migrated, deliberately excluded, or quarantined for manual handling.
Step 3 — Rehearse it, more than once
- Make the migration a repeatable script, never a sequence of manual steps.
- Run a full-volume rehearsal against a copy of production to get a real timing estimate for the cutover window.
- Have business users validate a sample in the target system — they spot wrong data that passes every technical check.
- Rehearse the rollback too.
Step 4 — Reconcile with numbers, not vibes
- Record counts per entity, source versus target.
- Control totals on financial and quantity columns — sums must match to the cent.
- Referential integrity checks in the target.
- Spot-check the oldest, newest, largest, and strangest records; edge cases break first.
Step 5 — Choose a cutover style honestly
Big-bang cutover is simpler and needs a maintenance window plus a firm rollback point. Parallel running is safer for critical systems but means dual entry and a reconciliation process for as long as it lasts. Whichever you choose, keep the source system available read-only for months afterwards — it is the cheapest insurance in the entire project.