The Hardest-Working Table in the Warehouse

Why the date dimension is generated instead of sourced, what it must contain, and how it keeps fiscal calendars, business days, holidays, and unfinished milestones from leaking into every query.

Every fact table wants a date. Most want several. Order date, ship date, delivered date, snapshot date, effective date, expiration date, fiscal period, week start, month end, holiday flag, business-day count. The date dimension looks humble because everyone understands a calendar. That is exactly why teams underbuild it.

A thin dim_date with date_day, month, and year is a start. It is not a warehouse calendar. A real date dimension is the table that prevents every analyst from re-implementing fiscal logic in a dashboard filter and every model from disagreeing about whether a week starts Sunday, Monday, or the day finance says it does.

Unlike dim_book or dim_customer, dim_date is not sourced from the business. You generate it. That makes it one of the few dimensions where the warehouse should be more authoritative than the source systems.

The grain is one row per day

The normal date dimension grain is one row per calendar date:

select
    {{ dbt_utils.generate_surrogate_key(["date_day"]) }} as date_sk,
    date_day,
    extract(year from date_day) as calendar_year,
    extract(month from date_day) as calendar_month,
    extract(day from date_day) as day_of_month,
    dayname(date_day) as day_name
from date_spine

That surrogate key should be deterministic. In this series, facts hash their date into date_sk the same way dimensions do, which means facts can be built before dim_date exists and still land on the correct key once it does.

Some warehouses use an integer YYYYMMDD key instead. That is also defensible:

to_number(to_char(date_day, 'YYYYMMDD')) as date_sk

The important thing is consistency. Pick one date-key strategy and use it everywhere. A fact with order_date_sk as 20260525 and another with an MD5 hash is not a galaxy. It is a reconciliation meeting waiting to happen.

Generate more dates than you need

Do not generate dates only through today. Facts can point into the future: preorders, delivery promises, subscription renewals, planned inventory receipts. Generate a wide range:

with date_spine as (
    {{
        dbt_utils.date_spine(
            datepart="day",
            start_date="to_date('2020-01-01')",
            end_date="to_date('2035-12-31')"
        )
    }}
)

If the warehouse does not support that macro cleanly, generate the spine with native SQL. The result is what matters: a complete, deterministic set of days.

Regenerating dim_date should be safe. It is derived data. If your calendar logic changes, you rebuild it. That is another reason to keep business-maintained exceptions, like company holidays, in their own seed or source and join them in.

Calendar attributes are only the first layer

A useful date dimension includes obvious calendar fields:

  • day of week;
  • day name;
  • week start and end;
  • month number and name;
  • month start and end;
  • quarter;
  • year;
  • ISO week and ISO year.

ISO week is not trivia. The first days of January can belong to the previous ISO year. If the business reports by ISO weeks and one analyst uses extract(week) while another uses ISO logic, the first week of the year becomes a permanent argument.

Make the attributes explicit:

select
    date_day,
    date_trunc('week', date_day) as week_start_date,
    last_day(date_day, 'week') as week_end_date,
    date_trunc('month', date_day) as month_start_date,
    last_day(date_day, 'month') as month_end_date,
    quarter(date_day) as calendar_quarter,
    yearofweekiso(date_day) as iso_year,
    weekiso(date_day) as iso_week
from date_spine

Function names vary by warehouse. Snowflake has weekiso; other engines spell it differently. The design does not vary.

Relative-date flags belong to the calendar, not the dashboard

Ask ten analysts to filter “the last 7 days” and you get ten slightly different filters. Some use >= dateadd('day', -7, current_date()), which is eight days inclusive. Some use -6. Some anchor on getdate() in the local session timezone, some on UTC, some on whatever their BI tool thinks “today” is after its overnight cache refresh. The window everyone believes is standardized is quietly forked across every report.

The fix is to compute the relative window once, in dim_date, and expose it as a boolean flag. The definition lives in one governed model, and every dashboard filters on where is_last_7_days — a phrase that cannot drift:

