Surrogate Keys

Concepts covered: Surrogate Keys, Key Generation

A surrogate key is an integer the warehouse assigns to a dimension row at load time. It means nothing to the business, has no relation to any source system, and never changes. The source's customer_id sits beside it as a natural key, kept for traceability and never referenced by a fact table, and fact_sales joins to dim_customer on customer_sk alone.

Where natural keys break

Natural keys come from source systems built for their own needs, and Ridgeline's fail in 3 ways. Emails change: 4 percent of customers update theirs each year, and a fact keyed on email loses its history at every update. SKUs get reused: the supplier retired RL-2208, a 2-person tent, in 2023 and reissued it for wool socks in 2025, so 2 years of tent sales keyed on SKU now join to socks. And a type 2 dimension, which adds a new row for each change to a tracked attribute instead of overwriting the old one, holds several rows per customer_id, which a fact row cannot point at without a date to pick the version.

One key per version

Priya Natarajan moved from Portland to Denver on 14 August 2024. The dimension keeps both versions: the Portland row, customer_sk 3310562, closed on the move date, and the current Denver row, 8827414. A June 2024 sale references 3310562 and an October 2024 sale references 8827414, so revenue by city puts each sale where Priya lived when she bought, with no date logic in the query.

SELECT c.city, SUM(f.line_amount) AS revenue
FROM fact_sales f
JOIN dim_customer c ON c.customer_sk = f.customer_sk
GROUP BY c.city;

SELECT c.city, SUM(f.line_amount) AS revenue
FROM fact_sales f
JOIN dim_customer c
  ON c.customer_id = f.customer_id
 AND f.sale_date >= c.valid_from
 AND f.sale_date <  c.valid_to
GROUP BY c.city;

In the second query the engine hashes on customer_id, then checks each fact row against every version of that customer with a residual range filter, which costs more than a single equality probe. It is also fragile: a sale stamped at exactly valid_to matches 2 versions or none depending on which boundary convention the dimension and the query assumed. The surrogate key resolved the version once, at load time, and stored the answer in the fact row.

Generating the key

The load assigns the key. A customer_id the dimension has never seen gets a new row and the next value from a sequence. A change to a tracked attribute closes the current row and inserts a new one with a new key. The alternative to a sequence is a hash, such as the MD5 of customer_id and valid_from, which produces the same key on every rerun of the same input, so a failed load replays without consulting the previous run. A row with key -1 stands for 'unknown customer', so a fact whose customer has not arrived yet still joins and its amount stays in the totals.

A BIGINT key is 8 bytes and a UUID string is 36, a difference of 28 bytes per row for each foreign key column, and fact_sales carries 5 of them. dim_date is the conventional exception to meaningless keys: its key is the date written as 20260902, because the table is never versioned and a readable key makes partition filters legible.

• 23.5 GB

The payoff of keys the warehouse owns shows up the day a second source system arrives with its own numbering.

Ridgeline acquires a competitor whose system numbers customers from 1, and 410,000 of its customer_id values collide with Ridgeline's. Both lists load into dim_customer. What keeps the fact tables correct?

• Add 10,000,000 to every acquired customer_id so the values no longer collide.
• Key the dimension on email address, which both systems hold.
• Keep the surrogate keys as they are and widen the natural key to (source_system, customer_id).
• Reassign every surrogate key from 1 across the merged customer list.

Do

• Assign surrogate keys in the load, from a sequence or a deterministic hash
• Keep the natural key beside the surrogate key to trace rows to the source
• Give each dimension version its own key so facts join on equality alone
• Carry a -1 'unknown' row so facts with a late dimension still join and count

Avoid

• Reassign or renumber surrogate keys once facts reference them
• Join facts to dimensions on a natural key the source can change or reuse
• Use UUID strings where an 8-byte integer key will do
• Offset colliding source ids instead of widening the natural key with source_system

About this lesson

This section is part of the Star Schemas lesson on DataDriven, the home of data engineering. Lessons cover SQL, Python, Spark, data modeling and pipeline architecture, and each section pairs its explanation with worked examples and practice you run as you go.