Snowflake SQL Tutorial 44 - Recursive CTEs in Snowflake - I Run the Recursion by Hand So You Can See 1 + 5 + 2 = 8

Snowflake SQL · Episode 44

In this post we are going to cover recursive CTEs in Snowflake.

A recursive CTE is a CTE that refers to itself. We write two queries instead of one, with UNION ALL between them. The first one runs once and gives us the starting rows. The second one runs again and again, each time against the rows the previous run produced, and it stops when a run returns nothing.

We will run one against an org chart first. Then we will do the same walk by hand, with three ordinary queries and no recursion at all, so you can count the rows each pass adds. After that we will build a path up as we descend, start the recursion halfway down the tree, make rows out of no table at all, and check the whole thing against CONNECT BY by subtracting one result from the other.

Every query below was run on a real Snowflake account, and every result you see is the output it gave back.

Episode 44 of the Snowflake SQL: Zero to Hero series on TechBrothersIT.

On this page

1. Let's create some sample data
2. Let's start with the shape, then run one
3. Now let's run the recursion by hand
4. Now let's add the three passes up
5. Now let's build a value up as the recursion runs
6. Can we start somewhere other than the top?
7. Can we make rows out of nothing?
8. Does it agree with CONNECT BY? Let's subtract and find out
9. So what do we need to remember?

Let's create some sample data

Everything here runs against EMPLOYEES, which has eight rows. The column that matters today is MANAGER_ID. It holds another row's EMP_ID, and that is the only thing turning a flat table into a hierarchy.

Sample data

-- EMPLOYEES, from Episode 1. The column this episode is about is
-- MANAGER_ID: it points at another row's EMP_ID, which is what makes this flat
-- table a hierarchy.
USE DATABASE DEMO_DB;
USE SCHEMA PUBLIC;

CREATE OR REPLACE TABLE EMPLOYEES (
    EMP_ID          INT,
    FIRST_NAME      VARCHAR,
    LAST_NAME       VARCHAR,
    EMAIL           VARCHAR,
    DEPT_ID         INT,
    MANAGER_ID      INT,
    SALARY          NUMBER(10,2),
    COMMISSION_PCT  NUMBER(4,2),
    HIRE_DATE       DATE
);

-- Read the MANAGER_ID column. Daniel, EMP_ID 6, has NULL - he is the top of the
-- tree. Tomas reports to 3, which is Lena. Sofia reports to 5, which is Priya.
-- Everybody else reports to 6.
INSERT INTO EMPLOYEES VALUES
    (1, 'Aisha',  'Khan',      'aisha.khan@example.com',      10,    6, 118000.00, NULL,  '2019-03-11'),
    (2, 'Marcus', 'Reid',      'marcus.reid@example.com',     10,    6,  96500.00, NULL,  '2021-07-01'),
    (3, 'Lena',   'Fischer',   'lena.fischer@example.com',    20,    6, 104000.00, 0.05,  '2018-01-22'),
    (4, 'Tomas',  'Novak',     'tomas.novak@example.com',     20,    3,  87250.00, NULL,  '2022-09-05'),
    (5, 'Priya',  'Raman',     'priya.raman@example.com',     30,    6,  96500.00, 0.10,  '2020-11-17'),
    (6, 'Daniel', 'O''Connor', 'daniel.oconnor@example.com',  10, NULL, 131000.00, NULL,  '2016-05-30'),
    (7, 'Sofia',  'Marino',    'sofia.marino@example.com',    30,    5,  71400.00, 0.08,  '2023-02-13'),
    (8, 'Grace',  'Lin',       'grace.lin@example.com',     NULL,    6,  92000.00, NULL,  '2021-04-19');

Daniel, EMP_ID 6, has a NULL MANAGER_ID. He is the top of the tree, and that NULL is how a query finds the top without being told who it is.

Everybody else points at somebody. Tomas points at Lena, Sofia points at Priya, and the rest point at Daniel.

Not the usual NULL

Grace has a manager even though her DEPT_ID is NULL. The two columns are unrelated, and she appears in every tree on this page — worth noting because she goes missing in several other episodes for the department reason.

Let's start with the shape, then run one

A recursive CTE is WITH RECURSIVE, a name, a column list, and then two queries with UNION ALL between them.

The shape, before any real columns

WITH RECURSIVE TREE (EMP_ID, FIRST_NAME, MANAGER_ID, LVL) AS (

    <anchor>            -- an ordinary query. Runs ONCE.

    UNION ALL

    <recursive part>    -- refers to TREE. Runs again, and again, and
                        -- again, until it returns no rows.
)
SELECT ... FROM TREE;

