learning-it by example

#261 Data modelling 6.9 min

Star, Snowflake or One Big Table: Measured in SQLite

One big table took almost 5 times the space, and renaming one category touched 66,082 rows.

1. The same 200,000 made-up sales were stored as a star schema, a snowflake and one big table in SQLite. Which shape took by far the most space?
2. What is the difference between a star schema and a snowflake schema?
3. In the SQLite comparison, how many rows had to change to rename one category in each shape?
4. In the video's SQLite run at the default 4096-byte page size, the snowflake saved no space compared with the star. What happened to its size, and why?
5. The one big table answered the test query faster than the star and the snowflake in the video's SQLite run. What does the video say that result means?

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

  1. Predict the four runs

    Copy the code folder first. Read run.py and shapes.py, then predict which of the commands size, same, rename and time shows the largest difference between the shapes. Run each command with python run.py followed by its name and check your guess. The time command prints only yes or no answers, so your timings may differ from mine.

    A hint

    The big table copies every label onto every sale.

  2. Shrink the data to 50,000 sales

    In data.py, change FACT_ROWS from 200_000 to 50_000. Predict whether the big table is still at least 4 times the size of the star, then run python run.py size. When I ran it, the star was 962 kB, the snowflake 966 kB and the big table 4,558 kB, and both checks still printed True. Change FACT_ROWS back to 200_000 afterwards, because the next tasks expect the full data.

    A hint

    Only the number of sales changes. The dimension tables stay the same size.

  3. Ask about July instead of June

    In run.py, change '2026-06' to '2026-07' in the JUNE line near the top. Predict whether the three shapes still give one answer, then run python run.py same. When I ran it, the first row was ('Garden', 'East', 28560) for all three shapes and the check printed True.

    A hint

    The JUNE line holds the month filter that all three queries use.

  4. Rename a bigger category

    In run.py, replace the word Kitchen with Garden in the RENAME dictionary, in all three statements. Predict which shape changes the most rows, then run python run.py rename. When I ran it, the star changed 200 rows, the snowflake 1 row and the big table 66,710 rows.

    A hint

    Garden is a different category from Kitchen, so the number of sales rows in the big table differs.

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

What this does not show

Made-up data in SQLite, not DuckDB; row counts are exact, kB depend on SQLite's page size, and speed is a yes or no answer from one machine for this toy query only.

Up next in Data modelling

More in Data modelling

All Data modelling videos

Related videos