Almost every fact table has at least one date, and almost every report filters or groups by some part of it: month, quarter, week, fiscal period, business day, holiday. The date dimension (dim_date) is a table with one row per calendar day and a column for every way the business describes a day. It is the most reused table in a dimensional model and usually the first one you should build.
This guide covers what to put in it, how to generate it with SQL on the main warehouses, how to handle fiscal calendars and holidays, and how one date dimension plays several roles on the same fact.
Why not just use date functions?
You can compute extract(month from order_date) in every query. The problems show up quickly:
- Business rules are not functions. No SQL function knows your fiscal year starts in February, that 26 December is a bank holiday in the UK but not the US, or that the retail calendar uses 4-5-4 weeks.
- Consistency. Ten analysts will write ten slightly different expressions for "week". Some will use ISO weeks, some will start on Sunday.
- Usability. BI tools and semantic layers work best with named attributes (
month_name,fiscal_quarter) they can offer as dimensions. - Sorting and labels.
month_nameneeds a matchingmonth_numberfor correct ordering. A dimension makes that pairing explicit.
Choosing the key
The conventional key is an integer in YYYYMMDD form: 20261103 for 3 November 2026.
- It is readable, so debugging fact tables is easier.
- It sorts correctly.
- It is a deliberate exception to the rule that surrogate keys should be meaningless. Dates are stable; there is no risk of the "natural key" changing.
Many teams on modern warehouses instead use the date value itself as the key. That is also fine, and it removes a conversion step. Pick one and use it everywhere.
Include special rows for unknown and not-yet-occurred dates, for example date_key = -1 ("Unknown") and date_key = 99991231 ("Not yet"). Facts with a missing date or an unreached milestone then still join with an inner join.
Columns to include
| Group | Columns |
|---|---|
| Key and value | date_key, full_date |
| Day | day_of_week_number, day_name, day_name_short, day_of_month, day_of_year |
| Week | iso_week_number, iso_year, week_start_date, week_end_date |
| Month | month_number, month_name, month_name_short, year_month (e.g. 2026-11), month_start_date, month_end_date, is_month_end |
| Quarter and year | quarter_number, quarter_name (Q4), year_quarter (2026-Q4), year_number |
| Fiscal | fiscal_year, fiscal_quarter, fiscal_month_number, fiscal_period_label |
| Flags | is_weekend, is_weekday, is_holiday, holiday_name, is_business_day |
| Relative (optional) | days_from_today, is_current_month (only if rebuilt daily) |
Keep relative columns out unless you rebuild the table every day. A stale is_current_month is worse than none.
Generating dim_date in SQL
All of these generate a row per day from 2015 to 2035. Adjust the range to cover your oldest data and a few years ahead.
Postgres
create table dim_date asselect to_char(d, 'YYYYMMDD')::int as date_key, d::date as full_date, extract(isodow from d)::int as day_of_week_number, -- 1 = Monday to_char(d, 'FMDay') as day_name, to_char(d, 'Dy') as day_name_short, extract(day from d)::int as day_of_month, extract(doy from d)::int as day_of_year, extract(week from d)::int as iso_week_number, extract(isoyear from d)::int as iso_year, date_trunc('week', d)::date as week_start_date, (date_trunc('week', d) + interval '6 day')::date as week_end_date, extract(month from d)::int as month_number, to_char(d, 'FMMonth') as month_name, to_char(d, 'Mon') as month_name_short, to_char(d, 'YYYY-MM') as year_month, date_trunc('month', d)::date as month_start_date, (date_trunc('month', d) + interval '1 month - 1 day')::date as month_end_date, extract(quarter from d)::int as quarter_number, 'Q' || extract(quarter from d) as quarter_name, extract(year from d)::int as year_number, extract(isodow from d) in (6, 7) as is_weekendfrom generate_series(date '2015-01-01', date '2035-12-31', interval '1 day') as g(d);Snowflake
create or replace table dim_date aswith days as ( select dateadd(day, seq4(), '2015-01-01'::date) as d from table(generator(rowcount => 7700)))select to_number(to_char(d, 'YYYYMMDD')) as date_key, d as full_date, dayofweekiso(d) as day_of_week_number, dayname(d) as day_name_short, day(d) as day_of_month, dayofyear(d) as day_of_year, weekiso(d) as iso_week_number, yearofweekiso(d) as iso_year, date_trunc('week', d) as week_start_date, month(d) as month_number, monthname(d) as month_name_short, to_char(d, 'YYYY-MM') as year_month, date_trunc('month', d) as month_start_date, last_day(d) as month_end_date, d = last_day(d) as is_month_end, quarter(d) as quarter_number, year(d) as year_number, dayofweekiso(d) in (6, 7) as is_weekendfrom dayswhere d <= '2035-12-31';Note that seq4() can have gaps in some circumstances; row_number() over (order by seq4()) - 1 is safer if you need a strictly contiguous series. Also check the WEEK_START session parameter, which affects date_trunc('week', ...).
BigQuery
create or replace table analytics.dim_date asselect cast(format_date('%Y%m%d', d) as int64) as date_key, d as full_date, extract(dayofweek from d) as day_of_week_number, -- 1 = Sunday format_date('%A', d) as day_name, extract(day from d) as day_of_month, extract(dayofyear from d) as day_of_year, extract(isoweek from d) as iso_week_number, extract(isoyear from d) as iso_year, date_trunc(d, isoweek) as week_start_date, extract(month from d) as month_number, format_date('%B', d) as month_name, format_date('%Y-%m', d) as year_month, date_trunc(d, month) as month_start_date, last_day(d, month) as month_end_date, extract(quarter from d) as quarter_number, extract(year from d) as year_number, extract(dayofweek from d) in (1, 7) as is_weekendfrom unnest(generate_date_array('2015-01-01', '2035-12-31')) as d;BigQuery numbers Sunday as 1. If you want ISO numbering, compute mod(extract(dayofweek from d) + 5, 7) + 1.
Databricks
create or replace table dim_date asselect cast(date_format(d, 'yyyyMMdd') as int) as date_key, d as full_date, extract(dayofweek from d) as day_of_week_number, -- 1 = Sunday date_format(d, 'EEEE') as day_name, dayofmonth(d) as day_of_month, dayofyear(d) as day_of_year, weekofyear(d) as iso_week_number, date_trunc('week', d) as week_start_date, month(d) as month_number, date_format(d, 'MMMM') as month_name, date_format(d, 'yyyy-MM') as year_month, trunc(d, 'month') as month_start_date, last_day(d) as month_end_date, quarter(d) as quarter_number, year(d) as year_number, extract(dayofweek from d) in (1, 7) as is_weekendfrom (select explode(sequence(date'2015-01-01', date'2035-12-31', interval 1 day)) as d);Fiscal calendars
If the fiscal year starts on 1 February and is named for the calendar year in which it ends, fiscal attributes are simple date arithmetic:
-- Postgres; FY2027 runs 2026-02-01 to 2027-01-31select full_date, extract(year from full_date + interval '11 month')::int as fiscal_year, ((extract(month from full_date)::int + 10) % 12) + 1 as fiscal_month_number, ceil((((extract(month from full_date)::int + 10) % 12) + 1) / 3.0)::int as fiscal_quarterfrom dim_date;Retail 4-4-5 or 4-5-4 calendars, and 53-week years, do not reduce to a formula reliably. Generate them once from the official finance calendar and store them in a seed table that you join into dim_date. Finance owns that table; the data team should not reinvent it.
Holidays and business days
Holidays depend on country, region and sometimes company policy. Keep them in a separate small table:
create table ref_holiday ( holiday_date date not null, country_code char(2) not null, holiday_name varchar(100) not null, primary key (holiday_date, country_code));If you operate in one country, flatten is_holiday and holiday_name onto dim_date. If you operate in many, keep the reference table and either add a column per major country (is_holiday_us, is_holiday_gb) or join through a calendar-by-country bridge. A banking model calculating settlement days, for example, needs the calendar of the settlement currency, not the customer's location.
Then is_business_day = not is_weekend and not is_holiday. A useful extra is business_day_of_month, which supports reports like "sales on the third working day".
Role-playing dates
An accumulating snapshot for order fulfilment has several dates: placed, paid, shipped, delivered. Each is a foreign key to the same dim_date. That is a role-playing dimension: one physical table playing several logical roles.
create table fct_order_fulfilment ( order_id varchar(40) primary key, placed_date_key integer not null references dim_date (date_key), shipped_date_key integer not null references dim_date (date_key), delivered_date_key integer not null references dim_date (date_key), order_amount numeric(12,2) not null);To query by more than one role, join the dimension once per role with distinct aliases:
select placed.year_month as placed_month, shipped.year_month as shipped_month, count(*) as ordersfrom fct_order_fulfilment fjoin dim_date placed on placed.date_key = f.placed_date_keyjoin dim_date shipped on shipped.date_key = f.shipped_date_keygroup by 1, 2;For BI users, create one view per role with renamed columns, so a field list shows "Shipped Month" rather than an ambiguous "Month":
create view dim_ship_date asselect date_key as ship_date_key, full_date as ship_date, year_month as ship_year_month, is_business_day as ship_is_business_dayfrom dim_date;The same pattern applies in healthcare (admission date, discharge date), insurance (policy start, claim incurred, claim reported) and banking (trade date, settlement date).
Common mistakes
Joining on timestamps. A fact with ordered_at timestamp joined directly to full_date silently drops every row that is not exactly midnight. Convert to a date key when loading the fact.
Time zones. "The day an order happened" depends on whose day. Decide whether the date key reflects UTC, the store's local time or the customer's local time, and apply it consistently. For global businesses it is common to store both a UTC date key and a local date key.
Mixing week definitions. ISO weeks start on Monday and week 1 contains the first Thursday of the year. US weeks often start on Sunday. Week 53 exists in some ISO years. Pick a definition, name the columns accordingly (iso_week_number), and pair week numbers with the right year (iso_year, not year_number).
Putting time of day in dim_date. A row per minute per day makes a huge table. If you need time-of-day analysis, use a separate dim_time_of_day with one row per minute or second.
Not covering the future. Accumulating snapshots, forecasts and contract end dates need future dates. Extend the range well past today.
For how dates fit into the wider design, read what is grain in data modeling and fact vs dimension tables.
Build one in offdataai Learn
The Dimensional Modeling (Kimball) track in offdataai Learn includes a date dimension exercise and a role-playing dates exercise: you build dim_date and wire it to a multi-milestone fact in the Model Builder, and an instant check confirms the key, attributes and role-playing relationships. It is hands-on and included in the Explorer plan at $1/month.
You can also generate DDL for your warehouse from any of the templates, all of which include a date dimension.