select
    date_sk,
    date_day,

    -- point-in-time flags, evaluated against current_date() at build time
    date_day = current_date()                                    as is_today,

    date_trunc('month', date_day) = date_trunc('month', current_date())
                                                                 as is_current_month,

    date_trunc('year', date_day) = date_trunc('year', current_date())
                                                                 as is_current_year,

    date_day between dateadd('day', -6, current_date())
                 and current_date()                             as is_last_7_days,

    date_day between dateadd('day', -29, current_date())
                 and current_date()                             as is_last_30_days,

    -- year to date: same calendar year, on or before today
    (   calendar_year = extract(year from current_date())
        and date_day <= current_date()   )                       as is_ytd,

    -- the whole prior calendar year, for clean year-over-year totals
    calendar_year = extract(year from current_date()) - 1        as is_prior_year,

    -- prior-year YTD: same day-of-year window, one year back
    (   calendar_year = extract(year from current_date()) - 1
        and date_day <= dateadd('year', -1, current_date())   )  as is_prior_year_ytd

from calendar

Two things make this design work, and one thing makes it dangerous.

It works because the flags are conformed. is_ytd and is_prior_year_ytd use the same anchor, so a year-over-year comparison lines up the same day-of-year in both periods automatically — no analyst has to remember to shift the comparison window by a year and a leap day. And it works because the logic is testable: a relative window is now a column you can assert on, not an ad-hoc predicate buried in a dashboard nobody audits.

The danger is that current_date() is evaluated when the model builds, not when the report runs. These flags are a snapshot of “now” frozen at build time. If dim_date last built at 02:00 and someone opens a dashboard at 23:00 the next day, is_today still points at yesterday. That is not a bug in the pattern; it is the pattern’s contract. You accept it by rebuilding dim_date on a daily schedule — a cheap, seconds-long run, because the table is small — early enough that the flags are correct for the working day.

This is worth stating plainly to your consumers, because the alternative failure is worse. If you leave “last 7 days” to each dashboard, the number is live but inconsistent — every tile computes a slightly different window, and two reports that should agree never do. Pre-computed flags trade a bounded, well-understood staleness (correct as of the last build) for exact agreement across the whole warehouse. For governed reporting, that is the trade you want. For a genuinely live operational screen, compute the window in the BI layer and keep the pre-computed flag for everything else — but do not let both definitions of “today” leak into the same executive dashboard.

A related rule: never pre-compute a flag whose window is finer than your build cadence. is_last_7_days on a daily build is honest. An is_last_15_minutes flag on a daily build is a trap. If the window is shorter than the interval between rebuilds, the column lies for most of its life. Time-of-day windows belong to a different table entirely — which is the next thing this dimension is missing.

Fiscal calendars belong here

If finance says the year starts in February, that logic does not belong in BI. Put it in dim_date:

case
    when calendar_month >= 2 then calendar_year
    else calendar_year - 1
end as fiscal_year

Then derive fiscal period, fiscal quarter, fiscal year start, and fiscal year end. A simple shifted-year calendar — “the fiscal year starts in February” — is arithmetic, and the case expression above is the whole of it.

Retail is not arithmetic. If the bookshop reports on a 4-4-5 calendar, the fiscal year is 52 weeks split into four 13-week quarters, and each quarter is three “months” of 4, 4, and 5 weeks. Fiscal periods start on a fixed weekday and never straddle a week boundary, which is the entire reason retailers use it: every period contains whole weeks, so week-over-week comparisons are clean and every period has the same number of selling weekends. The catch is that 52 weeks is only 364 days, so roughly every fifth or sixth year gets a 53-week year to catch back up to the sun. There is no closed-form extract() for any of this.

Do not try to compute 4-4-5 with modular arithmetic in SQL. You will get it almost right, ship it, and discover the off-by-one during the 53-week year when finance is closing the books. Encode the period boundaries as data — a seed finance owns — and join by date range:

# seeds/seed_fiscal_periods.csv
fiscal_year,fiscal_period,fiscal_quarter,period_start_date,period_end_date
2026,1,1,2026-02-01,2026-02-28
2026,2,1,2026-03-01,2026-03-28
2026,3,1,2026-03-29,2026-05-02
2026,4,2,2026-05-03,2026-05-30
left join {{ ref('seed_fiscal_periods') }} as fp
    on d.date_day between fp.period_start_date and fp.period_end_date

Now fiscal_year, fiscal_period, and fiscal_quarter come straight from the seed, and the 4-4-5 shape lives in a spreadsheet finance can hand you every year — including the year they insert period 53. Derive the finer grains with windows over the joined result:

