In this post we are going to cover dimension tables: what they are, what goes inside one, and what makes a good one.
A dimension table stores the descriptive context of the business: who, what, where and when. Customers, products, stores and dates are all dimensions. Fact tables hold the numbers, and dimensions hold the words we use to filter, group and label those numbers in a report.
We will look inside a product dimension, use it in a report, and then see why a special Unknown row is better than a NULL key in the fact table.
Question 16 of the Real DWH Question and Answers series on TechBrothersIT.
The interview answer
A dimension table stores the descriptive context of the business: who, what, where, when. Each row has a surrogate key, a natural (business) key, and many descriptive attributes, mostly text, used to filter, group and label the facts. Dimensions are wide, denormalized, and much smaller than fact tables.
Inside a dimension
Every dimension row has three kinds of columns:
- Surrogate key - a simple number generated by the warehouse. Fact tables point to this key.
- Natural key - the business key from the source system, like a product code. The load pipeline uses it to match incoming rows to existing ones.
- Descriptive attributes - name, category, subcategory, brand, status and so on. A real product dimension can have fifty or more of these.
Hierarchies are usually flattened: category, subcategory and product all sit on one row. That is deliberate denormalization, so users can drill down without extra joins.
Let's create some sample data
We need a product dimension, dim_product, and a small fact_sales table that points to it through product_key.
DROP TABLE IF EXISTS fact_sales;
DROP TABLE IF EXISTS dim_product;
CREATE TABLE dim_product (
product_key INT PRIMARY KEY, -- surrogate key
product_code VARCHAR(10), -- natural (business) key
product_name VARCHAR(50),
category VARCHAR(50),
subcategory VARCHAR(50),
brand VARCHAR(50),
status VARCHAR(20)
);
INSERT INTO dim_product VALUES
(-1, 'N/A', 'Unknown', 'Unknown', 'Unknown', 'Unknown', 'Unknown'),
( 1, 'P-100', 'Laptop Stand', 'Accessories', 'Stands', 'TB Pro', 'Active'),
( 2, 'P-200', 'USB-C Cable', 'Accessories', 'Cables', 'TB Basics', 'Active'),
( 3, 'P-300', 'Wireless Mouse', 'Electronics', 'Mice', 'TB Pro', 'Active'),
( 4, 'P-400', 'Desk Lamp', 'Home', 'Lighting', 'TB Home', 'Discontinued');
CREATE TABLE fact_sales (
sale_id INT PRIMARY KEY,
product_key INT REFERENCES dim_product(product_key),
amount DECIMAL(10,2)
);
INSERT INTO fact_sales VALUES
(1, 1, 35.00), (2, 2, 18.00), (3, 3, 25.00), (4, 4, 30.00),
(5, 1, 70.00), (6, 3, 25.00), (7, 2, 27.00),
(8, -1, 15.00); -- product code was missing in the source: points to the Unknown rowLet's look at the dimension.
SELECT * FROM dim_product ORDER BY 1;| product_key | product_code | product_name | category | subcategory | brand | status |
|---|---|---|---|---|---|---|
| -1 | N/A | Unknown | Unknown | Unknown | Unknown | Unknown |
| 1 | P-100 | Laptop Stand | Accessories | Stands | TB Pro | Active |
| 2 | P-200 | USB-C Cable | Accessories | Cables | TB Basics | Active |
| 3 | P-300 | Wireless Mouse | Electronics | Mice | TB Pro | Active |
| 4 | P-400 | Desk Lamp | Home | Lighting | TB Home | Discontinued |
product_key is the surrogate key and product_code is the natural key. The values are readable text: "Accessories", not a code, and "Discontinued", not "D". The first row, key -1, is the Unknown member.
SELECT * FROM fact_sales ORDER BY 1;| sale_id | product_key | amount |
|---|---|---|
| 1 | 1 | 35.00 |
| 2 | 2 | 18.00 |
| 3 | 3 | 25.00 |
| 4 | 4 | 30.00 |
| 5 | 1 | 70.00 |
| 6 | 3 | 25.00 |
| 7 | 2 | 27.00 |
| 8 | -1 | 15.00 |
Eight sales. Sale 8 came from the source with a missing product code, so the pipeline pointed it to key -1 instead of storing NULL.
Step 1: Dimensions filter, group and label
Let's see the three jobs a dimension does in a query. We want sales of Accessories, by brand. The category filters the rows, the brand groups them, and the brand names label the result.
SELECT p.brand, SUM(f.amount) AS sales
FROM fact_sales f
JOIN dim_product p
ON p.product_key = f.product_key
WHERE p.category = 'Accessories'
GROUP BY p.brand
ORDER BY sales DESC;| brand | sales |
|---|---|
| TB Pro | 105.00 |
| TB Basics | 45.00 |
TB Pro sold 105.00 and TB Basics 45.00 in Accessories. Everything except the amount came from the dimension: the filter, the grouping and the words in the result. That is why good dimensions use clear, readable text.
Step 2: Why key -1 beats NULL
Now let's group all sales by category with a normal inner join, including sale 8 that has no known product.
SELECT p.category, SUM(f.amount) AS sales
FROM fact_sales f
JOIN dim_product p
ON p.product_key = f.product_key
GROUP BY p.category
ORDER BY sales DESC;| category | sales |
|---|---|
| Accessories | 150.00 |
| Electronics | 50.00 |
| Home | 30.00 |
| Unknown | 15.00 |
The report shows an Unknown category with 15.00, and the four categories add up to 245.00, the true total of all eight sales. If sale 8 had a NULL key, the inner join would drop it, the 15.00 would silently vanish from every report that joins to products, and the totals would not match finance. This is a classic interview point.
What makes a good dimension
Four habits separate a good dimension from a painful one:
- Descriptive text, not codes - "Accessories", not "ACC". Business users should never need a lookup sheet.
- Flattened hierarchies - category, subcategory and product on one row.
- An Unknown member row - key -1 instead of NULL in the fact table.
- Planned history - decide how each attribute changes over time. That is slowly changing dimensions, covered in questions 32 to 35.
Dimension vs fact
| Dimension table | Fact table | |
|---|---|---|
| HOLDS | Descriptions: who, what, where, when | Numbers about business events |
| SHAPE | Wide, relatively short | Narrow, very long |
| CHANGES | Rows change over time (SCD) | Mostly appended |
| KEYS | Surrogate key + natural key | Foreign keys to dimensions |
Facts are mostly appended, while dimension rows change over time, as customers move or products are renamed. Later in the series we cover the special dimension types: conformed, degenerate, junk, role-playing, mini and slowly changing dimensions.
Follow-up questions interviewers ask
Why use an Unknown row instead of NULL?
Inner joins drop facts with NULL keys, so totals silently stop matching. Key -1 keeps every fact.
Why descriptive text instead of codes?
Dimension attributes become report labels and filters; users should not need lookup tables.
Why are dimensions denormalized?
To avoid extra joins: hierarchies like category/subcategory are flattened onto one row.
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 #16 - What is a dimension table?
-- 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_q16; SET search_path TO dwh_q16;
-- ========================================================================
-- Step 0: create and load the sample data
DROP TABLE IF EXISTS fact_sales;
DROP TABLE IF EXISTS dim_product;
CREATE TABLE dim_product (
product_key INT PRIMARY KEY, -- surrogate key
product_code VARCHAR(10), -- natural (business) key
product_name VARCHAR(50),
category VARCHAR(50),
subcategory VARCHAR(50),
brand VARCHAR(50),
status VARCHAR(20)
);
INSERT INTO dim_product VALUES
(-1, 'N/A', 'Unknown', 'Unknown', 'Unknown', 'Unknown', 'Unknown'),
( 1, 'P-100', 'Laptop Stand', 'Accessories', 'Stands', 'TB Pro', 'Active'),
( 2, 'P-200', 'USB-C Cable', 'Accessories', 'Cables', 'TB Basics', 'Active'),
( 3, 'P-300', 'Wireless Mouse', 'Electronics', 'Mice', 'TB Pro', 'Active'),
( 4, 'P-400', 'Desk Lamp', 'Home', 'Lighting', 'TB Home', 'Discontinued');
CREATE TABLE fact_sales (
sale_id INT PRIMARY KEY,
product_key INT REFERENCES dim_product(product_key),
amount DECIMAL(10,2)
);
INSERT INTO fact_sales VALUES
(1, 1, 35.00), (2, 2, 18.00), (3, 3, 25.00), (4, 4, 30.00),
(5, 1, 70.00), (6, 3, 25.00), (7, 2, 27.00),
(8, -1, 15.00); -- product code was missing in the source: points to the Unknown row
-- Step 1: Dimensions filter, group and label the facts
SELECT p.brand, SUM(f.amount) AS sales
FROM fact_sales f
JOIN dim_product p
ON p.product_key = f.product_key
WHERE p.category = 'Accessories'
GROUP BY p.brand
ORDER BY sales DESC;
-- Step 2: The Unknown row keeps every fact in the report
SELECT p.category, SUM(f.amount) AS sales
FROM fact_sales f
JOIN dim_product p
ON p.product_key = f.product_key
GROUP BY p.category
ORDER BY sales DESC;
Key points
- Dimensions hold descriptive context: who, what, where, when
- Each row: surrogate key + natural key + many attributes
- They filter, group and label the facts
- Good habits: readable text, flattened hierarchies, Unknown row, planned history
- Wide and short, while facts are narrow and long
Question 17 — What is grain? What is the grain of a fact table, and why does it matter so much?
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.