DAA-C01 Question 154
Single answerPerform casting — change data types to ensure data can be presented consistentlyA retail analytics team loads clickstream events into a Snowflake table named RAW_EVENTS. The EVENT_TS column is VARCHAR because different source systems send timestamps in different formats. Analysts need a view that exposes a single timestamp column for dashboards without causing query failures when malformed values appear. The team also wants rows with invalid timestamps to remain queryable so data quality issues can be investigated later.
Which approach should the data analyst use to present the timestamp values consistently while meeting these requirements?
- A
Create the view with TRY_TO_TIMESTAMP(EVENT_TS) as EVENT_TIMESTAMP so valid strings are converted and invalid values return NULL.
- B
Create the view with CAST(EVENT_TS AS TIMESTAMP) as EVENT_TIMESTAMP because Snowflake automatically standardizes all malformed timestamp strings.
- C
Create the view with TO_TIMESTAMP(EVENT_TS) as EVENT_TIMESTAMP because it skips invalid rows and returns only successfully converted timestamps.
- D
Create the view with EVENT_TS::TIMESTAMP as EVENT_TIMESTAMP and set the column default to NULL so failed casts are ignored at query time.
Show answer and explanation
Correct answer: A
Explanation
The best solution is to use TRY_TO_TIMESTAMP in the view so timestamps are presented in a consistent TIMESTAMP data type without causing query failures when malformed strings are encountered. In Snowflake, the TRY_TO_DATE, TRY_TO_TIME, and TRY_TO_TIMESTAMP family of functions are best suited for resilient data presentation and ingestion scenarios because they return NULL instead of raising conversion errors. By contrast, CAST, ::, and TO_TIMESTAMP raise errors when conversion fails. This makes TRY_* functions especially useful in analytical views over semi-clean source data, where preserving row visibility is important for downstream quality checks. This aligns with Snowflake documentation on conversion/casting functions and error-handling conversion functions.
- A. Correct.
Correct. TRY_TO_TIMESTAMP is designed for safe casting when source data may contain invalid or inconsistent timestamp strings. It returns a TIMESTAMP value when conversion succeeds and NULL when conversion fails, which prevents the entire query from erroring out. This is appropriate for a reporting view where dashboards must remain available while still allowing analysts to identify problematic rows by checking for NULL converted values.
- B. Incorrect.
Incorrect. CAST(... AS TIMESTAMP) performs a regular conversion and raises an error if Snowflake cannot parse a value. Snowflake does not automatically standardize malformed strings into valid timestamps. In this scenario, malformed source values would cause query failures, which violates the requirement to keep the view queryable.
- C. Incorrect.
Incorrect. TO_TIMESTAMP attempts conversion, but when provided an invalid value, it raises an error rather than silently skipping rows. It does not filter out bad records by itself. Someone might choose this option because TO_TIMESTAMP is commonly used for conversion, but it is not the safe choice when bad data must not break queries.
- D. Incorrect.
Incorrect. The ::TIMESTAMP syntax is shorthand for CAST and has the same failure behavior as CAST when the input cannot be converted. A column default does not change how cast errors are handled during query execution. This option reflects a common misconception that default values can absorb runtime conversion failures in a SELECT statement.