Home / Learn / Data & AI / Data modelling / Lesson 4.1
All lessons

Module 4: Dimension tables · Lesson 4.1 · about 25 min

Surrogate keys: the warehouse's own row numbers

Why a warehouse gives every dimension row its own meaningless number, how to make one with ROW_NUMBER, and how an "unknown" row numbered -1 keeps guest orders from vanishing.

Key points, meanings and formulas on one page: choose "Save as PDF" in the print box.

Module 4 · about 25 minutes · read first: 2.4. You’ll be able to explain why a warehouse uses its own keys, make them with ROW_NUMBER, add an “unknown” member, and load a fact table that never loses a row.

A warehouse gives every dimension row its own meaningless whole-number key, so facts never depend on ids that another system can reuse, clash or leave blank.

In everyday terms: a cloakroom ticket. The attendant does not file your coat under your name (two people may share it, and some give none). You get ticket 57, which means nothing except “this coat”, and it never gets mixed up.

1. Why this matters

Every star schema you meet at work joins facts to dimensions on columns called customer_key, product_key or date_key, not on the ids the shop’s system uses. People new to BI often ask why. The answer is that source ids are owned by someone else: they get reused, they clash when two systems are merged, and they are missing for guests. A warehouse that leans on them breaks quietly. Surrogate keys are the cheap fix, and every later lesson (history, late data, conformed dimensions) depends on them.

2. Natural keys and surrogate keys

A natural key (also called a business key) is the id a row has in the source system: customers.customer_id, a product’s SKU, an employee number. It means something to the business and people type it into searches.

A surrogate key is a whole number the warehouse makes up for each dimension row. It means nothing at all. Its only job is to be unique and never change. Fact tables store the surrogate key; the natural key stays in the dimension as an ordinary column so people can still look a customer up.

Source: orders

  • customer_id 17
  • customer_id 142
  • customer_id 601
  • no customer (guest)

Warehouse: dim_customer

customer_keyname
17Uma Novak
142Yara Garcia
601Uma Novak (signed up again)
-1Guest / unknown
The fact table stores only customer_key. Every source id maps to exactly one key, and orders with no customer map to the special row -1, so no sale is ever dropped by a join. (Keys here equal the source ids because they were numbered in id order; they need not.)

Why not just use the natural key?

  • Two systems, one id. Northbay’s shop numbers customers from 1. If it buys a web shop that also numbers customers from 1, “customer 1” means two people.
  • Missing values. 482 Northbay orders were placed by guests, with customer_id NULL. NULL can’t be a key: it matches nothing in a join.
  • History. When a customer moves city and you keep both versions (type 2, Module 5), one customer_id has two rows. Each row needs its own key.
  • Speed and size. A small integer joins faster than a long text code, and fact tables have millions of rows.

Here is the clash in miniature. Two systems each have a customer 1 and a customer 2:

-- try
SELECT 'shop' AS source, customer_id, first_name FROM customers WHERE customer_id IN (1, 2)
UNION ALL
SELECT 'web', 1, 'Tess'
UNION ALL
SELECT 'web', 2, 'Omar'

customer_id alone can’t tell Hiro from Tess. The pair (source, customer_id) can, and the warehouse then gives each pair one simple number.

3. Making keys with ROW_NUMBER

ROW_NUMBER() OVER (ORDER BY ...) numbers rows 1, 2, 3 in the order you choose. That is all a surrogate key needs. Press Run, then Show how it runs, and notice that the key has nothing to do with the id: sorting by name gives Eli key 1.

-- try
SELECT ROW_NUMBER() OVER (ORDER BY name) AS customer_key,
       id AS customer_id,
       name
FROM mini_customers

Now build the real customer dimension. We number in customer_id order (so the keys happen to match the ids, which makes checking easy), tidy the stray spaces in city, and then add one extra row: the unknown member, key -1, for every fact that has no customer.

-- noviz
DROP TABLE IF EXISTS dim_customer;
CREATE TABLE dim_customer AS
SELECT ROW_NUMBER() OVER (ORDER BY customer_id) AS customer_key,
       customer_id,
       first_name || ' ' || last_name AS customer_name,
       TRIM(city) AS city,
       loyalty_tier
FROM customers;

