SnowPro Associate: Platform Question 208
Single answer● Querying dataA retail analytics team stores order events in a Snowflake table named ORDERS_RAW. The table includes a VARIANT column called ORDER_DATA with nested JSON such as customer details and an array of purchased items. An analyst needs to return one row per purchased item, showing the order_id, item_sku, and item_quantity for orders placed in the last 7 days. Which query pattern should the analyst use?
- A
Use LATERAL FLATTEN on ORDER_DATA:items and select scalar values with JSON path notation, filtering by the order timestamp in the WHERE clause.
- B
Use GET_DDL on ORDERS_RAW to expand the JSON array into rows, then filter the returned DDL text for orders from the last 7 days.
- C
Use RESULT_SCAN on the most recent query against ORDERS_RAW to automatically extract each item from the JSON array into separate rows.
- D
Use COPY INTO a temporary table with STRIP_OUTER_ARRAY=TRUE, then query that table for item_sku and item_quantity.
Show answer and explanation
Correct answer: A
Explanation
The correct approach is to query the VARIANT data directly and use LATERAL FLATTEN to turn each element of a JSON array into a separate row. In Snowflake, semi-structured data in VARIANT, OBJECT, and ARRAY types can be accessed with path notation, and FLATTEN is the standard table function for exploding arrays. A realistic query pattern would look like: SELECT order_id, f.value:sku::string AS item_sku, f.value:quantity::number AS item_quantity FROM orders_raw, LATERAL FLATTEN(input => order_data:items) f WHERE order_timestamp >= DATEADD(day, -7, CURRENT_TIMESTAMP()); This aligns with Snowflake documentation on querying semi-structured data and using the FLATTEN table function. The other options misuse metadata functions, result-set functions, or data loading features rather than applying the correct querying pattern.
- A. Correct.
Correct. In Snowflake, querying semi-structured data stored in a VARIANT column commonly uses path notation such as ORDER_DATA:items and LATERAL FLATTEN to expand arrays into multiple rows. This is the appropriate way to produce one row per purchased item. The analyst can then select fields like f.value:sku and f.value:quantity and filter for orders from the last 7 days using the order timestamp column or a timestamp value extracted from the VARIANT.
- B. Incorrect.
Incorrect. GET_DDL returns the DDL statement used to create an object such as a table, view, or schema. It does not query table data and cannot expand JSON arrays into rows. A candidate might choose this if they confuse object metadata functions with data querying functions.
- C. Incorrect.
Incorrect. RESULT_SCAN can query the result set of a previously executed query, but it does not inherently transform nested arrays into separate rows. If the previous query did not already flatten the JSON array, RESULT_SCAN will not solve the requirement. This option reflects a misconception that RESULT_SCAN performs semi-structured parsing automatically.
- D. Incorrect.
Incorrect. COPY INTO is used for loading or unloading data, not for querying nested arrays already stored in a table. STRIP_OUTER_ARRAY is a file loading option relevant when ingesting JSON from staged files, not when querying a VARIANT column in an existing table. This is a plausible distractor because it references a real JSON-related capability, but it applies to ingestion rather than SELECT queries.