Recursive CTEs in BigQuery

WITH RECURSIVE in BigQuery needs UNION ALL, one self-reference, the same column types in both terms, and stops with an error after 500 iterations. Cycles in graph data must be cut by hand.

A recursive CTE is a WITH RECURSIVE query that refers to itself. It has a base term, which runs once, and a recursive term, which runs again on the rows the previous round produced, until a round produces no rows. In BigQuery the two terms are joined with UNION ALL (nothing else is allowed), there can be exactly one reference to the CTE in the recursive term, and the query fails after 500 iterations. Use it for hierarchies and graphs of unknown depth. For plain number or date series, GENERATE_ARRAY is simpler.

The shape

WITH RECURSIVE counter AS (
  SELECT 1 AS n
  UNION ALL
  SELECT n + 1 FROM counter WHERE n < 5
)
SELECT n FROM counter ORDER BY n

The base term SELECT 1 AS n produces the first row. The recursive term reads the rows from the previous round (FROM counter), adds 1, and stops when WHERE n < 5 filters everything out. The CTE’s result is the union of all rounds. Without an ORDER BY the order of rows is not defined, so sort if it matters.

Walking a hierarchy

The usual use is a table of parent and child ids. The base term picks the root, and each round joins the previous round’s rows to their children:

WITH RECURSIVE
  employees AS (
    SELECT 1 AS id, CAST(NULL AS INT64) AS manager_id, 'Ada' AS name UNION ALL
    SELECT 2, 1, 'Ben' UNION ALL
    SELECT 3, 1, 'Cy' UNION ALL
    SELECT 4, 2, 'Di' UNION ALL
    SELECT 5, 4, 'Ed'
  ),
  chain AS (
    SELECT id, name, 0 AS depth, name AS path
    FROM employees
    WHERE manager_id IS NULL
    UNION ALL
    SELECT e.id, e.name, c.depth + 1, CONCAT(c.path, ' > ', e.name)
    FROM chain AS c
    JOIN employees AS e ON e.manager_id = c.id
  )
SELECT id, name, depth, path
FROM chain
ORDER BY id

Each round adds one level of the tree, so the number of rounds is the depth of the deepest branch. A hierarchy deeper than 500 levels can’t be walked this way.

The limit counts the base term as the first iteration. A counter with WHERE n < 500 runs and returns 500 rows. With WHERE n < 501 it needs 501 iterations and fails with “A recursive CTE has reached the maximum number of iterations: 500.” (we ran both). These two runs aren’t examples on the page because the failing one takes over two minutes.

Graphs: paths multiply

In a graph where a node can be reached by several routes, UNION ALL keeps one row per route, not one row per node. In this graph, node 4 can be reached from 2 and from 3:

WITH RECURSIVE
  edges AS (
    SELECT 1 AS from_node, 2 AS to_node UNION ALL
    SELECT 1, 3 UNION ALL
    SELECT 2, 4 UNION ALL
    SELECT 3, 4 UNION ALL
    SELECT 4, 5
  ),
  reachable AS (
    SELECT 1 AS node
    UNION ALL
    SELECT e.to_node FROM reachable AS r JOIN edges AS e ON e.from_node = r.node
  )
SELECT node, COUNT(*) AS times_reached
FROM reachable
GROUP BY node
ORDER BY node

If you only want the set of reachable nodes, finish with SELECT DISTINCT node. Counting routes can blow up fast on large graphs, because every route is a row that the next round has to process.

Cycles never end

If the data has a cycle, a recursive term that always finds a next row never returns an empty round. BigQuery stops it at 500 iterations. We ran this query on a three-node cycle a > b > c > a:

WITH RECURSIVE
  edges AS (
    SELECT 'a' AS from_node, 'b' AS to_node UNION ALL
    SELECT 'b', 'c' UNION ALL
    SELECT 'c', 'a'
  ),
  walk AS (
    SELECT 'a' AS node
    UNION ALL
    SELECT e.to_node FROM walk AS w JOIN edges AS e ON e.from_node = w.node
  )
SELECT COUNT(*) AS steps FROM walk

It ran for about 2 minutes and 17 seconds and ended with:

A recursive CTE has reached the maximum number of iterations: 500.

