Skip to content
LakeBench
ProblemsCommunityPricing
Sign inStart practicing
Back
  1. Home
  2. Interview prep
  3. Primary key of a fact table

Data modeling · Extra High-Value

Primary key of a fact table

Mediummodel-32
fact primary keygraincomposite key

Question

What is the primary key of a fact table?

Solution

A fact table's primary key is usually a composite of the grain columns: the set of keys that uniquely identify one fact row. Sometimes teams add a single surrogate fact key, but the logical PK is still the grain.

Grain: one row per order line
Logical PK: (order_id, line_number)
  or (order_sk, line_number) if orders are dimensioned

Grain: one row per product per warehouse per day
Logical PK: (date_key, product_sk, warehouse_sk)

What is NOT enough

  • Only date_key (many facts per day)
  • Only customer_sk (many purchases)
  • A random surrogate alone without documenting grain (hides duplicates)

Practice

Declare the grain, then put a uniqueness test on exactly those columns. That test *is* the primary key contract in many warehouses.

Interview tip

> "Fact PK = the grain key. Say the grain sentence, then list the columns that must be unique."

PreviousNext