Date Scaffolding in Snowflake

I am currently aiming to complete every 2021 Preppin' Data challenge in Snowflake. The link to my progress can be found at the bottom of this blog

In my most recent challenge I ran into an issue that required scaffolding. Scaffolding is used when you have missing rows in a dataset. For example, a company may not make any sales on a given day and, therefore, the dataset would have a gap for that day. Scaffolding helps by filling in those gaps, so that operations like time series analysis or trend forecasting can still be performed without the disruption of missing values.


To create a table of dates, firstly you need to collect the earliest and latest date in your data set:


Once this has been achieved a date table can be created using the following syntax:

I will break down each part of the syntax above, to help explain how it works:

From clause

The from clause here is creating a table with the same number of rows as there are dates between my start and end point. In my case, I want a row for every minute, so my datediff is calculating the number of minutes between my start and end point. An array is then created, counting from 0 to the total number of minutes. This array is subsequently flattened so each value becomes a unique row.

Select clause

We now have our total number of rows. To generate our dates we can use the Row_Number() function. We can add this row number to our start_date to obtain our full list of date values across each row. In my example I have used 'minute' but the same logic applies when trying to scaffold other granularities of dates, such as 'day' or 'month'.


Now we have our date table, the last step is to join this back to our initial dataset. To do this, we want can state that a join should occur on any date greater than or equal to our smallest date, or any date less than our largest date. This can be achieved using the following syntax:

We have now successfully made a scaffolded date table and joined it back to our initial table!


GitHub - Dan-Booth-Data/PreppinData
Contribute to Dan-Booth-Data/PreppinData development by creating an account on GitHub.
Author:
Dan Booth
Powered by The Information Lab
1st Floor, 25 Watling Street, London, EC4M 9BR
Subscribe
to our Newsletter
Get the lastest news about The Data School and application tips
Subscribe now
© 2025 The Information Lab