Planning a Data Migration Without Losing (or Trusting) the Wrong Things

The script is the easy part

A data migration project is rarely won or lost on the migration script itself. Writing the code that moves a record from one shape to another, once every decision upstream of it has actually been made, is mechanical and well understood. What decides whether a migration goes well is the set of decisions made before a single record moves: what's actually sitting in the old system, what shouldn't be carried across at all, how a field's meaning in one system maps onto a differently-shaped field in the new one, and how to prove afterwards that nothing was lost or quietly corrupted on the way.

Inventory and profiling first

Before anything gets mapped, it has to be measured. That means going through the source system table by table, or sheet by sheet, and establishing what's actually there: how many records, how many of them are genuinely active versus dormant or superseded, which fields are consistently populated and which are mostly empty, and where duplicates already exist under slightly different spellings or formats. This sounds like a formality and rarely is — it's common for a business to discover during profiling that a "customers" table everyone assumed held a few thousand active records is mostly years of one-off entries nobody has touched since they were created, with the genuinely active set a fraction of the total. A migration plan built on an assumed size and shape of the data, rather than a measured one, tends to be wrong in ways that only show up once the real migration is already running.

Deciding what NOT to migrate

Not everything in the old system deserves a seat in the new one. Test records, duplicate entries, and fields the business stopped using years ago don't become more useful by being carried into a clean new system — they just relocate the mess. The harder judgement calls sit around historical data: a decade of closed orders might be genuinely irrelevant to how the new system will be used day to day, better kept as an archived export than imported as live records the new system now has to account for in every report and every query. That said, some records may be subject to statutory retention obligations — tax, employment, or audit-related — that don't require the data to live inside the new operational system, but do require it to remain accessible somewhere for a defined period. Which records that applies to, and for how long, is worth confirming with the business's own accountant or company secretary before anything is deleted or excluded, rather than assumed either way from a general description like this one.

Field mapping and its judgement calls

Field mapping looks like a technical exercise and is very often a business decision wearing one. Two systems rarely define the same concept the same way. An old system might carry fifteen legacy order statuses that accumulated over years of ad hoc additions; the new system might define four. Deciding that "Cancelled – Refunded" maps to "Cancelled" and not to a status of its own is a decision about what the business considers meaningfully different, not a decision about database schemas — and it's one that should be made and signed off by someone who understands what the data actually means operationally, not left to whoever happens to be writing the migration script under a deadline. The same applies to free-text fields being forced into structured ones, inconsistent name formats, and phone numbers stored in a dozen different formats across years of manual entry. None of these have a technically "correct" answer; they have a business-appropriate one, and it needs to be written down and agreed before the mapping is built, not discovered as an exception mid-run.

A dry run against production-shaped data

Testing a migration against a small, clean sample proves the script runs. It proves almost nothing about whether it will survive the real data, because the problems that break migrations — encoding issues, unexpected duplicate keys, multibyte characters in Malay, Chinese, or Tamil names handled inconsistently by whichever system created them, fields that are null in ways nobody documented — tend to be rare enough that a hand-picked sample skips straight past them. A proper dry run means running the actual migration script against a full copy of production data, in an environment that isn't production, and watching what genuinely breaks at real scale and in the real shape of the data, not the tidy version. It also gives an honest answer to a separate, practical question: how long the migration actually takes to run, which determines how long a cutover window realistically needs to be.

Reconciliation: counting, not assuming

Confirming that a migration moved, say, 1,240 customer records out of an old system into 1,240 in the new one is a necessary check and a genuinely weak one on its own — two matching totals can hide records that moved into the wrong category, or duplicates that cancelled each other out in the count by coincidence. A more honest reconciliation checks by category rather than one grand total: active customers against active customers, archived against archived; the sum of every invoice's value before migration against the same sum after; and a manually spot-checked sample compared field by field against the original source, not just confirmed to exist. Reconciliation that only checks that a record landed somewhere, without checking that it landed correctly, tends to find problems months later instead of on migration day, when they're far more expensive to unpick.

The rollback plan

A rollback plan has to answer a specific question honestly: if something goes wrong after cutover, can the business actually go back to the old system, and for how long does that remain true? The answer usually isn't "yes, indefinitely." Once new transactions start happening only in the new system, a clean rollback stops being possible the moment those transactions exist nowhere in the old one — reverting would mean losing them. That point of no return needs to be defined and communicated before cutover, not discovered afterwards when someone asks to roll back and learns it's no longer an option. A rollback plan that doesn't name that moment explicitly isn't really a plan; it's an assumption that there will always be time to change your mind.

What migrations tend to reveal

It's worth being honest about this rather than treating it as a footnote: migrations very often surface data-quality problems the business didn't know it had. Duplicate customer records under slightly different names, orders with no linked customer, invoices whose stored total doesn't match the sum of their own line items — these aren't evidence that anyone did anything wrong. They're the kind of thing that accumulates quietly through years of manual entry across a system's life and rarely gets forced into view until every single record has to pass through a consistent set of rules at once, rather than being looked at one at a time as needed. The useful decision to make early is whether the project will clean these up before cutover, which adds time upfront, or migrate the data as-is with a scheduled clean-up afterward — both are legitimate choices. Discovering the option exists partway through the migration, instead of deciding on it beforehand, is usually what turns a planned timeline into an open-ended one.

Before committing to a cutover date

A migration that's been inventoried, mapped with documented judgement calls, dry-run against real data, and reconciled by category rather than by one total is a fundamentally different undertaking from one where the script gets written and everyone hopes for the best on the night. JagaWeb's Fixed-Scope Project (from RM30,000, excluding SST) covers exactly this kind of migration work end to end; where the source data itself needs profiling and an honest account of its condition before a scope or a price can be set at all, the Essential System Review (RM1,500, reduced to RM999 until 16 September 2026, excluding SST) is built for that first look.