A reference data set of branch codes is maintained by an operations team. A branch closes in March and its code is retired. In September the code is assigned to a new branch in another city. Six months later finance asks why one branch's historical performance looks impossible. What happens across the estate?

What happens, step by step

At retirement (March), nothing visibly changes. Historical facts still carry the code, and joins to the reference table still resolve, because the row is usually flagged inactive rather than deleted.

At reassignment (September), every join silently changes meaning. Any query joining facts to the current reference row now attributes March's transactions to the new city. No error is raised and no row count changes, which is why this is discovered by a human noticing an implausible number rather than by a pipeline.

In downstream aggregates, the corruption compounds: regional totals shift, a year-on-year comparison breaks in both directions at once, and a machine-learning feature built on branch attributes now trains on a mixture of two branches under one key.

In previously published reports, the numbers no longer reproduce, which is the part that becomes an audit finding rather than a data quality ticket. A 6-month gap between the two events is typical, and it is long enough that nobody connects them.

Where it amplifies

The worst amplification is through derived keys: a surrogate key built by hashing the branch code plus a date, a partition path containing the code, a cached dimension in a BI tool. Each one holds the old meaning at a different age, so different reports disagree by different amounts.

What stops it

• Never reuse an identifier. This is the structural fix and it costs almost nothing: the reference system issues the next unused code, forever. ISO 3166, first published in 1974, does exactly this: a withdrawn country code is transitionally reserved rather than reissued, so historical records keep their meaning. Choose reuse only where an external system forces it on you. Everything else on this list is mitigation for having not done it.
• Make the reference dimension temporal: valid-from and valid-to on every row, and joins performed as-of the fact's date. This is correct even without reuse, because attributes change.
• Test for it. A scheduled check that no retired code changes its attributes, and that no code's valid_from precedes another row's valid_to for the same key.
• Publish a reference data contract that states the reuse policy explicitly, because the operations team retiring the code has no idea an analytical estate depends on the promise.

When this is over-thinking it

For a genuinely static code list — ISO currency codes, country codes — the temporal machinery is usually unnecessary, and even there the exceptions are instructive: currencies are redenominated and country codes are reassigned, so the right default is to timestamp the join and skip the worry. For codes minted by an internal team on a weekly basis, do the full temporal model.