INSERT INTO dim_customer VALUES (-1, NULL, 'Guest / unknown', 'Unknown', 'Unknown');

SELECT * FROM dim_customer ORDER BY customer_key LIMIT 4;

The table has 604 rows: 603 customers plus the unknown member. Why -1? Any number the key generator will never hand out works; -1 is the common choice because it sorts first and is easy to spot. Some teams add more: -2 “not applicable”, -3 “not yet loaded” (Lesson 5.3).

In a real warehouse the database hands out keys for you: IDENTITY(1,1) in SQL Server, GENERATED ALWAYS AS IDENTITY in PostgreSQL, INTEGER PRIMARY KEY in SQLite. ROW_NUMBER is how you do it by hand, or for a first load.

4. Looking the key up in the fact load

The fact load swaps each natural key for its surrogate key. Use a LEFT JOIN, so that rows with no match survive, and COALESCE(..., -1) to send them to the unknown member:

-- noviz
DROP TABLE IF EXISTS fact_sales;
CREATE TABLE fact_sales AS
SELECT oi.order_id,
       o.order_date,
       COALESCE(dc.customer_key, -1) AS customer_key,
       oi.product_id,
       oi.quantity,
       ROUND(oi.quantity * oi.unit_price * (1 - oi.discount), 2) AS sales_amount
FROM order_items oi
JOIN orders o ON o.order_id = oi.order_id
LEFT JOIN dim_customer dc ON dc.customer_id = o.customer_id;

SELECT COUNT(*) AS fact_rows FROM fact_sales;

7,310 rows, one per order line, exactly as many as order_items. Notice that the unknown row’s own customer_id is NULL, so guests do not match it in the join (NULL = NULL is not true). COALESCE is what sends them there.

Now any report can inner join safely. Check where the rows went:

-- try
SELECT d.customer_name = 'Guest / unknown' AS is_guest,
       COUNT(*) AS lines,
       ROUND(SUM(f.sales_amount), 2) AS sales
FROM fact_sales f
JOIN dim_customer d ON d.customer_key = f.customer_key
GROUP BY is_guest

6,436 lines ($114,066.30) belong to known customers and 874 lines ($15,710.55) to guests. Together that is all 7,310 lines and $129,776.85. Compare a report built on natural keys, which joins orders to customers directly:

-- try
SELECT COUNT(*) AS lines_kept
FROM order_items oi
JOIN orders o ON o.order_id = oi.order_id
JOIN customers c ON c.customer_id = o.customer_id

Only 6,436 lines. The 874 guest lines simply vanished, and nobody got an error. That silent loss is the main thing the unknown member protects you from.

Natural key (business key)
The id a row has in the source system, such as customer_id or a SKU.
Surrogate key
A whole number the warehouse makes up for each dimension row; it means nothing and never changes.
Unknown member
A dimension row with a reserved key (here −1) for facts with no or an unmatched natural key.
Key lookup
The fact load swapping each natural key for its surrogate key.
Physical meaning
The dimension load numbers each row (ROW_NUMBER or IDENTITY) and keeps the natural key as an ordinary column. The fact load LEFT JOINs on the natural key and writes the surrogate, with COALESCE(..., −1) for anything unmatched, so no fact row is lost.

“Just join facts to customers on customer_id; it is already unique.” Guests have NULL, which matches nothing, so their 874 lines vanish without an error. Two merged systems can also both have a customer 1. The surrogate key and unknown member fix both.

Watch it happen, one step at a time (and press Show how it runs on any live SQL box in this lesson to animate the real query):

Source lines.
linecustomer_id
110
2NULL
311
dim_customer.
customer_keycustomer_idname
-1NULLGuest / unknown
110Uma
211Ben
LEFT JOIN on the natural key.
linecustomer_key
11
2NULL
32
COALESCE to −1.
linecustomer_key
11
2-1
32
3 rows in, 3 out.

Try one with fresh numbers:

Maths and principle

Principle: a key is a function that is injective (one-to-one): different rows always get different keys. A surrogate key adds a second rule: the key is chosen by the warehouse and is never reused or changed.

