dim_dates Dataset Overview
Our Date Dimension / dim_date table unlocks your time based analytics by providing core date components along with enhanced attributes like holidays, business days and more. Spend less time on complex date calculations and more on extracting insights from your data.
Key Features
Core Components
- Core date components and groupings: date, month, year, quarter
- Period Beginning and End: First and last day of the quarter/month/week
- Categorization: holidays and adjacent days, business days
- Trending: alignment of weekdays for year-over-year, month-over-month analysis
Business Benefits
- Ensure consistency, performance, and simplicity across all your queries by using our precalculated centralized date groupings and calculations
- Enable your team to conduct accurate year-over-year, month-over-month reporting by utilizing aligned weekdays
- Account for funky data around the holidays by utilizing our holiday_adjacent flag
Data Dictionary
| Name | Type | Description |
|---|---|---|
| date_id | Number | Unique id for the date |
| date | Date | The calendar date |
| day | Number | The day component of the date |
| weekday | Varchar | The name of the day of week |
| day_of_week | Number | Numeric representation of the day of week |
| month | Number | Numeric representation of the month |
| month_start | Date | The first day of the month |
| month_end | Date | The last day of the month |
| month_name | Varchar | The name of the month |
| year | Number | The year component of the date |
| year_start | Date | The first day of the year |
| year_end | Date | The last day of the year |
| year_month | Varchar | Combination of year and month: YYYY-MM |
| is_leap_year | Boolean | Indicates if the year is a leap year |
| week_number | Number | The week number of the year |
| week_start | Date | The first day of the week (Sunday) |
| week_end | Date | The last day of the week (Saturday) |
| is_weekday | Boolean | Indicates if the day is a weekday |
| is_weekend | Boolean | Indicates if the day on the weekend |
| is_holiday | Boolean | Indicates if the day is a holiday |
| is_holiday_adjacent | Boolean | Flags the business day immediately before and after a holiday, as well as any days in between, as TRUE |
| is_business_day | Boolean | Indicates if the day is a business day (not a weekend and not a holiday) |
| prior_year_date_day_of_week_aligned | Date | Last year's equivalent day, day of week aligned. Example: Friday 1/10/2025 is aligned to Friday 1/12/2024 |
| prior_month_date_day_of_week_aligned | Date | Last month's equivalent day, day of week aligned. Example: Friday 1/10/2025 is aligned to Friday 12/13/2024 |
Usage Examples
YoY reporting of event data by aligning corresponding days of the week
SELECT
f.date,
f.events,
d.last_year_date,
f_last_year.events AS last_year_events,
events - last_year_events AS event_diff
FROM
fct_event f
JOIN dim_date d
ON f.date = d.date
JOIN fct_event f_last_year
ON d.last_year_date = f_last_year.date
Consistently group event data by month and year
SELECT
d.year,
d.month,
SUM(f.events)
FROM
fct_event f
JOIN dim_date d
ON f.date = d.date
GROUP BY d.year, d.month
ORDER BY d.year, d.month
Business Logic Notes
prior_year_date_day_of_week_aligned
- Cannot take holidays into account due to country-specific variations
- For non leap years, this year's date was equivalent to last year's date + 1 (e.g., 12/6/2023 = 12/7/2023)
- For leap years:
- This year's date is also equivalent to last year's date + 1
- From 2/29 and forward, the difference becomes + 2 (e.g., 3/1/2024 = 3/3/2023)
- Until the 2/28 of the next year, then the difference returns to 1
Technical Details
- Optimized for Snowflake performance
- Regular updates for holiday definitions
- Compatible with major BI tools
Getting Started
The dim_dates dataset is available for purchase on the Snowflake Marketplace. Contact us to learn more about implementing dim_dates in your data warehouse.