Surrogate Keys & Effective Dating

## The problem this solves A dimension table describes an entity — a customer, a product, a store. In a source system that entity has exactly one row, updated in place. In a warehouse that tracks history with SCD Type 2, a change to a tracked attribute does not overwrite the row; it closes the old row and inserts a new one. The customer now occupies two rows, and will occupy five after four more changes. That immediately breaks the assumption that one identifier means one row. You need two different identifiers doing two different jobs. ## The two keys **The business key** (also called the natural key, or the *durable* key when you want to stress the point) is the identifier the source system uses: `customer_id = 'C7'`. It is identical on every version of that customer. It answers "which entity is this?" and it is what you group by for "total revenue per customer, all time, regardless of how many times they moved". **The surrogate key** is a warehouse-generated identifier — `customer_sk` — that is unique per *row of the dimension*, not per entity. C7's March version and C7's August version get different surrogate keys. It answers "which version of which entity is this?" It is the dimension's primary key, and it is the column fact tables foreign-key to. The surrogate key means nothing to the business and should never be shown to a user or matched against a source system. Its whole purpose is to be a stable handle on one row of history. ## What the fact table sees When a fact row is loaded, the ETL resolves the event's business key plus its timestamp against the dimension's effective windows and writes the *resolved surrogate key* into the fact: ```sql -- resolve once, at load time SELECT d.customer_sk FROM dim_customer d WHERE d.customer_id = :order_customer_id AND :order_ts >= d.effective_from AND :order_ts < d.effective_to; ``` From then on, every report is a plain equality join `fct_orders.customer_sk = dim_customer.customer_sk`. No date logic at query time, no range predicate, and — critically — no way for a later dimension change to alter what an old report says. The March order is welded to the March version. ## What breaks if you skip it If the fact table stores the business key instead, one of two bad things happens. Either the join matches *every* version of the customer, and the fact fans out and multiplies revenue by the number of versions; or you add a range predicate to every query and every analyst has to remember it, and the ones who forget produce the fan-out. Either way the model has pushed a correctness requirement out of the schema and into the query authors. If instead you keep only one row per customer and overwrite it (Type 1), the join is safe but the history is gone: a report of last year's sales by region re-attributes revenue to wherever the customer lives now, and last year's number changes every time someone edits a record. ## The durable key still has to be in the dimension A common design slip is to build the versioned dimension with only the surrogate key as an identifier. The business key must stay as a column on every version, because several real questions need it: - "How many *distinct customers* bought?" — `COUNT(DISTINCT customer_id)`, not `COUNT(DISTINCT customer_sk)`, which counts versions. - "Show me all of this customer's history" — filter the dimension on the business key. - "Report old facts by the customer's *current* attributes" — hop from the fact's version row to the business key, then back to the row currently in force. So the dimension typically has: `customer_sk` as primary key, `customer_id` as the durable business key, the tracked attributes, the effective window and a current-row flag. ## Compound business keys and multi-source dimensions When a dimension is fed by more than one system, the business key alone may not be unique — two systems can both issue `C7`. The durable key then becomes the pair (source system, source identifier), and the surrogate key is what hides that ugliness from every downstream query. This is one of the quieter reasons warehouses use surrogate keys at all: the fact table's join column stays a single narrow integer no matter how messy the upstream identifiers are. ## The one-line version The business key identifies the thing. The surrogate key identifies the thing *as it was*. Type 2 history only works because facts reference the second, not the first.