Identifier Reuse Hazard

An operations team retires a branch code in March. In September it assigns the same code to a new branch in another city, because the code space is small and reuse seems tidy.

From that day, every join between historical facts and the current reference row attributes March's transactions to the new city. No error is raised, no row count changes, no test fails. The corruption is discovered 6 months later by a person noticing an implausible number, usually during a period comparison.

The same hazard appears wherever an identifier is scarce and recycled: employee numbers, SIM numbers, email addresses reassigned to new staff, tenant slugs freed when an account closes.

Why it matters

Time to detection is the whole problem. A pipeline failure is noticed within 2 hours; this is noticed two quarters later, after the wrong values have propagated into aggregates, features, published reports and decisions.

It is also one of the few data defects that makes previously published numbers irreproducible, which converts a data quality ticket into an audit finding. A report signed off in April cannot be regenerated in October, and no code change explains why.

Implementation patterns

• Never reuse an identifier. The reference system issues the next unused value, forever. This costs almost nothing and removes the hazard entirely.
• Make reference dimensions temporal: valid_from and valid_to on every row, with joins performed as of the fact's date. This is correct even without reuse, because attributes change.
• State the reuse policy in the reference data contract, because the operations team retiring a code has no idea an analytical estate depends on the promise.
• Test for it: a scheduled check that no code's attributes change after retirement, and that no two validity windows for the same key overlap.
• Preserve the surrogate key mapping. Where facts carry a surrogate key generated at load time, reuse of a natural code is survivable — as long as the surrogate is never regenerated from the natural key later.

Industry example

ISO 3166, the country code standard first published in 1974, deals with this explicitly: a withdrawn alpha-2 code is transitionally reserved rather than reissued, so that historical records keep their meaning. Telephone number recycling is the counter-example that shows the cost — numbers are reassigned by regulation after a quarantine period, and account recovery and messaging systems have carried defects from it for as long as they have existed.

Failure scenarios

• Derived keys holding different ages of meaning: a hashed surrogate, a partition path containing the code, a cached BI dimension. Reports then disagree by different amounts.
• Features trained on two entities under one key, so a model learns an average of two branches and nobody can explain its behaviour.
• A year-on-year comparison breaking in both directions at once, which is the signature symptom worth memorising.
• Retro-active restatement of a closed accounting period, which is where the finding gets escalated.

Trade-offs

Non-reuse is nearly free for internally issued codes, so the trade-off only bites where the identifier is externally governed and scarce. Temporal reference data has a real cost: every join gains a date predicate, queries get slower and more verbose, and analysts must be taught to write them. Paying it on the five dimensions that carry history is proportionate; paying it on every lookup table is not.

When not to use it

For a genuinely static, externally governed code list — currencies, units of measure — the temporal machinery is usually unnecessary. Even there the exceptions are instructive, since currencies are redenominated and country codes change, so the pragmatic default is to timestamp the join on anything that could plausibly be restated and to leave the rest simple.

Interview question

Q: Finance says one branch's historical performance is impossible, and you find the branch code was retired in March and reassigned in September. Walk me through the blast radius, how you would remediate, and what you would change so it cannot recur.

What a strong answer covers: enumerating derived artefacts by lineage — aggregates, features, extracts, cached dimensions — rather than fixing the dimension and declaring victory; restating affected periods with a documented correction rather than silently rewriting; splitting the key into two entities with validity windows; the contract change with the issuing team as the actual fix; and naming that published reports were irreproducible for a period, because hiding that is how a data issue becomes an audit issue.

Quick check

Quiz: Why does identifier reuse produce no alert? — Joins still resolve and row counts do not change; only the meaning of the joined row is wrong.

Flashcard: What is the cheapest structural defence against identifier reuse? — Never reuse: issue the next unused value forever. Temporal validity windows are the mitigation when you do not control the issuer.