Building dim_date: Kiosko's calendar

Description

This is the lesson that closes the debt module 1 named explicitly in its very first paragraph: "there's no dim_date." In this lesson you actually build it — a pure Python function, generate_date_dim(start_date, end_date), that produces one row per calendar day within a fixed range, with columns ready to answer any business question by day, week, month, or weekend, without ever recalculating anything twice.

Connection to the module. This lesson builds the star's fourth table — the only one that was completely missing. Together with store_key and product_key from the previous lesson, dim_date leaves all three dimensions ready for the final assembly in lesson 7.

An analogy: the wall calendar, printed once

Think of one of those wall calendars hung in an office on January 1st: twelve months, each day with its own box, its day-of-week name already printed, weekends already marked in a different color. Nobody builds that calendar reacting to the events that are going to occur — it gets printed complete, all at once, before any event that will be recorded on it exists. The calendar is useful precisely because it's independent: it works for jotting down a meeting, a birthday, or a holiday, without any of those events having had to "generate" the day it occurs on.

dim_date is that wall calendar. It gets generated once, for a complete date range, without looking at a single row of fact_orders — in fact, this lesson's example is going to generate an entire month of August 2026, not just the seven days Kiosko had sales. That independence is the most important property of a well-built calendar dimension: any new fact Kiosko adds in the future — browsing sessions, store activity, whatever — can join against this same dim_date, with nobody having to regenerate or adjust it.

Worked example: generate_date_dim(), in pure Python

This lesson's central function uses no external library — only datetime.date and datetime.timedelta from Python's standard library — and doesn't depend on any system date: it takes a fixed range, start_date and end_date, and always produces the same list of rows for that same range, no matter when you run it.

# dim_date.py
from datetime import date, timedelta

import duckdb

DAY_NAMES = ["Monday", "Tuesday", "Wednesday", "Thursday", "Friday", "Saturday", "Sunday"]


def generate_date_dim(start_date: str, end_date: str) -> list[dict]:
    """Generates fixed dim_date rows between start_date and end_date (both inclusive, YYYY-MM-DD format).

    Pure Python, no datetime.now() and no other source of non-determinism: the same
    start_date/end_date always produces, byte for byte, the same list of rows.
    """
    start = date.fromisoformat(start_date)
    end = date.fromisoformat(end_date)
    if end < start:
        raise ValueError(f"end_date ({end_date}) is before start_date ({start_date})")

    rows = []
    current = start
    while current <= end:
        weekday_index = current.weekday()  # 0=Monday ... 6=Sunday, independent of the system locale
        rows.append({
            "date_key": int(current.strftime("%Y%m%d")),
            "calendar_date": current,
            "day_of_week": DAY_NAMES[weekday_index],
            "month": current.month,
            "quarter": (current.month - 1) // 3 + 1,
            "year": current.year,
            "is_weekend": weekday_index >= 5,
        })
        current += timedelta(days=1)
    return rows


dim_date_rows = generate_date_dim("2026-08-01", "2026-08-31")
print(f"Total rows generated for August 2026: {len(dim_date_rows)}\n")

print("=== First 3 rows ===")
for row in dim_date_rows[:3]:
    print(row)

print("\n=== Last 3 rows ===")
for row in dim_date_rows[-3:]:
    print(row)

What to expect. Running python3 dim_date.py, the output is exactly this:

Total rows generated for August 2026: 31

=== First 3 rows ===
{'date_key': 20260801, 'calendar_date': datetime.date(2026, 8, 1), 'day_of_week': 'Saturday', 'month': 8, 'quarter': 3, 'year': 2026, 'is_weekend': True}
{'date_key': 20260802, 'calendar_date': datetime.date(2026, 8, 2), 'day_of_week': 'Sunday', 'month': 8, 'quarter': 3, 'year': 2026, 'is_weekend': True}
{'date_key': 20260803, 'calendar_date': datetime.date(2026, 8, 3), 'day_of_week': 'Monday', 'month': 8, 'quarter': 3, 'year': 2026, 'is_weekend': False}

