Recursive CTEs in Snowflake with ARRAY_APPEND()

I have seen a number of different methods for generating the “path” value in a Recursive CTE (common table expression). Most rely on some form of string-based concatenation which is later parsed to split a hierarchy into multiple, level specific columns. Yesterday I ran into an issue where the delimiter that I was using was embedded in the source data. Sure, I could analyze the source data and choose delimiter characters that are not present, but what if I could eliminate the need for delimiter characters altogether. Enter the ARRAY_APPEND() function in Snowflake. I’ve seen many articles about recursive CTEs, but I have yet to see one that leverages this function, so I thought I’d share what I found.

If you’re not familiar with Recursive CTEs or querying hierarchical data, the Snowflake documentation explains them here and here.

The shortcomings of string-based concatenation in Recursive CTEs

Many examples of recursive CTEs traversing a hierarchy build a path through string concatenation and a delimiter.

Example:

CREATE OR REPLACE TEMPORARY TABLE departments (
  department_id         NUMBER,
  department_name       TEXT,
  parent_department_id  NUMBER
);

INSERT INTO departments
VALUES 
  (1, 'Organization', NULL),
  (2, 'Human Resources', 1),
  (3, 'Sales', 1),
  (4, 'Information Technology',1),
  (5, 'Finance/Accounting', 1),
  (6, 'Research & Development', 1),
  (7, 'Payroll', 2),
  (8, 'Benefits', 2),
  (9, 'Recruiting', 2),
  (10, 'North America', 3),
  (11, 'EMEA', 3),
  (12, 'LATAM', 3),
  (13, 'APAC', 3),
  (14, 'Help Desk/Support', 4),
  (15, 'System Administration', 4),
  (16, 'Financial Planning', 5),
  (17, 'Accounting', 5),
  (18, 'Analytics', 5),
  (19, 'Engineering', 6),
  (20, 'Platform', 6),
  (21, 'Project Management', 6),
  (22, 'United States', 10),
  (23, 'Canada', 10),
  (24, 'Mexico', 10);

WITH RECURSIVE department_hierarchy (
    department_id,
    department_name,
    parent_department_id,
    department_id_path,
    department_name_path,
    depth
  )
  AS (
    SELECT
      department_id,
      department_name,
      parent_department_id,
      '/' || department_id::TEXT AS department_id_path,
      '/' || department_name::TEXT AS department_name_path,
      1::NUMBER AS depth
    FROM departments
    WHERE parent_department_id IS NULL
    UNION ALL
    SELECT
      d.department_id,
      d.department_name,
      d.parent_department_id,
      dh.department_id_path || '/' || d.department_id::TEXT AS department_id_path,
      dh.department_name_path || '/' || d.department_name::TEXT AS department_name_path,
      dh.depth + 1 AS depth
    FROM departments d
    JOIN department_hierarchy dh ON d.parent_department_id = dh.department_id
  )
SELECT
  department_id,
  department_name,
  parent_department_id,
  department_id_path,
  department_name_path,
  depth  
FROM department_hierarchy;

Query Results

In the above example, I used the / delimiter to build the path. If you look closely at the original data, this delimiter is present in the name values of two of the records: Finance/Accounting and Help Desk/Support. This will cause an issue when we try to parse the different name levels into separate columns.

WITH RECURSIVE department_hierarchy (
    department_id,
    department_name,
    parent_department_id,
    department_id_path,
    department_name_path,
    depth
)
  AS (
    SELECT
      department_id,
      department_name,
      parent_department_id,
      '/' || department_id::TEXT AS department_id_path,
      '/' || department_name::TEXT AS department_name_path,
      1::NUMBER AS depth
    FROM departments
    WHERE parent_department_id IS NULL
    UNION ALL
    SELECT
      d.department_id,
      d.department_name,
      d.parent_department_id,
      dh.department_id_path || '/' || d.department_id::TEXT AS department_id_path,
      dh.department_name_path || '/' || d.department_name::TEXT AS department_name_path,
      dh.depth + 1 AS depth
    FROM departments d
    JOIN department_hierarchy dh ON d.parent_department_id = dh.department_id
  ),
parsed_path AS (
  SELECT
    department_id,
    department_name,
    parent_department_id,
    department_id_path,
    department_name_path,
    depth,
    REGEXP_SUBSTR(department_id_path, '[^/]+', 1, 1) AS department_level_1_id,
    REGEXP_SUBSTR(department_id_path, '[^/]+', 1, 2) AS department_level_2_id,
    REGEXP_SUBSTR(department_id_path, '[^/]+', 1, 3) AS department_level_3_id,
    REGEXP_SUBSTR(department_id_path, '[^/]+', 1, 4) AS department_level_4_id,
    REGEXP_SUBSTR(department_name_path, '[^/]+', 1, 1) AS department_level_1_name,
    REGEXP_SUBSTR(department_name_path, '[^/]+', 1, 2) AS department_level_2_name,
    REGEXP_SUBSTR(department_name_path, '[^/]+', 1, 3) AS department_level_3_name,
    REGEXP_SUBSTR(department_name_path, '[^/]+', 1, 4) AS department_level_4_name
  FROM department_hierarchy
)
SELECT
  department_id,
  department_name,
  depth,
  department_id_path,
  department_name_path,
  department_level_1_name,
  department_level_2_name,
  department_level_3_name,
  department_level_4_name
