learning-it by example

#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.

1. Why build a date dimension table for reporting?
2. In a SQLite date dimension for 2026, the fiscal year starts in April and is named by the year it ends. What fiscal year and fiscal period does 1 April 2026 get?
3. A SQLite script built a date dimension with one row per day for 2026. How many day rows did its checks report, and what did the span comparison show?
4. Why does the SQLite date dimension code take day and month names from fixed strings instead of the machine's language settings?
5. How does the recursive query in the SQLite date dimension generate every day of 2026?

Practice only: nothing is saved and no score counts towards anything. Each answer comes with the reason.

  1. 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.

  2. 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.

  3. 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

All Data modelling videos

Related videos