select
    d.date_day,
    fp.fiscal_year,
    fp.fiscal_period,
    fp.fiscal_quarter,
    -- week within the fiscal period (1..5)
    floor(datediff('day', fp.period_start_date, d.date_day) / 7) + 1
        as fiscal_week_of_period,
    -- day within the fiscal period (1..35)
    datediff('day', fp.period_start_date, d.date_day) + 1
        as fiscal_day_of_period
from calendar d
left join {{ ref('seed_fiscal_periods') }} fp
    on d.date_day between fp.period_start_date and fp.period_end_date

Restated calendars are the next layer of pain. When the bookshop acquires a competitor whose fiscal year started in a different month, finance often wants two years of history reported on both the old and the new calendar so trends stay comparable across the boundary. Resist the urge to duplicate rows per calendar — that changes the grain of dim_date from one-row-per-day to one-row-per-day-per-calendar and quietly doubles every count that forgets to filter. Instead, keep one row per day and widen it: carry fiscal_year, fiscal_period, fiscal_quarter for the current calendar and fiscal_year_legacy, fiscal_period_legacy alongside them, each fed by its own seed. A report picks a calendar by picking a column, and the grain never moves. Only reach for a separate dim_fiscal_calendar and a bridge if you genuinely maintain many named calendars at once — most shops need exactly two, and two extra columns is the cheaper answer.

The date dimension is where calendar weirdness becomes a governed table instead of tribal knowledge.

Holidays and business days need ownership

Holiday logic is not universal. Country, region, company policy, and business line all matter. Put the maintained list in a seed:

date_day,holiday_name,country_code
2026-01-01,New Year's Day,US
2026-07-04,Independence Day,US

Then join it into the dimension:

left join {{ ref('seed_holidays') }} as holidays
    on date_spine.date_day = holidays.date_day

From there, derive:

  • is_weekend;
  • is_holiday;
  • is_business_day;
  • previous_business_day;
  • next_business_day;
  • business_day_of_month.

Those columns are not decorative. They make questions like “ship within three business days” queryable without a custom calendar CTE in every report.

The counting columns are the ones people re-derive most, so compute them once with windows. business_day_of_month is a running count of business days within the month; business_days_in_month is the total, which fulfillment SLAs and finance accruals both lean on:

with flagged as (
    select
        date_day,
        (not is_weekend and holiday_name is null) as is_business_day
    from calendar
)

select
    date_day,
    is_business_day,

    sum(iff(is_business_day, 1, 0)) over (
        partition by date_trunc('month', date_day)
        order by date_day
        rows between unbounded preceding and current row
    ) as business_day_of_month,

    sum(iff(is_business_day, 1, 0)) over (
        partition by date_trunc('month', date_day)
    ) as business_days_in_month

from flagged

With those in place, business_days_in_month - business_day_of_month answers “how many working days are left to hit the quota” as a column, not a support ticket. The previous_business_day and next_business_day columns are worth precomputing the same way — a lag/lead over the business-day-only rows — so a promise date can skip weekends and holidays without every query re-teaching the warehouse what a working day is.

One calendar, many regions

Holidays are the place where “the calendar” stops being one thing. The bookshop ships from a US warehouse and a UK warehouse, and July 4th is a working day in Manchester while August’s bank holiday means nothing in Denver. A single is_holiday flag has to pick a country, and whichever it picks is wrong for the other half of the business.

Two shapes solve this. When the number of regions is small and stable — and for most companies it is — keep one row per day and pivot the holiday seed into per-region columns:

holidays_by_region as (
    select
        date_day,
        max(iff(country_code = 'US', holiday_name, null)) as us_holiday_name,
        max(iff(country_code = 'GB', holiday_name, null)) as uk_holiday_name
    from {{ ref('seed_holidays') }}
    group by date_day
)
select
    d.date_day,
    h.us_holiday_name,
    h.uk_holiday_name,
    (h.us_holiday_name is not null) as is_holiday_us,
    (h.uk_holiday_name is not null) as is_holiday_uk,
    (not d.is_weekend and h.us_holiday_name is null) as is_business_day_us,
    (not d.is_weekend and h.uk_holiday_name is null) as is_business_day_uk
