What is a Surrogate Key?

How it works

A natural key (business key) is the id the source uses: a customer number, an SKU, an email address. A surrogate key is created by the warehouse. Dimensions need surrogates for several reasons:

1. History. Under SCD Type 2, a customer who moved city twice has three rows that share the natural key; each version needs its own key, and facts point at the version that was current when they happened.
2. Several sources. The CRM and the billing system may both have a customer 1001 who are different people; the natural key becomes the pair (source system, source id).
3. Source changes. Systems get migrated and re-keyed, ids get recycled. With surrogates, only the mapping changes; facts do not.
4. Special members. Unknown and not-applicable rows need keys (often -1 and -2) so facts never carry NULL foreign keys.
5. Smaller fact rows: a 4- or 8-byte integer instead of a long string, although columnar compression narrows this gap.

The natural key still stays in the dimension: loads use it to find the existing row, and analysts search by it.

Surrogates are generated either by a sequence (small, needs one central generator) or by hashing the source system and natural key (larger, but any parallel job can compute the same value without a lookup). Classic Kimball warehouses use integers; Data Vault and many lakehouse pipelines use hash keys.

Example

Two versions of one customer share the natural key but get different surrogate keys from an identity column (PostgreSQL 16).

CREATE TABLE dim_customer (
  customer_key int GENERATED ALWAYS AS IDENTITY PRIMARY KEY,
  customer_id  text NOT NULL,
  city         text,
  valid_from   date,
  is_current   boolean
);
INSERT INTO dim_customer (customer_id, city, valid_from, is_current) VALUES
  ('C1', 'Pune',   '2025-01-01', false),
  ('C1', 'Mumbai', '2026-03-10', true),
  ('C2', 'Leeds',  '2025-06-01', true);

SELECT customer_key, customer_id, city, is_current FROM dim_customer ORDER BY customer_key;

 customer_key | customer_id |  city  | is_current
--------------+-------------+--------+------------
            1 | C1          | Pune   | f
            2 | C1          | Mumbai | t
            3 | C2          | Leeds  | t

Common mistakes

• Using the source id as the dimension’s primary key, which makes Type 2 history impossible.
• Generating surrogates in each pipeline run with a non-deterministic method, so a rebuild changes every key and breaks facts.
• Hashing the natural key alone for a Type 2 dimension, which gives every version the same key; include the version start or keep a separate version key.
• Dropping the natural key from the dimension, so loads cannot find existing rows.