There are three things to know about that shape.

• The anchor is an ordinary query. It runs once and produces the starting rows.
• The recursive part refers to the CTE's own name. It runs repeatedly, each time against the rows the previous run produced.
• It stops when a run returns no rows. That is the only termination rule, and it is why the recursive part needs something in it that eventually fails to match.

For an org chart the two halves read almost like English. The anchor is the person with no manager, at level 1. The recursive part is everybody whose manager is already in the tree, at that manager's level plus one.

Let's run it.

Query

WITH RECURSIVE TREE (EMP_ID, FIRST_NAME, MANAGER_ID, LVL) AS (
  SELECT EMP_ID, FIRST_NAME, MANAGER_ID, 1
  FROM EMPLOYEES
  WHERE MANAGER_ID IS NULL
  UNION ALL
  SELECT E.EMP_ID, E.FIRST_NAME, E.MANAGER_ID, T.LVL + 1
  FROM EMPLOYEES E
  JOIN TREE T ON E.MANAGER_ID = T.EMP_ID
)
SELECT EMP_ID, FIRST_NAME, MANAGER_ID, LVL
FROM TREE
ORDER BY LVL, EMP_ID;

Result8 rows · 4 columns

EMP_ID	FIRST_NAME	MANAGER_ID	LVL
6	Daniel	NULL	1
1	Aisha	6	2
2	Marcus	6	2
3	Lena	6	2
5	Priya	6	2
8	Grace	6	2
4	Tomas	3	3
7	Sofia	5	3

Eight rows, every employee, each carrying a LVL. One row at level 1, and the rest sorted underneath it.

Keep that LVL column in every recursive query you write. It costs nothing, and it is the difference between a recursion you can read and one you have to trust.

Now let's run the recursion by hand

“It just works” is not an explanation. So let's do the same walk with three ordinary queries and no recursion at all. Each one stamps the LVL the recursion would have given it.

First the anchor, on its own

Level 1 is the person whose MANAGER_ID is NULL.

The anchor, run as a plain query

SELECT EMP_ID, FIRST_NAME, MANAGER_ID, 1 AS LVL
FROM EMPLOYEES WHERE MANAGER_ID IS NULL;

Result1 row · 4 columns

EMP_ID	FIRST_NAME	MANAGER_ID	LVL
6	Daniel	NULL	1

One row, Daniel. This is what the recursive part will be handed on its first run.

Now the first recursive pass

Level 2 is everybody whose manager is Daniel. We write it here as an IN against the anchor's query rather than as a reference to a CTE, so there is nothing recursive about it.

The first pass, by hand

SELECT E.EMP_ID, E.FIRST_NAME, E.MANAGER_ID, 2 AS LVL
FROM EMPLOYEES E
WHERE E.MANAGER_ID IN (SELECT EMP_ID FROM EMPLOYEES WHERE MANAGER_ID IS NULL)
ORDER BY E.EMP_ID;

Result5 rows · 4 columns

EMP_ID	FIRST_NAME	MANAGER_ID	LVL
1	Aisha	6	2
2	Marcus	6	2
3	Lena	6	2
5	Priya	6	2
8	Grace	6	2

Five rows: Aisha, Marcus, Lena, Priya and Grace. These are the rows the second run will be handed.

And now the second recursive pass

Level 3 is everybody whose manager is one of those five.

The second pass, by hand

SELECT E.EMP_ID, E.FIRST_NAME, E.MANAGER_ID, 3 AS LVL
FROM EMPLOYEES E
WHERE E.MANAGER_ID IN (
  SELECT E2.EMP_ID FROM EMPLOYEES E2
  WHERE E2.MANAGER_ID IN (SELECT EMP_ID FROM EMPLOYEES WHERE MANAGER_ID IS NULL))
ORDER BY E.EMP_ID;

Result2 rows · 4 columns

EMP_ID	FIRST_NAME	MANAGER_ID	LVL
4	Tomas	3	3
7	Sofia	5	3

Two rows: Tomas, under Lena, and Sofia, under Priya.

Nobody reports to Tomas and nobody reports to Sofia. You can confirm that in the MANAGER_ID column of the sample data, where neither 4 nor 7 appears. So a third pass would return nothing, and returning nothing is what makes the recursion stop.

Now let's add the three passes up

Three passes, three grids, three row counts. Here they are together, with the source of every number named. This is the one line on the page you cannot check by looking at a single grid, so it is worth being careful about.