from calendar d
left join holidays_by_region h using (date_day)

A US fulfillment report reads is_business_day_us; the UK report reads its own column; both share every other calendar attribute — one week definition, one fiscal year, one set of relative flags. That conformance is exactly what you would lose by maintaining two separate date tables.

The other shape — a dim_date_region bridge keyed on (date_sk, region_code) — is the right call only when you have many regions or the regional attributes go well beyond holidays. It multiplies the row count by the number of regions, so reach for it deliberately, not by default. For two warehouses, columns win.

Add the special rows deliberately

Facts sometimes have dates that have not happened yet. fct_orders in this series is an accumulating snapshot, and it points three date roles at dim_date: order_date_sk, ship_date_sk, and delivery_date_sk. Every order has an order date. Most, at any given moment, do not yet have a ship date — the box is still on a shelf. So what should ship_date_sk be for an order that has not shipped?

Do not use null. Fact foreign keys should not be null if you can avoid it; nulls break inner joins silently, drop open orders out of any report that joins through the ship-date role, and force every query to special-case the unfinished states of the pipeline. An accumulating snapshot is defined by having incomplete milestones — null is not the exception here, it is half the table.

The answer is a small set of designated member rows in dim_date, one per missing-date meaning:

date_sk              date_day   date_description
__unknown__          null       Unknown
__not_applicable__   null       Not applicable
__not_yet_occurred__ null       Not yet occurred

Because this series hashes dates into string surrogate keys, the sentinels are fixed string literals, not negative integers — but the rule is identical either way: every missing-date state gets a named, deterministic member. Append them to the calendar with a union all so they are part of the dimension, not a runtime fix-up:

with calendar as (
    -- one row per generated day, all attributes derived above
    ...
),

special_members as (
    select '__unknown__'          as date_sk,
           null::date             as date_day,
           'Unknown'              as date_description
    union all
    select '__not_applicable__',   null, 'Not applicable'
    union all
    select '__not_yet_occurred__', null, 'Not yet occurred'
)

select date_sk, date_day, date_description, /* ...calendar columns... */ from calendar
union all
select date_sk, date_day, date_description, /* ...nulls for the rest... */ from special_members

The fact side coalesces the missing key onto the matching sentinel, so an unshipped order lands on a real dimension member instead of a null:

-- in fct_orders
coalesce(
    {{ dbt_utils.generate_surrogate_key(['ship_date']) }},
    '__not_yet_occurred__'
) as ship_date_sk

Now ship_date_sk is never null. Reports can count open orders — “how many are ordered but not yet shipped” is a filter on dim_ship_date.date_description = 'Not yet occurred' — without those orders vanishing in an inner join. When the box ships, the next build recomputes a real ship_date_sk and the row moves off the sentinel on its own. The three states earn their keep: “Unknown” for a date that should exist but the source lost, “Not applicable” for a milestone this order will never reach (a cancelled order has no delivery date, ever), and “Not yet occurred” for a milestone that is simply still in the future.

Role-playing comes later, but starts here

dim_date is one table. Facts may use it many ways:

order_date_sk      -> dim_date.date_sk
ship_date_sk       -> dim_date.date_sk
delivered_date_sk  -> dim_date.date_sk

That is called a role-playing dimension. You do not copy the date dimension three times. You join the same table with three aliases, or create thin views named dim_order_date, dim_ship_date, and dim_delivered_date if the BI tool needs separate logical tables.

The conformance is the point. Order date and ship date should share the same fiscal calendar, holiday flags, and week definitions. One calendar, many roles.

Time of day is a separate dimension

The moment someone asks “what time of day do most orders come in?” the one-row-per-day grain runs out. You cannot answer it from dim_date, and you should not try. The instinct to fold hours and minutes into the calendar is exactly the mistake that makes date dimensions unusable.

The reason is cardinality. dim_date for the 2020–2035 range in this series is a hair under 6,000 rows — small enough to scan, cache, and rebuild in seconds. Add minute-level time to it and every day multiplies into 1,440 rows: about 8.4 million. Go to second grain and it is 500 million. A dimension you could hold in memory becomes a table you have to think hard about, and the calendar attributes — fiscal year, holiday name, week start — get copied 1,440 times per day for no benefit.

