Dimensional Modeling: Facts & Dimensions

In the grain chapter you learned that every table has a grain: what one row means. This chapter builds tables for analytics on top of that idea.

A manager asks a question: what is the paid revenue for each country?

The answer needs two parts. One part is a number to add up: the order amount. The other part is a word to group by: the country.

Dimensional modeling keeps these two parts in two kinds of table.

• A fact table stores numbers about things that happened, with one row for each thing. An order is one such event.
• A dimension table stores the words that describe an event: who, what and when.

In the shop, the numbers are the order amount and the item quantity. The words are the customer, the product and the date.

The five tables

You will build five tables from the shop. The name starts with fact_ or dim_, so you can see the kind at once.

What you will learn

1. What goes in a fact table, and which numbers you can add.
2. What goes in a dimension table, and why the date gets its own.
3. How a star query joins them.
4. The four design steps, and what to do with missing values.
5. How two fact tables share one dimension.
6. Three special kinds of dimension: role-playing, junk and degenerate.

The shop database from earlier chapters is built for writing. Each fact lives in one place, so an update is safe.

Analysts ask different questions. They add up a number and group it by a word. The star shape has the words ready, so these questions are short.

Both shapes hold the same shop data. Compare how each one answers four questions:

The joins look alike. The difference is in the words. In the star, someone made the words once, and every query reuses them.

A fact table has one row for each event you measure. An event is something that happened, like an order.

Start with the grain, as in chapter 4. Here it is one row per order. This is fact_orders:

Each column is one of three kinds:

• Keys: customer_key and date_key. They point to rows in dimension tables.
• A measure: amount. It is the number you add up.
• Other columns: order_key is the order number, and status says what state the order is in.

Order 105 is pending, so its amount is NULL. The money does not exist yet.

A second fact table

The shop also has item lines. One order can have many lines, so this is a different grain: one row per product on an order.

The measure here is quantity. There is no money column. The money belongs to the whole order, not to one line.

If you copied amount onto every line, order 101 would show 40.00 twice. A sum would give 80.00. That is the fan-out from the grain chapter.

A measure is a number in a fact table. You cannot add every measure in every way. There are three kinds.

Additive

An additive measure can be added across every dimension. The quantity in fact_order_items is additive. Here is the quantity for each customer:

SELECT customer_key, SUM(quantity) AS units
FROM fact_order_items
GROUP BY customer_key
ORDER BY customer_key;

Group the rows any way you like. The total stays 10. The measure amount on fact_orders is also additive.

Semi-additive

A semi-additive measure can be added across some dimensions, but not across time. Stock is the usual case.

Suppose the shop saves its stock once a day. This is a snapshot table, made only for this example. The grain is one row per product per day:

On one day, you can add across products. That gives the units in the shop on that day:

SELECT SUM(units_in_stock) AS units_on_hand
FROM stock_snapshot
WHERE day = '2026-01-11';

Do not add across days. All four rows add to 51, but that counts the 20 Pens twice, once for each day.

Use the last day, or an average, instead.

A non-additive measure can never be added. Averages and ratios are non-additive. Look at the paid orders for each country:

The last row is right: 160.50 divided by 4 orders is 40.125. The average of the three averages is (32.50 + 15.50 + 80.00) / 3 = 42.67. That is wrong.

So store the parts, revenue and order count. Divide at the end.

A dimension table has one row for each customer, product or day. Its columns are attributes. An attribute is a word you filter by or group by.

This is dim_customer. The attributes are name and country:

The first column is a surrogate key: a number the warehouse makes up. The keys chapter explained it. customer_id is the shop’s own number, so it is called the natural key.

Facts point to customer_key, not to customer_id. If the shop changes its ids, the facts still work. We start the keys at 11 so you do not mix them up with customer_id.

Look at Dev. The shop has no country for him, so the shop’s table holds NULL.

The dimension stores the word Unknown instead. A report then shows a named group, not a blank.

Wide on purpose

A dimension keeps its words in one wide table. The country sits right inside dim_customer. There is no separate countries table.

Chapter 5 showed this trade: a few repeated words buy a simpler query.

This is dim_product. It is smaller:

fact_order_items keeps customer_key and date_key, copied from its order. That lets it use dim_customer and dim_date directly, with no join through fact_orders.

Every fact happened on a day. You could keep a date column on the fact. But questions use words: month, weekday, weekend.

So the date gets its own table, dim_date. It has one row for each day. The words are written once, so no query has to work them out.

date_key is the date as a number: 20260111 is 2026-01-11. Many teams make this key themselves, because it sorts well and is easy to read.

A real dim_date has every day for many years, also days with no orders. Here we show only the days we need.

Now ask: how much paid revenue came on weekends?

SELECT d.is_weekend, SUM(f.amount) AS revenue
FROM fact_orders AS f
JOIN dim_date AS d ON d.date_key = f.date_key
WHERE f.status = 'paid'
GROUP BY d.is_weekend
ORDER BY d.is_weekend DESC;

The fact table holds only date_key. Every date word comes from the dimension.

Two more kinds of dimension appear often. Both solve a small, real problem.

Role-playing dimension

A fact can have two dates: the day the customer ordered, and the day the shop shipped. You do not build two date tables. Both keys point to the same dim_date, and each plays a different role.

Suppose fact_orders had a second key, ship_date_key. Order 101 was ordered on 2026-01-03 and shipped on 2026-01-10. You join dim_date twice and give each copy its own name, od and sd:

