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.
Video #261 ยท Data modelling
Steps in the video
- 0:00 One big table: almost 5 times the size, 66,082 rows to rename
- 0:30 The idea
- 1:09 Setup
- 1:29 Making the data
- 1:57 Star and snowflake
- 2:25 One big table
- 2:40 One question, three queries
- 3:02 Timing, kept loose
- 3:26 Run each measure
- 5:04 What this does not show
- 5:32 Mistakes to avoid
- 6:01 My take and next steps
- 6:24 Recap
Files
- data.py (20 lines)
- run.py (87 lines)
- shapes.py (74 lines)
Tested with
Python 3.12.15
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.
Sources
- https://learn.microsoft.com/en-us/power-bi/guidance/star-schema
- https://www.kimballgroup.com/data-warehouse-business-intelligence-resources/kimball-techniques/dimensional-modeling-techniques/dimension-table-structure/
- https://www.kimballgroup.com/data-warehouse-business-intelligence-resources/kimball-techniques/dimensional-modeling-techniques/snowflake-dimension/
- https://www.kimballgroup.com/data-warehouse-business-intelligence-resources/kimball-techniques/dimensional-modeling-techniques/star-schema-olap-cube/
- https://www.sqlite.org/pragma.html
Learning IT by Example.