Why does a fact table store the dimension surrogate key resolved at load time rather than the business key?

## The choice A fact row refers to a customer. In a Type 2 dimension that customer is several rows. There are exactly two places the "which version?" decision can be made: - **At load time**, by the pipeline, once per fact row, writing a resolved surrogate key into the fact. - **At query time**, by every query, by carrying the business key on the fact and joining with a range predicate against the effective window. Dimensional modelling takes the first, and the reasons are worth being able to list. ## Reason one: correctness stops depending on the query author With the surrogate key resolved, the join is: ```sql SELECT d.region, SUM(f.order_amount) FROM fct_order f JOIN dim_customer d ON d.customer_sk = f.customer_sk GROUP BY d.region; ``` There is no way to write this wrong. There is no predicate to forget. With the business key on the fact, the *correct* query is: ```sql SELECT d.region, SUM(f.order_amount) FROM fct_order f JOIN dim_customer d ON d.customer_id = f.customer_id AND f.order_date >= d.effective_from AND f.order_date < d.effective_to GROUP BY d.region; ``` and the *catastrophic* query is the same thing with the two date lines missing. It runs. It returns plausible-looking numbers. It has multiplied every order by the number of versions that customer has — one for a customer who never changed, three for one who moved twice. Revenue is inflated non-uniformly, so it does not even look obviously wrong. Making that mistake unwritable is the single strongest argument for load-time resolution. ## Reason two: the join gets much cheaper An equality join on a single narrow key is the shape every analytical engine is built to execute well. A range join — equality on the business key plus two inequalities — is materially harder: it cannot be satisfied by a straight hash match on the full predicate, and the engine ends up matching on the key and then filtering candidate versions. Multiply that by every query, every dashboard refresh, and every dimension in the star, and the difference is not academic. The pipeline does the range lookup once per fact row at load; queries never do it again. ## Reason three: history is frozen by construction This is the reason that survives every re-litigation. Once `customer_sk` is in the fact, no later dimension change can move that fact. The customer moves to APAC in August; the March order still points at the EMEA version, so the March report reproduces exactly as it did in March. Auditors, regulators and finance close all want this property, and it is the entire point of Type 2. Query-time resolution *can* give the same answer, but only for as long as every query keeps the date predicate — the guarantee is behavioural rather than structural. ## How the resolution is done At load, each incoming fact is joined once to the dimension on business key plus window: ```sql INSERT INTO fct_order (order_sk, order_date, customer_sk, order_amount) SELECT s.order_sk, s.order_date, d.customer_sk, s.order_amount FROM stg_order s JOIN dim_customer d ON d.customer_id = s.customer_id AND s.order_ts >= d.effective_from AND s.order_ts < d.effective_to; ``` Two details decide whether this is safe. **Order of operations.** The dimension must be loaded before the facts that reference it in the same batch, or the newest version does not exist yet and recent facts resolve to the previous version — a subtle, systematic misattribution that is hard to spot afterwards. **Which timestamp you resolve against.** The event timestamp, not the load timestamp. Resolving a backfilled week of orders against "now" attributes all of them to today's version. An inner join here also silently drops facts whose business key has no matching version — which is why production loads use an outer join and route unmatched rows somewhere deliberate rather than letting them vanish. (What that placeholder looks like, and how to repair it once the dimension catches up, is the late-arriving problem and belongs to its own discussion.) ## What the choice costs Committing the fact to a version means the fact can no longer be trivially reported by the entity's *present* attributes: "total lifetime revenue by the customer's current sales region" needs a second hop, from the version row to the durable business key and back to the row currently in force. That is a real and frequent requirement, and the model has to provide for it explicitly. It also means facts and dimensions are coupled at load time: the fact load cannot run correctly if the dimension load has not. That ordering constraint is a genuine operational cost, traded for the removal of a whole class of query-time errors. ## When query-time resolution is defensible Occasionally you keep the business key on the fact *in addition* — not instead. It is useful for reconciliation against the source system, for the current-attribute join described above, and for rebuilding facts if the dimension's surrogate keys are ever regenerated. Storing both is cheap and is a common, pragmatic choice. What is not defensible is storing *only* the business key and relying on every analyst to remember two inequalities.