FLATTEN for nested data in Snowflake - Practice Problems & Coding Challenges

Challenge - 5 Problems

❓ service_behavior

intermediate

Understanding FLATTEN output structure

Given a Snowflake table orders with a column items containing a VARIANT array of objects, what will be the output of this query?

SELECT order_id, f.value AS item FROM orders, LATERAL FLATTEN(input => items) f;

A A table with columns order_id and item, where each item is a single element from the items array expanded into its own row.

B A table with columns order_id and item, where item contains the entire items array as a single JSON string.

C A table with columns order_id and item, where item contains only the first element of the items array repeated for all rows.

D A table with columns order_id and item, but item is NULL for all rows.

💡 Hint

Think about what FLATTEN does to arrays in Snowflake.

The FLATTEN function expands each element of the nested array into its own row, so each item appears separately with the corresponding order_id.

🧠 Conceptual

intermediate

Behavior of FLATTEN with empty arrays

What happens when you use FLATTEN on a VARIANT column containing an empty array?

SELECT f.value FROM my_table, LATERAL FLATTEN(input => variant_col) f;

A The query returns one row with an empty array as value.

B The query returns one row with value NULL.

C The query returns zero rows because there are no elements to flatten.

D The query returns an error because the array is empty.

💡 Hint

Consider how flattening an empty list behaves in general.

FLATTEN produces no rows when the input array is empty, so the query returns zero rows.

❓ Configuration

advanced

Using FLATTEN with OUTER = TRUE

Consider this query:

SELECT id, f.value FROM my_table, LATERAL FLATTEN(input => nested_col, outer => true) f;

What is the effect of setting outer => true in FLATTEN?

A It returns rows for all elements in the array, and also returns a row with NULL value if the array is empty or NULL.

B It filters out NULL values inside the array and returns only non-null elements.

C It causes FLATTEN to return only the first element of the array.

D It causes an error if the array is NULL.

💡 Hint

Think about how to preserve rows when the nested array is empty or missing.

Setting outer => true makes FLATTEN return a row with NULL value when the input is empty or NULL, preserving the original row.

❓ security

advanced

Security implications of FLATTEN on sensitive nested data

You have a VARIANT column storing nested JSON with sensitive user data. What is a best practice when using FLATTEN to query this data?

A Use FLATTEN without restrictions since it only changes data shape, not data visibility.

B Flatten the data first, then apply masking in the SELECT clause.

C Export the flattened data to an external system without encryption for faster processing.

D Apply column-level masking or restrict access before flattening to avoid exposing sensitive data.

💡 Hint

Think about when data masking should be applied in the query process.

Masking or access control should be applied before flattening to prevent sensitive data exposure during query processing.

❓ Architecture

expert

Optimizing performance when using FLATTEN on large nested datasets

You have a large Snowflake table with deeply nested JSON arrays. Which approach best improves query performance when using FLATTEN?

A Use FLATTEN with outer => true to ensure all rows are returned, improving performance.

B Use lateral flatten with a filter predicate on the nested array before flattening to reduce data scanned.

C Avoid using FLATTEN and instead extract nested data using multiple SELECT statements.

D Flatten the entire nested array without filters and then apply WHERE conditions on the flattened results.

💡 Hint

Filtering early reduces the amount of data processed.

Applying filters inside the FLATTEN input expression reduces the data volume processed, improving performance.