---
title: "Degenerate dimension"
description: "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."
canonical: "https://motherduck.com/glossary/degenerate-dimension/"
related:
  - title: "Data types | MotherDuck Docs"
    url: "https://motherduck.com/docs/sql-reference/duckdb-sql-reference/data-types/"
  - title: "Big Data is Dead"
    url: "https://motherduck.com/blog/big-data-is-dead/"
  - title: "MD_DELETE_DIVE | MotherDuck Docs"
    url: "https://motherduck.com/docs/sql-reference/motherduck-sql-reference/dives/md-delete-dive/"
gated_asset:
  title: "Postgres Is Full: A Field Guide to Analytics at Scale"
  url: "https://motherduck.com/lp/postgres-analytics-guide-full/"
---

# 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

```sql
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.