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”]) }}’
)
}}