=== Last 3 rows ===
{'date_key': 20260829, 'calendar_date': datetime.date(2026, 8, 29), 'day_of_week': 'Saturday', 'month': 8, 'quarter': 3, 'year': 2026, 'is_weekend': True}
{'date_key': 20260830, 'calendar_date': datetime.date(2026, 8, 30), 'day_of_week': 'Sunday', 'month': 8, 'quarter': 3, 'year': 2026, 'is_weekend': True}
{'date_key': 20260831, 'calendar_date': datetime.date(2026, 8, 31), 'day_of_week': 'Monday', 'month': 8, 'quarter': 3, 'year': 2026, 'is_weekend': False}

Thirty-one rows — one day for every day of August 2026, not one more, not one fewer. Notice something deliberate about the chosen range: it fully covers Kiosko's order week (August 3rd to 9th), but goes well beyond it — August 1st and 2nd (Saturday and Sunday, with no orders recorded) also get their row, just like the rest of the month. That's precisely the independence the wall-calendar analogy talks about: dim_date wasn't generated from fact_orders — it was generated on its own, complete, ready for any date in the month anyone might need to query, whether or not it had sales that day.

Now, load those rows into DuckDB with CREATE TABLE dim_date AS SELECT * FROM dim_date_rows and check Kiosko's week within that complete calendar:

# dim_date_load.py -- continues over dim_date_rows generated above
con = duckdb.connect()
con.execute("""
    CREATE TABLE dim_date (
        date_key INTEGER,
        calendar_date DATE,
        day_of_week VARCHAR,
        month INTEGER,
        quarter INTEGER,
        year INTEGER,
        is_weekend BOOLEAN
    )
""")
con.executemany(
    "INSERT INTO dim_date VALUES (?, ?, ?, ?, ?, ?, ?)",
    [(r["date_key"], r["calendar_date"], r["day_of_week"], r["month"],
      r["quarter"], r["year"], r["is_weekend"]) for r in dim_date_rows],
)

print("=== dim_date loaded into DuckDB: total count ===")
print(con.sql("SELECT COUNT(*) AS total_dates FROM dim_date"))

print("=== dim_date: only Kiosko's order week (2026-08-03 to 2026-08-09) ===")
print(con.sql("""
    SELECT date_key, calendar_date, day_of_week, is_weekend
    FROM dim_date
    WHERE calendar_date BETWEEN '2026-08-03' AND '2026-08-09'
    ORDER BY calendar_date
"""))

print("=== dim_date: how many days are weekend days in August 2026 ===")
print(con.sql("SELECT is_weekend, COUNT(*) AS days FROM dim_date GROUP BY is_weekend ORDER BY is_weekend"))

What to expect. Running python3 dim_date_load.py, the output is exactly this:

=== dim_date loaded into DuckDB: total count ===
┌─────────────┐
│ total_dates │
│    int64    │
├─────────────┤
│          31 │
└─────────────┘

=== dim_date: only Kiosko's order week (2026-08-03 to 2026-08-09) ===
┌──────────┬───────────────┬─────────────┬────────────┐
│ date_key │ calendar_date │ day_of_week │ is_weekend │
│  int32   │     date      │   varchar   │  boolean   │
├──────────┼───────────────┼─────────────┼────────────┤
│ 20260803 │ 2026-08-03    │ Monday      │ false      │
│ 20260804 │ 2026-08-04    │ Tuesday     │ false      │
│ 20260805 │ 2026-08-05    │ Wednesday   │ false      │
│ 20260806 │ 2026-08-06    │ Thursday    │ false      │
│ 20260807 │ 2026-08-07    │ Friday      │ false      │
│ 20260808 │ 2026-08-08    │ Saturday    │ true       │
│ 20260809 │ 2026-08-09    │ Sunday      │ true       │
└──────────┴───────────────┴─────────────┴────────────┘

=== dim_date: how many days are weekend days in August 2026 ===
┌────────────┬───────┐
│ is_weekend │ days  │
│  boolean   │ int64 │
├────────────┼───────┤
│ false      │    21 │
│ true       │    10 │
└────────────┴───────┘

