4
Accepted answer
Start with the execution plan, numbers beat guesses.
Employee hierarchy recursive CTE runs fine for 5 levels then blows past the query timeout at 6+.
WITH RECURSIVE org AS (
SELECT employee_id, manager_id, 1 AS depth FROM employees WHERE manager_id IS NULL
UNION ALL
SELECT e.employee_id, e.manager_id, o.depth + 1
FROM employees e JOIN org o ON e.manager_id = o.employee_id
)
SELECT * FROM org;I suspect a cycle from bad data but I'm not sure how to confirm it cheaply.
Accepted answer
Start with the execution plan, numbers beat guesses.
What batch size or interval worked for you at similar scale?
Added the guard, the query now finishes and returns two employees who report to each other. Definitely bad data.
Also cap the depth explicitly with WHERE depth < 20 as a safety net even after fixing the cycle. Orgs occasionally get weird edits.
Idempotent writes with merge keys saved us during backfills.
Add a visited-path array to the recursive term and a WHERE NOT employee_id = ANY(path) guard. If it still hangs after that, you have a genuine cycle.
Another path: push the compute to the warehouse if the data's already there.
Yes, the partial index was the win for us too.
Consider DuckDB or Polars for this size before spinning up a cluster.
Note that merge on Delta still needs unique keys defined correctly.
Note that merge on Delta still needs unique keys defined correctly.
Sign in to reply.
© 2026 Lakebench, operated by Hunnurji Rao. Bengaluru, Karnataka, India.
No cluster. No install. Just the tab.