Pass	LVL it stamps	Rows	Where that number is
The anchor	1	1	Captured row count of the anchor grid above
First recursive pass	2	5	Captured row count of the first-pass grid above
Second recursive pass	3	2	Captured row count of the second-pass grid above
Third recursive pass	—	0	Returns nothing, so there is no grid to show — no row in the sample data has a MANAGER_ID of 4 or 7
Total		8	Addition of the three counted grids — and separately the captured row count of the org chart grid we ran earlier

So the three terms are each a captured row count, and only the total is arithmetic. The terms of that arithmetic are all visible above it, and its answer is independently the row count of a grid we have already seen. The two routes to 8 are not the same measurement twice.

And the recursion can count itself

There is one more way to get the same three numbers without running anything by hand. We let the recursive CTE group its own output by level.

Query

WITH RECURSIVE TREE (EMP_ID, MANAGER_ID, LVL) AS (
  SELECT EMP_ID, MANAGER_ID, 1 FROM EMPLOYEES WHERE MANAGER_ID IS NULL
  UNION ALL
  SELECT E.EMP_ID, E.MANAGER_ID, T.LVL + 1
  FROM EMPLOYEES E JOIN TREE T ON E.MANAGER_ID = T.EMP_ID
)
SELECT LVL, COUNT(*) AS ROWS_ADDED FROM TREE GROUP BY LVL ORDER BY LVL;

Result3 rows · 2 columns

LVL	ROWS_ADDED
1	1
2	5
3	2

ROWS_ADDED reads 1, 5, 2 down the three levels. Those are the same three numbers the hand-run produced, in the same order, this time from inside the recursion instead of outside it. Add that column up and we have the row count of the org chart again.

Reading the depth off the grid

Three rows in this grid means the recursion reached three levels. The recursive part itself ran three times: the third run returned nothing, which is both why the recursion stopped and why there is no fourth row here. A pass that finds no rows adds no level.

Now let's build a value up as the recursion runs

The recursive part can read any column of the previous pass, including one it built itself. That is how we accumulate a value as we descend.

In the anchor the path is just the name. In the recursive part it is the parent's path, a separator, and this person's name.

Query

WITH RECURSIVE TREE (EMP_ID, FIRST_NAME, MANAGER_ID, LVL, PATH) AS (
  SELECT EMP_ID, FIRST_NAME, MANAGER_ID, 1, FIRST_NAME
  FROM EMPLOYEES WHERE MANAGER_ID IS NULL
  UNION ALL
  SELECT E.EMP_ID, E.FIRST_NAME, E.MANAGER_ID, T.LVL + 1, T.PATH || ' > ' || E.FIRST_NAME
  FROM EMPLOYEES E JOIN TREE T ON E.MANAGER_ID = T.EMP_ID
)
SELECT LVL, FIRST_NAME, PATH FROM TREE ORDER BY PATH;

Result8 rows · 3 columns

LVL	FIRST_NAME	PATH
1	Daniel	Daniel
2	Aisha	Daniel > Aisha
2	Grace	Daniel > Grace
2	Lena	Daniel > Lena
3	Tomas	Daniel > Lena > Tomas
2	Marcus	Daniel > Marcus
2	Priya	Daniel > Priya
3	Sofia	Daniel > Priya > Sofia

Every row now carries the chain that reached it, and ORDER BY PATH puts the tree into reading order. Tomas sits directly beneath Lena rather than down among the other level 3 rows, because his path begins with hers.

That is the job SYS_CONNECT_BY_PATH did in Episode 36, done here with string concatenation and no special function.

Why the sort works

The ordering is an ordinary string sort, not a tree walk. It comes out as reading order here because every child's path starts with its parent's path, and the separator sorts before the letters that follow it. Change the separator to something that sorts after letters and the tidy nesting goes away while every value stays correct.

Can we start somewhere other than the top?

We can. Nothing about a recursive CTE requires the anchor to be the top of the tree. It is just a query. Point it at one employee and we get that employee's subtree.

Anchored on EMP_ID = 3

WITH RECURSIVE TREE (EMP_ID, FIRST_NAME, MANAGER_ID, LVL) AS (
  SELECT EMP_ID, FIRST_NAME, MANAGER_ID, 1 FROM EMPLOYEES WHERE EMP_ID = 3
  UNION ALL
  SELECT E.EMP_ID, E.FIRST_NAME, E.MANAGER_ID, T.LVL + 1
  FROM EMPLOYEES E JOIN TREE T ON E.MANAGER_ID = T.EMP_ID
)
SELECT EMP_ID, FIRST_NAME, MANAGER_ID, LVL FROM TREE ORDER BY LVL, EMP_ID;

