learning-it by example

Back to the video

Build a Date Dimension in 20 Lines of SQL

Twenty lines of SQL, plus a small holiday table, build 365 days with fiscal periods, and the checks pass.

Video #260 ยท Data modelling

Steps in the video

  1. 0:00 365 days, 12 columns, 20 lines of SQL
  2. 0:32 The idea
  3. 1:08 Setup
  4. 1:28 Holidays and the build
  5. 1:46 Generating every day
  6. 2:16 Deriving the columns
  7. 2:52 Checking the table
  8. 3:19 Run the checks
  9. 4:41 What this does not show
  10. 5:06 Mistakes to avoid
  11. 5:39 My take and next steps
  12. 6:04 Recap

Files

  • date_dim.py (41 lines)
  • run.py (52 lines)

Download the code (zip)

Tested with

Python 3.12.15

What this does not show

One calendar year in SQLite with made-up holidays and fiscal rule; no ISO weeks, time of day or multi-year range.

Sources

Learning IT by Example.