SnowPro Associate: Platform Question 113
Single answer● Data typesA retail company is loading point-of-sale data from CSV files into a Snowflake table. One column, ORDER_METADATA, contains JSON strings with varying attributes such as coupon codes, cashier IDs, and nested payment details. Analysts need to query both top-level and nested attributes without redefining the table schema every time the JSON structure changes. Which Snowflake data type should be used for ORDER_METADATA to best meet this requirement?
- A
VARCHAR, because JSON should be stored as text and parsed only at query time
- B
OBJECT, because JSON data is always stored as key-value pairs at the top level
- C
VARIANT, because it can store semi-structured data such as JSON with flexible schema
- D
ARRAY, because JSON documents can contain multiple values and nested elements
Show answer and explanation
Correct answer: C
Explanation
The best answer is VARIANT because Snowflake recommends using VARIANT to store semi-structured data when the structure can change over time and users need to query nested attributes. In this scenario, ORDER_METADATA contains JSON documents with evolving fields, making a fixed relational schema impractical. VARIANT supports flexible ingestion and querying through path expressions such as ORDER_METADATA:payment.card_type. OBJECT and ARRAY are semi-structured data types as well, but they are more specific structures contained within VARIANT data rather than the best default choice for an evolving JSON column. Storing JSON in VARCHAR is possible, but it loses the benefits of Snowflake's native semi-structured processing. This aligns with Snowflake documentation on semi-structured data types, where VARIANT is the general-purpose type for JSON and other semi-structured formats.
- A. Incorrect.
Incorrect. VARCHAR can store the raw JSON text, but it does not preserve the semi-structured nature of the data for efficient attribute access. Storing JSON as plain text would require repeated parsing in queries and does not align with Snowflake best practices when the goal is to query elements inside the JSON document.
- B. Incorrect.
Incorrect. OBJECT is a semi-structured type that represents key-value pairs, but it is not the best general choice for a column that needs to store complete JSON documents with potentially mixed structures. JSON documents may contain arrays, nested objects, or scalar values. VARIANT is the flexible container designed to store any valid semi-structured value, including OBJECT and ARRAY structures.
- C. Correct.
Correct. VARIANT is Snowflake's primary data type for storing semi-structured data such as JSON, Avro, ORC, Parquet, and XML. It allows Snowflake to store the JSON document in a way that supports querying both top-level and nested elements using path notation, without requiring schema changes when the incoming JSON structure evolves.
- D. Incorrect.
Incorrect. ARRAY is intended for ordered lists of values, not for arbitrary JSON documents as a whole. While some JSON content may include arrays, the entire ORDER_METADATA column may contain objects, nested arrays, and scalar values. ARRAY would be too restrictive for this scenario.