Equation: let N be the set of natural keys plus one extra value ⊥ for “no customer”. The load builds a map k: N → ℤ with k(⊥) = −1, and k(a) ≠ k(b) whenever a ≠ b. Because k is defined for every input, including ⊥, the fact-to-dimension join is a total function: each fact row has exactly one partner. An inner join then keeps |fact| rows: 7,310 in, 7,310 out.

Analogy: a coat check. You hand in any coat, with or without a name tag, and get a numbered ticket. The number says nothing about the coat, and two coats never share a ticket. A coat with no name tag still gets a ticket, from the “unlabelled” rail.

Try it: if you deleted the -1 row and inner joined, how many fact rows would survive? (6,436, the known customers’ lines.)

5. Worked example: who are the best customers?

Question: who are Northbay’s top 3 known customers by sales, and how much of all sales came from guests?

  1. Join fact_sales to dim_customer on customer_key.
  2. Leave out key -1 for the top list, since “Guest / unknown” is not a person.
  3. Sum sales_amount per customer.
-- try
SELECT d.customer_name, ROUND(SUM(f.sales_amount), 2) AS sales
FROM fact_sales f
JOIN dim_customer d ON d.customer_key = f.customer_key
WHERE f.customer_key <> -1
GROUP BY d.customer_key, d.customer_name
ORDER BY sales DESC
LIMIT 3

Leila Garcia ($812.51), Quinn Rossi ($768.25) and Nadia Mensah ($726.21). Grouping by customer_key, not by name, matters: two different customers can share a name. Guests bought $15,710.55 of $129,776.85, about 12.1% of sales, which a natural-key report would have hidden.

6. Practice

-- exercise: How many fact rows, and how many different orders, point at the unknown member? Return two numbers.
-- hint: WHERE customer_key = -1, then COUNT(*) and COUNT(DISTINCT order_id).
SELECT COUNT(*) AS lines
FROM fact_sales
-- answer:
SELECT COUNT(*) AS lines, COUNT(DISTINCT order_id) AS orders
FROM fact_sales
WHERE customer_key = -1
-- exercise: Work out the percentage of sales that came from guests, rounded to 1 decimal place. Return one number.
-- hint: SUM(CASE WHEN customer_key = -1 THEN sales_amount END) divided by SUM(sales_amount), times 100.0.
SELECT ROUND(SUM(sales_amount), 1) AS guest_pct
FROM fact_sales
-- answer:
SELECT ROUND(100.0 * SUM(CASE WHEN customer_key = -1 THEN sales_amount END) / SUM(sales_amount), 1) AS guest_pct
FROM fact_sales
-- exercise: Show sales by city from the customer dimension, biggest first, so that guests appear as their own city "Unknown". Show the top 4.
-- hint: Join fact_sales to dim_customer on customer_key, GROUP BY d.city, ORDER BY the total DESC, LIMIT 4.
SELECT d.city, ROUND(SUM(f.sales_amount), 2) AS sales
FROM fact_sales f
JOIN dim_customer d ON d.customer_key = f.customer_key
WHERE f.customer_key <> -1
GROUP BY d.city
-- answer:
SELECT d.city, ROUND(SUM(f.sales_amount), 2) AS sales
FROM fact_sales f
JOIN dim_customer d ON d.customer_key = f.customer_key
GROUP BY d.city
ORDER BY sales DESC
LIMIT 4

Try it on your own data: paste a customer list into [[sql-playground]] and give it keys with ROW_NUMBER() OVER (ORDER BY ...).

  • Natural keys belong to the source: they can be reused, clash between systems or be missing.
  • Surrogate keys are made by the warehouse, mean nothing and never change.
  • Fact tables store surrogate keys; the natural key stays in the dimension.
  • An unknown member (−1) catches facts with no match, so inner joins lose nothing.
  • Make keys with ROW_NUMBER by hand, or IDENTITY in the database.

New words: natural key, business key, surrogate key, unknown member, key lookup.

Check yourself

1What is a surrogate key?
2Guest orders have customer_id NULL. What does the fact load give them?
3Why do slowly changing dimensions (type 2) need surrogate keys?

Sources

Your ticks stay in this browser. Save them as a file on the course home.

Updated 2026-11-22. Northbay Retail is a made-up company; its data is generated in your browser. Queries run on your device and nothing you type is sent anywhere.