Surrogate Keys vs Natural Keys
Why warehouses add their own integer or hash keys, how they enable SCD2 history, and when a natural key is good enough.
Data ModelingIntermediate2 min readUpdated 11 Oct 2026
Definitions
- Natural (business) key: an identifier from the source, e.g.
customer_id = 'C001'or an email. - Surrogate key: a key the warehouse generates, e.g.
customer_sk = 1048or a hash.
Why add a surrogate key?
| Reason | Example |
|---|---|
| History (SCD2) | C001 has three versions over time; each needs its own key |
| Multiple sources | C001 from the shop and C001 from the CRM are different people |
| Source keys change | A product SKU is re-coded |
| Join performance | Integer joins are cheap; long strings are not |
dim_customer
customer_sk | customer_id | loyalty_tier | valid_from | valid_to | is_current
1048 | C001 | Silver | 2025-01-01 | 2026-03-01 | false
2210 | C001 | Gold | 2026-03-01 | 9999-12-31 | trueA fact recorded in February points to customer_sk = 1048, so it stays attributed to Silver even after the customer upgrades.
Sequence or hash?
| Sequence / identity | Hash of business key (+ version) | |
|---|---|---|
| Size | Small integer | 16–32 bytes |
| Needs a lookup to assign | Yes | No, computed anywhere |
| Parallel / distributed loads | Harder | Easy and deterministic |
When natural keys are fine
For small, stable dimensions with no history requirement (a list of countries or currencies), the natural code is clearer and needs no lookup.
Finished this note?