Surrogate Keys
Concepts covered: Surrogate Keys
Surrogate keys are system-generated identifiers that replace natural keys in dimensional models. Every dimension row gets its own surrogate key (customer_sk, product_sk, date_sk). Fact tables reference dimensions using these surrogate keys, not the natural business keys.
Why Not Just Use Natural Keys?
Natural keys (email, SKU, employee_id) come from source systems. They have three problems in analytical models: they change (email updates), they get reused (SKU recycled for a new product), and they complicate SCD Type 2 (a customer with 5 historical versions would have 5 rows with the same email, and fact rows need to reference the specific version).
- Surrogate keys are immutable: once assigned, they never change.
- Surrogate keys are unique per version: each SCD2 historical row gets its own SK.
- Surrogate keys are compact: a BIGINT SK is 8 bytes vs a 36-byte UUID natural key.
- Natural keys from the source system can change, get reused, or have different formats across systems.
How Surrogate Keys Enable SCD Type 2
When a customer moves from Portland to Seattle, you create a new dimension row with a new surrogate key. The old row (SK 1001, Portland) stays. The new row (SK 1002, Seattle) is added. Fact rows from before the move reference SK 1001 (Portland). Fact rows after the move reference SK 1002 (Seattle). Revenue is attributed to the correct city at the time of each transaction.
- dim_customer (SCD2): customer_sk PK, customer_id, name, city, effective_from, effective_to, is_current
- fact_orders: order_sk PK, customer_sk FK, date_sk, amount
- dim_customer (SCD2).customer_sk references fact_orders.customer_sk
The fact table stores customer_sk, not customer_id. This is the critical detail. If the fact stored the natural key (customer_id), a point-in-time join would require a date-range filter on the dimension. With the surrogate key, the join is a simple equality: fact.customer_sk = dim.customer_sk. The SK already encodes which version of the customer this fact row relates to.
Surrogate Key Assignment
Surrogate keys are assigned during the ETL process, not by the source system. When a new customer appears, the ETL assigns the next SK. When an existing customer changes (SCD2), the ETL assigns a new SK for the new version. The source system's customer_id is preserved as a natural key column on the dimension for traceability.
8 bytes BIGINT surrogate key: vs 36 bytes for a UUID. On a billion-row fact table, that saves 28 GB per FK column.
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.