learning-it by example

#256 Data modelling 6.6 min

Star Schema from Scratch: Facts, Dimensions and Grain

Nine order lines, one fact table, three reports, and every report adds up to 64.

1. In a Kimball-style star schema, what does the grain of a fact table say?
2. After building a SQLite star schema from nine made-up order lines, what totals did the check print for the source and for the fact table?
3. In a SQLite star schema built from nine made-up order lines placed between January and June, why is there no March row in the sales by month report?
4. In a SQLite star schema with one fact table and three dimensions, what makes the reports by month, by category and by region and quarter follow one pattern?
5. In a star schema fact table whose grain is one row per product on one order, how does SQLite stop a product from appearing twice within one order?

Practice only: nothing is saved and no score counts towards anything. Each answer comes with the reason.

  1. Predict the build and reports

    Copy the code folder first. Read source.py and star.py, then write down the row counts and the two totals you expect from python run.py build, and the month totals from python run.py reports. Run both and compare. You should see 9 fact rows and 64 in both totals.

    A hint

    Each fact row is quantity times the product price.

  2. Add an order in March

    In source.py, add one more line to LINES: ("O7", "2026-03-10", "c2", "p1", 3). Predict the new row counts and totals, then run python run.py build and python run.py reports. You should see 7 date rows, 10 fact rows, and 70 for both totals, and a 2026-03 value of 6 in the monthly report. Remove the added line again afterwards, because the next task expects the original nine lines.

    A hint

    Three pens at 2 pounds each is 6.

  3. Write a sales by product report

    In reports.py, add a fourth report called sales by product. Join fact_sales to dim_product, group by p.name, order by p.name, and sum f.amount, following the pattern of the category report. Run python run.py reports. You should see Mug 24, Pad 24, Pen 16, which adds up to 64.

    A hint

    Copy the sales by category entry and change the column you group by.

  4. Break the grain

    In source.py, add a repeat of the first line to LINES: ("O1", "2026-01-05", "c1", "p1", 2). Predict what happens, then run python run.py build. The script should stop with sqlite3.IntegrityError: UNIQUE constraint failed: fact_sales.order_id, fact_sales.product_key. Remove the line afterwards.

    A hint

    The fact table key is the order and the product together.

These tasks are for you to try on your own machine. Nothing is checked or saved.

What this does not show

Nine made-up order lines in SQLite; it does not measure speed or cover slowly changing dimensions.

Up next in Data modelling

More in Data modelling

All Data modelling videos

Related videos