Skip to content
Back to challenges
Dimensional Modeling FoundationsConcept Challengeintermediate20 min

Degenerate dimensions

Design an order-line fact that keeps the transaction identifier on the fact instead of inventing a low-value order dimension.

How this preview works
Preview this problem before signing in. Sign in to save progress and submit work.

Concept

degenerate-dimensions

The primary modeling idea this problem reinforces.

Requirements

3

Business needs the model must satisfy.

Scenario

A commerce team needs order-line revenue analysis by product and customer. The source system provides an order number shared by multiple line items, but that identifier has no descriptive attributes beyond the transaction itself.

Why this matters

Degenerate dimensions are a small pattern with big interview signal. They test whether a modeler understands fact grain well enough to keep transaction identifiers queryable without adding unnecessary joins.

Requirements
  • Create an order-line fact table with one row per purchased line item.
  • Keep the order number or order id on the fact as a degenerate dimension.
  • Join product and customer dimensions through explicit surrogate-key foreign keys.
Read-only starter workspace
Inspect the authored starting ERD, then open the workspace to edit and run the checks. Saving your progress needs a free account.

Template

Order-line grain starter workspace

The model users start from in the full workspace.

Entities

2

Seeded tables visible in the public preview.

Dialect

postgresql

Generated SQL target for this starter model.

Mini Map

This preview is read-only. Open the workspace to edit and verify; saving needs a free account.

Open workspace

Try the question first.

The discussion has other people's approaches and solutions. Give it a real attempt before you read them.