Data modeling · Extra High-Value

What is a natural key?

Easynatural keybusiness keysurrogate key

Interview question

What is a natural key?

How to answer

The short answer

A (also called a business key) is an identifier that already exists in the real world or source system: sku, email, order_id, ssn (sensitive), ISBN.

Output
dim_product
  product_sk = 55        <-- surrogate
  sku        = 'SKU-44'  <-- natural key

Properties

  • Meaningful to the business
  • Comes from source systems
  • May change, collide across systems, or be reused (painful)
  • Used to match incoming records to dimension rows during

Contrast with surrogate

Warehouses still keep natural keys on dimensions for lineage and debugging, but facts usually join on surrogates, especially with SCD2.

Interview tip

> "Natural key = business identifier from the source. Surrogate = warehouse-generated identity."