SnowPro Associate: Platform Question 212
Single answer○ Structured dataA retail company loads daily order data into a Snowflake table named ORDERS_RAW. One column, ORDER_INFO, is defined as VARIANT and contains JSON such as {"customer_id": 101, "status": "SHIPPED", "items": 3}. Analysts frequently filter by customer_id and status, but they want stronger schema enforcement and simpler SQL for these fields while keeping the full JSON payload for less frequently used attributes. Which approach best meets these requirements?
- A
Keep ORDER_INFO as VARIANT only, because VARIANT provides the same schema enforcement as relational columns and is the recommended design for frequently filtered fields.
- B
Create a new table with separate relational columns for customer_id and status, keep the original JSON in a VARIANT column for the remaining attributes, and load both representations during ingestion.
- C
Convert the entire ORDER_INFO column to a VARCHAR column so analysts can use string functions to extract customer_id and status when needed.
- D
Store customer_id and status in a separate external stage file and join them to the ORDERS_RAW table at query time to avoid changing the table design.
Show answer and explanation
Correct answer: B
Explanation
Snowflake supports both structured and semi-structured data, and a common real-world design is to combine them. When certain attributes inside a VARIANT column become important for filtering, joining, reporting, or governance, extracting them into explicit relational columns improves usability and data quality. Typed columns provide clearer schema enforcement than leaving everything inside VARIANT, while the original VARIANT column preserves flexibility for less common or evolving attributes. This aligns with Snowflake guidance on using semi-structured data where appropriate, but modeling frequently accessed business-critical fields as standard table columns when practical.
- A. Incorrect.
Incorrect. VARIANT is useful for semi-structured data, but it does not provide the same level of schema enforcement as defining relational columns with explicit data types. For fields that are frequently filtered, joined, or governed, Snowflake best practice is often to extract them into structured columns for simpler SQL, stronger typing, and clearer data modeling.
- B. Correct.
Correct. This hybrid design is a common Snowflake pattern: keep the full semi-structured payload in VARIANT for flexibility, while extracting high-value attributes such as customer_id and status into typed relational columns. This gives analysts easier SQL access and stronger schema enforcement for critical fields without losing the raw JSON for evolving or less commonly queried attributes.
- C. Incorrect.
Incorrect. Converting JSON to VARCHAR removes native semi-structured querying benefits and does not improve schema enforcement. It usually makes querying more error-prone and less efficient because analysts must rely on string parsing instead of JSON path expressions or typed columns.
- D. Incorrect.
Incorrect. Putting core analytical attributes in external files and joining them at query time adds unnecessary complexity and does not address schema enforcement within the Snowflake table. This is not a typical best-practice solution for frequently used fields that logically belong in the table schema.