In this post we are going to cover dimensional modeling, the way most data warehouses organize data for reporting.
Dimensional modeling is the approach made popular by Ralph Kimball. It splits warehouse data into two kinds of tables. Fact tables hold the numbers from business events, like quantity sold and sales amount. Dimension tables hold the descriptive context around those numbers: who, what, where and when. The fact sits in the middle and the dimensions sit around it, which is why the shape is called a star schema.
We will build a small star for TB Shop with one fact table and four dimensions, and then see how every report question turns into the same simple query pattern.
Question 14 of the Real DWH Question and Answers series on TechBrothersIT.
The interview answer
Dimensional modeling (Ralph Kimball's approach) organizes warehouse data into fact tables, which store measurements of business events such as quantity and amount, and dimension tables, which store the descriptive context: who, what, where and when. Arranged as a star schema, it makes analytical queries simple and fast.
Read the business question first
Here is a trick that makes dimensional modeling easy. Write down a real business question, for example:
"Show me sales amount by product category, by month, by store."
- The number you want to add up, sales amount, is a fact. Facts are numeric and come from business events, like a sale.
- Every word after "by" (product category, month, store) points to a dimension. Dimensions are how you slice, filter and group the facts.
Do this for ten questions from the business and you have most of your model before you write any SQL.
Let's create some sample data
We build four dimensions (dim_date, dim_product, dim_customer, dim_store) and one fact table, fact_sales, with one row per product sold on an order line. The fact holds one key per dimension, plus two measures: quantity and amount.
DROP TABLE IF EXISTS fact_sales;
DROP TABLE IF EXISTS dim_date;
DROP TABLE IF EXISTS dim_product;
DROP TABLE IF EXISTS dim_customer;
DROP TABLE IF EXISTS dim_store;
-- dimensions: the descriptive context (who, what, where, when)
CREATE TABLE dim_date (
date_key INT PRIMARY KEY, -- e.g. 20260105
full_date DATE,
month_num INT,
month_name VARCHAR(10),
year INT
);
CREATE TABLE dim_product (
product_key INT PRIMARY KEY,
product_name VARCHAR(50),
category VARCHAR(50)
);
CREATE TABLE dim_customer (
customer_key INT PRIMARY KEY,
name VARCHAR(50),
city VARCHAR(50)
);
CREATE TABLE dim_store (
store_key INT PRIMARY KEY,
store_name VARCHAR(50),
channel VARCHAR(20)
);
-- fact: one row per product sold on an order line (the measurements)
CREATE TABLE fact_sales (
date_key INT REFERENCES dim_date(date_key),
product_key INT REFERENCES dim_product(product_key),
customer_key INT REFERENCES dim_customer(customer_key),
store_key INT REFERENCES dim_store(store_key),
quantity INT,
amount DECIMAL(10,2)
);
INSERT INTO dim_date VALUES
(20260105, '2026-01-05', 1, 'Jan', 2026), (20260109, '2026-01-09', 1, 'Jan', 2026),
(20260202, '2026-02-02', 2, 'Feb', 2026), (20260214, '2026-02-14', 2, 'Feb', 2026),
(20260303, '2026-03-03', 3, 'Mar', 2026);
INSERT INTO dim_product VALUES
(1, 'Laptop Stand', 'Accessories'), (2, 'USB-C Cable', 'Accessories'),
(3, 'Wireless Mouse', 'Electronics'), (4, 'Desk Lamp', 'Home');
INSERT INTO dim_customer VALUES
(1, 'Sara Khan', 'Toronto'), (2, 'Omar Ali', 'Dallas'), (3, 'Lina Park', 'Seattle'), (4, 'Mei Chen', 'Vancouver');
INSERT INTO dim_store VALUES (1, 'TB Shop Online', 'Online'), (2, 'TB Shop Toronto', 'Store');
INSERT INTO fact_sales VALUES
(20260105, 1, 1, 1, 1, 35.00), (20260105, 2, 1, 1, 2, 18.00),
(20260109, 3, 2, 2, 1, 25.00), (20260202, 4, 1, 1, 1, 30.00),
(20260214, 1, 3, 1, 2, 70.00), (20260214, 3, 3, 1, 1, 25.00),
(20260303, 2, 4, 2, 3, 27.00);Let's look at the fact table first.
SELECT * FROM fact_sales ORDER BY 1;| date_key | product_key | customer_key | store_key | quantity | amount |
|---|---|---|---|---|---|
| 20260105 | 1 | 1 | 1 | 1 | 35.00 |
| 20260105 | 2 | 1 | 1 | 2 | 18.00 |
| 20260109 | 3 | 2 | 2 | 1 | 25.00 |
| 20260202 | 4 | 1 | 1 | 1 | 30.00 |
| 20260214 | 1 | 3 | 1 | 2 | 70.00 |
| 20260214 | 3 | 3 | 1 | 1 | 25.00 |
| 20260303 | 2 | 4 | 2 | 3 | 27.00 |
Seven rows. Each row has four dimension keys and two measures. The keys are the lines of the star.
SELECT * FROM dim_product ORDER BY 1;| product_key | product_name | category |
|---|---|---|
| 1 | Laptop Stand | Accessories |
| 2 | USB-C Cable | Accessories |
| 3 | Wireless Mouse | Electronics |
| 4 | Desk Lamp | Home |
Each dimension is a table of descriptive attributes. Here category sits right on the product row, so no extra join is needed to get it.
SELECT * FROM dim_store ORDER BY 1;| store_key | store_name | channel |
|---|---|---|
| 1 | TB Shop Online | Online |
| 2 | TB Shop Toronto | Store |
Two stores, one Online and one Store channel.
Step 1: Sales amount by month by category
Now let's answer the question from the start: sales amount by month and category. The amount comes from the fact table. We join to dim_date for the month and to dim_product for the category, then group.
SELECT d.month_name, p.category,
SUM(f.amount) AS sales
FROM fact_sales f
JOIN dim_date d ON d.date_key = f.date_key
JOIN dim_product p ON p.product_key = f.product_key
GROUP BY d.month_num, d.month_name, p.category
ORDER BY d.month_num, p.category;| month_name | category | sales |
|---|---|---|
| Jan | Accessories | 53.00 |
| Jan | Electronics | 25.00 |
| Feb | Accessories | 70.00 |
| Feb | Electronics | 25.00 |
| Feb | Home | 30.00 |
| Mar | Accessories | 27.00 |
January has 53.00 in Accessories and 25.00 in Electronics. February has three categories, and March only has 27.00 in Accessories. Every query against a star looks like this: one join per dimension you need, then GROUP BY.
Step 2: Swap the question - sales by channel
What if the business asks for sales by channel instead? We do not need a new design. We just join a different dimension and group by its attribute.
SELECT s.channel, SUM(f.amount) AS sales
FROM fact_sales f
JOIN dim_store s ON s.store_key = f.store_key
GROUP BY s.channel;| channel | sales |
|---|---|
| Online | 178.00 |
| Store | 52.00 |
Online brought in 178.00 and Store 52.00. Same pattern, different dimension. That predictability is the real power of a star schema.
Kimball's four steps
Kimball describes four steps to design any dimensional model. Interviewers like it when you list them in order:
- Choose the business process - order taking, shipments, inventory and so on.
- Declare the grain - exactly what one fact row means. For us: one product on one order line. This is the most important step.
- Identify the dimensions - date, product, customer and store.
- Identify the facts - quantity and amount.
Dimensional vs normalized
A normalized model is designed for writing data safely. A dimensional model is designed for reading and analysis.
| Normalized (3NF) | Dimensional (star) | |
|---|---|---|
| DESIGNED FOR | Writing data safely | Reading and analysis |
| SHAPE | Many small tables, many joins | Fact + a few wide dimensions |
| REDUNDANCY | None | Deliberate, in dimensions |
| USERS | Applications | Analysts, BI tools |
Dimensions are denormalized on purpose: category sits on the product row and month sits on the date row. A common follow-up is Kimball vs Inmon. Inmon builds a normalized enterprise warehouse first, while Kimball builds dimensional models from the start. That comparison has its own post later in the series (question 41).
Follow-up questions interviewers ask
What are Kimball's four steps?
Choose the business process, declare the grain, identify the dimensions, identify the facts.
Kimball vs Inmon?
Inmon: normalized enterprise warehouse first, marts later. Kimball: dimensional models (star schemas) from the start, linked by conformed dimensions.
Is a dimensional model normalized?
No, dimensions are deliberately denormalized; the fact table is narrow and keyed.
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 #14 - What is dimensional modeling?
-- 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_q14; SET search_path TO dwh_q14;
-- ========================================================================
-- Step 0: create and load the sample data
DROP TABLE IF EXISTS fact_sales;
DROP TABLE IF EXISTS dim_date;
DROP TABLE IF EXISTS dim_product;
DROP TABLE IF EXISTS dim_customer;
DROP TABLE IF EXISTS dim_store;
-- dimensions: the descriptive context (who, what, where, when)
CREATE TABLE dim_date (
date_key INT PRIMARY KEY, -- e.g. 20260105
full_date DATE,
month_num INT,
month_name VARCHAR(10),
year INT
);
CREATE TABLE dim_product (
product_key INT PRIMARY KEY,
product_name VARCHAR(50),
category VARCHAR(50)
);
CREATE TABLE dim_customer (
customer_key INT PRIMARY KEY,
name VARCHAR(50),
city VARCHAR(50)
);
CREATE TABLE dim_store (
store_key INT PRIMARY KEY,
store_name VARCHAR(50),
channel VARCHAR(20)
);
-- fact: one row per product sold on an order line (the measurements)
CREATE TABLE fact_sales (
date_key INT REFERENCES dim_date(date_key),
product_key INT REFERENCES dim_product(product_key),
customer_key INT REFERENCES dim_customer(customer_key),
store_key INT REFERENCES dim_store(store_key),
quantity INT,
amount DECIMAL(10,2)
);
INSERT INTO dim_date VALUES
(20260105, '2026-01-05', 1, 'Jan', 2026), (20260109, '2026-01-09', 1, 'Jan', 2026),
(20260202, '2026-02-02', 2, 'Feb', 2026), (20260214, '2026-02-14', 2, 'Feb', 2026),
(20260303, '2026-03-03', 3, 'Mar', 2026);
INSERT INTO dim_product VALUES
(1, 'Laptop Stand', 'Accessories'), (2, 'USB-C Cable', 'Accessories'),
(3, 'Wireless Mouse', 'Electronics'), (4, 'Desk Lamp', 'Home');
INSERT INTO dim_customer VALUES
(1, 'Sara Khan', 'Toronto'), (2, 'Omar Ali', 'Dallas'), (3, 'Lina Park', 'Seattle'), (4, 'Mei Chen', 'Vancouver');
INSERT INTO dim_store VALUES (1, 'TB Shop Online', 'Online'), (2, 'TB Shop Toronto', 'Store');
INSERT INTO fact_sales VALUES
(20260105, 1, 1, 1, 1, 35.00), (20260105, 2, 1, 1, 2, 18.00),
(20260109, 3, 2, 2, 1, 25.00), (20260202, 4, 1, 1, 1, 30.00),
(20260214, 1, 3, 1, 2, 70.00), (20260214, 3, 3, 1, 1, 25.00),
(20260303, 2, 4, 2, 3, 27.00);
-- Step 1: Sales amount by month by category
SELECT d.month_name, p.category,
SUM(f.amount) AS sales
FROM fact_sales f
JOIN dim_date d ON d.date_key = f.date_key
JOIN dim_product p ON p.product_key = f.product_key
GROUP BY d.month_num, d.month_name, p.category
ORDER BY d.month_num, p.category;
-- Step 2: Swap the question: sales by channel, same pattern
SELECT s.channel, SUM(f.amount) AS sales
FROM fact_sales f
JOIN dim_store s ON s.store_key = f.store_key
GROUP BY s.channel;
Key points
- Facts: numeric measurements of business events
- Dimensions: descriptive context: who, what, where, when
- Trick: the number is the fact, every word after "by" is a dimension
- Kimball's 4 steps: process, grain, dimensions, facts
- Star schema: one join per dimension, simple and fast
Question 15 — Fact tables and additive measures What is a fact table? What are additive, semi-additive and non-additive measures?
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.