Legacy data migration: reading meaning nobody wrote down

The field was called ZONE, three characters, on the customer master. The specification said “delivery zone”. The plant had used it that way until roughly 2014, when someone needed a flag for customers requiring a certified weighing note and there was no field for it. They agreed, informally, that ZONE would carry a W suffix for those customers. Nobody wrote it down. The person who decided left in 2018.

Twelve years later, a migration mapped ZONE to the target’s delivery zone field, correctly according to every document that existed. Four hundred customers silently lost the only marker that told the warehouse to produce a legal document.

This is what legacy migration actually is. Not old technology: old decisions that were never recorded, and that survive only in the shape of the data.

The documentation describes the system as it was delivered

Start from the assumption that your documentation is accurate and obsolete at the same time. It usually describes the system as designed, which was true on the day it went live. Every adaptation since then, and there are twenty years of them, exists in three places: the code, the data, and the memory of people still in the building.

The code is often available and it is worth reading, but it tells you what is technically possible, not what is actually done. A field can be free text in the schema and hold exactly six values in practice, because the team standardised on six and everyone knows it.

The data is the only source that records what actually happened. Reading it is a discipline, and it is most of what a serious data audit does on a legacy estate.

What the distribution of values tells you

Profile every field. Not a sample, every field, on the full set. Four measurements do most of the work:

Cardinality against expectation. A field documented as free text that holds 6 distinct values across 400,000 records is not free text, it is an undeclared code list. Migrate it as a code list. Conversely, a field documented as a code list holding 3,000 distinct values has been used as a comment box, and mapping it as a code list will fail on the day of the load.

Format clustering. Sort the distinct values by pattern rather than alphabetically. AB-1234, AB-1234-R, 1234 and ab 1234 in the same column usually means three eras of data entry convention, and often three different meanings. Each cluster needs its own rule, and the cluster boundaries are dates.

Distribution shape. A field where one value covers 94% of records and the rest is a long tail is a field with a default. The default carries no information and the tail carries all of it. That changes what you have to test: 94% of your volume validates nothing.

Null rate by period. A field that is 80% empty overall but 3% empty since 2019 became mandatory at some point, by process rather than by constraint. That date is the boundary of a rule.

Fields used for something other than their name

The ZONE case is not rare, it is the normal outcome of a system that outlived its specification. When a business need appears and no field exists, people use the field that is available. Three signatures give it away:

• A suffix or prefix convention on a field that should have a fixed format, appearing after a certain date and only on a subset of records.
• A value that is syntactically valid and semantically impossible, like a delivery date in 1900 or a quantity of 99999, used as a marker.
• Correlation with something unrelated. If ZONE ending in W correlates almost perfectly with a customer type, or a country, or the presence of a specific document, it is carrying that meaning.

That last one is the technique that actually finds them. Cross-tabulate suspicious fields against every other field and look for correlations that the documented model does not explain. A field that predicts something it has no reason to predict is a field with a second job.

Codes whose meaning changed without the column changing

Harder than overloading, because nothing about the value looks unusual.

Status code 04 meant “awaiting customer validation” until the process was reorganised in 2016, after which it meant “awaiting quality release”. Same code, same column, same format. Records from before and after carry the same value and mean different things.

The only way to find these is temporal. For each code value, plot its frequency by year. A code that appears, disappears, or changes volume sharply at a specific date is a code with a history. Then ask someone what happened that year.

This matters more than it sounds, because the target system will have one meaning per code. Migrating both eras into the same target status silently merges two different business states, and the error surfaces months later in a report nobody can reconcile.

The decision here is not technical. Either you map by era, which means the migration rule depends on a date, or you accept the merge and record it as a known loss. Both are defensible. Doing it without noticing is not.

Ask the users, not the archives

At some point the data stops answering and you have to ask people. Two rules make this productive.

Ask about the exception, not the process. “How does the order process work” produces the official version, which is in the documentation you already have. “Why do these 340 orders have a delivery date before their creation date” produces the real answer, usually immediately, and usually from someone who is surprised anyone noticed.

Ask the people who create the data, not their managers. The convention lives with whoever types it in. Bring the actual records to the conversation, printed or on screen. Abstract questions get abstract answers; a screen with 340 anomalous rows gets an explanation in two minutes.

Record every answer against the records it explains. That log becomes the specification the system never had, and it is a deliverable in its own right, useful long after the migration.

Knowing when to stop

You will not resolve everything. Some conventions are genuinely lost, because the people are gone and the correlation is ambiguous. Pretending otherwise produces a rule invented by whoever was writing the code that week, which is worse than an acknowledged gap.

When a residue cannot be explained, three honest options:

• Migrate as-is into a free-text or legacy field, preserving the value without asserting a meaning. Cheap, reversible, and it keeps the information available if someone later works out what it was.
• Exclude and archive, if the volume is small and the target has no place for it. Record what was excluded and why.
• Escalate as a business decision, if the volume is large. It is not a migration decision at that point, and presenting it as one is how migration teams end up owning choices they have no authority to make.

What is not an option is a silent default. Every unexplained residue that gets quietly mapped to something plausible is a defect scheduled to appear after go-live, when it is no longer cheap to fix.

Why this changes the shape of the project

Legacy migration inverts the usual assumption. On a modern source, the model is known and the work is transformation. On a legacy source, discovering the model is the work, and transformation is what follows.

Practically, that means the audit is longer, the arbitration volume is higher, and the plan needs its own workstream for business decisions rather than a cleansing task assigned to the migration team. It also means the number of times you can run the full migration matters more than usual, because each run surfaces another convention nobody remembered. That is the argument for tooling you can rerun cheaply rather than scripts written for one attempt.

It also sharpens one decision more than any other. On a legacy estate, carrying the source structure into the target means inheriting every convention described above: the overloaded field, the code whose meaning drifted, the numbering scheme that ran out of digits. That is the trade-off examined in the strategy decision that is not the cutover.

The full sequence, and where this fits, is in the ERP data migration guide.