Reference Data Can Rewrite History
3 min read

Reference Data Can Rewrite History


Most incremental pipelines are very good at asking which transactions changed. They have timestamps, row hashes, merge keys, and increasingly elaborate ways to avoid touching yesterday’s data.

Then yesterday’s report changes even though none of yesterday’s transactions did.

The culprit may be a lookup table.

The fact didn’t change. Its interpretation did.

Imagine a sale recorded in February for account A17. In February, the customer master assigns A17 to region East. In June, a correction moves that account to Central, effective from January.

There are two defensible answers to the question “What were East’s February sales?”

  • As originally reported: include the sale under East because that was the relationship known at the time.
  • Restated using corrected history: include it under Central because the revised relationship is effective for February.

Neither answer is universally right. Finance, sales operations, and audit may need different versions. A report that silently switches between them has a semantic bug, even if every SQL query succeeds.

An unchanged February transaction is interpreted differently under original and corrected reference-data timelines.

A historical fact and the rules used to interpret it have separate timelines.

This is where a normal transaction watermark runs out of information. No modified_at advanced on the sale. Its amount and business key are identical. Re-running a merge against only changed transactions won’t reconsider the February dimension assignment.

More than one clock

It helps to name three separate times:

  1. Event time: when the business transaction occurred.
  2. Reference effective time: when a relationship is considered valid in the business.
  3. Knowledge time: when the data platform learned or changed that relationship.

A slowly changing dimension can preserve effective ranges. If you also need to reproduce what the system knew on a particular day, you need the knowledge-time story too—sometimes through versions, snapshots, or a bitemporal model. Adding valid_from to a table isn’t automatically enough.

This can also happen with product classifications, reseller hierarchies, exchange-rate policies, rebate mappings, and organizational ownership. The transaction doesn’t carry the final answer to every reporting question. Some answers are derived from mutable context.

Three ways to handle it

Preserve the original resolution. Store the resolved dimension key or historical snapshot used at processing time. This is useful for “what did we report then?” but can resist intentional restatements.

Resolve at query time. Join the fact to the current or effective-dated reference relationship whenever someone reads it. This keeps interpretation flexible but makes query semantics and performance more demanding. Reproducing past answers also requires retaining past reference versions.

Rebuild affected fact windows. When a mapping changes, rederive an appropriate slice from durable source data and current business rules. This can be simpler than row-level dependency tracking, provided the window is complete enough and the target replacement is safe.

These choices optimize for different promises. A regulatory snapshot may need immutable historical attribution. A current-territory sales dashboard may intentionally restate history. A financial close may need both with a visible switch.

The hard work is identifying the dependency: which historical facts could this reference-data edit affect? A rolling window is robust only inside its boundary. A change effective two years ago won’t be fixed by a 60-day rebuild. At that point you need a broader targeted replay, a versioned interpretation, or an explicit rule that history outside the window is frozen.

The question I now ask before choosing a watermark

Before optimizing the changed-row feed, I ask whether the output is a function only of changed source rows. If it also depends on mutable reference data, the change signal must include those dependencies—or the architecture must have a way to reconstruct affected outputs.

That extends the distinction in Incremental Load Is Not One Thing. The source can be perfectly incremental while the business interpretation is not.

I used to think of reference tables as the quiet part of the model. They are often the part capable of changing the meaning of everything else without touching a single transaction.

More on Data engineering Two Customer IDs, Both Correct →