Recursive CTEs in Production: Org Charts, Bill of Materials, and Cycle Protection

The first recursive CTE I put in production was an org chart rollup. It worked for eight months. Then HR loaded a record where an employee’s manager was, through four hops, the employee himself, and the query ran until the warehouse killed it.

Nothing about the SQL was wrong. Recursive CTEs terminate when a pass produces no new rows, and a cycle guarantees every pass produces new rows forever. The data was wrong, and the query had no opinion about that.

That is the theme of this post. WITH RECURSIVE is a genuinely good tool for hierarchies, graph traversal, and generated sequences, and it is one of the few places in SQL where a missing guard turns into an outage rather than a wrong number. So the patterns here always include the guard.

I will also make the case that for read-heavy hierarchies you should stop recursing at query time and precompute a closure table, because a 200-millisecond recursion executed on every dashboard load is a bad trade you can make once and forget.

How WITH RECURSIVE actually runs

The name is misleading. There is no call stack. A recursive CTE is a fixed-point iteration:

1. Run the anchor member once. Its output becomes the working set and goes into the result.
2. Run the recursive member against the working set only — not the full accumulated result.
3. Whatever it produces becomes the new working set and is appended to the result.
4. Repeat until step 2 produces zero rows.

flowchart TD
  anchor["Anchor: seed rows"] --> ws["Working set"]
  ws --> rec["Recursive member joins working set to base table"]
  rec --> new{"Rows produced?"}
  new -->|Yes| append["Append to result, replace working set"]
  append --> rec
  new -->|No| done["Terminate, return accumulated result"]

Each pass sees only the previous pass. Termination depends entirely on eventually producing zero new rows.

Two consequences follow directly and explain most confusion.

The recursive member cannot see the full accumulated result, only the last pass. So “have I already visited this node?” is not a question you can ask by referencing the CTE — you have to carry the answer forward in a column. That is why every cycle guard in this post threads a path down through the recursion.

The number of passes equals the depth of the hierarchy, and each pass is a join. A 12-level org chart is 12 joins. This is also why none of the CTE inlining discussion from the CTE materialization post applies here: a recursive CTE is always materialized iteratively.

The keyword requirements differ. Postgres, SQL Server, and DuckDB require WITH RECURSIVE. Snowflake accepts it and also allows a plain WITH for recursive queries. BigQuery requires WITH RECURSIVE. Write it explicitly everywhere.

The org chart pattern

The canonical case: an employees table with employee_id and manager_id, and you want every employee’s depth, path, and top-level executive.

with recursive org as (
  -- Anchor: employees with no manager are the roots.
  select
    employee_id,
    manager_id,
    full_name,
    1                                   as depth,
    employee_id                         as root_id,
    array_construct(employee_id)        as path,
    full_name                           as path_names
  from dim_employees
  where manager_id is null

  union all

  -- Recursive: attach direct reports of the current working set.
  select
    e.employee_id,
    e.manager_id,
    e.full_name,
    o.depth + 1,
    o.root_id,
    array_append(o.path, e.employee_id),
    o.path_names || ' > ' || e.full_name
  from dim_employees e
  join org o on o.employee_id = e.manager_id
  where o.depth < 20                              -- hard depth cap
    and not array_contains(e.employee_id::variant, o.path)  -- cycle guard
)
select employee_id, full_name, depth, root_id, path_names
from org
order by path_names;

Three details carry the weight.

The depth cap. o.depth < 20 is a circuit breaker. Real org charts are under 15 levels; if you hit 20, the data is broken and you want to stop, not to keep going. I set the cap at roughly double the plausible maximum and alert when any row reaches it.

The path array. array_contains on the accumulated path is the real cycle guard. A depth cap alone stops the runaway but still emits up to 20 levels of garbage rows for the cycle. The path check refuses to traverse into a node already on this branch, so a cycle simply stops there and the rest of the tree is unaffected.

UNION ALL, not UNION. UNION deduplicates on every pass, which is expensive and, worse, some engines reject it in recursive CTEs. It is also not a cycle guard: with a path column every row is distinct anyway, so UNION costs a sort and prevents nothing.

Syntax for arrays varies. Postgres: array[employee_id], path || e.employee_id, e.employee_id <> all(o.path). BigQuery: [employee_id], array_concat(path, [e.employee_id]), and e.employee_id not in unnest(o.path). Same shape, different spellings.

To find orphans — rows whose manager_id points at nothing — compare the recursion output against the base table:

select e.employee_id, e.manager_id
from dim_employees e
where not exists (select 1 from org o where o.employee_id = e.employee_id);

Any row here is either an orphan or inside a cycle. Both are data quality failures and belong in a test that fails the build, in the spirit of the dbt testing strategy post.

Bill of materials: the multiplication people forget

BOM explosion looks like an org chart with an extra column, and that extra column is where the bugs live. Each edge carries a quantity, and the required quantity of a component at depth N is the product of every quantity along the path.

with recursive bom as (
  select
    p.product_id                as root_product_id,
    c.component_id,
    c.quantity                  as qty_per_unit,
    1                           as level,
    array_construct(p.product_id, c.component_id) as path
  from dim_products p
  join product_components c on c.product_id = p.product_id
  where p.product_id = 'BIKE-001'

  union all

  select
    b.root_product_id,
    c.component_id,
    -- Multiply down the path. Adding here is the classic bug.
    b.qty_per_unit * c.quantity,
    b.level + 1,
    array_append(b.path, c.component_id)
  from bom b
  join product_components c on c.product_id = b.component_id
  where b.level < 15
    and not array_contains(c.component_id::variant, b.path)
)
select
  component_id,
  -- Same component can appear on several branches. Sum at the end.
  sum(qty_per_unit) as total_qty,
  min(level)        as first_appears_at
from bom
group by component_id
order by total_qty desc;

The sum at the end is not optional. A bolt used in three subassemblies appears three times in the exploded output with different quantities, and the material requirement is the sum across branches. A DISTINCT here would be exactly the kind of mistake the fan-out post is about: the rows are not duplicates, they are legitimately different paths to the same part.

Note also that a BOM is a directed acyclic graph, not a tree. The same component reachable by two routes is normal and correct. The path-based cycle guard handles this properly, because it only blocks revisiting a node on the current branch, not one already seen on a different branch.

Cycle protection that actually works

Three levels of protection, in increasing order of usefulness.

Depth cap only. where depth < 20. Stops the runaway. Still emits 20 rows of nonsense per cycle, and if the legitimate hierarchy is deeper than your cap you silently truncate real data. Necessary, not sufficient.

Path membership check. Carry an array or delimited string of visited nodes and refuse to extend into a member. This is the one that actually works, and it costs one array append per row.

Native cycle detection. Postgres 14+ and SQL Server have CYCLE clause support:

with recursive org as (
  select employee_id, manager_id, 1 as depth
  from dim_employees where manager_id is null
  union all
  select e.employee_id, e.manager_id, o.depth + 1
  from dim_employees e join org o on o.employee_id = e.manager_id
) cycle employee_id set is_cycle using cycle_path
select * from org where not is_cycle;

Cleaner where available, and it maintains the path array for you. Not portable — Snowflake and BigQuery have neither — so in a multi-warehouse dbt project I still hand-roll the path column.

Whichever you use, detect and alert, do not just survive:

-- Any row that hit the cap is a cycle or an unexpectedly deep tree.
select root_id, count(*) as rows_at_cap
from org
where depth >= 20
group by root_id;

Zero rows or the build fails. A recursive query that quietly truncates is worse than one that errors, because the report still renders.

Generating sequences and date spines

The other everyday use of recursion has nothing to do with hierarchies: producing rows that do not exist in any table.

-- Postgres and BigQuery have generate_series / generate_date_array.
-- Everywhere else, recursion is the portable spine.
with recursive dates as (
  select date '2026-01-01' as d
  union all
  select dateadd(day, 1, d) from dates where d < date '2026-12-31'
)
select d from dates;

Use the built-in when your engine has one — generate_series on Postgres and DuckDB, generate_date_array on BigQuery, table(generator(rowcount => n)) on Snowflake. They are faster and clearer. Recursion is the fallback, and honestly a date spine should be a persisted dimension table rather than regenerated in every model that needs one.

Date spines matter for gap filling, which pairs directly with the frame behavior in the window frames deep dive — a trailing-7-day average over a series with missing days needs either a spine or a value-based frame.

When to stop recursing: closure tables

If a hierarchy is queried far more often than it changes — which describes nearly every org chart, category tree, and account hierarchy I have worked with — precompute it.

A closure table stores every ancestor-descendant pair with the distance between them. Build it once per load with the recursive query, then every downstream query is a plain join.

