SNOWFLAKE HOW TO: Create Date Dimensions Table, Calendar Table, Date Scaffold, or Date Spine
Learn how to create a Date Dimensions or Calendar Table, also known as a Date Scaffold or Date Spine.
Use Case: I'm using Snowflake as my data warehouse and I have a client table with sales data, but my visual analytics aren'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?
Answer: Create a Date Dimensions or Calendar Table, also known as a Date Scaffold or Date Spine.
Build a Dates Table From Scratch
For this technique, you do not need any pre-existing date table.
- Create a temp table that contains the start and end dates of the date range so that you can calculate a date diff from the start and end dates.
create temp table date_dummy_1(days int) as
select datediff('day', '2020-01-01', current_date);The above statement will create a temp table called date_dummy_1 with the dat diff of 2020-01-01 to today or current_date.
2. Create your final table or view using the date diff created above.
CREATE OR REPLACE TABLE SCHEMA.MY_DATE_DIMENSION (
MY_DATE DATE NOT NULL
)
AS
WITH CTE_MY_DATE AS (
SELECT DATEADD(DAY, SEQ4(), '2020-01-01') AS MY_DATE
FROM TABLE(GENERATOR(ROWCOUNT=>(SELECT MAX(days) FROM date_dummy_1)))
)
SELECT MY_DATE
FROM CTE_MY_DATE;The above statement will create a table with one column MY_DATE that includes every day from January 1st, 2020 to today.
3. You will need to join this table to your client table. Most often you will find that and inner join on 1 = 1 will do the trick. Then left join your primary table back to this client date dimension table.
This will ensure that each client in your client table is joined to every single date in your date dimensions table.
Final Thoughts
The above will get you your desired Date Dimensions/Date Scaffold/Date Spine Table. You should then consider implementing an automated process that allows you to keep this Data Dimensions table up-to-date. I strongly recommend an ETL tool like Zuar Runner. This solution allows you to run all sorts of transformation jobs while placing them all on automated schedules (all while monitoring the jobs for any possible issues). Zuar Runner also has pre-built integration with a number of database technologies, including Snowflake! Learn more.
