learning-it by example

Back to the video

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

  1. 0:00 One big table: almost 5 times the size, 66,082 rows to rename
  2. 0:30 The idea
  3. 1:09 Setup
  4. 1:29 Making the data
  5. 1:57 Star and snowflake
  6. 2:25 One big table
  7. 2:40 One question, three queries
  8. 3:02 Timing, kept loose
  9. 3:26 Run each measure
  10. 5:04 What this does not show
  11. 5:32 Mistakes to avoid
  12. 6:01 My take and next steps
  13. 6:24 Recap

Files

  • data.py (20 lines)
  • run.py (87 lines)
  • shapes.py (74 lines)

Download the code (zip)

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

Learning IT by Example.