Result2 rows · 4 columns

EMP_ID	FIRST_NAME	MANAGER_ID	LVL
3	Lena	6	1
4	Tomas	3	2

Two rows: Lena and the one person under her. Now have a look at the LVL column. Lena is at level 1 here, where she was at level 2 in the org chart.

Let's move the anchor and run the same query again.

Anchored on EMP_ID = 5

WITH RECURSIVE TREE (EMP_ID, FIRST_NAME, MANAGER_ID, LVL) AS (
  SELECT EMP_ID, FIRST_NAME, MANAGER_ID, 1 FROM EMPLOYEES WHERE EMP_ID = 5
  UNION ALL
  SELECT E.EMP_ID, E.FIRST_NAME, E.MANAGER_ID, T.LVL + 1
  FROM EMPLOYEES E JOIN TREE T ON E.MANAGER_ID = T.EMP_ID
)
SELECT EMP_ID, FIRST_NAME, MANAGER_ID, LVL FROM TREE ORDER BY LVL, EMP_ID;

Result2 rows · 4 columns

EMP_ID	FIRST_NAME	MANAGER_ID	LVL
5	Priya	6	1
7	Sofia	5	2

Priya at level 1 and Sofia at level 2. LVL is relative to the anchor, not to the company. It counts how many steps the recursion took to reach a row, and the anchor is step one by definition.

This is the difference worth knowing against CONNECT BY from Episode 36, whose START WITH in that episode was the root. Here the starting point is a predicate we choose, so everything under this node is a one-line change.

Can we make rows out of nothing?

Yes, and this part has nothing to do with hierarchies. The anchor is a query, and a query does not need a FROM clause. So a recursive CTE can generate rows that are not in any table.

Query

WITH RECURSIVE COUNTER (N) AS (
  SELECT 1
  UNION ALL
  SELECT N + 1 FROM COUNTER WHERE N < 10
)
SELECT N FROM COUNTER ORDER BY N;

Result10 rows · 1 column

N
1
2
3
4
5
6
7
8
9
10

Ten rows, out of nothing. The anchor is a single literal. The recursive part adds one to the previous row and keeps going while the condition in its WHERE clause holds.

The one thing that can run away

That WHERE clause is the stop condition, and it is the only one. Remove it and the recursive part never returns an empty result, so the query runs until Snowflake's own recursion limit stops it. When you write a generator, write the bound first.

Now let's make the version people actually need

Swap the integers for dates and the same four lines produce a calendar. The anchor is a date literal. The recursive part adds a month while the date is still before the last one we want.

Query

WITH RECURSIVE MONTHS (M) AS (
  SELECT DATE '2024-01-01'
  UNION ALL
  SELECT DATEADD(MONTH, 1, M) FROM MONTHS WHERE M < DATE '2024-12-01'
)
SELECT M AS MONTH_START FROM MONTHS ORDER BY M;

Result12 rows · 1 column

MONTH_START
2024-01-01
2024-02-01
2024-03-01
2024-04-01
2024-05-01
2024-06-01
2024-07-01
2024-08-01
2024-09-01
2024-10-01
2024-11-01
2024-12-01

Twelve rows, the first of every month. This is called a date spine, and it is the standard answer to a reporting problem that has nothing to do with recursion.

A month with no sales has no row in your sales table, so a GROUP BY on that table cannot report a zero for it. LEFT JOIN the spine to your data instead and the quiet months are there, with NULL measures you can turn into zeros.

Reading the bound

The stop condition compares against the last date you want, not the one after it. The recursive part only runs while the comparison holds, and the row it adds is one month past the row it read — which is why the grid ends on the month named in the WHERE clause rather than before it. Off-by-one errors in generators are almost always here.

Does it agree with CONNECT BY? Let's subtract and find out

Episode 36 did the org chart with CONNECT BY. Here is that query again, unchanged.

The CONNECT BY version

SELECT EMP_ID, FIRST_NAME, MANAGER_ID, LEVEL AS LVL
FROM EMPLOYEES
START WITH MANAGER_ID IS NULL
CONNECT BY MANAGER_ID = PRIOR EMP_ID
ORDER BY LEVEL, EMP_ID;

Result8 rows · 4 columns

EMP_ID	FIRST_NAME	MANAGER_ID	LVL
6	Daniel	NULL	1
1	Aisha	6	2
2	Marcus	6	2
3	Lena	6	2
5	Priya	6	2
8	Grace	6	2
4	Tomas	3	3
7	Sofia	5	3

