In this post we are going to cover the most common hands-on data warehouse interview question: design a star schema for an online store.
This is not a definition question. The interviewer wants to see a method. The method we will use is Kimball's four steps: choose the business process, declare the grain, identify the dimensions, and identify the facts. Then we check the design against the business questions, and explain each choice as we go.
We will walk through the four steps for a small online store, build the star schema with SQL, and prove it works by answering the three business questions in the brief.
Question 45 of the Real DWH Question and Answers series on TechBrothersIT.
- The interview answer
- The brief
- Design steps 1 and 2: business process and grain
- Design step 3: the dimensions
- Design step 4: the facts
- Let's create some sample data
- Step 1: Revenue by category and channel
- Step 2: Revenue by month and customer segment
- Step 3: Orders and average order value by channel
- The interview checklist
- Follow-up questions interviewers ask
- The full script
The interview answer
Use Kimball's four steps: 1 choose the business process, 2 declare the grain, 3 identify the dimensions, 4 identify the facts. Then validate the design against the business questions, and explain your choices: surrogate keys, SCD types, a date dimension, degenerate keys and additivity.
Interviewers are listening for the method, and for the reasons behind each choice.
The brief
TB Shop sells online and in stores. The business wants three things:
- Revenue by product category and sales channel.
- Revenue by month and customer segment.
- The number of orders and the average order value by channel.
Before drawing anything, repeat the questions back and ask: what is the lowest level of detail anyone needs? Here it is individual products on an order. That one question decides the grain.
Design steps 1 and 2: business process and grain
Step 1, the business process: order placement, the event that creates revenue.
Step 2, the grain, in one sentence: one row per product on an order, the order line.
Why not one row per order? Because then we lose the product and cannot answer revenue by category. Always pick the lowest grain the source has. We can roll up from a low grain, but we can never drill down below it.
Design step 3: the dimensions
To find the dimensions, ask who, what, when, where and how.
- When -
dim_date, with month and day names, keyed by an integer like20260601. - Who -
dim_customer, with a surrogate key and SCD Type 2 on city, so history stays correct. - What -
dim_product, with its category. - How -
dim_channel, web or store. A tiny dimension that could also be part of a junk dimension.
The order number has no attributes of its own, so it stays on the fact table as a degenerate dimension.
Design step 4: the facts
The facts are the numbers measured at that grain.
quantityandamountare additive: we can sum them across every dimension.unit_priceis non-additive: never sum it. Recompute it as total amount divided by total quantity.
There is no order count column. We will count distinct order numbers instead.
Let's create some sample data
The script creates the four dimensions and the fact table fact_order_line, with four dimension keys, the degenerate order_number, and three measures.
DROP TABLE IF EXISTS fact_order_line;
DROP TABLE IF EXISTS dim_date;
DROP TABLE IF EXISTS dim_customer;
DROP TABLE IF EXISTS dim_product;
DROP TABLE IF EXISTS dim_channel;
-- STEP 3: the dimensions
CREATE TABLE dim_date (date_key INT PRIMARY KEY, full_date DATE, month_name VARCHAR(10), day_name VARCHAR(10));
INSERT INTO dim_date VALUES
(20260601, '2026-06-01', 'June', 'Monday'),
(20260602, '2026-06-02', 'June', 'Tuesday'),
(20260701, '2026-07-01', 'July', 'Wednesday');
CREATE TABLE dim_customer (
customer_key INT PRIMARY KEY, customer_id VARCHAR(10), name VARCHAR(50), segment VARCHAR(20),
city VARCHAR(50), valid_from DATE, valid_to DATE, is_current CHAR(1) -- SCD Type 2 on city
);
INSERT INTO dim_customer VALUES
(1, 'C101', 'Sara Khan', 'Retail', 'Toronto', '2025-01-01', '9999-12-31', 'Y'),
(2, 'C102', 'Omar Ali', 'Business', 'Dallas', '2025-01-01', '9999-12-31', 'Y');
CREATE TABLE dim_product (product_key INT PRIMARY KEY, product_id VARCHAR(10), name VARCHAR(50), category VARCHAR(20));
INSERT INTO dim_product VALUES (1, 'P10', 'Pen', 'Office'), (2, 'P20', 'Notebook', 'Office'), (3, 'P30', 'Headphones', 'Electronics');
CREATE TABLE dim_channel (channel_key INT PRIMARY KEY, channel VARCHAR(10));
INSERT INTO dim_channel VALUES (1, 'Web'), (2, 'Store');
-- STEPS 2 + 4: grain = one row per order line; the measures
CREATE TABLE fact_order_line (
date_key INT REFERENCES dim_date(date_key),
customer_key INT REFERENCES dim_customer(customer_key),
product_key INT REFERENCES dim_product(product_key),
channel_key INT REFERENCES dim_channel(channel_key),
order_number VARCHAR(10), -- degenerate dimension
quantity INT, -- additive
unit_price DECIMAL(10,2), -- non-additive
amount DECIMAL(10,2) -- additive
);
INSERT INTO fact_order_line VALUES
(20260601, 1, 1, 1, 'SO-1', 2, 5.00, 10.00),
(20260601, 1, 3, 1, 'SO-1', 1, 60.00, 60.00),
(20260602, 2, 2, 2, 'SO-2', 5, 4.00, 20.00),
(20260602, 2, 1, 2, 'SO-2', 10, 5.00, 50.00),
(20260701, 1, 3, 1, 'SO-3', 1, 60.00, 60.00);Here is the fact table. It is narrow and numeric, with one row per order line.
SELECT * FROM fact_order_line ORDER BY 1;| date_key | customer_key | product_key | channel_key | order_number | quantity | unit_price | amount |
|---|---|---|---|---|---|---|---|
| 20260601 | 1 | 1 | 1 | SO-1 | 2 | 5.00 | 10.00 |
| 20260601 | 1 | 3 | 1 | SO-1 | 1 | 60.00 | 60.00 |
| 20260602 | 2 | 2 | 2 | SO-2 | 5 | 4.00 | 20.00 |
| 20260602 | 2 | 1 | 2 | SO-2 | 10 | 5.00 | 50.00 |
| 20260701 | 1 | 3 | 1 | SO-3 | 1 | 60.00 | 60.00 |
Order SO-1 has two lines, a pen and headphones, so it becomes two fact rows. Order SO-2 also has two lines, and SO-3 has one.
The customer dimension carries the SCD Type 2 columns valid_from, valid_to and is_current, ready for a change of city.
SELECT * FROM dim_customer ORDER BY 1;| customer_key | customer_id | name | segment | city | valid_from | valid_to | is_current |
|---|---|---|---|---|---|---|---|
| 1 | C101 | Sara Khan | Retail | Toronto | 2025-01-01 | 9999-12-31 | Y |
| 2 | C102 | Omar Ali | Business | Dallas | 2025-01-01 | 9999-12-31 | Y |
Step 1: Revenue by category and channel
Now let's prove the design works. Every business question should be a simple join and GROUP BY. The first question needs the product and channel dimensions.
SELECT p.category, ch.channel, SUM(f.amount) AS revenue
FROM fact_order_line f
JOIN dim_product p ON p.product_key = f.product_key
JOIN dim_channel ch ON ch.channel_key = f.channel_key
GROUP BY p.category, ch.channel
ORDER BY revenue DESC, p.category;| category | channel | revenue |
|---|---|---|
| Electronics | Web | 120.00 |
| Office | Store | 70.00 |
| Office | Web | 10.00 |
Electronics on the web leads with 120.00, Office in stores brought 70.00, and Office on the web 10.00.
Step 2: Revenue by month and customer segment
The second question uses the date and customer dimensions instead. Same fact table, different dimensions.
SELECT d.month_name, c.segment, SUM(f.amount) AS revenue
FROM fact_order_line f
JOIN dim_date d ON d.date_key = f.date_key
JOIN dim_customer c ON c.customer_key = f.customer_key
GROUP BY d.month_name, c.segment
ORDER BY MIN(d.full_date), c.segment;| month_name | segment | revenue |
|---|---|---|
| June | Retail | 70.00 |
| June | Business | 70.00 |
| July | Retail | 60.00 |
In June, Retail and Business each brought 70.00. In July, Retail brought 60.00. Ordering by MIN(d.full_date) keeps the months in calendar order instead of alphabetical order.
Step 3: Orders and average order value by channel
The third question needs a count of orders, but our grain is the order line. This is where the degenerate order_number earns its place: we count distinct order numbers, with no order dimension needed.
SELECT ch.channel,
COUNT(DISTINCT f.order_number) AS orders,
CAST(SUM(f.amount) / COUNT(DISTINCT f.order_number)
AS DECIMAL(10,2)) AS avg_order_value
FROM fact_order_line f
JOIN dim_channel ch ON ch.channel_key = f.channel_key
GROUP BY ch.channel
ORDER BY ch.channel;| channel | orders | avg_order_value |
|---|---|---|
| Store | 1 | 70.00 |
| Web | 2 | 65.00 |
The store had 1 order with an average value of 70.00, and the web had 2 orders averaging 65.00. All three business questions are answered with short queries, so the design holds up.
The interview checklist
Finally, here is what to say out loud in the interview:
- State the grain in one sentence.
- Use surrogate keys and a date dimension.
- Name the SCD type for each important attribute.
- Label every measure additive, semi-additive or non-additive.
- Mention degenerate dimensions and unknown members for missing keys.
- Explain the load: dimensions first, idempotent, with quality checks.
Every one of these points has its own question earlier in this series.
Follow-up questions interviewers ask
Why the order line and not the order as grain?
The order grain loses the product, so revenue by category is impossible; always choose the lowest available grain.
Why is order_number on the fact table?
It is a degenerate dimension: an identifier with no attributes of its own, used to group and count orders.
Can you sum unit_price?
No: it is non-additive; store additive amount and quantity, and derive prices as SUM(amount) / SUM(quantity).
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 #45 - Design a star schema: the interview walkthrough
-- 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_q45; SET search_path TO dwh_q45;
-- ========================================================================
-- Step 0: create and load the sample data
DROP TABLE IF EXISTS fact_order_line;
DROP TABLE IF EXISTS dim_date;
DROP TABLE IF EXISTS dim_customer;
DROP TABLE IF EXISTS dim_product;
DROP TABLE IF EXISTS dim_channel;
-- STEP 3: the dimensions
CREATE TABLE dim_date (date_key INT PRIMARY KEY, full_date DATE, month_name VARCHAR(10), day_name VARCHAR(10));
INSERT INTO dim_date VALUES
(20260601, '2026-06-01', 'June', 'Monday'),
(20260602, '2026-06-02', 'June', 'Tuesday'),
(20260701, '2026-07-01', 'July', 'Wednesday');
CREATE TABLE dim_customer (
customer_key INT PRIMARY KEY, customer_id VARCHAR(10), name VARCHAR(50), segment VARCHAR(20),
city VARCHAR(50), valid_from DATE, valid_to DATE, is_current CHAR(1) -- SCD Type 2 on city
);
INSERT INTO dim_customer VALUES
(1, 'C101', 'Sara Khan', 'Retail', 'Toronto', '2025-01-01', '9999-12-31', 'Y'),
(2, 'C102', 'Omar Ali', 'Business', 'Dallas', '2025-01-01', '9999-12-31', 'Y');
CREATE TABLE dim_product (product_key INT PRIMARY KEY, product_id VARCHAR(10), name VARCHAR(50), category VARCHAR(20));
INSERT INTO dim_product VALUES (1, 'P10', 'Pen', 'Office'), (2, 'P20', 'Notebook', 'Office'), (3, 'P30', 'Headphones', 'Electronics');
CREATE TABLE dim_channel (channel_key INT PRIMARY KEY, channel VARCHAR(10));
INSERT INTO dim_channel VALUES (1, 'Web'), (2, 'Store');
-- STEPS 2 + 4: grain = one row per order line; the measures
CREATE TABLE fact_order_line (
date_key INT REFERENCES dim_date(date_key),
customer_key INT REFERENCES dim_customer(customer_key),
product_key INT REFERENCES dim_product(product_key),
channel_key INT REFERENCES dim_channel(channel_key),
order_number VARCHAR(10), -- degenerate dimension
quantity INT, -- additive
unit_price DECIMAL(10,2), -- non-additive
amount DECIMAL(10,2) -- additive
);
INSERT INTO fact_order_line VALUES
(20260601, 1, 1, 1, 'SO-1', 2, 5.00, 10.00),
(20260601, 1, 3, 1, 'SO-1', 1, 60.00, 60.00),
(20260602, 2, 2, 2, 'SO-2', 5, 4.00, 20.00),
(20260602, 2, 1, 2, 'SO-2', 10, 5.00, 50.00),
(20260701, 1, 3, 1, 'SO-3', 1, 60.00, 60.00);
-- Step 1: Business question 1: revenue by category and channel
SELECT p.category, ch.channel, SUM(f.amount) AS revenue
FROM fact_order_line f
JOIN dim_product p ON p.product_key = f.product_key
JOIN dim_channel ch ON ch.channel_key = f.channel_key
GROUP BY p.category, ch.channel
ORDER BY revenue DESC, p.category;
-- Step 2: Business question 2: revenue by month and customer segment
SELECT d.month_name, c.segment, SUM(f.amount) AS revenue
FROM fact_order_line f
JOIN dim_date d ON d.date_key = f.date_key
JOIN dim_customer c ON c.customer_key = f.customer_key
GROUP BY d.month_name, c.segment
ORDER BY MIN(d.full_date), c.segment;
-- Step 3: Business question 3: orders and average order value by channel (degenerate dimension)
SELECT ch.channel,
COUNT(DISTINCT f.order_number) AS orders,
CAST(SUM(f.amount) / COUNT(DISTINCT f.order_number)
AS DECIMAL(10,2)) AS avg_order_value
FROM fact_order_line f
JOIN dim_channel ch ON ch.channel_key = f.channel_key
GROUP BY ch.channel
ORDER BY ch.channel;
Key points
- 1. Business process
- 2. Grain, in one sentence
- 3. Dimensions: who, what, when, where, how
- 4. Facts at that grain, with additivity
- Validate against the business questions
All 45 questions are listed on the Real DWH Question and Answers page on this blog.
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.