Create or download Date Dimension (dim_date)

In analytics, often times you need a table, that contains a continuous list of dates, 1 row per date. Here is where to get it from:

(1) Download from: https://raw.githubusercontent.com/devlace/datadevops/master/data/seed/DimDate.csv

(2) Auto-generate in Snowflake using this SQL:

select seq4() DAYS_FROM_JAN1_2000, dateadd(day, seq4(), TO_Date('2000-01-01')) as FULL_DATE
from   table    (generator(rowcount => 10950)) 
-- 10950 = 30 years

Output:

(3) Auto-generate in Snowflake using this SQL. This code will generate the number of days back from today, where today is 0. The key for the same date will changes from run to run.

select seq4() DAYS_FROM_TODAY, dateadd(day, '-' || seq4(), current_date()) as FULL_DATE
from   table     (generator(rowcount => 1095)) 
-- 1095 = 3 years

1 Like