Migrating legacy systems into a platform that models the work differently

Losing rows in a migration is not a risk to manage, it is a failure. The harder case is when every row arrives and the data is still wrong.

Over four years I moved close to two million records out of ten separate legacy systems and into one platform. The sources were not older versions of the same product. They were different commercial systems from different vendors, each with its own schema and its own opinion about what the central record is, plus the local conventions every organisation layers on top of whatever software it bought. Each one needed its own mapping, and each one was a fresh argument about what a given field actually means.

Most of the real work is field-level and unglamorous. On one system the client's analyst asked me to map a field that, on inspection, did not exist in the table he named. Something close to it did, spelled differently, and I asked before writing anything rather than quietly picking the near-match. On another, an address had to be assembled from five source columns. Concatenating them naively leaves double spaces wherever a column is empty, so each fragment carries its own trailing space inside the null check and only contributes when it is actually present. That is the texture of this work: dozens of small decisions, each of which is invisible when right and permanent when wrong.

The failures that matter are quiet. A script that reads the default amount defined on a fee's type instead of the amount recorded against the individual item will import a whole category of fee as a zero charge, and nothing about the result looks wrong from outside: every row is present and the totals reconcile against a source being read incorrectly in exactly the same way. Option tables that carry global rows belonging to no single tenant will, if you filter by tenant alone, quietly write status values that no dropdown in the destination offers. A permission grant with no tenant column means a revoke written for one organisation reaches staff accounts in every other organisation they work in. And the destination keeps generating its own identifiers while the import runs, so numbering has to be checked twice: once when the script is written, and again inside the transaction that writes.

Some records deliberately stay behind, and that number has to be published alongside the migrated one. Reported separately, later, it reads as something that went missing rather than a decision that was taken.

What makes this repeatable is not the scripts, it is the harness around them. A pre-flight check shaped exactly like the import it guards, so it measures the same rows rather than a layer above them. More than one reviewer, because on the most recent one my own pass found three serious defects and a second reviewer found three more I had not seen. And a rollback that has actually been run end to end at real volumes, because a rollback nobody has executed is a paragraph in a document, not a way back.