DWH Interview Question 19 - What is a surrogate key, and why do warehouses use them?

Data Warehouse Interview Q&A · Question 19

In this post we are going to cover the surrogate key: what it is, and why almost every data warehouse uses them for dimensions.

A surrogate key is a meaningless number generated by the warehouse, like 1, 2, 3, used as the primary key of a dimension. It replaces the natural key, the ID that comes from the source system, as the key that fact tables point to. The natural key is still kept in the dimension, just as a normal column.

We will load customers from two source systems whose IDs clash, keep history when a customer moves, and see how facts and reports stay correct because of the surrogate key.

Question 19 of the Real DWH Question and Answers series on TechBrothersIT.

On this page
  1. The interview answer
  2. Let's create some sample data
  3. Step 1: Generate surrogate keys while integrating two sources
  4. Step 2: Keep history - Sara moves
  5. Step 3: Facts point to the version that was true at the time
  6. Step 4: Reports keep the true history
  7. More reasons to use surrogate keys
  8. How surrogate keys are generated
  9. Follow-up questions interviewers ask
  10. The full script

The interview answer

Say this first

A surrogate key is a meaningless, system-generated integer used as the primary key of a dimension, instead of the source system's natural key. Warehouses use them to integrate sources whose IDs clash, to keep history (several rows per customer), to stay safe when source IDs change, for unknown members, and for smaller, faster joins.

Let's create some sample data

We have two source systems, the website and the in-store system, and both use the ID C001. We also create an empty dim_customer, with a surrogate key, the source, the natural key and validity dates, and an empty fact_sales.

Sample data — run this first
DROP TABLE IF EXISTS fact_sales;
DROP TABLE IF EXISTS dim_customer;
DROP TABLE IF EXISTS src_web_customers;
DROP TABLE IF EXISTS src_store_customers;

-- two source systems that BOTH use 'C001'
CREATE TABLE src_web_customers   (cust_id VARCHAR(10), name VARCHAR(50), city VARCHAR(50));
CREATE TABLE src_store_customers (cust_id VARCHAR(10), name VARCHAR(50), city VARCHAR(50));
INSERT INTO src_web_customers VALUES ('C001', 'Sara Khan', 'Toronto'), ('C002', 'Omar Ali', 'Dallas');
INSERT INTO src_store_customers VALUES ('C001', 'Ravi Mehta', 'Vancouver');

CREATE TABLE dim_customer (
  customer_key INT PRIMARY KEY,   -- surrogate key: meaningless, generated by the warehouse
  source       VARCHAR(10),       -- which system the row came from
  natural_key  VARCHAR(10),       -- the source system's own ID
  name         VARCHAR(50),
  city         VARCHAR(50),
  valid_from   DATE,
  valid_to     DATE
);
CREATE TABLE fact_sales (
  sale_date    DATE,
  customer_key INT REFERENCES dim_customer(customer_key),
  amount       DECIMAL(10,2)
);

Let's look at the two sources.

Query
SELECT * FROM src_web_customers ORDER BY 1;
Result2 rows · 3 columns
cust_idnamecity
C001Sara KhanToronto
C002Omar AliDallas

On the website, C001 is Sara Khan.

Query
SELECT * FROM src_store_customers ORDER BY 1;
Result1 row · 3 columns
cust_idnamecity
C001Ravi MehtaVancouver

In the store system, C001 is Ravi Mehta, a completely different person. If the warehouse used the source ID as its key, Sara and Ravi would collide and their sales would be mixed together.

Step 1: Generate surrogate keys while integrating two sources

Now let's load both sources into the customer dimension. While loading, the warehouse generates its own key with ROW_NUMBER(), and we keep the source and the natural key as normal columns so every row can be traced back.

Statement
INSERT INTO dim_customer
SELECT ROW_NUMBER() OVER (ORDER BY source, cust_id),
       source, cust_id, name, city,
       '2026-01-01', '9999-12-31'
FROM (SELECT 'WEB' AS source, cust_id, name, city
      FROM src_web_customers
      UNION ALL
      SELECT 'STORE', cust_id, name, city
      FROM src_store_customers) s;

Now let's check the result.

