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_holiday
Why 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."