Skip to main content

Recursive CTEs & Graph Queries

Relational databases are excellent at joining tables side-by-side. However, they traditionally struggle with hierarchical or "graph" data—data where a row relates to another row in the exact same table, creating a tree structure.

Examples include:

  • An Employee Organization Chart (Employee -> Manager -> Director -> CEO)
  • A Bill of Materials (Car -> Engine -> Piston -> Bolt)
  • Network routing paths

To query this data, Snowflake supports Recursive Common Table Expressions (CTEs) and the legacy CONNECT BY syntax.

Topics Covered​

  • The Anatomy of a Recursive CTE
  • Hierarchical Traversal (Org Charts)
  • Bill of Materials (BOM) Patterns
  • CONNECT BY vs Recursive CTEs

The Anatomy of a Recursive CTE​

A standard CTE (WITH cte_name AS (...)) defines a temporary result set. A Recursive CTE references itself inside its own definition to repeatedly loop through data until a condition is met.

It consists of three parts:

  1. The Anchor Member: The starting point of the query (e.g., finding the CEO).
  2. The UNION ALL: Connects the anchor to the recursive step.
  3. The Recursive Member: The query that references the CTE itself to find the "children" of the previous step.

Hands-On: The Organization Chart​

Let's build an HR Organization chart for an Indian IT company. We want to query a table of employees and generate a report showing every employee's "Level" in the company hierarchy, starting from the CEO down to the interns.

1. Database Setup​

(Run this in your Snowflake worksheet)

USE ROLE SYSADMIN;

CREATE DATABASE IF NOT EXISTS BEEMS_GRAPH_DB;
USE SCHEMA BEEMS_GRAPH_DB.PUBLIC;

-- Create the Employee Table
CREATE OR REPLACE TABLE EMPLOYEES (
EMP_ID INT,
NAME STRING,
TITLE STRING,
MANAGER_ID INT
);

-- Insert Hierarchical Data
INSERT INTO EMPLOYEES VALUES
(1, 'Kavitha', 'CEO', NULL), -- Level 1 (No Manager)
(2, 'Arjun', 'VP Engineering', 1), -- Level 2 (Reports to CEO)
(3, 'Priya', 'VP Sales', 1), -- Level 2 (Reports to CEO)
(4, 'Rohan', 'Engineering Manager', 2), -- Level 3 (Reports to VP Eng)
(5, 'Neha', 'Software Engineer', 4), -- Level 4 (Reports to Eng Mgr)
(6, 'Rahul', 'Sales Executive', 3); -- Level 3 (Reports to VP Sales)

2. The Recursive CTE​

We will write a query that starts at the CEO and walks down the tree.

WITH RECURSIVE ORG_CHART AS (

-- 1. THE ANCHOR MEMBER: Find the CEO (where MANAGER_ID is NULL)
SELECT
EMP_ID,
NAME,
TITLE,
MANAGER_ID,
1 AS HIERARCHY_LEVEL,
NAME::STRING AS PATH -- Cast to STRING to prevent type mismatch
FROM EMPLOYEES
WHERE MANAGER_ID IS NULL

UNION ALL

-- 2. THE RECURSIVE MEMBER: Join the EMPLOYEES table to the CTE itself
SELECT
e.EMP_ID,
e.NAME,
e.TITLE,
e.MANAGER_ID,
oc.HIERARCHY_LEVEL + 1 AS HIERARCHY_LEVEL,
oc.PATH || ' -> ' || e.NAME AS PATH
FROM EMPLOYEES e
JOIN ORG_CHART oc ON e.MANAGER_ID = oc.EMP_ID
)
-- 3. Query the final generated CTE
SELECT
HIERARCHY_LEVEL,
NAME,
TITLE,
PATH
FROM ORG_CHART
ORDER BY HIERARCHY_LEVEL, NAME;

Output Explanation: The query starts at Kavitha (Level 1). Then it feeds Kavitha's EMP_ID back into the recursive join to find Arjun and Priya (Level 2). It then feeds Arjun and Priya's IDs back into the join to find Rohan and Rahul, incrementing the HIERARCHY_LEVEL and concatenating the PATH string each time, until no more children are found.

Bill of Materials (BOM) Pattern​

A Bill of Materials query does the exact same thing but often calculates cumulative costs.

If a Bicycle consists of 2 Wheels and 1 Frame, and a Wheel consists of 1 Tire and 20 Spokes, a recursive CTE can multiply the quantities at each level to tell you exactly how many Spokes are needed to build 50 Bicycles.

The Legacy CONNECT BY Syntax​

Before Recursive CTEs became the SQL standard, databases like Oracle used the CONNECT BY syntax. Snowflake supports this for backwards compatibility, but Recursive CTEs are strongly preferred because they are more flexible and readable.

Here is the exact same Org Chart query written using CONNECT BY:

SELECT 
LEVEL AS HIERARCHY_LEVEL,
NAME,
TITLE,
SYS_CONNECT_BY_PATH(NAME, ' -> ') AS PATH
FROM EMPLOYEES
START WITH MANAGER_ID IS NULL
CONNECT BY PRIOR EMP_ID = MANAGER_ID
ORDER BY HIERARCHY_LEVEL, NAME;

Performance Considerations​

  1. Infinite Loops: The biggest danger of a recursive CTE is an infinite loop. If Arjun's manager is Rohan, and Rohan's manager is Arjun, the query will loop forever. Snowflake will automatically abort the query if it hits the internal recursion depth limit (default is 100).
  2. Compute Heavy: Graph traversal is computationally expensive. If you have an Org Chart with 500,000 employees, use an appropriately sized Virtual Warehouse (e.g., Medium or Large).

Summary​

  • Relational tables represent trees/graphs using self-referencing foreign keys (e.g., MANAGER_ID).
  • Recursive CTEs are the standard way to traverse this data.
  • They consist of an Anchor Member (the root) and a Recursive Member (the loop), joined by UNION ALL.
  • Snowflake supports the legacy CONNECT BY syntax, but CTEs are preferred.
  • Beware of infinite loops caused by circular data references.