Date Dimension, Date Scaffold, Date Spine in Snowflake

Runner
#1

A quick and easy way to create a Date Dimension / Date Scaffold / Date Spine in Snowflake.
Common Use Case: I’m using snowflake as my data warehouse and I have a client table with sales data but my visual analytics isn’t looking correct because there are days with no data. How do I fill in the dates so that I have a record for every date even if there is no data?

2 Likes
#2

This is great! I find that adding date properties to the spine aids in transformations and criteria involving different date parts. For example, some companies have fiscal years that do not start in January, so storing fiscal year and quarter data against each date can save time, as they don’t need to be built out on demand. Here’s some example SQL for PostgreSQL with a fiscal year beginning in May:

CREATE TABLE date_spine
(
id serial not null,
full_date date,
day_of_week integer,
day_of_month integer,
day_of_year integer,
day_name varchar,
day_abbrv varchar,
week_num integer,
week_start_date date,
week_end_date date,
month_num integer,
month_name varchar,
month_abbrv varchar,
month_start_date date,
month_end_date date,
qtr_num integer,
qtr_name varchar,
qtr_start_date date,
qtr_end_date date,
year integer,
year_start_date date,
year_end_date date,
fiscal_qtr_num integer,
fiscal_qtr_name varchar,
fiscal_qtr_start_date date,
fiscal_qtr_end_date date,
fiscal_year integer,
fiscal_year_name varchar,
fiscal_year_start_date date,
fiscal_year_end_date date
);

INSERT INTO public.date_spine (full_date)
SELECT CAST('2000-01-01' AS date) + generate_series(0, 365*100+24)
;

UPDATE public.date_spine
SET day_of_week = date_part('dow', full_date)
, day_of_month = date_part('day', full_date)
, day_of_year = date_part('doy', full_date)
, day_name = to_char(full_date, 'Day')
, day_abbrv = to_char(full_date, 'Dy')
, week_num = date_part('week', full_date + INTERVAL '1 day')
, week_start_date = date_trunc('week', full_date + INTERVAL '1 day') - INTERVAL '1 day'
, week_end_date = date_trunc('week', full_date + INTERVAL '1 day') + INTERVAL '5 days'
, month_num = date_part('month', full_date)
, month_name = to_char(full_date, 'Month')
, month_abbrv = to_char(full_date, 'Mon')
, month_start_date = date_trunc('month', full_date)
, month_end_date = date_trunc('month', full_date + INTERVAL '1 month') - INTERVAL '1 day'
, qtr_num = to_char(full_date, 'Q')::INT
, qtr_name = concat('Q', to_char(full_date, 'Q'))
, qtr_start_date = date_trunc('quarter', full_date)
, qtr_end_date = date_trunc('quarter', full_date + INTERVAL '3 months') - INTERVAL '1 day'
, year = date_part('year', full_date)
, year_start_date = date_trunc('year', full_date)
, year_end_date = date_trunc('year', full_date + INTERVAL '1 year') - INTERVAL '1 day'
, fiscal_qtr_num = to_char(date_trunc('month', full_date) - INTERVAL '4 months', 'Q')::INT
, fiscal_qtr_name = concat('Q', to_char(date_trunc('month', full_date) - INTERVAL '4 months', 'Q'))
, fiscal_qtr_start_date = date_trunc('quarter', full_date - INTERVAL '4 months') + INTERVAL '4 months'
, fiscal_qtr_end_date = date_trunc('quarter', full_date - INTERVAL '1 month') + INTERVAL '4 months' - INTERVAL '1 day'
, fiscal_year = date_part('year', date_trunc('month', full_date) - INTERVAL '4 months')
, fiscal_year_name = concat(
CAST (date_part('year', date_trunc('month', full_date) - INTERVAL '4 months') AS VARCHAR)
, '/'
, CAST (date_part('year', date_trunc('month', full_date) + INTERVAL '8 months') AS VARCHAR)
)
, fiscal_year_start_date = date_trunc('year', full_date - INTERVAL '4 months') + INTERVAL '4 months'
, fiscal_year_end_date = date_trunc('year', full_date + INTERVAL '8 months') + INTERVAL '4 months' - INTERVAL '1 day'
;

CREATE INDEX IF NOT EXISTS date_spine_full_date_idx
ON date_spine (full_date);
4 Likes