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

Data modeling · Extra High-Value

What is a degenerate dimension?

Mediummodel-26
degenerate dimensionorder_idfacts

Question

What is a degenerate dimension?

Solution

A degenerate dimension is a dimension-like identifier stored on the fact table itself, without a separate dimension table, usually because it has no interesting attributes of its own.

Classic example: order_id or invoice_number on fct_order_item.

fct_order_item
  date_key
  customer_sk
  product_sk
  order_id      <-- degenerate dimension (no dim_order table)
  line_number
  amount

Why not make dim_order?

If the only column would be order_id, a separate table adds joins with no descriptive value. Keep the id on the fact for drill-to-detail ("show me order 100's lines").

When it stops being degenerate

If orders gain rich attributes (status history, shipping method, salesperson), promote to a real dimension or an accumulating snapshot fact.

Interview tip

> "Degenerate dimension = operational ticket number living on the fact, no dim table."

PreviousNext