SNOWFLAKE HOW TO: Create Date Dimensions Table, Calendar Table, 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.

  1. 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.

Snowflake Cheat Sheet | Zuar
In this blog post we will be documenting common questions and answers we see inthe field from Snowflake users and Snowflake account admins. What’s my current user, role, warehouse, database, etc? SELECT CURRENT_USER();SELECT CURRENT_ROLE();SELECT CURRENT_WAREHOUSE();SELECT CURRENT_DATABASE();…
The Pros and Cons of Snowflake Cloud Data Warehouse | Zuar
What are Snowflake’s pros and cons? Learn about Snowflake benefits, considerations, and more.
Snowflake vs Redshift vs BigQuery: Comparisons & How to Choose | Zuar
Unsure which data warehouse is best for your organization? We compare different aspects of Snowflake, Amazon Redshift, and Google BigQuery.