FROM parsed_path
WHERE department_name_path ILIKE '%finance/accounting%';

Query Results: Parsing levels using string concatenation approach

The parsing above uses the REGEXP_SUBSTR()function to split the department_name_path generated by the recursive CTE into their respective level columns. Since the source data contains the / delimiter that I’m using, the split columns are not working as expected. See the Query Results screenshot above. We would expect the DEPARTMENT_LEVEL_2_NAME to contain the value Finance/Accounting, instead this single name is being split into the DEPARTMENT_LEVEL_2_NAME and DEPARTMENT_LEVEL_3_NAME columns.

Get Matt Feeser’s stories in your inbox

Join Medium for free to get updates from this writer.

Remember me for faster sign in

Now to solve this problem we could choose a different delimiter for the concatenation, but what should we choose? I’ve seen some examples that use -> or others that enclose the source values in double quotes " before concatenation. However, any string concatenation based approach relies on the those delimiters not appearing in the source values. Can you always predict what values may exist in the source data, especially when dealing with user entered data?

Using the ARRAY_APPEND() function instead

An alternative to concatenating strings together in the recursive CTEs is to use the ARRAY_APPEND() function. I found this in the Snowflake documentation; however it appears this is available in other platforms as well, including Postgres and Databricks.

In the context of a recursive CTE, this function allows you to construct an array consisting of the path with each element of the array representing an level within the hierarchy. In this implementation, the position of the element in the array identifies the level of the hierarchy. This method of building the path is not susceptible to embedded delimiter characters in the source data.

WITH RECURSIVE department_hierarchy (
    department_id,
    department_name,
    parent_department_id,
    department_id_path_array,
    department_name_path_array,
    depth
)
  AS (
    SELECT
      department_id,
      department_name,
      parent_department_id,
      COALESCE(department_id::TEXT, '')::ARRAY AS department_id_path_array,
      COALESCE(department_name, '')::ARRAY AS department_name_path_array,
      1::NUMBER AS depth
    FROM departments
    WHERE parent_department_id IS NULL
    UNION ALL
    SELECT
      d.department_id,
      d.department_name,
      d.parent_department_id,
      ARRAY_APPEND(dh.department_id_path_array, COALESCE(d.department_id::TEXT,'')) AS department_id_path_array,
      ARRAY_APPEND(dh.department_name_path_array, COALESCE(d.department_name::TEXT,'')) AS department_name_path_array,
      dh.depth + 1 AS depth
    FROM departments d
    JOIN department_hierarchy dh ON d.parent_department_id = dh.department_id
  ),
parsed_path AS (
  SELECT
    department_id,
    department_name,
    parent_department_id,
    department_id_path_array,
    department_name_path_array,
    depth,
    NULLIF(department_id_path_array[0], '')::NUMBER AS department_level_1_id,
    NULLIF(department_id_path_array[1], '')::NUMBER AS department_level_2_id,
    NULLIF(department_id_path_array[2], '')::NUMBER AS department_level_3_id,
    NULLIF(department_id_path_array[3], '')::NUMBER AS department_level_4_id,
    NULLIF(department_name_path_array[0], '')::TEXT AS department_level_1_name,
    NULLIF(department_name_path_array[1], '')::TEXT AS department_level_2_name,
    NULLIF(department_name_path_array[2], '')::TEXT AS department_level_3_name,
    NULLIF(department_name_path_array[3], '')::TEXT AS department_level_4_name
  FROM department_hierarchy
)
SELECT
  department_id,
  department_name,
  depth,
  department_id_path_array,
  department_name_path_array,
  department_level_1_name,
  department_level_2_name,
  department_level_3_name,
  department_level_4_name
FROM parsed_path
WHERE ARRAY_CONTAINS(
  'Finance/Accounting'::VARIANT,
  department_name_path_array::VARIANT
);

Query Results: Parsing levels using ARRAY_APPEND() approach

With this approach, each iteration of the recursion is appended to the previous iteration of the recursion.

A couple of notes on this approach:

• This handles null values in the hierarchy using the COALESCE() function when appending values into the array. This is important as it preserves the structure of the hierarchy.
• When parsing the array to get the individual levels, it handles empty string elements of the array using the NULLIF() function.

Conclusion

If you’re querying a hierarchy using a recursive CTE, using the ARRAY_APPEND() function adds resilience to your query with minimal additional implementation effort. I highly recommend this over a string-based concatenation method. I’ll be using this array-based approach from here on out.