SnowPro Associate: Platform Question 213
Single answer○ Semi-structured dataA retail company stores clickstream events in a Snowflake table named EVENT_LOG. The table has a VARIANT column called RAW_EVENT containing JSON documents. Analysts need a query that returns the customer_id as a number and the page URL as text for each event. Sample JSON in RAW_EVENT looks like: {"customer":{"id":12345},"page":{"url":"/products/sku-100"}}. Which query correctly extracts both values in the required data types?
- A
SELECT RAW_EVENT:customer.id::NUMBER AS customer_id, RAW_EVENT:page.url::VARCHAR AS page_url FROM EVENT_LOG;
- B
SELECT GET(RAW_EVENT, 'customer.id')::NUMBER AS customer_id, GET(RAW_EVENT, 'page.url')::VARCHAR AS page_url FROM EVENT_LOG;
- C
SELECT PARSE_JSON(RAW_EVENT):customer:id::NUMBER AS customer_id, PARSE_JSON(RAW_EVENT):page:url::VARCHAR AS page_url FROM EVENT_LOG;
- D
SELECT RAW_EVENT['customer']['id'] AS customer_id, RAW_EVENT['page']['url'] AS page_url FROM EVENT_LOG WHERE RAW_EVENT IS JSON;
Show answer and explanation
Correct answer: A
Explanation
Snowflake stores semi-structured data such as JSON in the VARIANT data type. To query nested elements, Snowflake supports path traversal using colon notation from the VARIANT column and dot or bracket notation for nested keys. Extracted values remain semi-structured unless explicitly cast, so using ::NUMBER and ::VARCHAR is important when downstream consumers require standard SQL data types. This pattern is documented in Snowflake guidance for querying semi-structured data: use path expressions on VARIANT columns and cast results as needed for relational output.
- A. Correct.
Correct. In Snowflake, values inside a VARIANT column can be traversed using path notation with the colon operator and dot notation for nested JSON attributes. Casting with ::NUMBER and ::VARCHAR converts the extracted VARIANT values into the required SQL data types. This is the standard and most direct approach for querying semi-structured JSON stored in VARIANT.
- B. Incorrect.
Incorrect. GET can retrieve a top-level field from an object or an element from an array, but 'customer.id' and 'page.url' are not top-level keys in this JSON document. Snowflake does not interpret those strings as nested JSON paths in this context, so this approach reflects a common misconception about how nested access works.
- C. Incorrect.
Incorrect. PARSE_JSON is used to convert a string containing JSON into VARIANT. In this scenario, RAW_EVENT is already a VARIANT column, so reparsing is unnecessary and invalid for this use case. Also, the path syntax shown is not correct Snowflake syntax for nested traversal from an already parsed VARIANT value.
- D. Incorrect.
Incorrect. Bracket notation can be used to access JSON elements, but this query does not cast the results to the required SQL types, so it does not fully satisfy the requirement. In addition, the predicate 'RAW_EVENT IS JSON' is not valid syntax for checking a VARIANT column in Snowflake. Candidates may choose this option because bracket notation looks familiar from other systems.