So each iteration takes a fraction of a second, even for three rows, and a runaway recursion is slow before it fails. This example is not on the page for that reason.

UNION DISTINCT is not a way out. BigQuery doesn’t allow it in a recursive CTE:

WITH RECURSIVE counter AS (
  SELECT 1 AS n
  UNION DISTINCT
  SELECT n + 1 FROM counter WHERE n < 3
)
SELECT n FROM counter

The usual fix is to carry the path in an array and refuse to step to a node already in it. The check is a plain NOT IN UNNEST(array) on a column of the previous round, not a subquery on the CTE:

WITH RECURSIVE
  edges AS (
    SELECT 'a' AS from_node, 'b' AS to_node UNION ALL
    SELECT 'b', 'c' UNION ALL
    SELECT 'c', 'a' UNION ALL
    SELECT 'c', 'd'
  ),
  walk AS (
    SELECT 'a' AS node, ['a'] AS visited
    UNION ALL
    SELECT e.to_node, ARRAY_CONCAT(w.visited, [e.to_node])
    FROM walk AS w
    JOIN edges AS e ON e.from_node = w.node
    WHERE e.to_node NOT IN UNNEST(w.visited)
  )
SELECT node, ARRAY_TO_STRING(visited, '>') AS path
FROM walk
ORDER BY path

Node c links back to a, but a is already in the path a>b>c, so that route ends. Add a depth counter with a cap in the WHERE as a second guard if the path array could get large.

Rules the recursive term must follow

The rules are enforced with errors. These are the ones we ran into.

RECURSIVE is required. Without it the CTE name isn’t visible inside its own definition:

WITH counter AS (
  SELECT 1 AS n
  UNION ALL
  SELECT n + 1 FROM counter WHERE n < 3
)
SELECT n FROM counter

Only one reference to the CTE is allowed in the recursive term, so you can’t join it to itself, and a reference inside a subquery in WHERE counts as a second one:

WITH RECURSIVE counter AS (
  SELECT 1 AS n
  UNION ALL
  SELECT a.n + b.n FROM counter AS a JOIN counter AS b ON TRUE WHERE a.n < 3
)
SELECT n FROM counter

The recursive term must produce the type of the base term. BigQuery does not widen it: SELECT 1 is INT64, so a recursive term that returns FLOAT64 fails:

WITH RECURSIVE total AS (
  SELECT 1 AS n
  UNION ALL
  SELECT n + 0.5 FROM total WHERE n < 3
)
SELECT n FROM total

Make the base term the wider type:

WITH RECURSIVE total AS (
  SELECT 1.0 AS n
  UNION ALL
  SELECT n + 0.5 FROM total WHERE n < 2
)
SELECT n FROM total ORDER BY n

The recursive term can’t have ORDER BY:

WITH RECURSIVE counter AS (
  SELECT 1 AS n
  UNION ALL
  SELECT n + 1 FROM counter WHERE n < 3 ORDER BY n
)
SELECT n FROM counter

The dry run does not catch every rule

Google’s reference lists aggregation and window functions on the CTE as disallowed in the recursive term. A dry run of such queries passes, and the failure comes only when the query runs. This one fails at run time with a message that doesn’t match the cause:

WITH RECURSIVE counter AS (
  SELECT 1 AS n
  UNION ALL
  SELECT n + 1 FROM counter WHERE n < 3 GROUP BY n
)
SELECT n FROM counter

The message mentions IN and EXISTS, but the query has neither. We saw the same message for SUM(n) OVER () in the recursive term. A dry run of GROUP BY, SUM() OVER and COUNT(*) all validated with 0 bytes, so a clean dry run is not proof that a recursive query will run.

Alternatives for series

If the number of rounds is known, you don’t need recursion. A series of dates or numbers is one function call, and it is a single step instead of up to 500 rounds:

SELECT day
FROM UNNEST(GENERATE_DATE_ARRAY(DATE '2026-03-01', DATE '2026-03-05')) AS day
ORDER BY day

For graph traversal with a known maximum depth, a loop in scripting (REPEAT with an INSERT into a temp table) is the other option. Google’s guide suggests that loop for finding out whether a recursion terminates.

Materialization

Google’s reference notes that BigQuery materializes the result of a recursive CTE but not of a non-recursive one, which is executed once per reference.