SnowPro Associate: Platform Question 211
Single answer○ Structured dataA retail company stores online order data in a Snowflake table named ORDERS. One column, ORDER_INFO, uses a structured OBJECT type with a defined schema that includes customer_id NUMBER, order_total NUMBER(10,2), and order_date DATE. An analyst needs to return only orders where order_total is greater than 500 and wants to avoid unnecessary casting in the query. Which SQL statement best meets this requirement?
- A
SELECT * FROM ORDERS WHERE ORDER_INFO:order_total::NUMBER > 500;
- B
SELECT * FROM ORDERS WHERE ORDER_INFO['order_total'] > 500;
- C
SELECT * FROM ORDERS WHERE GET_PATH(ORDER_INFO, 'order_total')::NUMBER > 500;
- D
SELECT * FROM ORDERS WHERE TO_VARIANT(ORDER_INFO):order_total > 500;
Show answer and explanation
Correct answer: B
Explanation
Snowflake supports structured data types such as structured OBJECT, ARRAY, and MAP, where the schema is known and enforced at definition time. A key benefit is that when accessing elements or fields, Snowflake can return the native declared type instead of a generic VARIANT. In this scenario, ORDER_INFO.order_total is defined as NUMBER(10,2), so direct comparison is possible without explicit casting. This differs from semi-structured OBJECT data stored in VARIANT, where path access typically yields VARIANT and often requires casting. Snowflake documentation on structured data types and querying semi-structured versus structured values highlights this distinction.
- A. Incorrect.
This syntax is commonly used with semi-structured VARIANT data, where attribute access returns a VARIANT value and explicit casting is often needed before numeric comparison. In this scenario, ORDER_INFO is a structured OBJECT with a defined schema, so Snowflake can preserve the native data type of the field. While this may still work in some contexts, it does not best satisfy the requirement to avoid unnecessary casting.
- B. Correct.
Correct. For structured OBJECT types, fields can be accessed with bracket notation using the field name, and Snowflake returns the declared field type rather than a generic VARIANT. Since order_total is defined as NUMBER(10,2), it can be compared directly to 500 without an explicit cast. This is the most appropriate choice for structured data.
- C. Incorrect.
GET_PATH is more commonly associated with extracting values from semi-structured data. Adding ::NUMBER introduces an explicit cast, which the analyst wants to avoid. Because the source is a structured OBJECT with known field types, this is not the best approach.
- D. Incorrect.
Converting the structured OBJECT to VARIANT removes the advantage of working with structured data. After conversion, field access behaves more like semi-structured access, and this adds unnecessary complexity. It does not align with the goal of avoiding unnecessary casting or preserving the benefits of the structured schema.