SnowPro Associate: Platform Question 210
Single answer○ Structured dataA retail company stores point-of-sale transactions in a Snowflake table named SALES_RAW. The table includes a VARIANT column called RECEIPT_JSON that contains nested receipt data, and separate relational columns such as STORE_ID and SALE_TS. Analysts frequently need to report the customer loyalty ID found in RECEIPT_JSON:customer.loyaltyId and join it to a structured CUSTOMER_DIM table. Query performance is inconsistent because every report repeatedly extracts the loyalty ID from the VARIANT column at runtime. Which approach is the most appropriate to improve usability and performance for this structured value while keeping the raw JSON available?
- A
Add a new relational column to the table and populate it with the extracted loyalty ID from RECEIPT_JSON, then use that column for joins and filtering
- B
Convert the entire RECEIPT_JSON VARIANT column into an ARRAY so Snowflake can index the loyalty ID more efficiently
- C
Store the loyalty ID in a separate stage file and join to it during query execution when needed
- D
Replace the CUSTOMER_DIM table with a VARIANT column so both sides of the join use semi-structured data
Show answer and explanation
Correct answer: A
Explanation
This scenario tests the practical use of structured and semi-structured data together in Snowflake. Snowflake supports storing raw JSON in VARIANT while also allowing teams to extract commonly used fields into relational columns. For frequently accessed attributes used in joins, filters, or reporting, a structured column is typically the best choice because it reduces repeated path traversal of semi-structured data and simplifies SQL for analysts. This aligns with Snowflake best practices for balancing flexibility of VARIANT with performance and ease of use of structured data types. Snowflake documentation on semi-structured data explains querying elements in VARIANT with path notation, while general modeling guidance supports promoting heavily used attributes into standard columns when appropriate.
- A. Correct.
Correct. If a specific attribute inside semi-structured data is queried frequently and used for joins or filters, extracting it into a dedicated relational column is a practical design choice. This improves usability because analysts can reference a standard typed column directly, and it can improve performance by avoiding repeated JSON path extraction at query time. The raw VARIANT column can still be retained for flexibility and auditability.
- B. Incorrect.
Incorrect. ARRAY is a semi-structured type used for ordered collections, not a performance feature for extracting a named object attribute like customer.loyaltyId. Converting the full JSON document to an ARRAY would not be appropriate and would likely make the data model less usable.
- C. Incorrect.
Incorrect. Stages are intended for loading and unloading files, not for storing frequently joined business attributes for query-time access. Moving the loyalty ID to stage files would complicate the architecture and likely worsen query performance and maintainability.
- D. Incorrect.
Incorrect. Replacing a structured dimension table with VARIANT would move the design away from a best-practice relational model for commonly joined business entities. CUSTOMER_DIM is appropriately structured, and the better solution is to expose the repeatedly used JSON attribute as a structured column on the fact side.