#260 Data modelling 6.5 min
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.
Run the three views
Copy the code folder first. Run python run.py checks, then python run.py sample, then python run.py report. Before each run, predict the number of day rows, the fiscal period of 1 April 2026 and the number of holidays. Compare your predictions with the output.
A hint
Each command reads the same table. The holidays come from the four rows in HOLIDAYS in date_dim.py.
Add one more day off
In date_dim.py, add a fifth entry to HOLIDAYS: ("2026-05-25", "Extra day off"). Predict how the holiday count and the working days for the first fiscal quarter of 2027 will change, then run python run.py report. When I ran it the holiday count became 5 and FY2027 Q1 went from 64 to 63 working days.
A hint
Holidays live in a table, so no SQL query needs to change.
Stretch the table to two years
In date_dim.py, change the end date '2026-12-31' in the recursive query to '2027-12-31'. Predict the number of day rows, then run python run.py checks. When I ran it, there were 730 day rows, no missing dates, no duplicates and 730 of 730 weekday names matching Python.
A hint
The rule that stops the recursion is the where clause comparing the date with the end date.
These tasks are for you to try on your own machine. Nothing is checked or saved.
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.
Up next in Data modelling
More in Data modelling
- #110 Slowly Changing Dimensions in SQL: Type 1 vs Type 2
- #130 Define a Metric Once: Views and a Metrics Layer
- #250 Data Modelling Basics: Entities, Keys, Relationships
- #252 Normalisation 1NF to 3NF on a Messy Spreadsheet
- #253 Primary Keys: Natural, Surrogate and Hash Keys
- #254 Constraints That Catch Bad Data: PK, FK, CHECK