Stop at the first table showing Kiosko's week: the comments in raw_orders.py, since module 1, already called August 8th "Saturday" and August 9th "Sunday" — but that was a human annotation, written by hand in a code comment, never verified. Now dim_date confirms it independently, calculated, not copied: 2026-08-08 really is Saturday, 2026-08-09 really is Sunday, with is_weekend = true for both. This isn't a coincidence — it's the first time this guide verifies, with code, something that until now only existed as a human comment.

Diagram: dim_date's seven columns

┌──────────────────────────────────────────────────────────────────────┐
│  dim_date                                                              │
├──────────────┬───────────────────────────────────────────────────────┤
│ date_key     │ INTEGER, surrogate key -- YYYYMMDD (e.g. 20260803)     │
│ calendar_date│ DATE -- the actual date (e.g. 2026-08-03)              │
│ day_of_week  │ VARCHAR -- 'Monday'...'Sunday', calculated with no locale│
│ month        │ INTEGER -- 1 to 12                                    │
│ quarter      │ INTEGER -- 1 to 4, derived from month                 │
│ year         │ INTEGER -- e.g. 2026                                   │
│ is_weekend   │ BOOLEAN -- true if Saturday or Sunday                  │
└──────────────┴───────────────────────────────────────────────────────┘

Going deeper: date_key is a deliberate exception to lesson 3's rule

In the previous lesson you learned that a surrogate key "has no business meaning" — store_key = 1 tells you nothing about the store until you join it against dim_store. date_key, as this lesson built it, breaks that rule on purpose: date_key = 20260803 does have meaning — anyone looking at it recognizes "August 3rd, 2026," with no JOIN needed.

This is exactly what Kimball calls, in industry practice, a smart key, and it's the only widely accepted exception to the "the surrogate key shouldn't carry meaning" rule. The reason it's accepted specifically for dim_date: the calendar is, of all possible dimensions, the most stable and universally understood one that exists — a day never changes meaning, never gets renamed, never merges with another day. That absolute stability is what makes it safe to encode the meaning directly into the key, something that would be far riskier to do with, say, a store_key based on the store's name (which can change, as you saw in the previous lesson's exercise 3).

A second decision worth explaining: day_of_week was calculated with a fixed Python list (DAY_NAMES), indexed by date.weekday(), instead of using a date-formatting function that depended on the operating system's configured language. This is a reproducibility decision, not a style one: a locale-sensitive date function could return "lunes" on a machine configured for Spanish and "Monday" on another configured for English, for the exact same date — breaking the guarantee that this code always produces the same output, byte for byte, no matter which computer it runs on.

Common mistakes

Generating dim_date only for the date range fact_orders already has. What happens: someone, seeing that Kiosko only has orders between August 3rd and 9th, generates dim_date with exactly that seven-day range — not one day more. Why it happens: it seems more efficient to generate only what's "going to be used today," avoiding rows that have no associated order right now. How to spot it: if your dim_date has no row for dates with no sales — like August 1st and 2nd in this lesson — any future business question ("how many days without sales did Kiosko have this month?") becomes impossible to answer, because those days don't even exist in your calendar. How to fix it: dim_date gets generated for the full calendar range the business needs — typically months or years, well beyond what current data covers — exactly as this lesson did with the complete month of August instead of just the week with sales.

