Creating a date dimension in dbt and Snowflake

A lot of the data that I am working with doesn't lend itself to creating complex star schemas (more on data modelling in a later post) however I want to try and at least want to go to some effort. The one thing that pretty much all my tables have in common is date, so I want to build a date dimension. My strength training happens on a day, my running and walking, parkrun and hopefully the weather data I intend to integrate will have a date on it. This means if I have a date dimension and I aggregate my various facts at date level I can use them together in a single visualisation. 

There are various methods I could have used to create the date dimension and I went with using the dbt spine, this was really simple using the utilities: 

	{{ config (
	materialized="view"
	)}}
	{{ dbt_utils.date_spine(
	datepart="day",
	start_date="cast('2024-01-01' as date)",
	end_date="cast('2025-01-01' as date)"
	)
	}}

view raw dbtdatesall.sql hosted with ❤ by GitHub

Used the date functions in Snowflake to extract relevant fields such as year, month, day in words and year month etc. 

	{{ config (
	materialized="table"
	)}}
	select date_day,
	year(date_day) date_year,
	month(date_day) date_month,
	quarter(date_day) date_quarter,
	DAYOFWEEK( date_day ) day_of_week,
	DAYOFYEAR( date_day ) day_of_year,
	WEEK( date_day ) week_of_day,
	DAYNAME( date_day ) day_name,
	date_day -1 lag_date_1,
	year(date_day)*100+ lpad(month(date_day),2,'0') year_month
	FROM
	{{ source('exercise_data', 'ALL_DATES') }}

view raw datedimSnowfale.sql hosted with ❤ by GitHub