SnowPro Associate: Platform Question 188
Single answer3.1 Describe considerations when working with structured and semi-structured data.A retail company loads daily JSON files from an external stage into a Snowflake table named RAW_ORDERS using a single VARIANT column called ORDER_DATA. Analysts need to report on customer_id, order_date, and total_amount, but the JSON structure is not fully consistent: some files contain extra attributes, and some nested attributes are missing for certain orders. The team wants a solution that minimizes load failures while still allowing SQL analysis of both known fields and unexpected attributes. Which approach is the MOST appropriate?
- A
Load the JSON into a relational table with fixed columns only, so Snowflake rejects rows that contain unexpected attributes and preserves schema consistency.
- B
Load the JSON into a VARIANT column, then use dot/bracket notation and casting in queries or views to extract customer_id, order_date, and total_amount as needed.
- C
Convert the JSON files to CSV before loading, because Snowflake can query semi-structured data only after it has been flattened into a structured format.
- D
Store each JSON attribute in a separate VARCHAR column without using VARIANT, because Snowflake does not support querying nested elements directly from semi-structured data.
Show answer and explanation
Correct answer: B
Explanation
The best answer is to store the JSON in a VARIANT column and extract required fields with path notation and casting. This is a core Snowflake best practice for working with semi-structured data when the schema may evolve or contain optional fields. It enables schema-on-read, allows missing attributes to return NULL rather than causing load failures in many scenarios, and preserves additional attributes for later analysis. Analysts can create views on top of the VARIANT data to present structured columns such as customer_id, order_date, and total_amount while still retaining access to the full JSON document. This aligns with Snowflake documentation on querying semi-structured data using VARIANT, dot/bracket notation, and explicit casting to native SQL types.
- A. Incorrect.
Incorrect. Loading only into fixed relational columns reduces flexibility and can be a poor fit for semi-structured JSON when attributes vary between files. One of Snowflake's strengths is storing semi-structured data in VARIANT so that evolving or unexpected attributes can still be retained. Rejecting rows with extra attributes is not the best approach when the stated goal is to minimize load failures and preserve access to unexpected attributes.
- B. Correct.
Correct. Snowflake is designed to store JSON, Avro, ORC, Parquet, and XML in semi-structured columns such as VARIANT. With JSON in a VARIANT column, analysts can query elements using path notation such as ORDER_DATA:customer_id and cast values to needed SQL types, for example ORDER_DATA:total_amount::NUMBER. This approach handles optional or missing attributes gracefully, supports schema-on-read, and preserves extra attributes for future use.
- C. Incorrect.
Incorrect. Snowflake can load and query semi-structured JSON directly; converting JSON to CSV is not required. In fact, converting to CSV may discard hierarchical structure or make nested data harder to manage. Flattening or extraction can be done later in SQL when needed.
- D. Incorrect.
Incorrect. Snowflake does support direct querying of nested elements in semi-structured data stored in VARIANT. Storing every attribute as VARCHAR adds unnecessary preprocessing, weakens type handling, and makes it harder to retain evolving attributes. It also undermines the benefit of semi-structured support for variable schemas.