Implement a recursive query for hierarchy (employee-manager). Explain the termination guarantees, depth limits, and when a recursive CTE becomes a scalability bottleneck. What alternatives exist for graph-scale hierarchies in Spark or a data lake?

Implementation: WITH RECURSIVE emp_tree AS (SELECT id, name, manager_id, 1 AS level FROM employees WHERE manager_id IS NULL UNION ALL SELECT e.id, e.name, e.manager_id, t.level + 1 FROM employees e JOIN emp_tree t ON e.manager_id = t.id) SELECT * FROM emp_tree. Why it works: Base case = roots; recursive case = join to prior level. Termination: Ensured by acyclic graph; cycles require cycle detection or LIMIT. Scalability: Recursive CTEs materialize each level; deep hierarchies (10+ levels) can explode intermediate rows. In Spark: No native recursive CTE; use GraphFrames, iterative DataFrame joins, or export to graph DB. Cost: Iterations = network shuffles; deep graphs = expensive. Best practice: Index parent key; validate acyclicity; for graph-scale, use GraphFrames or Neo4j.