DAA-C01 Question 159
Single answerWork with hierarchical dataA retail analytics team stores product categories in a Snowflake table named PRODUCT_CATEGORY with the columns CATEGORY_ID, PARENT_CATEGORY_ID, and CATEGORY_NAME. Analysts frequently need a report that returns every category along with its top-level department and its depth in the hierarchy. The hierarchy can be several levels deep, and some rows have a NULL PARENT_CATEGORY_ID because they are root departments. Which approach should the team use to produce this result correctly and maintainably in Snowflake?
- A
Use a recursive CTE that starts with root categories, joins child rows to parent rows in each recursion step, carries the root department name forward, and increments a level counter.
- B
Use the FLATTEN table function on PRODUCT_CATEGORY to expand the parent-child relationships and extract the top-level department for each row.
- C
Use a window function such as LAST_VALUE(CATEGORY_NAME) OVER (PARTITION BY CATEGORY_ID ORDER BY PARENT_CATEGORY_ID) to identify the top-level department and compute depth.
- D
Use a self-join between PRODUCT_CATEGORY and itself one time, then apply GROUP BY CATEGORY_ID to derive the full hierarchy depth for all possible levels.
Show answer and explanation
Correct answer: A
Explanation
For hierarchical data modeled as an adjacency list in Snowflake, the recommended approach is a recursive common table expression (CTE). Recursive CTEs let you define an anchor query for root nodes and a recursive query that repeatedly joins children to parents until the hierarchy is exhausted. This pattern is appropriate when you need lineage attributes such as the top-level ancestor, full path, or level/depth. FLATTEN is intended for nested semi-structured data in VARIANT, ARRAY, or OBJECT values, not relational parent-child tables. Window functions and single self-joins are useful in many analytical cases, but they do not solve arbitrary-depth hierarchical traversal. See Snowflake documentation on WITH and recursive CTEs, as well as FLATTEN for semi-structured data handling.
- A. Correct.
Correct. In Snowflake, recursive CTEs are the standard SQL approach for traversing hierarchical parent-child relationships stored in relational tables. The anchor member starts from root nodes where PARENT_CATEGORY_ID is NULL. The recursive member repeatedly joins child rows to the previous level, allowing the query to propagate the root department and calculate depth using a level counter. This is the most appropriate and maintainable solution for variable-depth hierarchies.
- B. Incorrect.
Incorrect. FLATTEN is used to explode semi-structured data such as arrays or objects in VARIANT columns. PRODUCT_CATEGORY is a relational parent-child table, so FLATTEN does not traverse these relationships. This option reflects a common misconception that FLATTEN can be used for any hierarchy, when it is specifically for nested semi-structured structures.
- C. Incorrect.
Incorrect. Window functions operate across rows in a defined partition and order, but they do not recursively walk an unknown number of parent-child levels in an adjacency-list hierarchy. LAST_VALUE on CATEGORY_NAME ordered by PARENT_CATEGORY_ID would not reliably identify the root ancestor or the depth across multiple recursive levels.
- D. Incorrect.
Incorrect. A single self-join can only traverse one known level of the hierarchy, such as parent to child. Because the hierarchy depth is variable and can be several levels deep, one self-join plus aggregation cannot correctly derive all ancestors and depth values unless the maximum depth is fixed and additional joins are hardcoded, which is not maintainable.