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.
- 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
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:
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!