DAA-C01 Question 113
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. Each row contains a VARIANT column called PAYLOAD with data such as customer identifiers, event timestamps, product arrays, and nested attributes. Business analysts need a consumable relational view for BI tools with these requirements: one row per product in an event, the event timestamp converted to TIMESTAMP_NTZ, numeric quantities exposed as NUMBER, and rows without products must still be retained for auditing. Which approach best meets these requirements?
- A
Create a view that selects fields from PAYLOAD using colon notation, casts timestamp and quantity values with ::TIMESTAMP_NTZ and ::NUMBER, and uses LATERAL FLATTEN(INPUT => PAYLOAD:products, OUTER => TRUE) to expand the product array while preserving events that have no products.
- B
Create a view that selects PAYLOAD as-is and rely on BI tools to infer the timestamp and numeric data types automatically, because Snowflake VARIANT values are already consumable without explicit casting.
- C
Create a view that uses FLATTEN(INPUT => PAYLOAD) without a lateral join, then cast all returned VALUE fields to VARCHAR so downstream tools can parse them consistently.
- D
Create a materialized view that uses PARSE_JSON on PAYLOAD, then apply CROSS JOIN FLATTEN(INPUT => PAYLOAD:products) so each product becomes a row; rows without products will be excluded, which is acceptable because FLATTEN only works on populated arrays.
Show answer and explanation
Correct answer: A
Explanation
The best solution is to project semi-structured attributes from VARIANT into explicitly typed relational columns and use LATERAL FLATTEN to normalize arrays. In Snowflake, common best practice for making semi-structured data consumable is to extract elements with path notation, cast them to appropriate SQL types, and flatten arrays only where needed. LATERAL allows the FLATTEN table function to reference columns from the current row, and OUTER => TRUE retains rows when the array is missing or empty. This is especially important when business requirements include audit completeness. Snowflake documentation for querying semi-structured data, using FLATTEN, and casting VARIANT values supports this pattern. Explicit typing improves compatibility with BI and reporting tools, while targeted flattening avoids over-expanding unrelated JSON attributes.
- A. Correct.
Correct. This is the most appropriate approach for preparing semi-structured data into a consumable relational format in Snowflake. Accessing nested elements in a VARIANT column with path notation is standard practice. Explicit casting to TIMESTAMP_NTZ and NUMBER ensures BI tools receive stable, typed columns rather than semi-structured values. Using LATERAL FLATTEN on the products array creates one row per product, and OUTER => TRUE preserves source rows even when the array is missing or empty, which satisfies the auditing requirement.
- B. Incorrect.
Incorrect. Although Snowflake can store semi-structured values in VARIANT, BI tools generally work best with explicit relational columns and stable data types. Relying on downstream tools to infer types can lead to inconsistent behavior, especially for timestamps and numbers embedded in JSON. This option also does not address the one-row-per-product requirement.
- C. Incorrect.
Incorrect. FLATTEN is typically used with a lateral join so that each input row can be correlated with its flattened results. Flattening the entire payload instead of specifically the products array would produce rows for unrelated top-level attributes, not one row per product. Casting everything to VARCHAR makes the data less consumable for analytics because timestamps and quantities would lose their native types.
- D. Incorrect.
Incorrect. PARSE_JSON is used to convert string data into VARIANT; if PAYLOAD is already a VARIANT column, applying PARSE_JSON is unnecessary and not the right transformation. CROSS JOIN FLATTEN without OUTER => TRUE excludes rows where products are null or empty, which violates the requirement to retain rows without products for auditing. Also, materialized view usage is not the key requirement here; the main issue is the transformation logic.