Fact Table Design

The last two chapters gave you the grain sentence (what one row means) and the five shop tables. This chapter designs a fact table and solves the problems that come next.

A warehouse is a database built for reports. To load is to copy data into it.

We use the shop from the SQL chapters. This is its orders table:

Money is in the amount column. Order 105 is pending, so its amount is empty.

Omar (customer 5) has no order. Dev has no country. Both matter later in this chapter.

Then this chapter solves eight problems that real fact tables meet. The recap table lists them.

What you will learn

1. Use the four design steps on a new fact table.
2. Add up measures correctly, and keep an amount at order level.
3. Pick the right type of fact table.
4. Store a refund. Handle an empty measure and an unknown country.
5. Load a late fact. Store a fact with no number. Track an order journey.

The last chapter named four design steps: process, grain, dimensions, facts. Here you use them on a new question.

The two fact tables from the last chapter are below. They use customer_key 11 to 15 and product_key 21 to 25. The dimension tables are the same.

Order 105 has no item rows, because it has no items yet. Start with a simple question: how many orders did each customer place?

SELECT c.name, COUNT(*) AS orders
FROM fact_orders f
JOIN dim_customer c ON c.customer_key = f.customer_key
GROUP BY c.name
ORDER BY c.name;

A new table: the money events

Now the finance team asks for sales and refunds. Design a third fact table with the four steps.

You will fill this table in the refunds section. Notice the order. The grain sentence came before any column.

order_key has no dimension table. Last chapter called it a degenerate dimension.

It becomes a real dimension when it gains attributes. Ask what the id describes. order_id describes nothing: it has no name and no category.

Suppose the shop starts to save the payment method for each order. Now the order has an attribute to group by. This dim_order table is an example:

The fact rows keep order_key. It now points to dim_order, so you can group revenue by payment method. Without an attribute, keep the id on the fact and build no table.

Not every number can be added across customers, products and days. Last chapter named three kinds of measure.

The hard case is the average. The four customer averages are 32.50, 60.00, 15.50 and 80.00.

Their average is 47.00. That is wrong.

Asha has two orders and the others have one. The average of averages gives every customer the same weight.

The fix is to store the parts: the sum of the amounts, and the count of orders that have an amount. Then divide the two sums when you ask the question.

SELECT SUM(amount)               AS amount_sum,
       COUNT(amount)             AS amounts,
       SUM(amount) / COUNT(amount) AS average
FROM fact_orders;

For all customers: 220.50 / 5 = 44.10. COUNT(amount) skips order 105, because its amount is NULL.

The amount belongs to the whole order. Order 101 is 40.00 for two items. It does not say how much money each item cost.

It is tempting to copy the amount onto every item row. This is the fan-out mistake from the grain chapter, now inside a column. Look at the total:

SELECT SUM(o.amount) AS total
FROM fact_order_items f
JOIN fact_orders o ON o.order_key = f.order_key;

The join does the copying: each item row gets its order’s amount. The true total is 220.50.

Orders 101 and 106 have two item rows each, so their amounts count twice. (2 × 40.00) + 25.00 + 60.00 + 15.50 + (2 × 80.00) = 340.50.

Choice 1: keep two fact tables

Keep the amount in fact_orders and the quantity in fact_order_items. Each table has one grain. This is what we built.

Do not join the two tables row by row to add an amount. Sum each table first, then join the results.

Choice 2: allocate the amount

Sometimes you need an amount on each item. Then you split the order amount by a rule that the business agrees. This is allocation.

The shop has no prices, so we split by quantity. Each item gets its share of the units, times the order amount.

• Order 101 has 3 units. Pen gets 2/3: 40.00 × 2 / 3 = 26.666…, which rounds to 26.67.
• Notebook gets 1/3: 40.00 × 1 / 3 = 13.333…, which rounds to 13.33.
• Order 106 has two single units. Each item gets half: 80.00 / 2 = 40.00.
• Orders with one item row keep the whole amount.

Check the sum: 26.67 + 13.33 + 25.00 + 60.00 + 15.50 + 40.00 + 40.00 = 220.50. The pieces add back to the true total.

