DAA-C01 Question 160
Single answerWork with hierarchical dataA retail company stores its product catalog in a Snowflake table named PRODUCTS with the columns PRODUCT_ID, PRODUCT_NAME, PARENT_PRODUCT_ID, and CATEGORY. Analysts need a query that returns each product together with its full lineage from the top-level parent down to the current product, so they can validate category rollups and identify misplaced items. The hierarchy can be several levels deep, and some products are roots with NULL PARENT_PRODUCT_ID. Which approach should be used to produce this result most effectively in Snowflake?
- A
Use a recursive CTE that starts from rows where PARENT_PRODUCT_ID is NULL and repeatedly joins child rows to parent rows while building a path column.
- B
Use the FLATTEN table function on PRODUCTS to automatically expand the parent-child hierarchy into multiple rows and derive the lineage path.
- C
Use LISTAGG(PRODUCT_NAME, ' > ') within a simple GROUP BY CATEGORY query because aggregation can reconstruct the hierarchy without recursion.
- D
Use a window function such as LAG over PRODUCT_ID ordered by PARENT_PRODUCT_ID to identify each ancestor chain and concatenate the lineage.
Show answer and explanation
Correct answer: A
Explanation
For hierarchical data modeled as an adjacency list in Snowflake, the best-practice approach is to use a recursive CTE. Snowflake supports recursive common table expressions through WITH RECURSIVE, allowing an anchor clause to select root nodes and a recursive clause to repeatedly join child rows until the hierarchy is exhausted. This pattern is appropriate when the depth is not fixed and when analysts need a lineage path, level number, or root-to-leaf expansion. FLATTEN is appropriate for nested semi-structured data in VARIANT, ARRAY, or OBJECT columns, not relational self-joins. Likewise, LISTAGG and window functions can complement analysis after traversal but cannot replace recursion for discovering arbitrary-depth hierarchies. This aligns with Snowflake SQL documentation for recursive CTEs and hierarchical querying patterns.
- A. Correct.
Correct. In Snowflake, hierarchical parent-child relationships stored in relational columns are typically traversed with a recursive CTE using WITH RECURSIVE. The anchor query identifies root rows, and the recursive member joins children to the current level. This is the standard SQL approach in Snowflake for expanding variable-depth hierarchies and can also build a path string such as root > child > leaf.
- B. Incorrect.
Incorrect. FLATTEN is designed for traversing semi-structured data such as VARIANT arrays and objects, not relational self-referencing hierarchies stored across rows in standard columns. A candidate might pick this if they associate hierarchy processing with nested structures in general, but this table design requires row-by-row recursion rather than JSON expansion.
- C. Incorrect.
Incorrect. LISTAGG can concatenate values, but by itself it does not discover multi-level parent-child relationships. A simple GROUP BY CATEGORY loses the actual lineage order and does not traverse from parent to child across multiple levels. This option reflects a common misconception that string aggregation can replace hierarchical traversal logic.
- D. Incorrect.
Incorrect. Window functions like LAG operate over a defined ordered set of rows and are useful for comparing nearby rows, not for recursively walking an unknown-depth tree. Ordering by PARENT_PRODUCT_ID does not establish full ancestor chains. Someone might choose this if they confuse row sequencing with true hierarchy traversal.