Data modeling · Extra High-Value

Primary key of a fact table

Mediumfact primary keygraincomposite key

Interview question

What is the primary key of a fact table?

How to answer

The short answer

A 's 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.

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

Grain: 1 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."