There are three types of fact table. They differ in what one row means and in how the table grows. You choose by the question that must be fast and easy to answer.

You have seen the first type. A transaction fact is the default. It keeps the most detail, and its rows are not changed.

Periodic snapshot

A periodic snapshot has one row for each customer for each period, even when nothing happened. Here the period is January 2026. Omar has no orders, but he still gets a row.

SELECT c.customer_key,
       COUNT(f.order_key) AS orders,
       COALESCE(SUM(f.amount), 0) AS amount
FROM dim_customer c
LEFT JOIN fact_orders f
       ON f.customer_key = c.customer_key
      AND f.date_key BETWEEN 20260101 AND 20260131
GROUP BY c.customer_key
ORDER BY c.customer_key;

Asha has orders 101 and 102: 40.00 + 25.00 = 65.00. Carla has two orders, but order 105 has no amount, so her total is 15.50.

Omar has 0 orders and 0.00. This zero is real: we measured it. A transaction fact has no row for Omar, so a question like "who was inactive" is hard there.

Semi-additive in a snapshot

Snapshots often hold levels, like stock. These are the stock rows from the last chapter:

On 2026-01-11 you can add across products: 20 + 5 = 25 units. Do not add across days. All four rows give 51, and that counts the 20 Pens twice.

Order 103 was paid, then refunded. You can store this in two ways. The choice decides which questions you can still answer.

Way 1: a status

The order row has a status column. When the refund happens, you change paid to refunded. This is simple, and the history is gone.

Now you cannot tell that the shop sold 60.00 on 2026-01-11. You also cannot tell when the money went back.

Way 2: a negative row

For money, never change a row that records an event. Add a new row with a negative amount.

The sale stays. The refund is its own event on its own day.

This is fact_order_money, the third fact table. Its grain is one money event on one order.

The refund of order 103 happened on 2026-01-16. That date is new data for this chapter.

Order 105 has no row. Nothing has happened to the money yet. Now ask for the money by row type.

SELECT row_type, SUM(amount) AS total
FROM fact_order_money
GROUP BY row_type
ORDER BY row_type;

Sales are 40.00 + 25.00 + 60.00 + 15.50 + 80.00 = 220.50. Sales on 2026-01-11 are still 60.00. The net money is one plain SUM of the whole column:

SELECT SUM(amount) AS net
FROM fact_order_money;

The net is 220.50 - 60.00 = 160.50. A returned item works the same way: add a row to fact_order_items with quantity -1.

Order 105 is pending. Its amount is NULL.

NULL means "no value". Decide what NULL means in a measure before the first load.

The tempting fix is to turn NULL into 0. See what it does. The third column turns NULL into 0.

SELECT SUM(amount)                AS total,
       AVG(amount)                AS avg_with_null,
       AVG(COALESCE(amount, 0))   AS avg_with_zero
FROM fact_orders;

SUM skips NULL, and adding 0 changes nothing, so the total is 220.50 either way. The average is not the same.

With NULL, five amounts count: 220.50 / 5 = 44.10. With zero, six count: 220.50 / 6 = 36.75.

A zero says "this order was free". It was not.

It is not paid yet. Use 0 only when you really measured zero, like Omar’s 0 orders in the snapshot.

One more edge. A group whose amounts are all NULL gives SUM = NULL.

COALESCE(SUM(amount), 0) shows 0. Do that only in the final report, not in the stored table.

Last chapter used key 0 for a guest order with no customer. Here the missing value is Dev’s country.

Many teams use -1 (or 0) for the unknown row. The database does not require it.

We give countries their own small table, dim_country, because many tables use a country. dim_customer gets a country_key that points to it. Here is the Unknown row:

dim_customer keeps the shop’s country code, so Dev’s is NULL. The load fills country_key in two steps. Look at the rows before and after:

UPDATE dim_customer
SET country_key = (SELECT d.country_key FROM dim_country d
                   WHERE d.country_code = dim_customer.country)
WHERE country IS NOT NULL;

UPDATE dim_customer
SET country_key = -1
WHERE country IS NULL;

