# Date Dimensions

**URL:** <https://discourse.getdbt.com/t/date-dimensions/735>\
**Category:** Show and Tell\
**Tags:** date-and-time\
**Created:** [December 3, 2019, 7:36pm UTC](https://discourse.getdbt.com/t/date-dimensions/735 "2019-12-03T19:36:59Z")\
**Posts on this page:** 7\
**Page:** 1

<div class="post-metadata">

**Author:** ![josh](https://avatars.discourse-cdn.com/v4/letter/j/45deac/32.png) [@josh](https://discourse.getdbt.com/u/josh)\
**Post date:** [December 3, 2019, 7:36pm UTC](https://discourse.getdbt.com/t/date-dimensions/735/1 "2019-12-03T19:36:59Z")

</div>

Hello All –

We have built a date dimension. Just wanted to share our code and results here and see if others have built their date dimensions differently, anything that you would add/remove, etc.?

> {{  
> config(  
> pre\_hook=“ALTER SESSION SET WEEK\_OF\_YEAR\_POLICY=1, WEEK\_START=7”  
> )  
> }}
> 
> WITH DATE\_SPINE AS  
> (  
> {{ dbt\_utils.date\_spine(  
> datepart=“day”,  
> start\_date=“to\_date(‘01/01/2018’, ‘mm/dd/yyyy’)”,  
> end\_date=“TO\_DATE(CONCAT(YEAR(CURRENT\_DATE) + 6,‘-12-31’))”  
> )  
> }}  
> )  
> SELECT  
> CAST(DATE\_DAY AS DATE) AS DATE\_KEY  
> ,CAST(DAYOFYEAR(DATE\_KEY) AS INT) AS DAY\_OF\_YEAR  
> ,CAST(DECODE (RIGHT(EXTRACT(DAYOFMONTH FROM DATE\_KEY), 1),  
> 1 , ‘st’,  
> 2 , ‘nd’,  
> 3 , ‘rd’,  
> ‘th’ – Default option, all other days ending aside from 1st, 2nd, and 3rd end in ‘th’  
> ) AS VARCHAR(2)) AS DAY\_SUFFIX  
> ,CAST(YEAR(DATE\_KEY) || RIGHT(‘0’ || WEEK(DATE\_KEY), 2) AS INT) AS WEEK\_KEY  
> ,CAST(WEEKOFYEAR(DATE\_KEY) AS INT) AS WEEK\_OF\_YEAR  
> ,CAST(DAYOFWEEK(DATE\_KEY) AS INT) AS DAY\_OF\_WEEK  
> ,CAST(DAYNAME(DATE\_KEY) AS VARCHAR(5)) AS WEEK\_DAY\_SHORT\_DESC  
> ,CAST(DECODE (EXTRACT(DAYOFWEEK FROM DATE\_KEY),  
> 1 , ‘Sunday’,  
> 2 , ‘Monday’,  
> 3 , ‘Tuesday’,  
> 4 , ‘Wednesday’,  
> 5 , ‘Thursday’,  
> 6 , ‘Friday’,  
> 7 , ‘Saturday’  
> ) AS VARCHAR(9)) AS WEEK\_DAY\_DESC  
> ,CAST(TRUNC(DATE\_KEY, ‘Week’) AS DATE) AS FIRST\_DAY\_OF\_WEEK  
> ,CAST(LAST\_DAY(DATE\_KEY, ‘Week’) AS DATE) AS LAST\_DAY\_OF\_WEEK  
> ,CAST(YEAR(DATE\_KEY) || RIGHT(‘0’ || MONTH(DATE\_KEY), 2) AS INT) AS MONTH\_KEY  
> ,CAST(MONTH(DATE\_KEY) AS INT) AS MONTH\_OF\_YEAR  
> ,CAST(DAYOFMONTH(DATE\_KEY) AS INT) AS DAY\_OF\_MONTH  
> ,CAST(MONTHNAME(DATE\_KEY) AS VARCHAR(5)) AS MONTH\_SHORT\_DESC  
> ,CAST(TO\_CHAR(DATE\_KEY, ‘MMMM’) AS VARCHAR(50)) AS MONTH\_DESC  
> ,CAST(TRUNC(DATE\_KEY, ‘Month’) AS DATE) AS FIRST\_DAY\_OF\_MONTH  
> ,CAST(LAST\_DAY(DATE\_KEY, ‘Month’) AS DATE) AS LAST\_DAY\_OF\_MONTH  
> ,CAST(YEAR(DATE\_KEY) || QUARTER(DATE\_KEY) AS INT) AS QUARTER\_KEY  
> ,CAST(QUARTER(DATE\_KEY) AS INT) AS QUARTER\_OF\_YEAR  
> ,CAST(DATE\_KEY - TRUNC(DATE\_KEY, ‘Quarter’) + 1 AS INT) AS DAY\_OF\_QUARTER  
> ,CAST(‘Q’ || QUARTER\_OF\_YEAR AS VARCHAR(5)) AS QUARTER\_SHORT\_DESC  
> ,CAST('Quarter ’ || QUARTER\_OF\_YEAR AS VARCHAR(50)) AS QUARTER\_DESC  
> ,CAST(TRUNC(DATE\_KEY, ‘Quarter’) AS DATE) AS FIRST\_DAY\_OF\_QUARTER  
> ,CAST(LAST\_DAY(DATE\_KEY, ‘Quarter’) AS DATE) AS LAST\_DAY\_OF\_QUARTER  
> ,CAST(YEAR(DATE\_KEY) AS INT) AS YEAR\_KEY  
> ,CAST(TRUNC(DATE\_KEY, ‘Year’) AS DATE) AS FIRST\_DAY\_OF\_YEAR  
> ,CAST(LAST\_DAY(DATE\_KEY, ‘Year’) AS DATE) AS LAST\_DAY\_OF\_YEAR  
> ,CAST(ROW\_NUMBER() OVER (PARTITION BY YEAR(DATE\_KEY), MONTH(DATE\_KEY), DAYOFWEEK(DATE\_KEY) ORDER BY DATE\_KEY) AS INT) AS ORDINAL\_WEEKDAY\_OF\_MONTH  
> ,CAST(CASE  
> WHEN MONTH(DATE\_KEY) = 1 AND DAYOFMONTH(DATE\_KEY) = 1  
> THEN ‘New Year’‘s Day’  
> WHEN MONTH(DATE\_KEY) = 1 AND DAYOFMONTH(DATE\_KEY) = 20 AND ((YEAR(DATE\_KEY) - 1) % 4) = 0  
> THEN ‘Inauguration Day’  
> WHEN MONTH(DATE\_KEY) = 1 AND DAYOFWEEK(DATE\_KEY) = 2 AND ORDINAL\_WEEKDAY\_OF\_MONTH = 3  
> THEN ‘Martin Luther King Jr Day’  
> WHEN MONTH(DATE\_KEY) = 2 AND DAYOFMONTH(DATE\_KEY) = 14  
> THEN ‘Valentine’‘s Day’  
> WHEN MONTH(DATE\_KEY) = 2 AND DAYOFWEEK(DATE\_KEY) = 2 AND ORDINAL\_WEEKDAY\_OF\_MONTH = 3  
> THEN ‘President’‘s Day’  
> WHEN MONTH(DATE\_KEY) = 3 AND DAYOFMONTH(DATE\_KEY) = 17  
> THEN ‘Saint Patrick’‘s Day’  
> WHEN MONTH(DATE\_KEY) = 5 AND DAYOFWEEK(DATE\_KEY) = 1 AND ORDINAL\_WEEKDAY\_OF\_MONTH = 2  
> THEN ‘Mother’‘s Day’  
> WHEN MONTH(DATE\_KEY) = 5 AND DAYOFWEEK(DATE\_KEY) = 2  
> AND LAST\_VALUE(DAYOFMONTH(DATE\_KEY)) OVER (PARTITION BY MONTH\_KEY  
> ORDER BY TO\_DATE(DATE\_KEY)) - 7 \<= DAYOFMONTH(DATE\_KEY)  
> THEN ‘Memorial Day’  
> WHEN MONTH(DATE\_KEY) = 6 AND DAYOFWEEK(DATE\_KEY) = 1 AND ORDINAL\_WEEKDAY\_OF\_MONTH = 3  
> THEN ‘Father’‘s Day’  
> WHEN MONTH(DATE\_KEY) = 7 AND DAYOFMONTH(DATE\_KEY) = 4  
> THEN ‘Independence Day’  
> WHEN MONTH(DATE\_KEY) = 9 AND DAYOFWEEK(DATE\_KEY) = 2 AND ORDINAL\_WEEKDAY\_OF\_MONTH = 1  
> THEN ‘Labor Day’  
> WHEN MONTH(DATE\_KEY) = 10 AND DAYOFWEEK(DATE\_KEY) = 2 AND ORDINAL\_WEEKDAY\_OF\_MONTH = 2  
> THEN ‘Columbus Day’  
> WHEN MONTH(DATE\_KEY) = 10 AND DAYOFWEEK(DATE\_KEY) = 31  
> THEN ‘Halloween’  
> WHEN MONTH(DATE\_KEY) = 11 AND DAYOFWEEK(DATE\_KEY) = 5 AND ORDINAL\_WEEKDAY\_OF\_MONTH = 4  
> THEN ‘Thanksgiving Day’  
> WHEN MONTH(DATE\_KEY) = 12 AND DAYOFMONTH(DATE\_KEY) = 25  
> THEN ‘Christmas Day’  
> WHEN MONTH(DATE\_KEY) = 12 AND DAYOFMONTH(DATE\_KEY) = 26  
> THEN ‘Boxing Day’  
> ELSE NULL  
> END AS VARCHAR(50)) AS HOLIDAY\_DESC  
> ,CAST(CASE WHEN HOLIDAY\_DESC IS NULL THEN 0 ELSE 1 END AS BOOLEAN) AS IS\_HOLIDAY  
> FROM DATE\_SPINE
> 
> {{  
> config(  
> post\_hook=‘{{ add\_not\_null\_constraints([“DATE\_KEY”  
> ,“DAY\_OF\_YEAR”  
> ,“DAY\_SUFFIX”  
> ,“WEEK\_KEY”  
> ,“WEEK\_OF\_YEAR”  
> ,“DAY\_OF\_WEEK”  
> ,“WEEK\_DAY\_SHORT\_DESC”  
> ,“WEEK\_DAY\_DESC”  
> ,“FIRST\_DAY\_OF\_WEEK”  
> ,“LAST\_DAY\_OF\_WEEK”  
> ,“MONTH\_KEY”  
> ,“MONTH\_OF\_YEAR”  
> ,“DAY\_OF\_MONTH”  
> ,“MONTH\_SHORT\_DESC”  
> ,“MONTH\_DESC”  
> ,“FIRST\_DAY\_OF\_MONTH”  
> ,“LAST\_DAY\_OF\_MONTH”  
> ,“QUARTER\_KEY”  
> ,“QUARTER\_OF\_YEAR”  
> ,“DAY\_OF\_QUARTER”  
> ,“QUARTER\_SHORT\_DESC”  
> ,“QUARTER\_DESC”  
> ,“FIRST\_DAY\_OF\_QUARTER”  
> ,“LAST\_DAY\_OF\_QUARTER”  
> ,“YEAR\_KEY”  
> ,“FIRST\_DAY\_OF\_YEAR”  
> ,“LAST\_DAY\_OF\_YEAR”  
> ,“IS\_HOLIDAY”]) }}’  
> )  
> }}

 ![19 AM](https://us1.discourse-cdn.com/flex020/uploads/getdbt/original/1X/e1bf058af65a07620bbac735aca1c1ff8c54441c.png)

 ![58 AM](https://us1.discourse-cdn.com/flex020/uploads/getdbt/original/1X/296dd949b24841841d031cf8ccdf24a03eec1e7c.png)

---

<div class="post-metadata">

**Author:** ![emilie](https://sea2.discourse-cdn.com/flex020/user_avatar/discourse.getdbt.com/emilie/32/23_2.png) [@emilie](https://discourse.getdbt.com/u/emilie)\
**Post date:** [December 4, 2019, 12:33am UTC](https://discourse.getdbt.com/t/date-dimensions/735/2 "2019-12-04T00:33:15Z")

</div>

Hey @josh!

I wrote about the approach we took at GitLab around this a bit ago. Take a look and let me know what you think: [Building a Calendar Table using dbt](https://discourse.getdbt.com/t/building-a-calendar-table-using-dbt/325)

---

<div class="post-metadata">

**Author:** ![josh](https://avatars.discourse-cdn.com/v4/letter/j/45deac/32.png) [@josh](https://discourse.getdbt.com/u/josh)\
**Post date:** [December 4, 2019, 1:10am UTC](https://discourse.getdbt.com/t/date-dimensions/735/3 "2019-12-04T01:10:33Z")

</div>

Thanks @emilie I took a look at the date dimension code that you referenced. Looks like we have many similar columns. You also have some fiscal year columns that we don’t. On the other hand, we have some additional columns like DAY\_SUFFIX (e.g. “1st”, “2nd”), WEEK\_DAY\_DESC (not just “Wed” but “Wednesday”, "Thursday), some keys like MONTH\_KEY, and, most importantly, a holiday indicator and actual names for specific holidays.

Also I think some of the calculations in your date dimension could be simplified as we have done, e.g. instead of:

`FIRST_VALUE(date_day) OVER (PARTITION BY year_actual, month_actual ORDER BY date_day) AS first_day_of_month,`

you could do:

`TRUNC(date_day, 'Month')`

for this:

```
decode(extract('month',date_day),

           1, 'January',

           2, 'February',

           3, 'March',

           4, 'April',

           5, 'May',

           6, 'June',

           7, 'July',

           8, 'August',

           9, 'September',

           10, 'October',

           11, 'November',

           12, 'December') AS month_name,

```

you could do:

`TO_CHAR(date_day, 'MMMM')`

and for this:

```
(year_actual || '-' || DECODE(extract(quarter,date_day),

           1, 'Q1',

           2, 'Q2',

           3, 'Q3',

           4, 'Q4')) AS quarter_name,

```

you could do:

`'Q' || EXTRACT(QUARTER FROM date_day)`

Just simplifying and making the code cleaner. You could also simplify many of the other window functions in the same way using the TRUNC() and LAST\_DAY() functions.

Do you really not have a need for holiday calculations? We often need to do workday calculations or similar things and need to know the holidays for that.

---

<div class="post-metadata">

**Author:** ![claus](https://sea2.discourse-cdn.com/flex020/user_avatar/discourse.getdbt.com/claus/32/75_2.png) [@claus](https://discourse.getdbt.com/u/claus)\
**Post date:** [December 4, 2019, 3:05am UTC](https://discourse.getdbt.com/t/date-dimensions/735/4 "2019-12-04T03:05:05Z")

</div>

Very cool! Just wanted to mention this macro I added to the [dbt-date](https://github.com/calogica/dbt-date/) package a few weeks ago that does some of that as well.

> <https://github.com/calogica/dbt-date/blob/master/macros/get_date_dimension.sql>

---

<div class="post-metadata">

**Author:** ![emilie](https://sea2.discourse-cdn.com/flex020/user_avatar/discourse.getdbt.com/emilie/32/23_2.png) [@emilie](https://discourse.getdbt.com/u/emilie)\
**Post date:** [December 26, 2019, 8:08pm UTC](https://discourse.getdbt.com/t/date-dimensions/735/5 "2019-12-26T20:08:15Z")

</div>

@josh Thanks for the feedback. I created [https://gitlab.com/gitlab-data/analytics/merge\_requests/2121](https://gitlab.com/gitlab-data/analytics/merge_requests/2121) to make those improvements and add some global holidays. We haven’t had a need for them yet, but that’s no reason not to add it!

---

<div class="post-metadata">

**Author:** ![mampionona.rajao](https://sea2.discourse-cdn.com/flex020/user_avatar/discourse.getdbt.com/mampionona.rajao/32/2690_2.png) [@mampionona.rajao](https://discourse.getdbt.com/u/mampionona.rajao)\
**Post date:** [July 13, 2023, 11:58am UTC](https://discourse.getdbt.com/t/date-dimensions/735/6 "2023-07-13T11:58:57Z")

</div>

Hi there, how do you guys do with the function concat and year, mine is not working.

---

<div class="post-metadata">

**Author:** ![jedg83](https://sea2.discourse-cdn.com/flex020/user_avatar/discourse.getdbt.com/jedg83/32/5991_2.png) [@jedg83](https://discourse.getdbt.com/u/jedg83)\
**Post date:** [September 24, 2025, 8:29pm UTC](https://discourse.getdbt.com/t/date-dimensions/735/7 "2025-09-24T20:29:18Z")

</div>

I noticed the USA-s holiday are not working.

You’ve got an off-by-one weekday mapping in the holiday logic.

### What’s going wrong (in short)

- You use `DAYOFWEEK(date_key)` (Snowflake) for holiday rules, but you treat it like ISO numbering.

- In Snowflake, `DAYOFWEEK` returns **0=Sun, 1=Mon, 2=Tue, …, 6=Sat**.  
Your CASE assumes **1=Sun, 2=Mon** , so Monday holidays shift to Tuesday and Sunday holidays shift to Monday.

- There’s also a small bug where **Halloween** checks `dayofweek(date_key) = 31` instead of `dayofmonth(date_key) = 31`.

### Minimal, safe fix

1. Use ISO weekday **everywhere** (for both comparisons and ordinal grouping), i.e., `EXTRACT('dayofweek_iso', date_key)` where **1=Mon … 7=Sun**.

2. Fix the Halloween predicate.

Here’s a targeted patch you can apply to your model:

```sql
-- 1) Make ordinal weekday-of-month use ISO weekday to match the predicates
CAST(
  ROW_NUMBER() OVER (
    PARTITION BY YEAR(date_key), MONTH(date_key), EXTRACT('dayofweek_iso', date_key)
    ORDER BY date_key
  ) AS INT
) AS ordinal_weekday_of_month,

-- 2) Replace every `dayofweek(date_key)` comparison in the holiday CASE with ISO equivalents:
-- Monday -> EXTRACT('dayofweek_iso', date_key) = 1
-- Thursday-> EXTRACT('dayofweek_iso', date_key) = 4
-- Sunday -> EXTRACT('dayofweek_iso', date_key) = 7

CASE
  WHEN MONTH(date_key) = 1 AND DAYOFMONTH(date_key) = 1 THEN 'New Year''s Day'
  WHEN MONTH(date_key) = 1 AND DAYOFMONTH(date_key) = 20 AND ((YEAR(date_key) - 1) % 4) = 0 THEN 'Inauguration Day'
  WHEN MONTH(date_key) = 1 AND EXTRACT('dayofweek_iso', date_key) = 1 AND ordinal_weekday_of_month = 3 THEN 'Martin Luther King Jr Day'
  WHEN MONTH(date_key) = 2 AND DAYOFMONTH(date_key) = 14 THEN 'Valentine''s Day'
  WHEN MONTH(date_key) = 2 AND EXTRACT('dayofweek_iso', date_key) = 1 AND ordinal_weekday_of_month = 3 THEN 'President''s Day'
  WHEN MONTH(date_key) = 3 AND DAYOFMONTH(date_key) = 17 THEN 'Saint Patrick''s Day'
  WHEN MONTH(date_key) = 5 AND EXTRACT('dayofweek_iso', date_key) = 7 AND ordinal_weekday_of_month = 2 THEN 'Mother''s Day'
  -- Memorial Day: last Monday in May
  WHEN MONTH(date_key) = 5
       AND EXTRACT('dayofweek_iso', date_key) = 1
       AND DATEADD(day, 7, date_key) > LAST_DAY(date_key, 'Month') THEN 'Memorial Day'
  WHEN MONTH(date_key) = 6 AND EXTRACT('dayofweek_iso', date_key) = 7 AND ordinal_weekday_of_month = 3 THEN 'Father''s Day'
  WHEN MONTH(date_key) = 7 AND DAYOFMONTH(date_key) = 4 THEN 'Independence Day'
  WHEN MONTH(date_key) = 9 AND EXTRACT('dayofweek_iso', date_key) = 1 AND ordinal_weekday_of_month = 1 THEN 'Labor Day'
  WHEN MONTH(date_key) = 10 AND EXTRACT('dayofweek_iso', date_key) = 1 AND ordinal_weekday_of_month = 2 THEN 'Columbus Day'
  WHEN MONTH(date_key) = 10 AND DAYOFMONTH(date_key) = 31 THEN 'Halloween' -- fixed
  WHEN MONTH(date_key) = 11 AND EXTRACT('dayofweek_iso', date_key) = 4 AND ordinal_weekday_of_month = 4 THEN 'Thanksgiving Day'
  WHEN MONTH(date_key) = 12 AND DAYOFMONTH(date_key) = 25 THEN 'Christmas Day'
  WHEN MONTH(date_key) = 12 AND DAYOFMONTH(date_key) = 26 THEN 'Boxing Day'
END AS holiday_desc

```