DAA-C01 Question 90
Single answerScalar functionsA retail analytics team ingests clickstream events into a Snowflake table named WEB_EVENTS. The EVENT_TS column is loaded as VARCHAR because source systems send mixed timestamp formats, including values like '2025-01-15 14:30:00', '01/15/2025 02:30 PM', and occasional malformed strings such as 'not_available'. Analysts need a daily dashboard that groups events by event date without failing when bad records arrive. Which approach should be used in the transformation query to produce the most reliable result?
- A
Use TO_DATE(EVENT_TS) directly in the SELECT and GROUP BY clauses so Snowflake automatically converts every value to a date.
- B
Use TRY_TO_TIMESTAMP(EVENT_TS) to parse the string, then CAST the result to DATE for grouping; malformed values will become NULL instead of causing the query to fail.
- C
Use DATE_TRUNC('DAY', EVENT_TS) directly on the VARCHAR column because DATE_TRUNC can normalize string timestamps and return the date portion.
- D
Use CONCAT(SUBSTR(EVENT_TS, 1, 10), '') to extract the first 10 characters and group on that value because all timestamp strings start with the date.
Show answer and explanation
Correct answer: B
Explanation
The best answer is to use Snowflake's TRY_ conversion scalar functions when source data may contain malformed values. TRY_TO_TIMESTAMP returns NULL instead of an error for rows that cannot be parsed, which is ideal for resilient analytics pipelines and dashboards. After parsing, the result can be cast to DATE for daily aggregation. This pattern is preferable to direct TO_DATE or TO_TIMESTAMP calls when input quality is uncertain. DATE_TRUNC is useful after values are already typed as date/time/timestamp, but it is not a replacement for parsing. Likewise, string manipulation with SUBSTR or CONCAT may appear to work for one format but breaks with mixed formats and creates inconsistent grouping. This aligns with Snowflake documentation and best practices for data conversion functions, especially the distinction between standard conversion functions and TRY_ variants for error-tolerant transformations.
- A. Incorrect.
Incorrect. TO_DATE can convert valid values, but if malformed strings such as 'not_available' are present, the query can fail rather than safely returning NULL for those rows. In this scenario, the requirement is to avoid dashboard failures when bad records arrive, so relying on direct conversion is not the most reliable choice.
- B. Correct.
Correct. TRY_TO_TIMESTAMP is designed for tolerant parsing: values that can be interpreted as timestamps are converted, while invalid values return NULL instead of raising an error. Casting that parsed timestamp to DATE, or equivalently applying TO_DATE to the parsed result, supports grouping by day safely. This is the appropriate use of Snowflake's TRY_ conversion scalar functions for messy source data.
- C. Incorrect.
Incorrect. DATE_TRUNC expects a date, time, or timestamp expression, not an arbitrary VARCHAR containing inconsistent timestamp formats. Snowflake does not use DATE_TRUNC as a parsing function. The value should be converted first, ideally with a TRY_ conversion function in this scenario.
- D. Incorrect.
Incorrect. String slicing is fragile and does not satisfy the requirement. The source has mixed formats, so the first 10 characters are not consistently a valid ISO date. For example, '01/15/2025 02:30 PM' yields '01/15/2025', while other rows yield '2025-01-15'; malformed strings also remain malformed. This can produce inconsistent grouping keys and hides data quality issues rather than handling them correctly.