In this post we are going to cover the degenerate dimension: a dimension that has no dimension table at all and lives directly on the fact row.
The most common example is an order number. A sales fact at the grain of one order line carries the order number on every line, but there is no dim_order table. Other examples are invoice numbers, ticket numbers and shipment tracking numbers. They are operational IDs from the source system.
We will see why the order number does not get its own table, and then use it for three jobs: grouping lines back into orders, calculating order-level KPIs, and tracing a fact back to the source system.
Question 25 of the Real DWH Question and Answers series on TechBrothersIT.
The interview answer
A degenerate dimension is a dimension attribute stored directly in the fact table, with no dimension table of its own. It is usually an operational ID like an order number, invoice number or ticket number. It's used to group line items back into their transaction and to trace facts to the source.
Why the order number has no table of its own
Think about what an order header contains: the order number, the order date, the customer and the store. In a star schema, each of those moves out to its own place.
- Order date goes to
dim_date. - Customer goes to
dim_customer. - Store goes to
dim_store. - Order number is all that is left, and it stays on the fact row.
After everything else has moved out, there is nothing left to describe the order. No name, no category. A dimension table holding only order numbers would have almost as many rows as the fact table, and joining to it would add nothing. That is what degenerate means here: a dimension reduced to just its key.
Let's create some sample data
We need one table: fact_sales at the grain of one order line, with order_number as the degenerate dimension.
DROP TABLE IF EXISTS fact_sales;
-- grain: one row per order line
CREATE TABLE fact_sales (
order_number VARCHAR(10), -- degenerate dimension: no dimension table
line_no INT,
date_key INT,
customer_key INT,
product VARCHAR(50),
quantity INT,
amount DECIMAL(10,2),
PRIMARY KEY (order_number, line_no)
);
INSERT INTO fact_sales VALUES
('SO-1001', 1, 20260105, 1, 'Laptop Stand', 1, 35.00),
('SO-1001', 2, 20260105, 1, 'USB-C Cable', 2, 18.00),
('SO-1002', 1, 20260109, 2, 'Wireless Mouse', 1, 25.00),
('SO-1003', 1, 20260202, 1, 'Desk Lamp', 1, 30.00),
('SO-1003', 2, 20260202, 1, 'USB-C Cable', 1, 9.00),
('SO-1003', 3, 20260202, 1, 'Laptop Stand', 1, 35.00);Let's look at the fact table.
SELECT * FROM fact_sales ORDER BY 1;| order_number | line_no | date_key | customer_key | product | quantity | amount |
|---|---|---|---|---|---|---|
| SO-1001 | 1 | 20260105 | 1 | Laptop Stand | 1 | 35.00 |
| SO-1001 | 2 | 20260105 | 1 | USB-C Cable | 2 | 18.00 |
| SO-1002 | 1 | 20260109 | 2 | Wireless Mouse | 1 | 25.00 |
| SO-1003 | 1 | 20260202 | 1 | Desk Lamp | 1 | 30.00 |
| SO-1003 | 2 | 20260202 | 1 | USB-C Cable | 1 | 9.00 |
| SO-1003 | 3 | 20260202 | 1 | Laptop Stand | 1 | 35.00 |
Six order lines from three orders. SO-1001 has two lines, SO-1002 has one and SO-1003 has three. Order number plus line number is the primary key. In a model diagram, a degenerate dimension is often marked DD.
Step 1: Group order lines back into orders
The first job of the order number is to rebuild each order from its lines. Let's group by order_number and count the lines and add up the amount.
SELECT order_number,
COUNT(*) AS lines,
SUM(amount) AS order_total
FROM fact_sales
GROUP BY order_number
ORDER BY order_number;| order_number | lines | order_total |
|---|---|---|
| SO-1001 | 2 | 53.00 |
| SO-1002 | 1 | 25.00 |
| SO-1003 | 3 | 74.00 |
SO-1001 has 2 lines totalling 53.00, SO-1002 has 1 line for 25.00, and SO-1003 has 3 lines totalling 74.00. No join was needed, the order number on every line is enough.
Step 2: Order-level KPIs
The second job is order-level KPIs. How many orders did we have? COUNT(*) would count lines, not orders. Let's use COUNT(DISTINCT order_number) instead and calculate the average basket from it.
SELECT COUNT(DISTINCT order_number) AS orders,
COUNT(*) AS order_lines,
ROUND(SUM(amount) / COUNT(DISTINCT order_number), 2)
AS avg_basket
FROM fact_sales;| orders | order_lines | avg_basket |
|---|---|---|
| 3 | 6 | 50.67 |
We have 3 orders from 6 order lines, and the average basket is 50.67. If we had divided by the number of lines, the average would be wrong.
Step 3: Trace a fact back to the source system
The third job is tracing. A customer calls support about order SO-1003. The same number exists in the shop system, so let's look it up in the warehouse.
SELECT line_no, product, amount
FROM fact_sales
WHERE order_number = 'SO-1003'
ORDER BY line_no;| line_no | product | amount |
|---|---|---|
| 1 | Desk Lamp | 30.00 |
| 2 | USB-C Cable | 9.00 |
| 3 | Laptop Stand | 35.00 |
The three lines are a Desk Lamp for 30.00, a USB-C Cable for 9.00 and a Laptop Stand for 35.00. Analytics and support are now looking at exactly the same order, with the same number.
When it should NOT be degenerate
So should every ID stay on the fact? No. It depends on what else belongs to it.
- Order flags like channel, payment method or status should not repeat as text on every line. Low-cardinality flags like these go into a junk dimension, which is the next post.
- Order measures like a shipping fee or an order discount belong in a fact table at order grain, not repeated on every line.
- Just the ID with nothing to describe stays as a degenerate dimension on the fact.
An ID with nothing to describe stays degenerate. An ID with real attributes gets a dimension.
Follow-up questions interviewers ask
Why not create dim_order?
After date, customer and store move to their own dimensions, only the number is left; a one-column dimension as large as the fact adds nothing.
How do you count orders in a line-grain fact?
COUNT(DISTINCT order_number).
Is a degenerate dimension a foreign key?
No, it references no table.
The full script
Here is everything from this post in one script. Copy it and run it top to bottom.
-- ========================================================================
-- Data Warehouse Interview Q&A #25 - What is a degenerate dimension?
-- TechBrothersIT. Sample data is invented for teaching.
-- Standard SQL, tested on PostgreSQL 16. Runs top to bottom in one go.
-- Run it in an empty database or schema: other videos reuse table names
-- like dim_customer, and their leftover tables can block the DROPs below.
-- PostgreSQL example: CREATE SCHEMA dwh_q25; SET search_path TO dwh_q25;
-- ========================================================================
-- Step 0: create and load the sample data
DROP TABLE IF EXISTS fact_sales;
-- grain: one row per order line
CREATE TABLE fact_sales (
order_number VARCHAR(10), -- degenerate dimension: no dimension table
line_no INT,
date_key INT,
customer_key INT,
product VARCHAR(50),
quantity INT,
amount DECIMAL(10,2),
PRIMARY KEY (order_number, line_no)
);
INSERT INTO fact_sales VALUES
('SO-1001', 1, 20260105, 1, 'Laptop Stand', 1, 35.00),
('SO-1001', 2, 20260105, 1, 'USB-C Cable', 2, 18.00),
('SO-1002', 1, 20260109, 2, 'Wireless Mouse', 1, 25.00),
('SO-1003', 1, 20260202, 1, 'Desk Lamp', 1, 30.00),
('SO-1003', 2, 20260202, 1, 'USB-C Cable', 1, 9.00),
('SO-1003', 3, 20260202, 1, 'Laptop Stand', 1, 35.00);
-- Step 1: Group order lines back into orders
SELECT order_number,
COUNT(*) AS lines,
SUM(amount) AS order_total
FROM fact_sales
GROUP BY order_number
ORDER BY order_number;
-- Step 2: Order-level KPIs: number of orders and average basket
SELECT COUNT(DISTINCT order_number) AS orders,
COUNT(*) AS order_lines,
ROUND(SUM(amount) / COUNT(DISTINCT order_number), 2)
AS avg_basket
FROM fact_sales;
-- Step 3: Trace a fact back to the source system
SELECT line_no, product, amount
FROM fact_sales
WHERE order_number = 'SO-1003'
ORDER BY line_no;
Key points
- A dimension attribute stored on the fact, with no table
- Usually an operational ID: order, invoice, ticket number
- Groups lines back into transactions; COUNT(DISTINCT) counts orders
- Traces facts back to the source system
- IDs with real attributes need a junk or proper dimension
Question 26 — What is a junk dimension? What is a junk dimension?
Every query and every result on this page was run on PostgreSQL 16 using the script above. The SQL is standard and runs on most databases with small changes (noted in the script). All tables and data are invented demo data for teaching.
No comments:
Post a Comment
Note: Only a member of this blog may post a comment.