Split time into its own dimension. Time-of-day attributes are genuinely independent of the calendar: 2:00 PM is business hours and afternoon on every date that has ever existed, so it deserves exactly one row, not one per day. dim_time at minute grain is a fixed 1,440 rows, generated once and never grown:

-- models/marts/dim_time.sql
with minute_spine as (
    {{
        dbt_utils.date_spine(
            datepart="minute",
            start_date="to_timestamp('2000-01-01 00:00:00')",
            end_date="to_timestamp('2000-01-02 00:00:00')"
        )
    }}
)

select
    -- integer HHMM key: 0000..2359, deterministic and human-readable
    to_number(to_char(date_minute, 'HH24MI'))   as time_sk,
    to_char(date_minute, 'HH24:MI')             as time_of_day,

    extract(hour   from date_minute)            as hour_24,
    extract(minute from date_minute)            as minute_of_hour,
    (extract(hour from date_minute) * 60
        + extract(minute from date_minute))     as minute_of_day,

    -- 12-hour presentation
    to_char(date_minute, 'HH12:MI AM')          as time_of_day_12h,
    iff(extract(hour from date_minute) < 12, 'AM', 'PM') as am_pm,

    -- coarse buckets people actually filter on
    case
        when extract(hour from date_minute) <  6 then 'Night'
        when extract(hour from date_minute) < 12 then 'Morning'
        when extract(hour from date_minute) < 17 then 'Afternoon'
        when extract(hour from date_minute) < 21 then 'Evening'
        else 'Night'
    end                                       as day_period,

    -- business hours are a time property, independent of the date;
    -- whether the DATE is a working day comes from dim_date
    (extract(hour from date_minute) >= 9
        and extract(hour from date_minute) < 17) as is_business_hours

from minute_spine

The date_spine end date is exclusive, so 00:00 through 23:59 gives exactly 1,440 rows and no duplicate midnight. The time_sk is the integer HHMM, which sorts correctly, reads at a glance in a query result, and never collides — the same “consistency over cleverness” rule the date key follows.

A fact with a timestamp now carries two foreign keys built from it — one into each dimension:

-- splitting an order_placed_at timestamp across both dimensions
{{ dbt_utils.generate_surrogate_key(['cast(order_placed_at as date)']) }}
    as order_date_sk,
to_number(to_char(order_placed_at, 'HH24MI')) as order_time_sk

One question this raises: if you need the exact duration between two events — say, minutes from order to ship — do not compute it by subtracting surrogate keys. Keep the raw timestamps on the fact for precise arithmetic, and use dim_time for the grouping and filtering it is good at: orders by hour, conversions during business hours, a heat map of the day. The dimension answers “when in the day,” the timestamp answers “exactly how long.”

Note what dim_time deliberately does not know: whether a given minute falls on a weekend or a holiday. is_business_hours is only the 09:00–17:00 window. A fully “working” moment is dim_time.is_business_hours AND dim_date.is_business_day — the two dimensions compose, each owning the half of the answer it can actually see. That is the whole argument for splitting them in one line.

Test it like infrastructure

The date dimension should be boring enough that tests rarely fail:

models:
  - name: dim_date
    columns:
      - name: date_sk
        data_tests:
          - unique
          - not_null
      - name: date_day
        data_tests:
          - unique
  - name: dim_time
    columns:
      - name: time_sk
        data_tests:
          - unique
          - not_null

Add custom tests for range completeness if your project depends on it. A missing day is not a small problem. It means a fact can land on a key with no matching dimension row, or a report can silently omit a date. The same completeness logic applies to dim_time: assert it holds exactly 1,440 rows, because a gap there hides orders from an hour of the day as quietly as a missing calendar date hides a whole day. And because the relative-date flags are recomputed on every build, add a freshness check — assert that at least one row has is_today = true — so a stalled schedule that leaves the flags pointing at last week fails loudly instead of silently skewing every “last 7 days” number in the business.

Final thoughts

The date dimension earns its place by centralizing the arguments nobody wants to have twice: fiscal years, ISO weeks, holidays, business days, unfinished milestones, and role-playing joins. It is generated, but it is not generic. It encodes how your business experiences time. Build it once, test it, and make every fact point at it. The calendar is not a helper table. It is the shared language that lets stars agree about when things happened.

Next: Slowly Changing Dimensions

Comments