Star Schemas: Fact and Dimension Tables for Reporting
Build a small star schema from the shop, answer reporting questions with simple joins and reconcile the result against the source.
In this lesson you will
- Explain facts, dimensions and grain
- Build a star from source tables
- Write reporting queries with single joins
- Reconcile a reporting copy
About 28 minutes
A different shape for a different job
The database in lessons 1 to 24 is built to run a shop: recording orders, changing stock, keeping customers. Such a design is tuned for many small changes, with each fact stored once. Now Maya’s accountant wants to answer big questions again and again: revenue by month and category, by city, by customer type. The shop tables can answer them, but with long joins. For reporting, many teams build a second copy of the data in a different shape, called a star schema. It is the standard layout for analysis and data warehouses, and it is simple.
Facts and dimensions
- A fact table holds the measurements, one row for each event. Here, one row for each shipped order line, with the quantity and the amount.
- Dimension tables hold the descriptions: products with their category, customers with their city, dates with their month.
- The fact table points to each dimension through a key. Drawn on paper, the fact table sits in the middle and the dimensions radiate out like the points of a star.
- The grain is the statement of what one fact row means. Choose it first, and never mix grains in one table.
Dimension tables are usually kept flat and a little repetitive on purpose, so that a report needs only one join to each, which makes questions simple to write. That is the opposite of the careful splitting in lesson 18, and it is fine, because a reporting copy is loaded by a controlled process and not edited by hand.
Build a star and ask questions
The setup for this lesson creates three dimensions and a fact table from the shop’s shipped orders, using the CREATE TABLE AS form. Five queries then use it. The fourth is the most important kind of query in any reporting project: a reconciliation, which checks the star against the source.
-- 1
SELECT COUNT(*) AS fact_rows, ROUND(SUM(amount), 2) AS revenue FROM fact_sales
-- 2
SELECT d.month, p.category, ROUND(SUM(f.amount), 2) AS revenue
FROM fact_sales AS f JOIN dim_date AS d ON d.date_key = f.date_key
JOIN dim_product AS p ON p.product_key = f.product_key
GROUP BY d.month, p.category ORDER BY d.month, p.category
-- 3
SELECT c.city, ROUND(SUM(f.amount), 2) AS revenue FROM fact_sales AS f
JOIN dim_customer AS c ON c.customer_key = f.customer_key GROUP BY c.city ORDER BY revenue DESC
-- 4
SELECT (SELECT ROUND(SUM(amount), 2) FROM fact_sales) AS star_total,
(SELECT ROUND(SUM(oi.qty * p.price), 2) FROM order_items AS oi JOIN orders AS o ON o.id = oi.order_id
JOIN products AS p ON p.id = oi.product_id WHERE o.status = 'shipped') AS source_total
-- 5
SELECT COUNT(*) AS rows_in_fact, COUNT(DISTINCT order_id || '-' || product_key) AS distinct_lines FROM fact_sales
-- 1
fact_rows | revenue
15 | 257.00
-- 2
month | category | revenue
2026-06 | care | 5.50
2026-06 | plant | 43.00
2026-06 | pot | 17.00
2026-07 | plant | 18.00
2026-07 | pot | 15.00
2026-08 | care | 15.00
2026-08 | plant | 30.50
2026-08 | pot | 8.50
2026-09 | care | 5.50
2026-09 | plant | 36.00
2026-10 | plant | 37.50
2026-10 | pot | 25.50
-- 3
city | revenue
Leeds | 116.50
Bath | 89.50
York | 51.00
-- 4
star_total | source_total
257.00 | 257.00
-- 5
rows_in_fact | distinct_lines
15 | 15
The fact table has 15 rows, one for each shipped order line, and they add up to 257.00, the shipped revenue found in lesson 10. The second query slices the facts by month and category with just two joins, and gives 12 rows. The biggest cell is 2026-06 in plant, with 43.00. The third query slices by city and finds Leeds on top with 116.50, then Bath with 89.50 and York with 51.00. The fourth query compares the star total, 257.00, with the total worked out from the original tables, 257.00. They match, which is the evidence that the copy was built correctly. The fifth query checks the grain: 15 rows in the fact table and 15 different order-and-product pairs, so each pair appears once.
Rules for a trustworthy reporting copy
- Reconcile every load. Compare totals and row counts with the source, every time it is built.
- Document the grain and every filter. Here only shipped orders are included. Anyone using the table must know that.
- Keep keys stable. A dimension row’s key should not change when the description changes.
- Rebuild, do not hand-edit. The reporting copy should be reproducible from the source.
- Add dimensions when questions need them. Do not guess every future question in advance.
Real warehouses add more: dates down to the day with weekday and holiday flags, history of how descriptions changed over time, and very large volumes with special storage. The core idea stays the same: facts in the middle, descriptions around them, and checks at every step.
Mixing grains, for example putting order totals and order line amounts in one fact table. Totals then get counted once per line, and the report quietly inflates. One grain per fact table.
The shop data is made up, in sample money. The star was built in SQLite with the CREATE TABLE AS form. Real warehouse tools, loading methods and syntax differ from system to system. This lesson shows the ideas and not a production design.
Practice for this lesson
Check what you learned
Five quick questions. Pick an answer and you will see straight away whether it is right and why.
What does a fact table hold?
- Descriptions only
- Passwords
- Measurements, one row per event
- Indexes
One row per shipped order line, with quantity and amount.
What does a dimension table hold?
- Descriptions such as product category or customer city
- Totals
- Constraints
- Backups
They give context to the facts.
What is the grain?
- The size of the disk
- What one fact row means
- The sort order
- The speed of a query
Here, one shipped order line.
Why reconcile the star with the source?
- To make it smaller
- To add dimensions
- To rename columns
- To prove the copy was built correctly
Both totals were 257.00.
Why is mixed grain dangerous?
- It makes tables too small
- Totals get counted several times and inflate
- It breaks keys
- It slows backups
An order total repeated on each line is added up too often.
Summary
- Facts are measurements; dimensions are descriptions.
- Decide the grain first.
- A star makes reports simple.
- Reconcile every load against the source.
Questions
Is a star schema the same as normalisation?
No. It is almost the opposite on purpose. Operational databases split data to avoid repetition, while reporting copies flatten dimensions to make questions easy.
Do I need a data warehouse?
Not for a small shop. A star in the same database, or a few summary queries, may be enough. Choose tools by the volume and the questions.
Is the schema here what real warehouses use?
It shows the core idea. Real ones add history, dates, loading controls and more.
