Databricks Recursive CTE Example: Querying Hierarchical Data

If you have ever tried to query hierarchical data in Databricks like an employee organizational chart, a bill of materials, or category trees you know it used to be a headache. Historically, data engineers had to write complex Python loops in PySpark to walk through parent-child relationships step-by-step.

Thankfully, that is no longer necessary. Databricks now natively supports recursive Common Table Expressions (CTEs).

Databricks Recursive CTE Example: Querying Hierarchical Data

Using the WITH RECURSIVE clause, you can easily query hierarchies and graph data directly in Databricks SQL. Let’s break down exactly how recursive CTEs work and look at a real-world example you can use today.

Page Contents

What is a Recursive CTE?

Example 1: The Basics (Number Sequence)

Example 2: Real-World Employee Hierarchy

Databricks Limits and Infinite Loops

Conclusion

What is a Recursive CTE?

A standard CTE creates a temporary result set that you can query once. A recursive CTE is a query that repeatedly references itself until it reaches a specific stopping point.

Every recursive CTE consists of two main parts, glued together by a UNION ALL:

The Anchor Query (Base Case): This is the starting point. It runs once and returns the initial set of rows (e.g., the CEO of a company, or the root node of a tree).
The Recursive Query: This part references the CTE’s own name. It takes the results from the previous step and uses them to find the next set of rows (e.g., finding the people who report directly to the CEO).

Databricks will continuously loop the recursive query until it stops finding new rows.

Example 1: The Basics (Number Sequence)

Before we look at real data, let’s look at the absolute simplest way a recursive CTE works. Here is how you generate a sequence of numbers from 1 to 5:

WITH RECURSIVE number_sequence(n) AS (
    -- 1. The Anchor: We start with the number 1
    SELECT 1 
    UNION ALL 
    -- 2. The Recursive Step: We add 1 to the previous result
    SELECT n + 1 
    FROM number_sequence 
    WHERE n < 5
)
-- 3. The Final Query: Display the results
SELECT * FROM number_sequence;

Example 2: Real-World Employee Hierarchy

Now, let’s look at a practical Databricks recursive CTE example.

Imagine you have an employees table with three columns: employee_id, name, and manager_id. You want to map out the entire corporate hierarchy starting from the top boss, calculating how many levels deep each employee is.

Here is how you write that query:

WITH RECURSIVE org_chart(employee_id,name,manager_id,hierarchy_level) AS (
    -- 1. Anchor: Find the top-level bosses (people with no manager)
    SELECT 
        employee_id, 
        name, 
        manager_id, 
        1 AS hierarchy_level
    FROM employees
    WHERE manager_id IS NULL
    UNION ALL
    -- 2. Recursive Step: Find employees who report to the people we just found
    SELECT 
        e.employee_id, 
        e.name, 
        e.manager_id, 
        oc.hierarchy_level + 1
    FROM employees e
    JOIN org_chart oc 
      ON e.manager_id = oc.employee_id
)
-- 3. Final Query: Order by level to see the structure clearly
SELECT * FROM org_chart 
ORDER BY hierarchy_level;

With just a few lines of standard SQL, you have completely replaced what used to require a multi-step PySpark script!

Databricks Limits and Infinite Loops

When working with recursion, your biggest risk is accidentally creating an infinite loop (where the query never stops finding “new” data).

To protect your compute clusters, Databricks has built-in safety rails for recursive CTEs:

Max Iterations: By default, Databricks limits recursive queries to 100 iterations.
Row Limits: Databricks will also automatically terminate the query if the recursive step attempts to generate an excessive number of rows (typically capped at 1 million).

Best Practice: Always ensure your dataset does not contain circular references (e.g., Employee A manages Employee B, but Employee B is mistakenly listed as managing Employee A), as this will cause the query to hit the recursion limit and fail.

Conclusion

The addition of the WITH RECURSIVE clause is a massive quality-of-life upgrade for Databricks SQL users. Whether you are mapping complex supply chains, unpacking nested JSON categories, or just generating sequence data, recursive CTEs allow you to keep your logic entirely within SQL, making your pipelines faster and much easier to read.