Eight rows with a level column, and it looks the same as the org chart we ran with WITH RECURSIVE. But looks the same is not a claim worth making when we can execute the comparison instead.

So let's take the recursive result and subtract the CONNECT BY result with MINUS.

recursive result MINUS connect-by result

WITH RECURSIVE TREE (EMP_ID, LVL) AS (
  SELECT EMP_ID, 1 FROM EMPLOYEES WHERE MANAGER_ID IS NULL
  UNION ALL
  SELECT E.EMP_ID, T.LVL + 1 FROM EMPLOYEES E JOIN TREE T ON E.MANAGER_ID = T.EMP_ID
)
SELECT EMP_ID, LVL FROM TREE
MINUS
SELECT EMP_ID, LEVEL FROM EMPLOYEES
START WITH MANAGER_ID IS NULL
CONNECT BY MANAGER_ID = PRIOR EMP_ID;

Result0 rows · 2 columns

EMP_ID	LVL

Nothing comes back. An empty result from MINUS means every row on the left also appeared on the right. So the recursive answer is contained in the CONNECT BY answer.

Which is not yet the same as equal

A one-way MINUS on its own would also come back empty if CONNECT BY had returned extra rows the recursive version missed. Three facts together close that gap, and all three are on this page.

Fact	Where you can see it
The recursive result is a subset of the CONNECT BY result	The empty MINUS grid above
Both return the same number of rows	Both grids are eight rows: the org chart and the CONNECT BY version
Neither contains duplicates	EMP_ID is distinct down both grids, and there are as many distinct values as there are rows

A subset of the same size, with no duplicates on either side, is the same set. That is the claim the evidence supports, and it is the claim being made.

What was compared

Read the MINUS query's own text above the grid: it selects EMP_ID and the level, so that is what the comparison covers. The names and MANAGER_IDs in the wider grids come from the same table rows, keyed by the same EMP_ID, so nothing is hiding in the columns the subtraction left out — but it is the two columns that were actually compared.

Nothing on this page was timed, so nothing here says which of the two is faster. This is a correctness comparison. The reason to prefer the recursive CTE is the two things we did above: the anchor can be any predicate, and it does not need a table at all.

So what do we need to remember?

Recursive CTEs have a reputation for being hard, and almost all of it comes from being read as a single clever expression instead of as a loop with two halves.

1. WITH RECURSIVE, a name, a column list, and two queries with UNION ALL between them.
2. The anchor runs once. The recursive part runs against the previous pass's rows, again and again, and stops when a pass returns nothing.
3. Carry a level column. It is how you read the recursion afterwards — and grouping on it tells you how many rows each pass added.
4. When a recursion surprises you, run the passes by hand as ordinary queries. Three plain SELECTs accounted for every row of the org chart on this page.
5. The anchor is just a predicate, so the level is relative to wherever you start.
6. With no FROM clause the anchor invents rows, and the WHERE clause in the recursive part is the only thing that ends the query.

Key points

• A recursive CTE is WITH RECURSIVE, an anchor query, UNION ALL, and a recursive part that refers to the CTE by name.
• The recursion stops when a pass returns no rows. That is the whole termination rule.
• The three passes of the org chart on this page return 1, 5 and 2 rows — three captured grids — and the recursive query returns 8, which is both their sum and its own captured row count.
• Grouping the finished tree by level gives the same three counts from inside the recursion: the depth of the recursion is the number of rows in that grid.
• Keep a level column. It turns a recursion into something you can read, and it costs one expression in each half.
• The recursive part can read a column it built itself, which is how a path accumulates — the job SYS_CONNECT_BY_PATH does, with no special function.
• The level is relative to the anchor, not to the root. Anchor on somebody halfway down and they are at level 1.
• The anchor needs no FROM clause, so a recursive CTE can generate numbers or a date spine out of nothing — and then the WHERE clause in the recursive part is the only thing stopping it.
• The recursive result and the CONNECT BY result are the same set, established by an empty MINUS plus equal row counts plus no duplicates — not by the two grids looking alike.

Next in this series

Episode 45 — VALUES. The last post of this level, and another way to get rows without a table: a list of literals used directly in the FROM clause.

Every query and every result grid on this page was captured from a live Snowflake account (version 10.34.101) running the demo script for this episode, including the empty MINUS result in section 8. The EMPLOYEES data is invented demo data created by that script. No performance or benchmark claims are made anywhere in this series — in particular, nothing here was timed, so nothing is said about recursive CTEs versus CONNECT BY on speed. Clause behaviour follows the Snowflake recursive CTE documentation.