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:
![](https://www.thedataschool.co.uk/content/images/2025/02/image-56.png)
Once this has been achieved a date table can be created using the following syntax:
![](https://www.thedataschool.co.uk/content/images/2025/02/image-54.png)
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.
![](https://www.thedataschool.co.uk/content/images/2025/02/image-55.png)
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'.
![](https://www.thedataschool.co.uk/content/images/2025/02/image-57.png)
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:
![](https://www.thedataschool.co.uk/content/images/2025/02/image-58.png)
We have now successfully made a scaffolded date table and joined it back to our initial table!