Query
SELECT * FROM dim_customer ORDER BY customer_key;
Result3 rows · 7 columns
customer_keysourcenatural_keynamecityvalid_fromvalid_to
1STOREC001Ravi MehtaVancouver2026-01-019999-12-31
2WEBC001Sara KhanToronto2026-01-019999-12-31
3WEBC002Omar AliDallas2026-01-019999-12-31

Ravi (STORE C001) gets key 1 and Sara (WEB C001) gets key 2. Omar is key 3. The natural key C001 appears twice, but there is no collision, because each real customer has their own surrogate key.

Step 2: Keep history - Sara moves

This is the most important reason. In March, Sara moves from Toronto to Ottawa, and we want to keep both facts: she lived in Toronto until February and in Ottawa after. So let's close her old row and add a new row with a new surrogate key.

Statement
UPDATE dim_customer SET valid_to = '2026-02-28'
WHERE source = 'WEB' AND natural_key = 'C001';
INSERT INTO dim_customer VALUES
  (4, 'WEB', 'C001', 'Sara Khan', 'Ottawa',
   '2026-03-01', '9999-12-31');

Now let's check the result.

Query
SELECT * FROM dim_customer ORDER BY customer_key;
Result4 rows · 7 columns
customer_keysourcenatural_keynamecityvalid_fromvalid_to
1STOREC001Ravi MehtaVancouver2026-01-019999-12-31
2WEBC001Sara KhanToronto2026-01-012026-02-28
3WEBC002Omar AliDallas2026-01-019999-12-31
4WEBC001Sara KhanOttawa2026-03-019999-12-31

Sara's old row, key 2, now ends on 2026-02-28. Her new row, key 4, starts on 2026-03-01 with city Ottawa. Her natural key C001 from the website now appears on two rows, and only the surrogate key (2 vs 4) tells the versions apart.

Step 3: Facts point to the version that was true at the time

Now let's load three sales. Each sale stores the surrogate key of the customer version that was true on the sale date. In a real pipeline this key is found by a lookup during the load.

Statement
INSERT INTO fact_sales VALUES
  ('2026-01-15', 2, 35.00),   -- Sara in Toronto
  ('2026-03-10', 4, 25.00),   -- Sara in Ottawa
  ('2026-01-20', 1, 30.00);   -- Ravi

The January sale for Sara points to key 2 (Toronto), the March sale points to key 4 (Ottawa), and Ravi's sale points to key 1.

Step 4: Reports keep the true history

Let's report sales by customer and city.

Query
SELECT d.name, d.city, SUM(f.amount) AS sales
FROM fact_sales f
JOIN dim_customer d
  ON d.customer_key = f.customer_key
GROUP BY d.name, d.city
ORDER BY d.name, d.city;
Result3 rows · 3 columns
namecitysales
Ravi MehtaVancouver30.00
Sara KhanOttawa25.00
Sara KhanToronto35.00

Sara Khan shows up twice: 35.00 in Toronto and 25.00 in Ottawa. Ravi Mehta has 30.00 in Vancouver. January sales stay in Toronto and March sales go to Ottawa. This is slowly changing dimension Type 2, and it only works with surrogate keys.

More reasons to use surrogate keys

  • Source IDs change or get reused - migrations, reformatting and recycled IDs do not break the warehouse.
  • Smaller, faster joins - a small integer is much cheaper to store and join than a long text code, and the fact table repeats that key on every row.
  • Unknown members - key -1 can mean unknown or missing, so facts never need a NULL key.

How surrogate keys are generated

MethodHowWatch out
IDENTITY / sequenceDatabase assigns the next numberSyntax differs per database
ROW_NUMBER() + max keyNumber new rows in the loadMust run one load at a time
Hash of natural keyMD5 / SHA of source + IDWider; same hash for every version

Our demo used ROW_NUMBER() on an empty table, so numbering started at 1. In a real incremental load you add the numbers to the current maximum key. Whatever method you choose, always keep the natural key in the dimension too: the surrogate key is for joins, the natural key is for tracing back to the source. And surrogate keys are internal plumbing, so business users filter on names, codes and dates, not on the keys.

Follow-up questions interviewers ask

Surrogate vs natural key?

Natural keys come from the source and can clash, change or be reused; surrogate keys are generated, stable and meaningless.

Do facts store natural keys?

No, they store surrogate keys found by a lookup during the load.

Can you use a hash as a surrogate key?