The first UPDATE finds each code in dim_country. NULL matches nothing, so it needs the second UPDATE. Dev gets -1.

Now join fact_orders to dim_customer to dim_country. Then every fact has a dimension row to point to:

SELECT d.country_name, SUM(f.amount) AS total
FROM fact_orders f
JOIN dim_customer c ON c.customer_key = f.customer_key
JOIN dim_country d  ON d.country_key = c.country_key
GROUP BY d.country_key, d.country_name
ORDER BY d.country_key;

All four groups add up to 220.50. India is orders 101 and 102: 40.00 + 25.00. The United States is only order 104, because order 105 has no amount.

Skip the second UPDATE and Dev’s country_key stays NULL. The join then drops order 106: 220.50 - 80.00 = 140.50. No error appears.

A late fact is an event that reaches the warehouse after its day. Order 106 was placed on 2026-01-21. Suppose it is loaded on 2026-01-23, two days late.

Suppose fact_orders also has a loaded_date_key column. The other paid orders are loaded on their own day. The load date is new data for this chapter.

The date_key is when the order happened. The loaded_date_key is when the warehouse got it. Place the row by the first one:

SELECT date_key, SUM(amount) AS total
FROM fact_orders
WHERE status = 'paid'
GROUP BY date_key
ORDER BY date_key;

Group by the load day, and the sale moves to the 23rd. The 21st is empty forever:

SELECT loaded_date_key, SUM(amount) AS total
FROM fact_orders
WHERE status = 'paid'
GROUP BY loaded_date_key
ORDER BY loaded_date_key;

A report run on the 22nd showed nothing for the 21st. After the load it shows 80.00. That is correct, but people ask why the number changed.

Use loaded_date_key to find new rows to load, and to explain why a number changed.

Keep dim_date filled for whole years, so a late fact always finds its day. A dimension row that arrives late gets the Unknown row for now. The next chapter shows how to fix it.

Some events have no number. A person viewed a product page.

A promotion applied to an order. The event is the fact.

A factless fact table has only keys, with no measure. You count the rows. There are two kinds: an event table, and a coverage table.

An event table records that something happened. This one has one row for each product page view. The rows are new data for this chapter.

Asha (11) viewed Pen (21) and Notebook (22) on 2026-01-02. Count the views for each product. Start from dim_product, so that a product with no views still appears.

SELECT p.name, COUNT(v.product_key) AS views
FROM dim_product p
LEFT JOIN fact_page_views v ON v.product_key = p.product_key
GROUP BY p.product_key, p.name
ORDER BY p.product_key;

Pen has two view rows. Chair has none, so it shows 0.

A query on fact_page_views alone would not show Chair at all. A missing event leaves no row.

A coverage table lists what could have happened, such as every product on sale. A coverage table helps you find what did not happen. Here dim_product played that role: it is why Chair shows 0.

An order moves through steps: ordered, paid, shipped, delivered. An accumulating snapshot keeps one row for the whole journey. The row is updated each time a step is done.

Each step has a date. A step that is not done yet is NULL.

The dates are shown as dates here, but the table stores date keys such as 20260122. The rows are new data for this chapter.

The days_to_deliver column is the number of days from ordered to delivered. 2026-01-07 minus 2026-01-03 is 4 days. You store it so nobody has to compute it each time.

Order 106 is paid but not shipped. On 2026-01-22 it ships.

You update the same row. You do not add a new one.

UPDATE fact_order_journey
SET shipped = '2026-01-22'
WHERE order_key = 106;

The table still has 6 rows. Only the shipped cell changed.

In a real load, find the row by order_key. If the same event arrives twice, the row stays the same.

Now ask: how long does delivery take? Use the table above.

SELECT AVG(days_to_deliver) AS avg_days
FROM fact_order_journey;

AVG skips NULL, so only the 3 delivered orders count: (4 + 3 + 4) / 3 = 3.67 days.

You used the four steps on a new fact table. Then you solved eight problems. The first four are about numbers and money.

The last four are about keys, time and steps.

Each exercise opens a drawing area. You draw the tables, and the review checks them against sample rows.

Start at the top. One exercise is harder and is marked optional.