Data modeling · Keys & Relationships

What is a composite key?

Easycomposite keygrainprimary keyuniqueness

Interview question

What is a composite key?

How to answer

The short answer

A composite key is a primary (or unique) key made of two or more columns together. Alone, each column may repeat; together they identify 1 row.

Output
order_items grain: 1 row per order line
composite key: (order_id, line_number)

order_id | line_number | sku
100      | 1           | A
100      | 2           | B
101      | 1           | A

order_id alone is not unique (many lines). line_number alone is not unique across orders. Together they are.

Where you see them

  • Fact grain keys: (date_key, store_sk, product_sk) for a daily inventory snapshot
  • Bridge tables: (customer_sk, account_sk)
  • Staging uniqueness tests in dbt: unique on multiple columns

Surrogate alternative

Some teams hash the composite into one order_item_sk for simpler joins, but the business grain is still composite underneath.

Interview tip

Tie composite keys to grain: "The uniqueness test is exactly the grain declaration."