Yes (common in big data and Data Vault), but it is wider than an integer and is the same for every version of a row.

The full script

Here is everything from this post in one script. Copy it and run it top to bottom.

Full script — copy and run top to bottom
-- ========================================================================
-- Data Warehouse Interview Q&A #19 - What is a surrogate key?
-- TechBrothersIT. Sample data is invented for teaching.
-- Standard SQL, tested on PostgreSQL 16. Runs top to bottom in one go.
-- Run it in an empty database or schema: other videos reuse table names
-- like dim_customer, and their leftover tables can block the DROPs below.
-- PostgreSQL example:  CREATE SCHEMA dwh_q19;  SET search_path TO dwh_q19;
-- ========================================================================

-- Step 0: create and load the sample data
DROP TABLE IF EXISTS fact_sales;
DROP TABLE IF EXISTS dim_customer;
DROP TABLE IF EXISTS src_web_customers;
DROP TABLE IF EXISTS src_store_customers;

-- two source systems that BOTH use 'C001'
CREATE TABLE src_web_customers   (cust_id VARCHAR(10), name VARCHAR(50), city VARCHAR(50));
CREATE TABLE src_store_customers (cust_id VARCHAR(10), name VARCHAR(50), city VARCHAR(50));
INSERT INTO src_web_customers VALUES ('C001', 'Sara Khan', 'Toronto'), ('C002', 'Omar Ali', 'Dallas');
INSERT INTO src_store_customers VALUES ('C001', 'Ravi Mehta', 'Vancouver');

CREATE TABLE dim_customer (
  customer_key INT PRIMARY KEY,   -- surrogate key: meaningless, generated by the warehouse
  source       VARCHAR(10),       -- which system the row came from
  natural_key  VARCHAR(10),       -- the source system's own ID
  name         VARCHAR(50),
  city         VARCHAR(50),
  valid_from   DATE,
  valid_to     DATE
);
CREATE TABLE fact_sales (
  sale_date    DATE,
  customer_key INT REFERENCES dim_customer(customer_key),
  amount       DECIMAL(10,2)
);


-- Step 1: Generate surrogate keys while integrating two sources
INSERT INTO dim_customer
SELECT ROW_NUMBER() OVER (ORDER BY source, cust_id),
       source, cust_id, name, city,
       '2026-01-01', '9999-12-31'
FROM (SELECT 'WEB' AS source, cust_id, name, city
      FROM src_web_customers
      UNION ALL
      SELECT 'STORE', cust_id, name, city
      FROM src_store_customers) s;

-- check the result
SELECT * FROM dim_customer ORDER BY customer_key;

-- Step 2: History: Sara moves, so she gets a NEW row with a NEW surrogate key
UPDATE dim_customer SET valid_to = '2026-02-28'
WHERE source = 'WEB' AND natural_key = 'C001';
INSERT INTO dim_customer VALUES
  (4, 'WEB', 'C001', 'Sara Khan', 'Ottawa',
   '2026-03-01', '9999-12-31');

-- check the result
SELECT * FROM dim_customer ORDER BY customer_key;

-- Step 3: Facts point to the version that was true at the time
INSERT INTO fact_sales VALUES
  ('2026-01-15', 2, 35.00),   -- Sara in Toronto
  ('2026-03-10', 4, 25.00),   -- Sara in Ottawa
  ('2026-01-20', 1, 30.00);   -- Ravi

-- Step 4: Reports keep the true history
SELECT d.name, d.city, SUM(f.amount) AS sales
FROM fact_sales f
JOIN dim_customer d
  ON d.customer_key = f.customer_key
GROUP BY d.name, d.city
ORDER BY d.name, d.city;

Key points

  • A meaningless, generated integer as the dimension key
  • Integrates sources whose IDs clash
  • Makes history possible: several versions per natural key
  • Protects from changing IDs, enables key -1, speeds up joins
  • Always keep the natural key too
Next in this series

Question 20 — The date dimension What is a date dimension, and why not just use date functions?

Every query and every result on this page was run on PostgreSQL 16 using the script above. The SQL is standard and runs on most databases with small changes (noted in the script). All tables and data are invented demo data for teaching.

No comments:

Post a Comment

Note: Only a member of this blog may post a comment.

Subscribe to: Post Comments (Atom)