Recursive CTEs (Common Table Expressions) function as any ordinary CTE, with the additional functionality of referencing itself – hence 'recursive.' These CTEs are useful for querying hierarchical information, such as organizational charts or family trees, that require you to keep moving up one layer of data until you've reached the level of granularity desired.
A lesser common but nonetheless powerful use case for recursive CTEs is creating a data scaffold (see my previous blog about data scaffolds and why they're important for data analysis). Below, I'll walk you through the steps necessary to generate all dates for 2026 using SQL in Snowflake (note: if you're using SQL elsewhere, you may need to adapt the syntax used here).
This is the code:
And this is what each line is doing:
with cte_count as – creates the name for our CTE and sets the stage for the rest of the code to be entered subsequently.
select to_date('2026-01-01' , 'YY-MM-DD') as date – tells SQL to turn a string into a date field with a certain format and call it 'date.' This is also called our "Anchor Member," which is a necessary line of code when creating a recursive CTE. It's the starting point of our recursion.
union all – creates a list of all dates in 2026 stacked on top of each other. 'Union all' won't look for duplicates – as opposed to 'union' – which is better for performance in recursion.
select date + 1 – generates the next date in the sequence from the previous row.
from cte_count – references the CTE itself, which is what makes everything recursive.
where date < '2026-12-31' – tells SQL when to stop creating new rows. This is the "Termination Condition." Without a Termination Condition, the query would never stop running.
) – closes the CTE
select date from cte_count – the final query that returns a complete list of all dates in 2026. This is run until the 'where' clause is met.
Beyond being able to write the code for a recursive CTE, it's important to understand how the code is being executed on the backend, especially as it's not being processed from top to bottom (logical, right?). Below is the order of operations:
And this is how the entire query is processed:
- First, SQL identifies the data source (cte_count).
- It then creates the first row: 2026-01-01.
- SQL combines the first result with the recursive results.
- References itself to start the recursion.
- Checks if the loop should continue.
- Generates the next row if the previous condition has been met.
- Returns all rows.