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
Files
- date_dim.py (41 lines)
- run.py (52 lines)
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
- https://docs.python.org/3.12/library/datetime.html
- https://learn.microsoft.com/en-us/power-bi/guidance/model-date-tables
- https://www.kimballgroup.com/data-warehouse-business-intelligence-resources/kimball-techniques/dimensional-modeling-techniques/calendar-date-dimension/
- https://www.sqlite.org/lang_datefunc.html
- https://www.sqlite.org/lang_with.html
Learning IT by Example.