DAA-C01 Question 133
Single answerConvert data typesA retail analytics team loads point-of-sale data from CSV files into a Snowflake staging table where all columns are stored as VARCHAR. The ORDER_TS column contains values such as '2024-01-15 13:45:22', '2024/01/16 09:10:05', and occasionally invalid strings like 'TBD'. Analysts need a query that converts ORDER_TS to a timestamp for downstream reporting without failing when bad values are encountered. The output should return valid timestamps where possible and NULL for malformed values. Which Snowflake expression best meets this requirement?
- A
CAST(ORDER_TS AS TIMESTAMP)
- B
TO_TIMESTAMP(ORDER_TS, 'YYYY-MM-DD HH24:MI:SS')
- C
TRY_TO_TIMESTAMP(ORDER_TS)
- D
AS_TIMESTAMP(ORDER_TS)
Show answer and explanation
Correct answer: C
Explanation
When converting raw string data to typed columns in Snowflake, the key distinction is whether conversion errors should stop execution or be tolerated. CAST and TO_TIMESTAMP generally raise errors for invalid input, which is useful for strict ETL enforcement but not when analysts need resilient queries over imperfect staging data. TRY_TO_TIMESTAMP is designed for this exact case: it converts valid values and returns NULL for invalid ones. This aligns with Snowflake best practices for handling semi-structured or dirty source data during analysis and incremental data cleansing. Snowflake documentation for data type conversion and error-handling conversion functions describes the TRY_CAST/TRY_TO_* family as the preferred approach when failed conversions should produce NULL instead of an exception.
- A. Incorrect.
Incorrect. CAST(ORDER_TS AS TIMESTAMP) attempts a direct conversion and raises an error if a value cannot be converted. In this scenario, malformed values such as 'TBD' would cause the query to fail instead of returning NULL. This is a common mistake when transforming semi-clean raw string data.
- B. Incorrect.
Incorrect. TO_TIMESTAMP with a specific format can be useful when input data strictly follows one known pattern, but here the source contains mixed formats such as both '2024-01-15 13:45:22' and '2024/01/16 09:10:05'. In addition, invalid strings like 'TBD' would still raise an error rather than safely returning NULL. This option reflects the misconception that format-based parsing alone solves data quality issues.
- C. Correct.
Correct. TRY_TO_TIMESTAMP attempts to convert the string to a timestamp and returns NULL if the conversion is not possible, which prevents the query from failing on bad records. This is appropriate for analyst-facing transformations over staging data with inconsistent or invalid string values. It is the safest choice among the options when the requirement is to preserve successful conversions and tolerate malformed input.
- D. Incorrect.
Incorrect. AS_TIMESTAMP is not the correct function for converting a VARCHAR column containing timestamp text in this scenario. Snowflake provides CAST/::, TO_TIMESTAMP, and TRY_TO_TIMESTAMP-style conversion functions for this use case. Choosing this option suggests confusion between Snowflake's conversion functions and other function naming patterns.