Surrogate keys vs natural keys: which should a dimension use, and why?

Detailed explanation

A natural key (business key) is the identifier the source system uses: a customer number, an SKU, an email address. A surrogate key is generated by the warehouse, has no business meaning and exists only to join facts to dimensions.

Why dimensions need surrogate keys

1. History (SCD Type 2). One customer who moved city twice has three dimension rows. They share the natural key, so the natural key cannot be the primary key; each version needs its own surrogate, and facts point at the version that was current when they happened.
2. Several sources. The CRM and the billing system both have customer 1001, and they are different people. The warehouse key must be unique across sources; the natural key becomes the pair (source_system, source_id).
3. Source changes you do not control. Systems get migrated and re-keyed, IDs get recycled after deletion, formats change (1001 becomes CUS-0001001). With surrogates, only the mapping changes; facts do not.
4. Special members. “Unknown”, “not applicable” and “not yet arrived” rows need keys (often -1, -2), so facts never carry NULL foreign keys.
5. Smaller fact rows and faster joins: a 4- or 8-byte integer instead of a long string, repeated across billions of fact rows. In columnar warehouses compression narrows this gap, but the first four reasons still hold.

The natural key still matters

Keep it in the dimension. It is how loads find the existing row, how analysts search (“show me customer CUS-0001001”), and, when stable across versions, it acts as the durable key that groups all Type 2 versions of one entity. If the source key itself is not stable, the warehouse generates a durable (supernatural) key once per entity and keeps it through re-keying.

Sequence or hash?

	Sequence / identity integer	Hash of natural key (for example MD5 or SHA-256 of source system and ID)
Size	4 or 8 bytes	16 to 32 bytes, or a string
Needs a lookup to assign	yes, a central generator	no: computed anywhere, the same value every time
Parallel and cross-system loads	needs coordination	trivially parallel; facts can compute the key without reading the dimension
Collisions	none	negligible for MD5/SHA at warehouse scale, but not zero
Type 2 versions	one new integer per version	hash the natural key plus the version start, or keep a separate version key

Data Vault and many lakehouse pipelines use hash keys for exactly the parallel-load reason. Classic Kimball warehouses use integers.

Example

Two sources share an ID, and the warehouse keeps them apart with a key map:

CREATE TABLE dim_customer (
  customer_key   int GENERATED ALWAYS AS IDENTITY PRIMARY KEY,
  source_system  text NOT NULL,
  source_id      text NOT NULL,
  customer_name  text,
  UNIQUE (source_system, source_id)          -- the natural key, scoped by source
);
-- Special member for facts whose customer is unknown
INSERT INTO dim_customer OVERRIDING SYSTEM VALUE VALUES (-1, 'n/a', 'unknown', 'Unknown customer');

INSERT INTO dim_customer (source_system, source_id, customer_name) VALUES
  ('crm',     '1001', 'Meera Iyer'),
  ('billing', '1001', 'Arjun Das');            -- same ID, different person

CREATE TABLE stg_payments (source_system text, customer_id text, amount numeric(10,2));
INSERT INTO stg_payments VALUES ('billing', '1001', 250.00), ('crm', '1001', 80.00), ('billing', '9999', 15.00);

-- Fact load: look up the surrogate by the scoped natural key; unmatched rows get -1, not NULL
SELECT p.source_system, p.customer_id,
       coalesce(d.customer_key, -1) AS customer_key,
       coalesce(d.customer_name, 'Unknown customer') AS resolved_to, p.amount
FROM stg_payments p
LEFT JOIN dim_customer d ON d.source_system = p.source_system AND d.source_id = p.customer_id
ORDER BY p.amount DESC;

 source_system | customer_id | customer_key |   resolved_to    | amount
---------------+-------------+--------------+------------------+--------
 billing       | 1001        |            2 | Arjun Das        | 250.00
 crm           | 1001        |            1 | Meera Iyer       |  80.00
 billing       | 9999        |           -1 | Unknown customer |  15.00

Joining on customer_id alone would have matched each payment to both people and doubled the revenue. A deterministic hash key gives the same separation without a lookup, which is why it suits parallel loads:

SELECT source_system, source_id,
       md5(source_system || '|' || source_id) AS customer_hash_key
FROM dim_customer WHERE customer_key > 0 ORDER BY customer_key;

 source_system | source_id |        customer_hash_key
---------------+-----------+----------------------------------
 crm           | 1001      | 47a1467e4e1849fa729b3b20afcf88e3
 billing       | 1001      | 8c3fe64b0d1224ea2795e8a15c8e87e1

The separator matters: without it, ('ab', 'c') and ('a', 'bc') hash to the same value.

Trade-offs and pitfalls

• Using the natural key as the fact foreign key works until the first Type 2 change or second source. Retrofitting surrogates later means rewriting every fact table.
• Surrogates with meaning (20261012 for a date is the accepted exception; PUNE-001 for a store is not) leak business rules into keys that then cannot change.
• Regenerating surrogates on a full rebuild with a new sequence breaks every downstream table and saved query that stored them. Keep the key map, or use deterministic hashes.
• Hashing without normalising inputs: '1001', ' 1001' and '1001 ' produce three keys. Trim, case-fold and type-cast first, and include the source system.
• No unknown member: inner joins silently drop facts with a missing dimension, and NULL keys break BI tools’ joins.

Follow-up answers

• Sequence or hash: sequences when one loader owns the dimension and joins must be as small as possible; hashes when many pipelines load in parallel, facts arrive before dimensions, or you load into a lake without a central generator.
• Durable key: an identifier that stays the same for one real-world entity across all its versions and source re-keyings. Type 2 rows share it; queries that want “the customer as they are now” join through it.
• Customer 1001 in two systems: scope the natural key by source, and if they are the same person, resolve them with a matching rule into one durable key (master data management), keeping both source IDs in the map.
• Columnar warehouses: joins on strings are cheaper than they used to be, but history, multiple sources, re-keying and unknown members are design problems, not performance problems, so surrogates still pay for themselves.