Skip to content
LakeBench
ProblemsCommunityPricing
Sign inStart practicing
Back
  1. Home
  2. Interview prep
  3. What is a surrogate key?

Data modeling · Keys & Relationships

What is a surrogate key?

Mediummodel-11
surrogate keynatural keySCD2dimensions

Question

What is a surrogate key, and why do warehouses use them?

Solution

A surrogate key is an artificial identifier created by the warehouse (integer sequence, UUID, or hash), not a business key from the source system.

dim_product
  product_sk = 918273   <-- surrogate (warehouse-owned)
  sku        = 'SKU-44' <-- natural / business key
  category   = 'Shoes'

Why they matter

  • Source natural keys can change, recycle, or collide across systems
  • SCD Type 2 needs a new key per version while sku stays the same
  • Facts join on stable warehouse keys, not fragile source strings

Mental model

The business still talks about SKU-44. The warehouse uses product_sk as the internal handle so history and joins stay stable.

Facts store surrogates

fct_order_item.product_sk points at the dimension version that applied at order time.

Interview tip

> "Surrogate keys are warehouse-generated. Natural keys come from the business. SCD2 and multi-source integration are why we prefer surrogates in dims and facts."

PreviousNext