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?

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.

Check out more snowflake tips in this cheat sheet:

Snowflake Cheat Sheet | Zuar
In this blog post we will be documenting common questions and answers we see in the 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();…

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 suggest some sort of ETL tool like Mitto. This will allow you to run all sorts of transformation jobs while placing them all on automated schedules and monitoring for any possible errors. Mitto also has prebuilt integration with a number of database technologies, including Snowflake!