DAA-C01 Question 134
Single answerConvert data typesA retail analytics team loads daily sales data into a Snowflake table named RAW_SALES. The column SALE_TS is defined as VARCHAR because source systems send mixed timestamp formats, including values such as '2024-11-01 13:45:22', '11/01/2024 01:45:22 PM', and occasionally invalid strings like 'UNKNOWN'. Analysts need a query that converts valid values to TIMESTAMP_NTZ for reporting, but the query must not fail when invalid values are encountered. Which approach should the team use?
- A
Use CAST(SALE_TS AS TIMESTAMP_NTZ) in the SELECT statement.
- B
Use TO_TIMESTAMP_NTZ(SALE_TS) in the SELECT statement because it automatically returns NULL for invalid values.
- C
Use TRY_TO_TIMESTAMP_NTZ(SALE_TS) in the SELECT statement.
- D
Use CONVERT_TIMEZONE('UTC', SALE_TS)::TIMESTAMP_NTZ in the SELECT statement.
Show answer and explanation
Correct answer: C
Explanation
The best solution is to use TRY_TO_TIMESTAMP_NTZ when converting a VARCHAR column that may contain invalid timestamp strings. In Snowflake, CAST and standard TO_TIMESTAMP/TO_TIMESTAMP_NTZ conversions can fail if the input cannot be parsed. By contrast, TRY_TO_TIMESTAMP_NTZ returns NULL for rows that cannot be converted, which is a best practice for analytics workloads that must continue operating despite occasional source data quality issues. This pattern is especially useful in staging and reporting layers, where analysts may want to preserve all rows and later identify problematic records with filters such as WHERE TRY_TO_TIMESTAMP_NTZ(SALE_TS) IS NULL. Snowflake documentation on conversion functions and error-handling conversion functions distinguishes between standard conversion functions and TRY_* variants for exactly this purpose.
- A. Incorrect.
Incorrect. CAST attempts a direct conversion and raises an error if a value cannot be converted to TIMESTAMP_NTZ. In this scenario, values such as 'UNKNOWN' would cause the query to fail, which does not meet the requirement for resilient reporting queries.
- B. Incorrect.
Incorrect. TO_TIMESTAMP_NTZ converts strings to TIMESTAMP_NTZ, but it does not safely suppress conversion errors for invalid inputs. If Snowflake cannot parse a value, the statement can fail. A common misconception is that all TO_* conversion functions behave like TRY_TO_* functions, but only the TRY_* variants return NULL instead of throwing an error on invalid conversion.
- C. Correct.
Correct. TRY_TO_TIMESTAMP_NTZ attempts to parse the VARCHAR value into TIMESTAMP_NTZ and returns NULL when conversion is not possible. This is the appropriate choice when the dataset may contain malformed or unexpected timestamp strings and the query must continue processing valid rows without failing.
- D. Incorrect.
Incorrect. CONVERT_TIMEZONE is used to translate timestamps between time zones, not to safely parse arbitrary VARCHAR input containing mixed formats. It also expects valid timestamp-compatible input, so invalid strings would still be problematic. Additionally, this option does not address the requirement to tolerate bad source values.