The short answer
A date dimension (dim_date) turns a calendar day into a rich set of attributes for filtering and grouping: day name, week, month, quarter, fiscal period, holiday flag, weekend flag, and more.
dim_date
date_key (e.g. 20260905)
full_date
day_name
week_of_year
month_name
quarter
fiscal_year
is_weekend
is_holidayWhy not only ORDER BY order_date?
SQL can extract month from a timestamp, but fiscal calendars, company holidays, and consistent week definitions are messy to re-implement in every query. Put them once in dim_date.
Role-playing
The same dim_date serves order date, ship date, and delivery date via different fact FKs.
Interview tip
> "Date dimension centralizes calendar logic (fiscal and holiday attributes) and supports role-playing dates."