Date Dimension Rich Attributes, Fiscal Calendars, Holidays, Weeks, and Why Date Is More Than a Timestamp

Learning outcomes

AtlasMart's finance team wants fiscal-period sales, operations wants weekday/weekend behavior, and fulfillment wants “orders due this week.” A raw timestamp can tell the engine an instant, but it does not by itself define AtlasMart's fiscal calendar, company holidays, ISO-week year, business-day labels, or special unknown/not-applicable semantics. The practical problem is therefore not formatting a timestamp; it is publishing one governed calendar vocabulary every analytical process can reuse.

1. Declare the date dimension grain before generating attributes

Grain: one row per AtlasMart calendar date, plus explicitly labeled non-date special members. That grain lets every fact row reference exactly one business date for a given role. Attributes such as month name, fiscal period, weekday, ISO week, and holiday label describe that date; they are not separate facts and do not change the row grain.

AtlasMart uses an April–March fiscal year for this course fixture. April 2026 through March 2027 is labeled FY2027, and fiscal period 1 is April. This is a synthetic company policy, not a legal/accounting standard. A real implementation must obtain the fiscal calendar from finance and version changes deliberately.

2. DDL: one governed calendar table plus special members

PRAGMA foreign_keys = ON;DROP TABLE IF EXISTS dim_date;CREATE TABLE dim_date (  date_sk INTEGER PRIMARY KEY,  full_date TEXT UNIQUE,  date_type TEXT NOT NULL,  calendar_year INTEGER,  calendar_quarter INTEGER,  month_number INTEGER,  month_name TEXT,  day_of_month INTEGER,  weekday_number INTEGER,  weekday_name TEXT,  iso_year INTEGER,  iso_week INTEGER,  fiscal_year INTEGER,  fiscal_period INTEGER,  is_weekend INTEGER NOT NULL CHECK (is_weekend IN (0,1)),  holiday_name TEXT);

date_sk = YYYYMMDD is convenient and readable, but filtering/grouping still uses governed dimension attributes. The smart-looking key must not become a substitute for fiscal or holiday semantics. The special keys 0 and -1 deliberately do not pretend to be real dates.

3. Why timestamps alone are insufficient

2026-09-20T23:30:00Z is an instant. “Fiscal period 6,” “Sunday,” “weekend,” and “AtlasMart fiscal year 2027” are business/calendar interpretations. A timestamp also cannot tell whether a future ship date is unknown, not applicable, not yet happened, or corrupted unless the model carries an explicit policy.

The same UTC instant can map to different local dates in different business time zones. Therefore source timestamp → governed business time zone → business date → date_sk is the safe conceptual sequence. This lab begins after that conversion and stores business dates explicitly; it does not pretend SQLite's local environment proves global time-zone policy.

4. Controlled failure: derive every reporting calendar from ad-hoc SQL

A team might let each dashboard compute week number, fiscal period, and “holiday” independently from timestamps. The visible failure is semantic drift: one report can use calendar year while another uses fiscal year, one can use ISO weeks while another uses a database-specific week function, and holiday logic can diverge by jurisdiction or business unit.

-- This hard-codes one report's private interpretation.SELECT event_date,       CASE WHEN CAST(substr(event_date,6,2) AS INTEGER) >= 4            THEN CAST(substr(event_date,1,4) AS INTEGER)+1            ELSE CAST(substr(event_date,1,4) AS INTEGER)       END AS fiscal_yearFROM fact_sales_history;

Repair: publish fiscal/calendar attributes once in dim_date, govern the rule with finance/operations, and make BI query the dimension. The query becomes simpler and the meaning becomes testable.

5. Observable validation

-- Fiscal boundarySELECT full_date,fiscal_year,fiscal_periodFROM dim_dateWHERE full_date IN ('2027-03-31','2027-04-01')ORDER BY full_date;-- Paid GMV by governed order date attributesSELECT d.weekday_name, SUM(f.extended_amount) AS paid_gmvFROM fact_sales_history fJOIN dim_date d ON d.date_sk = CAST(REPLACE(f.event_date,'-','') AS INTEGER)GROUP BY d.weekday_nameORDER BY d.weekday_name;-- Special members are explicit, not fake datesSELECT date_sk,date_type,full_date FROM dim_date WHERE date_sk IN (0,-1);

Expected controls remain 625 paid GMV, 380 cost, and 245 gross profit. Calendar joins may change labels and slices, but they must never change atomic sales totals.

6. Production judgment

A global warehouse may need multiple holiday calendars, retail 4-4-5 calendars, localized names, market-open flags, payroll periods, or business-unit fiscal calendars. Do not cram incompatible calendars into one ambiguous column. Either add clearly named parallel attributes or model separate governed calendar variants according to requirements.

A date dimension is tiny, but its semantic blast radius is huge: thousands of reports can depend on its week/fiscal labels. Treat calendar changes as governed releases with regression tests.

Knowledge check

Summary and next step

AtlasMart now has one governed calendar vocabulary. Lesson 2 reuses that single physical table three times—order date, ship date, and due date—without cloning calendar data or blurring temporal roles.

Authoritative references

• Kimball Group — Dimensional Modeling Techniques — Primary technique index for date, role-playing, junk, degenerate, mini-, and late-arriving dimension patterns.
• Kimball Group — Calendar Date Dimensions — Date attributes, fiscal periods, special dates, and explicit unknown/to-be-determined rows.
• Kimball Group — Role-Playing Dimensions — One physical dimension reused through logically distinct roles.
• Kimball Group — Junk Dimensions — Combining miscellaneous low-cardinality flags into one dimension.
• Kimball Group — Degenerate Dimensions — Transaction identifiers kept directly in fact tables when no descriptive dimension row exists.
• Kimball Group — Type 4 Mini-Dimension — Separating rapidly changing profile attributes and linking both base and mini-dimension keys to facts.
• Kimball Group — Late Arriving Dimension — Placeholder/inferred members and later completion of descriptive context.
• SQLite — CREATE TABLE — Constraint semantics used by the local deterministic lab.
• SQLite — Foreign Key Support — Local referential-integrity behavior used in the inferred-member failure test.