DAA-C01 Question 89
Single answerScalar functionsA retail analytics team loads clickstream events into a Snowflake table named WEB_EVENTS. The EVENT_TS column is stored as VARCHAR because different source systems send timestamps in mixed formats such as '2025-01-15 14:32:10', '01/15/2025 02:32:10 PM', and occasionally invalid values like 'TBD'. Analysts need a query that returns only valid events from January 2025 and avoids query failure when invalid strings are encountered. Which approach best meets this requirement using Snowflake scalar functions?
- A
Use TRY_TO_TIMESTAMP(EVENT_TS) in the WHERE clause and filter rows with TRY_TO_TIMESTAMP(EVENT_TS) >= '2025-01-01'::TIMESTAMP AND TRY_TO_TIMESTAMP(EVENT_TS) < '2025-02-01'::TIMESTAMP
- B
Use TO_TIMESTAMP(EVENT_TS) in the WHERE clause and rely on Snowflake to automatically skip rows where conversion fails
- C
Use CAST(EVENT_TS AS TIMESTAMP) in the WHERE clause because CAST returns NULL for invalid timestamp strings
- D
Use DATE_TRUNC('MONTH', EVENT_TS) = '2025-01-01'::DATE because DATE_TRUNC can interpret VARCHAR timestamps directly
Show answer and explanation
Correct answer: A
Explanation
The key requirement is to handle mixed-quality string input without failing the query. In Snowflake, the TRY_TO_DATE, TRY_TO_TIME, and TRY_TO_TIMESTAMP family of scalar functions are designed for this exact pattern: they return NULL when parsing fails instead of raising an exception. That makes them especially useful in ELT pipelines and analyst-facing queries over semi-clean source data. For timestamp filtering, using a range such as >= '2025-01-01' and < '2025-02-01' is preferred over applying functions like DATE_TRUNC to the column in the predicate, because it expresses the intended period clearly and avoids relying on successful coercion before filtering. Snowflake documentation for conversion functions and TRY_ variants describes this NULL-on-failure behavior, while DATE_TRUNC documentation shows it expects a date/time input rather than raw VARCHAR.
- A. Correct.
Correct. TRY_TO_TIMESTAMP is a Snowflake scalar conversion function that attempts to parse a value and returns NULL instead of raising an error when conversion fails. That behavior is exactly what is needed when some rows contain invalid values such as 'TBD'. Filtering with a half-open range from 2025-01-01 to 2025-02-01 is also a best practice for timestamp filtering because it correctly includes all valid times within January 2025. Rows where TRY_TO_TIMESTAMP(EVENT_TS) returns NULL are excluded by the comparison predicates.
- B. Incorrect.
Incorrect. TO_TIMESTAMP raises an error if a value cannot be converted, so rows containing invalid strings would cause the query to fail rather than be skipped. A common misconception is that Snowflake silently ignores bad conversions in predicates, but standard conversion functions do not behave that way.
- C. Incorrect.
Incorrect. CAST to TIMESTAMP does not safely absorb invalid timestamp strings. Like TO_TIMESTAMP, it can raise a conversion error when the string cannot be interpreted as a timestamp. This option reflects the mistaken assumption that CAST behaves like a TRY_ conversion function.
- D. Incorrect.
Incorrect. DATE_TRUNC operates on date/time expressions, not arbitrary VARCHAR input. EVENT_TS would need to be successfully converted to a temporal type first. In addition, this option would still fail on invalid strings unless paired with a TRY_ conversion function.