Degenerate dimension
A degenerate dimension is a dimension attribute — like an invoice or order number — that's stored directly as a column in the fact table, with no corresponding dimension table, because it has no other descriptive attributes.
Overview
Most dimension-like values get their own dimension table so you can attach descriptive attributes and join in additional context. A degenerate dimension is the exception: it's a value that behaves like a dimension for grouping and filtering purposes, but has no attributes of its own worth modeling separately — so it's kept as a plain column directly on the fact table instead of being split into a dim_* table with a single-column key.
The textbook example is an invoice_number or order_number on a transaction fact table. It's useful for grouping line items into an order and for looking up a specific transaction, but there's no separate "invoice dimension" with descriptive attributes to hang off it — everything interesting about the invoice (its date, customer, store) already lives in other, proper dimensions.
Example
Copy code
CREATE TABLE fact_order_lines (
order_number VARCHAR, -- degenerate dimension: no dim_order table
line_number INTEGER,
customer_key INTEGER REFERENCES dim_customer(customer_key),
product_key INTEGER REFERENCES dim_product(product_key),
date_key INTEGER REFERENCES dim_date(date_key),
quantity INTEGER,
revenue DECIMAL(10,2),
PRIMARY KEY (order_number, line_number)
);
-- order_number still supports grouping and counting, just like a dimension would
SELECT order_number, COUNT(*) AS line_items, SUM(revenue) AS order_total
FROM fact_order_lines
GROUP BY ALL;
Keeping order_number in the fact table avoids creating a near-useless dimension table that would just be a list of order numbers with a surrogate key and nothing else — extra storage and an extra join for no descriptive benefit.
DuckDB angle
There's no special handling needed for degenerate dimensions in DuckDB — they're ordinary fact table columns — but because DuckDB compresses repeated string/identifier columns well in its columnar storage, keeping something like order_number directly on a large fact table is cheap, and GROUP BY ALL on it works exactly like grouping by any other dimension key.
Related terms
A dimension table stores the descriptive, mostly-textual attributes — like customer name, product category, or region — that you use to filter, group, and label the measures in a fact table.
Junk dimension →A junk dimension is a single dimension table that groups together several low-cardinality, otherwise-unrelated flags and indicators, to avoid cluttering a fact table with many tiny separate dimension keys.
Conformed dimension →A conformed dimension is a dimension table — like a shared date or customer dimension — that is defined identically and reused across multiple fact tables or data marts, so metrics stay comparable across the warehouse.
Fact table →A fact table is the central table in a dimensional model that stores numeric measures (like revenue or quantity) alongside foreign keys linking to descriptive dimension tables.
Role-playing dimension →A role-playing dimension is a single physical dimension table, most often a date dimension, that is joined into a fact table multiple times to represent different roles, such as order date, ship date, and delivery date.
Star schema →A star schema is a dimensional data warehouse design where a central fact table of numeric measures connects directly to denormalized dimension tables, forming a shape like a star.
FAQS
If an identifier like an order number has no descriptive attributes of its own — nothing else to look up — a separate dimension table adds a join and storage for no benefit. Keeping it directly on the fact table as a degenerate dimension is simpler and just as queryable.