SELECT f.order_key,
       od.full_date AS order_date,
       sd.full_date AS ship_date
FROM fact_orders AS f
JOIN dim_date AS od ON od.date_key = f.date_key
JOIN dim_date AS sd ON sd.date_key = f.ship_date_key
WHERE f.order_key = 101;

Junk dimension

A flag is a small column with few values, like a status or a yes or no. One tiny table for each flag is too much, and many flag columns make the fact wide.

A junk dimension puts the flags in one small table. It has one row for each combination that really happens.

The fact keeps a single key. Here is an example with two flags:

The fact stores flag_key, such as 2 for a paid gift.

In this example, status moves out of fact_orders into the junk table. Junk only means small flags kept together. It is a normal, useful kind of table.

Look at fact_order_items again. The column order_key says which order a line belongs to. But there is no dim_order table.

This is a degenerate dimension: an ID that sits on the fact table and has no dimension table of its own. Degenerate here means the dimension has no table.

order_key is the shop’s own order number (101), not a number the warehouse made up.

Why no table? Everything about an order is already on the fact or in the other dimensions. A dim_order table would only hold the order number.

You use it to group lines back into orders. Count the lines, and count the orders:

SELECT COUNT(*) AS lines,
       COUNT(DISTINCT order_key) AS orders
FROM fact_order_items;

The grain of this table is one line, so COUNT(*) counts lines. The order number lets you count orders, a bigger unit than lines.

A star schema puts a fact table in the middle. Dimension tables sit around it, each linked by one key.

Look at the diagram. Each FK column holds the key of one dimension row. customer_key in a fact matches customer_key in dim_customer.

Every star query has the same shape. Join the fact to a dimension, filter, group by a dimension word, and add a measure.

Paid revenue by country

Here is the manager’s question from the start of the chapter:

SELECT c.country, SUM(f.amount) AS revenue
FROM fact_orders AS f
JOIN dim_customer AS c ON c.customer_key = f.customer_key
WHERE f.status = 'paid'
GROUP BY c.country
ORDER BY revenue DESC;

Ben’s order 103 is refunded, and Carla’s order 105 is pending. The filter removes both. Omar has no order, so he has no fact row and does not appear.

Units by product

The second question uses the other star: how many units of each product did we sell?

SELECT p.name, SUM(i.quantity) AS units
FROM fact_order_items AS i
JOIN dim_product AS p ON p.product_key = i.product_key
GROUP BY p.name
ORDER BY units DESC, p.name;

Ralph Kimball wrote the best-known book on warehouse design. He gave four steps. You take them in this order.

The order matters. You need the grain before you pick facts.

Each dimension must give exactly one value for each row. One order can have many products, so dim_product does not belong on fact_orders. It belongs on fact_order_items.

Sometimes a fact has no matching dimension row. Suppose the shop adds order 107: a guest paid 12.00 on 2026-01-21 and gave no customer. This order is only for this section.

In the fact table, customer_key is NULL. An ordinary join can not match NULL to anything. Add up the paid revenue through the join:

SELECT SUM(f.amount) AS revenue
FROM fact_orders AS f
JOIN dim_customer AS c ON c.customer_key = f.customer_key
WHERE f.status = 'paid';

The fix is an unknown row. Add one row to dim_customer with key 0 and the name Unknown.

Then load order 107 with customer_key 0. This query is the same as before:

INSERT INTO dim_customer (customer_key, customer_id, name, country)
VALUES (0, NULL, 'Unknown', 'Unknown');

SELECT c.country, SUM(f.amount) AS revenue
FROM fact_orders AS f
JOIN dim_customer AS c ON c.customer_key = f.customer_key
WHERE f.status = 'paid'
GROUP BY c.country
ORDER BY revenue DESC;

Dev is a real customer with no country. The guest is no customer at all. Both show as Unknown in the country column, so they fall in one group.

A LEFT JOIN would keep the row, but its country would be NULL, a blank group. Then every query must remember to use it. The unknown row fixes the data once.

A conformed dimension is one dimension table that many fact tables share. It has the same keys and the same meaning everywhere.

In the shop, dim_customer is used by both fact tables. A bus matrix shows this. A bus matrix lists the fact tables down the side and the dimensions across the top:

Because both facts share dim_customer, you can compare them by country. Revenue comes from one fact, and units from the other.

First sum each fact by country, then join the two small results:

WITH revenue AS (
  SELECT c.country, SUM(f.amount) AS revenue
  FROM fact_orders AS f
  JOIN dim_customer AS c ON c.customer_key = f.customer_key
  WHERE f.status = 'paid'
  GROUP BY c.country
),
units AS (
  SELECT c.country, SUM(i.quantity) AS units
  FROM fact_order_items AS i
  JOIN dim_customer AS c ON c.customer_key = i.customer_key
  GROUP BY c.country
)
SELECT u.country, u.units, r.revenue
FROM units AS u
LEFT JOIN revenue AS r ON r.country = u.country
ORDER BY u.units DESC;

This is called drilling across: you sum each fact table alone, then join the two small results on the shared dimension. The WITH parts are CTEs, named steps from SQL chapter 4.

Join the two fact tables first, and order 101 appears twice, once for each item line. Its 40.00 then counts twice.

The words must match too. If another team builds its own customer table with India instead of IN, the two reports will not line up.

Each exercise opens a drawing board. You draw the tables, and the check compares them with sample rows.

Start with the first three. The last two are harder, and are marked optional.