COF-C03 Question 295
Single answer4.4 Perform data transformation techniquesA retail company loads raw clickstream data into a Snowflake table named RAW_EVENTS. One column, EVENT_DATA, stores semi-structured JSON in a VARIANT column. Analysts need a relational view that returns one row per product in each event, along with the event timestamp and customer ID. Each JSON document contains an array named products and scalar attributes named event_ts and customer.id. Which approach should be used to transform this data efficiently in Snowflake?
- A
Use the FLATTEN table function on EVENT_DATA:products and select EVENT_DATA:event_ts, EVENT_DATA:customer.id, and the flattened product values.
- B
Cast the entire VARIANT column to VARCHAR and use SPLIT to separate product values into rows.
- C
Use Snowpipe to automatically convert the products array into separate relational rows during file ingestion.
- D
Create a materialized view directly on RAW_EVENTS that uses ARRAY_AGG to expand the products array into one row per product.
Show answer and explanation
Correct answer: A
Explanation
The correct solution is to use Snowflake's native semi-structured data features: path notation to extract scalar values from VARIANT and the FLATTEN table function to expand array elements into separate rows. A typical pattern would be to query RAW_EVENTS, join laterally to TABLE(FLATTEN(INPUT => EVENT_DATA:products)), and then select EVENT_DATA:event_ts, EVENT_DATA:customer.id, and the flattened VALUE for each product. This is a common real-world transformation pattern when working with JSON in Snowflake. Best practices and Snowflake documentation emphasize using VARIANT path expressions and FLATTEN for relationalizing nested JSON rather than converting JSON to raw strings for manual parsing. Snowpipe is for automated loading, not row explosion of arrays, and ARRAY_AGG performs aggregation rather than normalization.
- A. Correct.
Correct. FLATTEN is the standard Snowflake table function used to explode arrays or objects stored in VARIANT, OBJECT, or ARRAY data into individual rows. In this scenario, using FLATTEN on EVENT_DATA:products produces one row per product element, while scalar values such as EVENT_DATA:event_ts and EVENT_DATA:customer.id can be projected alongside each flattened row. This is the appropriate transformation technique for converting semi-structured array data into relational output.
- B. Incorrect.
Incorrect. Casting the entire VARIANT payload to VARCHAR and then parsing text with SPLIT is not an appropriate or reliable transformation technique for JSON arrays. It discards JSON structure, is brittle when formatting changes, and can produce incorrect parsing results. Snowflake provides native semi-structured data access and FLATTEN specifically to avoid this kind of string manipulation.
- C. Incorrect.
Incorrect. Snowpipe automates continuous data loading into Snowflake tables, but it does not itself transform a JSON array into multiple relational rows during ingestion in the way described here. The transformation from nested semi-structured data into row-wise relational output is typically done with SQL after loading, for example by querying staged data or loaded VARIANT data with FLATTEN.
- D. Incorrect.
Incorrect. ARRAY_AGG aggregates multiple rows into an array; it does not expand an array into multiple rows. This option reverses the needed transformation. Also, while materialized views can store precomputed query results under supported conditions, ARRAY_AGG is not the mechanism for exploding nested arrays into separate rows.