create or replace table dim_org_closure as
with recursive paths as (
  select employee_id as ancestor_id, employee_id as descendant_id, 0 as distance
  from dim_employees
  union all
  select p.ancestor_id, e.employee_id, p.distance + 1
  from paths p
  join dim_employees e on e.manager_id = p.descendant_id
  where p.distance < 20
)
select * from paths;

-- Downstream: no recursion, just a join.
-- All revenue under one VP, at any depth.
select sum(o.amount)
from fct_orders o
join dim_org_closure c on c.descendant_id = o.owner_employee_id
where c.ancestor_id = 'VP-042';

The closure table is roughly nodes x average_depth rows — a 10,000-person org at average depth 6 is around 60,000 rows, which is nothing. Rebuild it in the same job that loads the dimension.

The alternative precomputation is a materialized path column ('/1/17/93/') with prefix matching via LIKE '/1/17/%'. Smaller, and it works well when you mostly ask “everything under X.” The closure table is better when you also ask “how far apart are these two nodes” or need to join on distance.

I default to the closure table for anything a BI tool touches. Ad hoc recursion is for analysis, one-off investigations, and the nightly job that builds the closure table.

Pitfalls

No depth cap. One self-referencing row and the query runs until a timeout or a credit alarm. Every recursive CTE that reads a table containing user-entered relationships needs a cap.

Depth cap without a path guard. The cap stops the runaway but emits garbage rows up to the limit, and those rows land in aggregates. Carry the path.

UNION instead of UNION ALL. Deduplicates on every pass, costs a sort per level, prevents no cycles once a path column is present, and is rejected outright by some engines.

Adding quantities in a BOM instead of multiplying. Produces material requirements that are wrong rather than absent, which means procurement acts on them.

Referencing the recursive CTE twice in its own body. Not allowed on most engines, and where it is tolerated the semantics differ. One reference per recursive member.

Recursing on every dashboard query. Depth passes over a large dimension, executed hundreds of times a day, for a hierarchy that changes weekly. Build the closure table.

Missing anchors. If manager_id uses an empty string or a self-reference instead of NULL for roots, your anchor returns nothing and the whole CTE is empty. Check the anchor in isolation first.

FAQ

Why does my recursive CTE return no rows?

Almost always the anchor. Run the anchor member alone — if roots are marked with '', -1, or a self-reference rather than NULL, where manager_id is null matches nothing and the iteration never starts.

How deep can a recursive CTE go?

Engines impose limits: Snowflake defaults to 100 iterations, SQL Server to 100 via MAXRECURSION, Postgres has no hard limit and will happily run until you run out of memory. Your own cap should be well below the engine’s so you control the failure.

Is a recursive CTE slow?

Each level is a join, so cost scales with depth times the size of the working set. For a 10-level hierarchy over 100,000 rows it is fast. For deep graph traversal over millions of edges it is the wrong tool — use a graph engine or precompute.

Can I use window functions inside a recursive member?

Generally no. Most engines forbid aggregates and window functions in the recursive term because they would need the full accumulated result, which the iteration model does not provide. Apply them in the outer query over the finished CTE.

How do I find cycles before they break a build?

Run the recursion with a generous cap and flag any row that reaches it, or check for cycles of length one and two directly with self-joins. The path array from a full traversal gives you the exact offending chain, which is what you send to the data owner.

Should hierarchies live in the warehouse at all?

For rollups and reporting, yes — a closure table joins cleanly to facts and every BI tool understands it. For genuine graph workloads with variable-length shortest paths and centrality measures, a graph database earns its place. The dividing line is whether traversal depth is bounded and small.

What this means for your pipelines

Recursive CTEs are one of the few SQL constructs where a data quality problem becomes an availability problem. A duplicate key gives you a wrong number; a cycle gives you a query that never returns. That asymmetry should change how you write them: the guard is part of the pattern, not a hardening step you do later.

The template I reuse is always the same four pieces. An anchor that I have verified returns rows on its own. UNION ALL with a path array threaded through. A depth cap set to roughly double the plausible maximum. And a test that fails the build when any row reaches the cap, so a bad edge surfaces as a red CI run instead of a stalled dashboard.

Then ask how often the result is read. If the answer is “on every page load,” the recursion belongs in a nightly job that writes a closure table, and the query path becomes an ordinary join with ordinary pruning and ordinary cost. Recursion is a build-time tool. Joins are a query-time tool. Most of the pain I have seen with WITH RECURSIVE came from putting it on the wrong side of that line.