DAA-C01 Question 114
Single answer2.1 Prepare different data types into a consumable format.A retail analytics team loads clickstream events into a Snowflake table named RAW_EVENTS. The table contains EVENT_ID NUMBER, LOAD_TS TIMESTAMP_NTZ, and PAYLOAD VARIANT. The PAYLOAD column stores JSON such as: {"customer":{"id":"C102","signup_date":"2024-01-15"},"event_time":"2024-03-01T14:22:11Z","items":[{"sku":"SKU1","qty":2},{"sku":"SKU2","qty":1}],"purchase_amount":"149.95"}. Analysts want a consumable relational view for BI tools with one row per purchased item and correctly typed columns for customer_id, signup_date, event_time_utc, sku, qty, and purchase_amount. Which approach best meets this requirement?
- A
Create a view that selects PAYLOAD:customer:id::STRING AS customer_id, TO_DATE(PAYLOAD:customer:signup_date::STRING) AS signup_date, TO_TIMESTAMP_TZ(PAYLOAD:event_time::STRING) AS event_time_utc, ITEM.VALUE:sku::STRING AS sku, ITEM.VALUE:qty::NUMBER AS qty, TO_DECIMAL(PAYLOAD:purchase_amount::STRING, 10, 2) AS purchase_amount from RAW_EVENTS, LATERAL FLATTEN(input => PAYLOAD:items) ITEM.
- B
Create a view that selects PAYLOAD.customer.id AS customer_id, CAST(PAYLOAD.customer.signup_date AS DATE) AS signup_date, CAST(PAYLOAD.event_time AS TIMESTAMP_LTZ) AS event_time_utc, PAYLOAD.items.sku AS sku, PAYLOAD.items.qty AS qty, CAST(PAYLOAD.purchase_amount AS FLOAT) AS purchase_amount from RAW_EVENTS.
- C
Create a materialized view that uses FLATTEN on PAYLOAD:items and casts the JSON fields, because Snowflake materialized views fully support joins to table functions such as LATERAL FLATTEN for semi-structured data normalization.
- D
Store the JSON as VARCHAR instead of VARIANT, then use SPLIT and SUBSTR in a standard view to parse customer_id, event_time, items, and purchase_amount into relational columns.
Show answer and explanation
Correct answer: A
Explanation
The best answer is to keep the semi-structured data in VARIANT, use path notation to extract scalar fields, and use LATERAL FLATTEN to normalize arrays into one row per item. This is a core Snowflake best practice for preparing semi-structured data into a consumable format for analytics tools. Explicit casting is important because BI tools typically work best with relational columns that have stable data types such as STRING, DATE, TIMESTAMP, and NUMBER. For currency, fixed-point NUMBER/DECIMAL is preferable to FLOAT. For timestamps containing timezone information such as a trailing Z, TO_TIMESTAMP_TZ is the safest choice because it preserves timezone semantics. Snowflake documentation on querying semi-structured data, FLATTEN, and type conversion supports this approach.
- A. Correct.
Correct. This is the standard Snowflake pattern for preparing semi-structured JSON into a consumable relational format. JSON elements in VARIANT are accessed with path notation such as PAYLOAD:customer:id, arrays are expanded into one row per element using LATERAL FLATTEN, and explicit casts convert values into BI-friendly data types. TO_DATE on the signup_date string is appropriate, TO_TIMESTAMP_TZ preserves the timezone offset embedded in the ISO-8601 event_time string ending in Z, and TO_DECIMAL gives a fixed-point numeric suitable for currency instead of a floating-point type.
- B. Incorrect.
Incorrect. This option mixes incorrect JSON access patterns and mishandles the array. In Snowflake SQL, VARIANT traversal commonly uses colon path notation, and PAYLOAD.items.sku does not correctly produce one row per item from an array. Without FLATTEN, the items array remains nested and cannot be exposed as separate SKU and QTY rows for BI consumption. Casting purchase_amount to FLOAT is also a poor choice for currency because fixed-point NUMBER/DECIMAL is preferred to avoid precision issues.
- C. Incorrect.
Incorrect. While the transformation logic conceptually resembles what is needed, the statement about materialized views is not accurate. Snowflake materialized views have significant restrictions and do not support all constructs available in standard views, including this type of table-function-based normalization pattern with LATERAL FLATTEN. A standard view or ETL/ELT process into a relational table is the appropriate design here.
- D. Incorrect.
Incorrect. Converting structured JSON handling from VARIANT to raw VARCHAR parsing is an anti-pattern in Snowflake. VARIANT is designed for semi-structured querying with path access, automatic type handling, and FLATTEN for arrays. Using string functions like SPLIT and SUBSTR would be brittle, hard to maintain, and error-prone when JSON structures evolve or values contain unexpected characters.