Star schema in SQL: fact tables, dimension tables and grain, with an example

Part 1 wrote queries straight over the orders. That works for 1 question. A warehouse answers thousands, from many people, and they only agree if the tables underneath are shaped for it. The shape most warehouses use is the star schemaStar SchemaA way to arrange warehouse tables for reporting: a fact table of events and their numbers, joined by keys to dimension tables that describe them, such as customer, product and date.: facts in the middle, dimensions around them.

Declare the grain first

Kimball's design method makes 4 decisions in order: the business process, the grain, the dimensions and the facts. The process here is taking orders. The grain says what 1 row of the fact table stands for: 1 row per order. A shop with order lines would choose 1 row per line, the finest level its system records, because a fine grain can always be added up and a coarse one cannot be split.

With the grain fixed, the rest follows:

• Dimensions, the who, when and where of each order: the customer and the day. Each becomes a table with 1 row per member and the columns people group and filter by.
• Facts, the numbers of each order: the amount. It is additive, so it can be summed across every dimension. A ratio such as an average order value is not, so you store the parts, the amount and the count, and divide at the end.

A date dimension

Every warehouse has a date table, 1 row per day, so that reports can group by week day or month without working it out each time, and can show the days that had no orders. Spark makes the days with sequence and turns the array into rows with explode. Add this to a file star.sql and run it after setup.sql:

CREATE OR REPLACE TEMP VIEW dim_date AS
SELECT CAST(date_format(day, 'yyyyMMdd') AS INT) AS date_key,
       day,
       date_format(day, 'EEEE') AS weekday,
       dayofweek(day) IN (1, 7) AS is_weekend,
       date_format(day, 'yyyy-MM') AS month
FROM (SELECT explode(sequence(DATE'2026-10-01', DATE'2026-10-31', INTERVAL 1 DAY)) AS day);

SELECT * FROM dim_date WHERE day <= DATE'2026-10-05' ORDER BY day;

+--------+----------+--------+----------+-------+
|date_key|day       |weekday |is_weekend|month  |
+--------+----------+--------+----------+-------+
|20261001|2026-10-01|Thursday|false     |2026-10|
|20261002|2026-10-02|Friday  |false     |2026-10|
|20261003|2026-10-03|Saturday|true      |2026-10|
|20261004|2026-10-04|Sunday  |true      |2026-10|
|20261005|2026-10-05|Monday  |false     |2026-10|
+--------+----------+--------+----------+-------+

dayofweek returns 1 for Sunday and 7 for Saturday, so IN (1, 7) is the weekend. Saturday 3 October is here although nobody ordered that day. The key, 20261001, is the one place where a key may mean something: a date never changes, and the number sorts like the date.

A customer dimension and the fact table

The customer system records a row each time a customer changes. For now, the dimension keeps each customer's newest row, with the QUALIFY pattern from part 1:

CREATE OR REPLACE TEMP VIEW dim_customer_now AS
SELECT customer_id, name, country
FROM customer_changes
QUALIFY row_number() OVER (PARTITION BY customer_id ORDER BY changed_at DESC) = 1;

CREATE OR REPLACE TEMP VIEW fact_orders AS
SELECT order_id, customer_id,
       CAST(date_format(ordered_at, 'yyyyMMdd') AS INT) AS date_key,
       ordered_at, amount
FROM orders;

The fact table holds keys and numbers, nothing else: no names, no countries, no week days. Those live once, in their dimension, so changing a country is 1 update, not millions.

Join the star

A report joins the fact to the dimensions it needs and groups by their columns. Revenue by week day and country:

SELECT d.weekday, c.country, count(*) AS orders, sum(f.amount) AS revenue
FROM fact_orders f
JOIN dim_date d ON f.date_key = d.date_key
JOIN dim_customer_now c ON f.customer_id = c.customer_id
GROUP BY d.weekday, c.country
ORDER BY revenue DESC, weekday;

+--------+-------+------+-------+
|weekday |country|orders|revenue|
+--------+-------+------+-------+
|Friday  |DE     |1     |60.00  |
|Friday  |ES     |1     |40.00  |
|Sunday  |ES     |1     |30.00  |
|Thursday|ES     |1     |25.50  |
|Monday  |IE     |1     |22.75  |
|Friday  |IE     |1     |18.25  |
|Sunday  |DE     |1     |15.00  |
|Thursday|IE     |1     |12.00  |
+--------+-------+------+-------+

Every report has the same form: join by keys, group by dimension columns, add up facts. That sameness is the point. A dashboard tool, a person writing SQL and an AI assistant asking the warehouse a question all meet the same tables and get the same numbers.

Keep the grain through every join

A join returns 1 row for each pair of rows that match. If a dimension holds 2 rows for 1 key, each fact row with that key comes out twice, with no error. Join the facts to the raw change log instead of the dimension and count:

SELECT (SELECT count(*) FROM fact_orders) AS fact_rows,
       (SELECT count(*) FROM fact_orders f JOIN customer_changes c ON f.customer_id = c.customer_id) AS joined_to_changes,
       (SELECT count(*) FROM fact_orders f JOIN dim_customer_now c ON f.customer_id = c.customer_id) AS joined_to_current;

+---------+-----------------+-----------------+
|fact_rows|joined_to_changes|joined_to_current|
+---------+-----------------+-----------------+
|8        |11               |8                |
+---------+-----------------+-----------------+

Ana, C01, has 2 rows in the change log, so her 3 orders become 6 rows and revenue comes to 319.00 instead of 223.50. Switch the table in the demo and watch which totals move.

Try itJoin a fact to a dimension at the wrong grain

Join orders to

customer_changesdim_customer_now

Revenue by

Week dayCountry

The joined rows

order_id	weekday	country	amount
1001	Thursday	PT	25.50
1001	Thursday	ES	25.50
1002	Thursday	IE	12.00
1003	Friday	DE	60.00
1004	Friday	PT	40.00
1004	Friday	ES	40.00
1005	Friday	IE	18.25
1006	Sunday	PT	30.00
1006	Sunday	ES	30.00
1007	Sunday	DE	15.00
1008	Monday	IE	22.75

Shaded: an order that came out of the join twice.

Rows

11

for 8 orders

Revenue

319.00

Should be 223.50

weekday	revenue
Friday	158.25
Monday	22.75
Sunday	75.00
Thursday	63.00

This is the same fan-out module 2 met in PySpark, and the check is the same: after each join, the fact table's row count should not change. Part 4 makes that check stop the build when it fails.

Kimball would also keep the dimension flat. A table of countries that the customer table points to, a snowflake, holds the same facts but adds a join to every query and is harder to read, so the country's name sits in the customer row.

The report is still wrong

Look again at the first report. Ana lived in Portugal until 3 October and placed 2 orders there, 25.50 on Thursday and 40.00 on Friday. The report counts both for ES, because dim_customer_now knows only where she lives today. Portugal's revenue for those days is gone, and it would be gone from last month's report too if someone ran it again. Part 3 keeps every version of a customer, so each order joins to the customer as they were when they placed it.