DAA-C01 Question 29
Single answerPerform data filtering and/or transformationA retail analytics team stores clickstream events in a Snowflake table named RAW_EVENTS. One column, EVENT_DATA, is VARIANT and contains JSON such as {"eventType":"purchase","amount":"129.99","items":[{"sku":"A1"},{"sku":"B2"}]}. Analysts need a query that returns only purchase events from the last 7 days, converts the amount to a numeric value for aggregation, and excludes rows where amount is missing or not a valid number. Which query best meets these requirements?
- A
SELECT TRY_TO_DECIMAL(EVENT_DATA:amount::STRING, 10, 2) AS amount_num FROM RAW_EVENTS WHERE EVENT_DATA:eventType = 'purchase' AND EVENT_TS >= DATEADD(day, -7, CURRENT_TIMESTAMP()) AND amount_num IS NOT NULL;
- B
SELECT TO_DECIMAL(EVENT_DATA:amount::STRING, 10, 2) AS amount_num FROM RAW_EVENTS WHERE EVENT_DATA:eventType::STRING = 'purchase' AND EVENT_TS >= DATEADD(day, -7, CURRENT_TIMESTAMP());
- C
SELECT TRY_TO_DECIMAL(EVENT_DATA:amount::STRING, 10, 2) AS amount_num FROM RAW_EVENTS WHERE EVENT_DATA:eventType::STRING = 'purchase' AND EVENT_TS >= DATEADD(day, -7, CURRENT_TIMESTAMP()) QUALIFY amount_num IS NOT NULL;
- D
SELECT EVENT_DATA:amount::NUMBER(10,2) AS amount_num FROM RAW_EVENTS WHERE EVENT_DATA:eventType::STRING = 'purchase' AND DATEDIFF(day, EVENT_TS, CURRENT_TIMESTAMP()) <= 7 AND EVENT_DATA:amount IS NOT NULL;
Show answer and explanation
Correct answer: C
Explanation
The best answer is the query that safely transforms semi-structured data and then filters out bad results without aborting execution. In Snowflake, JSON fields in a VARIANT column are commonly accessed with path notation such as EVENT_DATA:eventType and EVENT_DATA:amount. For comparisons to string literals, explicitly casting with ::STRING is a best practice. For numeric conversion where malformed input is possible, TRY_TO_DECIMAL (or related TRY_TO_NUMBER functions) is preferred over TO_DECIMAL because TRY_ functions return NULL instead of raising conversion errors. To remove rows with invalid conversions when using a SELECT alias, QUALIFY can be used in Snowflake because it is evaluated after projection and supports filtering on expressions such as aliases and window-function results. This pattern is practical for analytics workloads that require robust filtering and transformation of semi-structured event data. Relevant Snowflake documentation includes sections on querying semi-structured data, conversion functions such as TRY_TO_DECIMAL/TRY_TO_NUMBER, and the QUALIFY clause.
- A. Incorrect.
Incorrect. The filter on EVENT_DATA:eventType is not properly cast to STRING before comparison, which can lead to type mismatch or implicit-cast issues when comparing a VARIANT value to a string literal. More importantly, Snowflake does not allow a SELECT-list alias such as amount_num to be referenced in the WHERE clause because WHERE is evaluated before SELECT. This is a common SQL mistake when filtering transformed values.
- B. Incorrect.
Incorrect. Casting EVENT_DATA:eventType to STRING is correct, and the last-7-days filter is reasonable, but TO_DECIMAL will raise an error if amount is malformed or contains non-numeric text. The requirement is to exclude invalid amounts, not fail the query. TRY_TO_DECIMAL is the appropriate function because it returns NULL instead of throwing an error for invalid conversions.
- C. Correct.
Correct. This query correctly extracts the JSON field from the VARIANT column, casts eventType to STRING for reliable comparison, uses TRY_TO_DECIMAL to safely convert amount values, and filters to the last 7 days. Using QUALIFY amount_num IS NOT NULL works in Snowflake because QUALIFY is evaluated after the SELECT list, so the alias amount_num is available there. This excludes rows with missing or invalid numeric amounts without causing query failure.
- D. Incorrect.
Incorrect. EVENT_DATA:eventType::STRING = 'purchase' is appropriate, and checking EVENT_DATA:amount IS NOT NULL addresses missing values. However, directly casting EVENT_DATA:amount::NUMBER(10,2) can still fail if the JSON contains non-numeric text such as an empty string or malformed value. Also, using DATEDIFF(day, EVENT_TS, CURRENT_TIMESTAMP()) <= 7 can include rows older than exactly 7 x 24 hours depending on time boundaries, whereas DATEADD(day, -7, CURRENT_TIMESTAMP()) more precisely expresses the rolling 7-day filter.