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.

Stored values provide evidence of actual usage. Combine them with source code, reference tables, logs and the people who maintain the data. A data audit uses those sources together to test hypotheses about meaning.

What the distribution of values tells you

Profile the fields and full dataset in the agreed scope. Four measurements help identify questions to investigate; none proves a field’s meaning on its own:

Cardinality against expectation. A free-text field with 6 distinct values across 400,000 records may hold an informal code list. Confirm this against the input rules, reference data and business usage before migrating it as one. Conversely, 3,000 distinct values may be a legitimate large code list, inconsistent formatting or comments entered in the wrong field. Count values outside the expected reference set and investigate them.

Format clustering. Group values by pattern: AB-1234, AB-1234-R, 1234 and ab 1234. Differences may reflect periods, sites, record types or formatting alone. Compare those contexts, then confirm which patterns preserve the same identity before defining conversion rules.

Distribution shape. A value covering 94% of records may be a default or a genuinely common category. Inspect the field’s defaulting rules and representative records before deciding which. Test the dominant value as well as the tail: both can carry business information, and a transformation error in the dominant value affects most of the scope.

Null rate by period. A field that is 80% empty overall but 3% empty since 2019 suggests a change worth investigating. It may reflect a new input rule, a different source population or a backfill. Confirm the cause and the relevant date before using it as a boundary in a migration rule.

Fields used for something other than their name

The ZONE case illustrates how a field can acquire a second purpose when a business need has no dedicated place in the system. Three signals can guide the investigation:

• 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 syntactically valid but implausible value, such as a delivery date in 1900 or a quantity of 99999. Check whether it is a marker, an error or a legitimate value in that context.
• An unexplained correlation. If ZONE ending in W tracks a customer type, country or document, investigate the relationship. Correlation alone does not establish that the field encodes that meaning.

Cross-tabulate suspicious fields against relevant business attributes and compare periods and sites. Then verify unexplained relationships in the source code, reference data or with the people responsible. Record both the evidence and the confirmed rule; keep an unresolved hypothesis out of the automatic mapping.

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.

Temporal analysis is one route to finding these changes. Plot frequencies by period and compare them with code history, reference-table versions and process changes. A change of meaning may leave the frequency unchanged; interviews and dated examples still matter.

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 supported free-text or legacy field, preserving the value without asserting a meaning. Business owners must confirm that operational processes do not depend on the unresolved meaning. Estimate and test any required field extension: retaining the value does not restore the missing business behaviour. If that behaviour is needed at go-live, keep the decision open until an acceptable treatment is agreed.
• Exclude and archive, if business use, access and retention requirements allow it. Record what was excluded, why and who approved the decision.
• Escalate as a business decision, when meaning or impact remains uncertain, even for a small number of records. The responsible business owner decides the treatment; record volume alone does not determine the risk.

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 makes repeatable execution useful: each pass can verify revised rules and reveal remaining discrepancies. Track what those runs demonstrate, including value equivalence and business behaviour, rather than treating the pass count itself as proof.

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.