Reference data change management

Reference data is not static, it is just slow

Almost every data platform starts by dividing the world into two: the facts, which change constantly and are modeled carefully, and the reference data, which describes what the facts are about and is treated as a lookup table. Instruments, entities, counterparties, currencies, sectors, regions, ratings scales, product hierarchies, calendars. The second group changes at perhaps a thousandth of the rate of the first, and that ratio is exactly what makes it dangerous, because a rate of change that low invites a design that cannot represent change at all.

The design in question is the one everybody writes first: a table with one row per entity, holding the current attributes, updated in place when the source sends something new. It is fast, it is easy to join against, every consumer understands it, and it silently destroys the only thing you will later need — the ability to say what you believed on a given day. Once a row has been updated in place, the previous value is gone, the reason is gone, and the date the change took effect is gone. All three matter, and the third one matters most.

The moment this becomes visible is always the same. Someone reruns a report over a historical period and gets a different answer than the one that was published at the time. The numbers are not wrong, exactly — they are computed with today’s reference data over yesterday’s facts, which is a perfectly reasonable thing to do and almost never what anybody wanted. A holding that moved between two sector buckets in April now appears to have been in the new bucket since inception. A hierarchy that restructured after an acquisition now rolls up the whole prior year to the acquirer. Nothing failed. There was simply no way to ask the question the way it needed to be asked.

You need two clocks, not one

The fix is old, well understood and underused: store two timestamps on every reference fact rather than one. The first is when the fact was true in the world — the effective date, the date a reclassification took hold, the date a name legally changed. The second is when you learned it — when the record landed in your system. Practitioners call these valid time and transaction time, and a store that keeps both is bitemporal.

Two clocks let you ask two different questions and get two different, both-correct answers. What is true about April, as best we know today? uses the first clock. What did we believe about April on the last day of April? uses both, and it is the question behind every reproduction of a published figure, every explanation of a moved number, and every review of a decision that was made with the information available at the time. A single-clock store can answer the first question. It cannot answer the second at all, and no amount of care downstream recovers the ability.

The cost is real but smaller than its reputation. Rows become intervals, joins acquire a date predicate, and the store grows with the number of changes rather than the number of entities — which for reference data is a modest multiple, not an explosion. The genuine difficulty is not storage, it is discipline: every consumer must now pass an as-of date, and the ones that do not will silently get the default. Make the default explicit and loud. A view named for what it is, a required parameter, a query that refuses to run without a date — whichever fits your stack, the goal is that nobody gets point-in-time semantics by accident and nobody gets current-state semantics by accident either.

If a full bitemporal model is more than you want to take on today, the cheap intermediate step is to stop updating in place. Append every version of every row with its effective date and the timestamp it arrived, keep a view that selects the latest for the consumers who only want current state, and you have preserved the option to build the rest later. Preserving the option costs almost nothing. Recovering the history after it has been overwritten costs a great deal, and usually cannot be done at all.

Three kinds of change that get called the same thing

Most reference data teams have one word, “update,” for three events with different causes, different owners and different downstream obligations. Separating them is the highest-value vocabulary change you can make, because it decides what a consumer has to do when they see one.

The practical consequence is that a change feed carrying only “field X on entity Y is now Z” is not enough information for a consumer to act correctly. It needs the kind, the effective date, and the reason. Those three fields turn a change log from an audit artifact into something a downstream team can automate against — ignore genuine changes, alert on corrections that touch a published period, halt and review on restatements.

The identifier lifecycle nobody models until it bites

Identifiers look like the stable part of reference data and are frequently the least stable thing in it. The failure modes are worth naming individually, because each breaks a different assumption:

Reuse. Some identifier spaces recycle. A short symbol released by one issuer can be assigned to a different one later. Any join that treats the symbol as a permanent key will, on the day of reuse, quietly attach one entity’s history to another’s. This is the single worst reference data defect we encounter, because the output is not obviously wrong — it is a plausible series that happens to be two different things stapled together.

Retirement and reissue. An identifier stops being valid, and a system that has never seen a retirement treats the absence as a data gap rather than an event. Model retirement explicitly, with a date, so the difference between “gone” and “missing” is visible.

Merges and splits. Two entities you tracked separately become one, or one becomes two. Both cases require a mapping with dates, not a rename, because history on either side of the event belongs to different things. A merge recorded as an in-place update erases the fact that there were ever two.

