Reference Data Drift

Month 1 of a migration, the reference tables — currency codes, tax classes, product categories, status enumerations — are copied to the new store and verified row for row. Month 9, a regulator forces a new tax class into the legacy system. Month 11, the new system starts rejecting roughly one order in 400 with a validation error, and nobody can reproduce it in test.

The rejection is correct behaviour by the new system's own rules: it has never heard of that code. The design decision that made it possible was treating reference data as a one-time migration artefact rather than as a stream with an owner. Transactional data got a replication pipeline and a reconciliation job. The lookup tables got a copy and a tick in a checklist, because they are small and static, and "static" turns out to be a property of a snapshot rather than of the data.

Why it matters

Reference data sits on the validation path of everything. One unknown code does not degrade a feature; it rejects whole transactions, and at a rate set by how common that code is in the business mix. It is also the category of data that is invisible to the controls built for the rest of the migration: nobody writes a row-count check for a nine-row status table, and nobody notices when it becomes ten.

Do the multiplication before the programme starts. A source system whose reference data changes quarterly, against an eighteen-month migration, will produce roughly six divergences. Whether they arrive as an alert or as a customer complaint is the only variable.

Implementation patterns

• Give every reference table a written disposition at the start: either continuously replicated from its owning system for the whole coexistence, or ownership transferred on a named date with a change process on both sides. "Copy it and check later" is how the missing third option is spelled.
• Make unknown-code rejections their own metric, distinct from malformed input, alerting on the first occurrence rather than on a rate. A code the system has never seen is categorically different from a bad field.
• Refresh non-production environments from current reference data, not from the migration-day snapshot. Seeding test from the same stale snapshot is what makes the defect unreproducible by construction.
• Record the source system's change cadence for each table during assessment, because that number and the migration's duration together predict the defect count.
• Fail loudly on unknown codes in the new system rather than defaulting to a catch-all value, which converts a visible rejection into a silent misclassification that is far more expensive to unwind.

Industry example

Regulatory code-list changes are the reliable trigger: tax classifications, payment scheme reason codes and product taxonomies all change on external timetables that no migration plan controls. Payment scheme rulebooks have carried mandatory code-set updates on roughly annual cycles for years, and that was still true as of 2025. The defect only ever appears in production, because production is the only place carrying the new codes. The archetype: an insurer's replacement policy engine rejected a slice of new business for seven weeks after a regulator introduced a new cover type, with the rejections logged as ordinary validation errors and the staging environment seeded from a snapshot that predated the change.

Failure scenarios

• The local fix. Someone inserts the missing row in an hour. It works, and it guarantees the next occurrence, because nothing about the mechanism changed.
• Threshold blindness. A 0.25% rejection rate sits under a 1% error-budget alert, so the signal exists and is averaged into invisibility for two months.
• Catch-all defaulting. Unknown codes mapped to "other" so that nothing rejects, producing months of silently miscategorised transactions and a reconciliation exercise instead of an alert.
• Reverse drift. The new system gains a code the old one lacks during coexistence, so records written on the new side fail on rollback.
• Ownership declared and not enforced, where both systems keep a maintenance screen for the same code list.

Trade-offs

When not to use it

A migration that completes inside a single change freeze does not need streaming reference data. If the source is frozen from Friday to Monday and the cutover sits inside that window, a one-time copy with row-for-row verification is exactly right, and replication for lookup tables is machinery with no failure to prevent. The rule is set by the duration of coexistence rather than by the size of the tables.

The same applies where the source system's reference data is genuinely immutable by regulation or contract for the life of the programme, though that is a claim to verify against the last three years of change history rather than to accept.

Interview question

Q: Eleven months into a migration, the new system starts rejecting about one transaction in 400 and the error cannot be reproduced in any lower environment. Where do you look, and what do you change so it does not happen again?

What a strong answer covers: suspecting reference data before suspecting the transfer; explaining why the test estate cannot reproduce it; the two thresholds that hid it; the distinction between the local fix and the structural one; and a disposition rule for every lookup table with the arithmetic that predicts how many divergences a programme of that length will see.

Quick check

Quiz: Why is an unknown reference code harder to detect than a transfer error? Because rejecting it is correct behaviour by the new system's rules, so nothing throws, no circuit trips, and the rate usually sits below a percentage-based error alert.

Flashcard: How long can a one-time reference-data copy be trusted? Until the source system's next change to it — so a quarterly change cadence against an eighteen-month migration produces roughly six divergences, each landing as a silent transaction rejection.