Calculating the day of the week with an OS-dependent function. What happens: someone uses current.strftime("%A") (the full day name, per the locale) instead of this lesson's fixed DAY_NAMES list. Why it happens: strftime("%A") seems simpler — one fewer line of code — and it works correctly on the machine where it's first tested. How to spot it: if you run the same script on two computers with different language settings and get different day names ("Monday" on one, "lunes" on the other), your code isn't reproducible — it directly violates this guide's rule that every "What to expect" must be identical, byte for byte, no matter where it runs. How to fix it: always use a fixed list indexed by date.weekday() (an integer from 0 to 6, which doesn't depend on any locale), as generate_date_dim() does in this lesson.

Confusing calculated quarter with fiscal quarter. What happens: someone assumes quarter = (month - 1) // 3 + 1 always corresponds to any company's fiscal quarter, without verifying that Kiosko — or any real business — uses the standard calendar (January-March, April-June...) for its financial reports. Why it happens: the calendar-quarter calculation is so common it's easy to assume it's universal. How to spot it: if a "Q1 sales" report doesn't match what a real company's finance team reports as its Q1, that company likely uses a shifted fiscal year (starting in April, July, or October, a common practice in many industries). How to fix it: for Kiosko, this guide assumes a standard calendar with no shifted fiscal year — a reasonable simplification for this case's scope — but in a real production warehouse, fiscal quarter (and year), when it differs from the calendar, gets added as explicit extra columns (fiscal_quarter, fiscal_year), never overwriting the calendar quarter dim_date already calculates.

Exercises

Exercise 1 — Generate dim_date for all of Q3 2026. Modify the call to generate_date_dim() to cover the full range "2026-07-01" to "2026-09-30" (July, August, and September), and confirm how many total rows it generates and how many of those rows are weekend days.

q3_rows = generate_date_dim("2026-07-01", "2026-09-30")
print(f"Total Q3 2026 rows: {len(q3_rows)}")
weekend_q3 = sum(1 for r in q3_rows if r["is_weekend"])
print(f"Weekend days in Q3 2026: {weekend_q3}")

Total Q3 2026 rows: 92
Weekend days in Q3 2026: 26

Exercise 2 — Verify each date_key matches its calendar_date exactly. Using dim_date already loaded into DuckDB, write a query that confirms, for every row, date_key (as text) matches calendar_date formatted as YYYYMMDD — a check that the smart key never drifts out of sync with the real date it represents.

print(con.sql("""
    SELECT COUNT(*) AS out_of_sync_rows
    FROM dim_date
    WHERE CAST(date_key AS VARCHAR) != strftime(calendar_date, '%Y%m%d')
"""))

┌──────────────────┐
│ out_of_sync_rows │
│      int64       │
├──────────────────┤
│                0 │
└──────────────────┘

Exercise 3 — Explain in your own words why date_key is an exception, not a general rule. Using this lesson's "going deeper" section, explain in 2-3 sentences why it would be a bad idea to apply the same "meaningful key" pattern to store_key, encoding, say, the city name inside the number.

Summary and next step

In this lesson you built dim_date end to end: generate_date_dim(start_date, end_date) in pure Python, with no source of non-determinism, generating 31 rows for August 2026 — a complete month, not just the seven days with orders — loaded into DuckDB with CREATE TABLE dim_date AS SELECT * FROM dim_date_rows. You verified, with a real query, that August 8th and 9th really are weekend days — confirming, for the first time with code, something that until now was only a human comment in raw_orders.py. You also learned that date_key is the only accepted exception to the "surrogate key with no meaning" rule, precisely because the calendar is the most stable dimension there is.

Before moving on you should be able to: write generate_date_dim()'s signature and its seven output columns from memory; explain why dim_date gets generated independently of fact_orders, not derived from it; and justify why date_key can be a smart key when store_key shouldn't be.

With all three dimensions complete — dim_store, dim_product with a surrogate key, dim_date freshly built — lesson 5 takes a conceptual step: what it means for a dimension to be conformed, and why dim_store and dim_date, as this module leaves them, are ready to serve a second business process that doesn't exist yet.

Resources

• Kimball Group — "Star Schema / OLAP Cube" — the source that documents dim_date as the most universal conformed dimension in any dimensional warehouse. kimballgroup.com/data-warehouse-business-intelligence-resources/kimball-techniques/dimensional-modeling-techniques/star-schema-olap-cube. In English.
• Python — official datetime documentation, the only library used for generate_date_dim(): always fixed dates, never datetime.now(). docs.python.org/3/library/datetime.html. In English.
• DuckDB — date formatting function documentation (strftime), used to build date_key and to verify its sync with calendar_date in this lesson. duckdb.org/docs/current/sql/functions/dateformat. In English.