Multiple identifier systems that disagree. Most organizations carry several — an internal key, one or more vendor keys, a public identifier for the instrument, another for the legal entity, and a name. They have different scopes and different lifecycles, and the mistake is treating any of them as the primary key. The durable pattern is an internal identifier that never changes and never gets reused, with every external identifier held as a dated mapping to it. That single decision prevents most of the incidents in this section, and it is much easier to make before there is a decade of data than after.

Run change as a release, not as an update

Reference data behaves much more like code than like transactional data, and the operational practices that work are the ones borrowed from software release rather than from database administration.

A change arrives as a proposal, not as a write. It is staged somewhere consumers can see it before it takes effect. Somebody with domain knowledge reviews it, and that review is recorded next to the change rather than in an email. It carries an effective date that is separate from the date it is applied, so a change known in advance can be loaded now and activated later. It is published to consumers with enough notice for them to react, and it is reversible — which is nearly free if you never update in place and nearly impossible if you do.

The piece most often missing is impact analysis before the change lands. Before a hierarchy restructure or a scheme version rolls forward, run it against a copy and compute what moves: how many entities change bucket, which published aggregates shift, and by how much. That report takes an afternoon to build once and turns a class of unpleasant surprises into a decision made with numbers. It also gives you the text for the release note, which is the thing your consumers actually need and rarely receive.

Two smaller habits pay disproportionately. Keep a change calendar, because a meaningful share of reference change is announced in advance — scheme versions, index reconstitutions, regulatory identifier rollouts, corporate actions with known dates. Nothing on a calendar should ever arrive as a surprise. And keep a dead-letter path for changes you cannot apply automatically, reviewed by a person on a schedule, so that unresolvable input accumulates somewhere visible rather than being dropped or force-fitted.

What to require from a data vendor

If reference data arrives from outside, the terms of that arrival determine how much of the above is even possible. Four things are worth insisting on, and none of them are unusual asks.

Effective dates, not just delivery dates. A file that says a value is now X, without saying when X became true, forces you to invent an effective date, and every consumer will invent a different one. This is the single most valuable field a vendor can add and the one most commonly absent.

A stated restatement policy. Ask directly: when history is reissued, are we told, how are we told, and how far back can it go. Then ask what happened the last two times. The answer to the second question is more informative than the answer to the first.

Deltas, or a way to compute them reliably. A full-file replacement with no change list means every consumer builds their own diff, and diffs built independently disagree. If deltas are not offered, keep every delivered file immutably so you can compute the diff yourself and get the same answer twice.

Deprecation notice for structural change. A new column is easy. A renamed column, a retyped field, a changed code list or a new file layout is a release, and the useful question at contract time is how much notice you get and whether both versions run in parallel for a period. Sixty to ninety days with an overlap is a common shape; verify what your actual agreement says rather than assuming, because the practice varies widely and the fallback is often much shorter than anyone expects.

Common mistakes

• Updating reference rows in place, which destroys the history irreversibly and for no benefit
• One clock instead of two, so “what did we know then” is unanswerable
• Joining on a vendor or public identifier rather than an internal key that is never reused
• Treating corrections, genuine changes and restatements as one event type, leaving consumers no way to react appropriately
• Effective date and load date collapsed into one field, usually the load date, usually silently
• No impact analysis before a scheme version rolls forward, so the size of the move is discovered by a client
• A change log written for auditors and not for consumers — no kind, no reason, no affected periods
• Governance that is a meeting rather than a mechanism, approving changes it has no tooling to evaluate

Where you do not need us

The most valuable decisions here are ones your own team should make this month, and they are not large pieces of engineering. Adopting an internal identifier that is never reused is a design decision and a migration script. Switching from update-in-place to append-with-effective-date is a schema change and a view. Adding kind and reason to your change log is two columns. Those three account for most of the benefit on this page, and hiring anyone to do them would be slower than doing them yourselves.

The work that genuinely benefits from outside hands is narrower: reconstructing point-in-time history for a period that was already overwritten, which is forensic work against archived files and vendor deliveries and is rarely fully recoverable; migrating a large single-clock store to a bitemporal model while it is serving production reads; and building the impact-analysis harness that tells you what a scheme version will move before it moves. If someone offers to sell you a governance program before any of the three structural fixes are in place, the program will produce documents rather than reproducible numbers.

Bottom line

Reference data changes slowly enough to invite a design that cannot represent change, and that design is the root of nearly every reproduction problem downstream. Give every reference fact an effective date and a load date, never update in place, key everything on an internal identifier that is never reused, and distinguish corrections from genuine changes from restatements in the feed your consumers read. Then run change like a release: staged, reviewed, dated, announced, reversible, with impact computed before it lands. None of it is novel and most of it is cheap at the start, which is the only time it is cheap.

Frequently asked questions