Surrogate Key

A surrogate key is an identifier the data warehouse assigns to each row of a dimension table, with no business meaning of its own. The Kimball Group describes dimension surrogate keys as simple integers, assigned in sequence starting from 1, every time a new key is needed. It sits alongside the natural key — the identifier the source system uses, such as a customer number or SKU — rather than replacing it.

Why not use the natural key?

The Kimball Group gives two reasons.

• History needs several rows per entity. When a dimension tracks changes by adding a row per version, one natural key has several rows, so it can no longer be the primary key.
• Source keys are outside the warehouse’s control. Natural keys from different source systems may conflict, and may be poorly maintained. Assigning its own keys lets the warehouse manage identity consistently. In practice this also protects against changes such as customer numbers being reissued or a system migration altering the key format.

The Kimball Group notes one standard exception: the date dimension, whose rows are predictable and stable, can use a more meaningful primary key.

How it makes history work

In a slowly changing dimension using Type 2, a new surrogate key is created each time a tracked attribute changes. The surrogate key a fact receives is looked up when the fact is loaded, but the lookup is by natural key and the date of the event, matching the version whose effective range covers it. A sale made on 9 March and loaded on 11 March, after the region changed on the 10th, still gets the key of the version that was current on the 9th. Each fact therefore stays attached to the version that was current when the event occurred, and a report grouped by region credits sales to the region at the time — without any date logic in the query.

The example is invented. Following one customer across all versions is what the natural key, or a dedicated durable key, is for.

Obligations that come with it

• Keep the natural key. Loads need it to match incoming records to dimension rows, late facts need it to find the right version, and reconciliation with the source needs it.
• Look up the right key at load time. Facts must receive the surrogate key of the version current when the event happened, which is harder for events that arrive late.
• Enforce uniqueness on the key facts join to. A duplicated surrogate key multiplies every fact joined to it, and nothing reports the error.
• Keep it meaningless. A key that encodes a region or a date becomes wrong when that attribute changes, which defeats its purpose.

How surrogate keys fit with the other clocks and history techniques a model needs is worked through in Modeling Time: Three Clocks, History, and Corrections.

References: Kimball Group, Dimension Surrogate Keys; Kimball Group, Slowly Changing Dimensions, Part 2.