What is a degenerate dimension, and why does an order number stay in the fact row?
answer
- a dimension key with no table
- look at the order header's leftovers
- what columns would that lookup table hold?
- the number stays in the fact row
- distinct count of it gives order counts
basics
~20 sA degenerate dimension is an operational identifier such as an order or invoice number stored directly in the fact table with no dimension table behind it, because every descriptive attribute it would carry already lives in other dimensions.
solid answer
~50 sA degenerate dimension is a dimension key that sits in the fact row with nothing to join to. The classic case is the order number on a sales fact at line-item grain: once the order header has been split into customer, date, channel, store and promotion dimensions, the number itself is all that is left, and a lookup table for it would hold one column and no descriptive attributes. It is still used like a dimension — you group by it to get order-level rollups, run `COUNT(DISTINCT order_number)` to count orders from line rows, and use it to trace a warehouse row back to the source document. Name it `order_number` rather than `order_key` so readers know there is nothing to join, and promote it to a real dimension only if descriptive attributes later attach to it.
go deeper
Be ready to name the pattern and give the canonical example: an order or invoice number stored in the fact row with no dimension table. Say why — there is nothing left to describe.
Explain the mechanics: the header's attributes migrated into other dimensions, so a lookup table would hold only the identifier. Show the three uses — grouping order lines, distinct-counting orders, tracing back to the source document.
Show judgment about when the pattern breaks down. Expect to be asked what you do when order status and order type appear, and to argue for an order-header dimension versus a junk dimension rather than reflexively creating a table.
Own the convention across a shared model: a naming rule that distinguishes degenerate identifiers from surrogate foreign keys, a stance on whether operational identifiers are published to consumers at all, and how lineage and audit needs justify keeping them.
## The pattern In a dimensional model, almost every non-measure column in a fact table is a foreign key pointing at a dimension table that supplies the descriptive context. A degenerate dimension is the deliberate exception: an operational identifier that lives directly in the fact row and has no dimension table at all. Order number, invoice number, ticket number, bill of lading number, transaction id, POS receipt number — the identifiers the source system stamped on the document that the fact rows came from. ## Why it has no table Work through what happens when you build a sales fact at one row per order line. The order header carries a customer, an order date, a sales channel, a promotion, a store, and an order number. The first five all migrate out into dimension tables, because each of them brings a bundle of descriptive attributes with it. After that migration, the only header item left is the number itself. A dimension table for it would have exactly one column — the number — and one row per order. It would be nearly as tall as the fact table, and joining to it would tell you nothing the fact row did not already say. So the would-be dimension "degenerates" to a bare key, and the key stays in the fact table. The value is kept in its natural form: no surrogate key is issued for it, because a surrogate key exists to point at a row of attributes and there are no attributes. ```sql CREATE TABLE fact_sales_line ( date_key INTEGER NOT NULL, customer_key INTEGER NOT NULL, product_key INTEGER NOT NULL, order_number VARCHAR(20) NOT NULL, -- degenerate quantity INTEGER NOT NULL, extended_amount DECIMAL(18,2) NOT NULL ); ``` ## What it is actually used for Three jobs, and they are the reason you must not simply drop the column: 1. **Grouping fact rows back into their document.** All lines of one order share the number, so it lets you compute basket size, lines per order, order-level totals and order-level discount behaviour from a line-grain fact. 2. **Counting.** `COUNT(DISTINCT order_number)` is the only honest way to answer "how many orders?" from a table whose rows are order lines. `COUNT(*)` answers a different question. 3. **Lineage and drill to detail.** Audit, reconciliation against the source system, and customer-service lookups all need the operational number that the human on the phone is reading out. ## Naming and physical treatment By convention it is named for what it is — `order_number`, `invoice_number` — not with the `_key` suffix used for surrogate foreign keys, so a reader of the DDL immediately knows there is no dimension on the other end. It is usually a string and usually high cardinality, and that is expected rather than a defect. Even when it is numeric it is an identifier, never a measure: nothing sensible comes from summing or averaging it. A fact table may carry more than one degenerate dimension. A shipment fact can reasonably hold both the order number and the bill of lading number, because both are operational identifiers that group rows and neither has attributes of its own. ## When it stops being degenerate The moment descriptive attributes attach at the same grain as the identifier, the emptiness argument fails and you have somewhere to put them. An order header that also carries order status, order type, payment terms and priority now has real content. Two reasonable responses: - If the attributes are a handful of low-cardinality flags, sweep them into a junk dimension and leave the number degenerate in the fact row. - If the header has genuine descriptive content with its own history, build an order-header dimension with a surrogate key and the order number as its natural key, and keep the number in the fact row as well if analysts rely on it directly. ## Common confusions A degenerate dimension is not a junk dimension: junk collapses several low-cardinality flags into one small table, whereas degenerate is one high-cardinality identifier with no table. It is also not a natural-key column awaiting a surrogate — there is nothing for a surrogate to point at. ## Pitfalls - Creating a one-column order dimension "for purity": you add a join, store the value twice, and gain nothing. - Dropping the number because "analysts don't slice by an order id": you lose order counts and the audit trail. - Summing or averaging a numeric identifier because it looks like a measure. - Assuming the identifier is unique in the fact table when the grain is finer than one row per document.
- How do you count orders from a fact table whose grain is one row per order line?Use `COUNT(DISTINCT order_number)` on the degenerate dimension; `COUNT(*)` counts lines, not orders. If order counts are queried constantly, an order-header fact at one row per order gives the same number as a plain row count and keeps the distinct-count cost out of everyday reporting, at the price of a second table to load and reconcile.
- When would you promote a degenerate dimension into a real dimension table?When descriptive attributes appear at the same grain as the identifier — order status, order type, payment terms, priority. Then build a dimension keyed by a surrogate with the order number as its natural key, or, if the attributes are just a few low-cardinality flags, put them in a junk dimension and leave the number degenerate.
- Can one fact table hold more than one degenerate dimension?Yes. A shipment fact can carry both the order number and the bill of lading number: each is an operational identifier that groups fact rows and neither brings descriptive attributes with it. There is no rule limiting a fact row to a single degenerate dimension.
saying these in an interview costs you the question
- Says every non-measure fact column must be a foreign key
- Builds a one-column order dimension to keep the star pure
- Confuses a degenerate dimension with a junk dimension
- Treats a numeric order number as a measure and sums it
- Claims a degenerate dimension needs its own surrogate key