Surrogate Key Binding

A regional sales report is signed off in March. In November it shows different numbers for March, and nobody deployed anything. The cause is a join: the fact carries the customer's natural key, the dimension is Type 2, and the report joins on is_current = true. Every customer who moved region since March silently took their historical revenue with them.

Surrogate key binding removes the ambiguity by making the fact row state which version it belongs to. The loader finds the dimension version whose validity window contains the event's date and writes that version's surrogate key onto the fact. The report then joins one column with no date predicate, and March's number stays March's number.

Why it matters

Type 2 dimensions are adopted to preserve history, and then the history is lost in the query layer, because as-at reporting needs both a versioned dimension and a correct join, and the second is delegated to whoever writes the SQL. The two wrong variants are easy to write and hard to see: joining to the current version, so history drifts, or joining with no effective-date predicate at all, so every fact row matches every version and additive measures inflate in proportion to how often attributes change. Binding at load time moves that decision from hundreds of query authors to one place, reviewed once.

Implementation patterns

• Look up by event date, not run date. The predicate is event_date between valid_from and valid_to on the natural key, so late-arriving facts bind correctly rather than to today's version.
• A high sentinel end date (9999-12-31) on the current version, so that predicate needs no NULL handling.
• An unknown-member row per dimension with a reserved key for when the version has not arrived: the fact loads, the measure stays countable, and the mismatch is visible as a count against the unknown member rather than as a dropped row - plus a bounded job that re-binds those facts later.
• Carry both keys during migration, so reports move one at a time and can be compared.

Industry example

This is the standard Kimball treatment, documented across editions of The Data Warehouse Toolkit (3^rd edition, 2013): facts hold surrogate keys resolved at load, dimensions hold versions. Regulated finance relies on it because restatement rules require a closed period to keep reporting the same figures, and the pattern recurs wherever the question is "what did this look like then" - insurance policy versions, subscription plan changes, employee organisational history.

Failure scenarios

• Binding to the current version by mistake, which is the drift above: last year's revenue moves regions each time a customer does.
• No effective-date predicate, which multiplies fact rows per dimension version - a 4% overstatement that looks like a data-quality problem rather than a join bug.
• Facts dropped by an inner join when the dimension version is missing, so revenue quietly disappears and totals do not tie to the source.
• Partial conversion, where some reports join on the surrogate and others on the natural key, so two dashboards disagree and both are defensible.

Trade-offs

Binding buys a stable past and pays in load complexity: the fact loader now depends on dimension loads completing first, which is an ordering constraint in the orchestrator and a decision about what to do when it is violated. It also makes corrections harder on purpose. If an attribute was wrong rather than changed, the fix is a restatement rather than a new version, and surrogate keys make you confront that distinction instead of overwriting it. Storage cost is trivial - one integer per fact row.

When not to use it

When no report asks about the past as it was. A dashboard showing current state only is better served by a Type 1 dimension and a natural-key join: fewer moving parts, no ordering constraint. It is also wrong for high-frequency attributes - a status that changes hourly belongs on the fact. And where facts and dimensions are rebuilt in full nightly from retained raw data, binding at query time is acceptable provided one semantic layer owns the date predicate.

Interview question

Q: "A closed quarter's revenue-by-region report changes six months later. Walk me from symptom to root cause, and tell me what you would change so it cannot recur - including how you would handle fact rows that arrive before their dimension row does."

What a strong answer covers: the drift mechanism and why it produces no error; the difference between joining on the current version and joining with no date predicate, and how each shows up in the numbers; load-time binding as the structural fix; the unknown member plus a bounded rebinding job; and whether a backdated correction is allowed to change a signed figure.

Quick check

Quiz: A fact row for 4 March arrives on 7 March and the customer's segment changed on 5 March. Which version binds? The version valid on 4 March, because the fact describes what was true when the event happened - binding by run date attributes the sale to the new segment.

Flashcard: What does surrogate key binding put on the fact row, and what does it remove from the query? — The dimension version's key, resolved at load time by event date; the query then needs no effective-date predicate and cannot accidentally report the present.