DAA-C01 Question 25
Select 3Evaluate which transformations are required:A retail analytics team loads clickstream events into a Snowflake table named RAW_EVENTS. The table contains one row per API payload, with columns: EVENT_ID, INGEST_TS, PAYLOAD VARIANT. The PAYLOAD column contains nested JSON such as customer attributes, an array of viewed products, and an event timestamp as a string in multiple source formats. Analysts need a curated table for BI reporting with these requirements: one row per viewed product, a consistently typed event timestamp, and only the most recent record when the same EVENT_ID is re-sent by the source system. Which set of transformations should be applied to meet these requirements with the least unnecessary processing?
- A
Use LATERAL FLATTEN on the viewed-products array in PAYLOAD to produce one row per viewed product.
- B
Cast the event timestamp string from PAYLOAD into a TIMESTAMP using an appropriate conversion function so BI tools query a consistent data type.
- C
Use a window function such as ROW_NUMBER() over EVENT_ID ordered by INGEST_TS descending, then filter to keep only the latest record per EVENT_ID.
- D
Cluster the RAW_EVENTS table by EVENT_ID and viewed product to remove duplicate resent events during query execution.
- E
Create a materialized view directly on RAW_EVENTS and rely on automatic refresh to normalize semi-structured fields without explicit transformation.
Show answer and explanation
Correct answers: A, B, C
Explanation
The required transformations are driven by the target analytical shape of the data: explode the nested array, standardize the timestamp type, and deduplicate resent events based on the latest ingestion time. In Snowflake, semi-structured arrays are typically expanded with FLATTEN used in a lateral join. String-based date/time values should be converted into native DATE or TIMESTAMP types for reliable analytics; TRY_ conversion functions are often useful when source formats vary. For duplicate-event handling, ROW_NUMBER() with QUALIFY or an outer filter is a common and efficient pattern to keep the latest record per business key. By contrast, clustering and materialized views are performance or deployment techniques, not substitutes for the logical transformations needed. These practices align with Snowflake guidance for transforming semi-structured data, using window functions for deduplication, and preparing curated analytic datasets from raw landing tables.
- A. Correct.
Correct. When a JSON document contains an array and the target model requires one row per array element, LATERAL FLATTEN is the appropriate transformation in Snowflake. It expands each element of the array into a separate row while preserving access to other columns from the source row. This directly satisfies the requirement for one row per viewed product.
- B. Correct.
Correct. BI reporting works best when temporal fields are stored as consistent native timestamp types rather than mixed-format strings embedded in VARIANT. Applying an explicit conversion, such as TO_TIMESTAMP or TRY_TO_TIMESTAMP with the right format handling, is the required transformation to standardize the event time for filtering, aggregation, and time intelligence.
- C. Correct.
Correct. The source can resend the same EVENT_ID, so deduplication is needed. A standard Snowflake pattern is to assign ROW_NUMBER() over partitions by EVENT_ID ordered by the ingestion timestamp descending, then retain only row number 1. This keeps the most recent record for each event and is a targeted transformation aligned to the business rule.
- D. Incorrect.
Incorrect. Clustering can improve pruning and query performance for some workloads, but it does not deduplicate rows or remove resent events. It is a physical optimization, not a logical transformation. Someone might choose this because EVENT_ID appears in the duplicate rule, but clustering does not enforce uniqueness or latest-record selection.
- E. Incorrect.
Incorrect. Materialized views can precompute query results, but they do not eliminate the need to define the actual transformations required. Automatic refresh maintains the view result after changes; it does not inherently normalize JSON arrays into one row per product or standardize mixed-format timestamps unless those transformations are explicitly coded. Also, relying on a materialized view is an implementation choice, not the transformation set itself.