# 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.

- Source: https://akshaythakare.com/notes/data-modeling/surrogate-vs-natural-keys
- Topic: Data Modeling
- Level: intermediate
- Reading time: 2 min
- Tags: data-modeling, keys, scd2, interview
- Updated: 2026-10-11

## 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 = 1048` or 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 |

```text
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 | true
```

A 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.

> **Warning: Keep the natural key too**
>
> Always store the business key next to the surrogate key. You need it to match incoming changes and to explain the data to users.