DAA-C01 Question 153
Single answerPerform casting — change data types to ensure data can be presented consistentlyA retail analytics team loads order data from multiple source systems into a Snowflake table named RAW_ORDERS. The ORDER_DATE column is VARCHAR and contains values such as '2024-01-15', '01/16/2024', and occasional invalid strings like 'TBD'. A BI dashboard requires a single field that can be displayed consistently as a DATE without causing query failures when bad source values are present. Which approach should the analyst use?
- A
Create a derived column using TO_DATE(ORDER_DATE) so Snowflake automatically converts every value to DATE.
- B
Create a derived column using CAST(ORDER_DATE AS DATE) and rely on Snowflake to return NULL for invalid values.
- C
Create a derived column using TRY_TO_DATE(ORDER_DATE) and standardize input patterns upstream or with explicit parsing logic as needed.
- D
Keep ORDER_DATE as VARCHAR and apply ORDER BY ORDER_DATE so dates are presented consistently in the dashboard.
Show answer and explanation
Correct answer: C
Explanation
When source data contains mixed date string formats and occasional invalid values, Snowflake's TRY_TO_DATE function is typically the safest choice for producing a consistently typed DATE field without breaking queries. Unlike CAST or TO_DATE, which can raise errors on malformed input, TRY_TO_DATE returns NULL when conversion fails. This is especially useful in reporting and dashboard scenarios where resiliency matters. In practice, analysts should also reduce ambiguity by standardizing source formats or applying explicit format strings where required, because mixed string representations can lead to inconsistent parsing behavior. This aligns with Snowflake documentation on conversion functions and error-handling conversion functions such as TRY_CAST and TRY_TO_DATE, which are designed for robust handling of imperfect source data.
- A. Incorrect.
Incorrect. TO_DATE can convert string values to DATE, but if the input contains invalid strings or ambiguous formats that Snowflake cannot parse, the query can fail. In this scenario, the requirement explicitly states that bad source values such as 'TBD' must not cause query failures. Using TO_DATE directly does not safely handle malformed records.
- B. Incorrect.
Incorrect. CAST(... AS DATE) does not safely suppress conversion errors for invalid strings. A common misconception is that failed casts automatically produce NULL, but in Snowflake, standard CAST can raise an error when conversion is not possible. That makes it unsuitable when the source data contains known bad values and the dashboard must remain stable.
- C. Correct.
Correct. TRY_TO_DATE returns NULL instead of failing when a value cannot be converted, which is the appropriate choice when input data may include invalid values. It is also a best practice to standardize source formats where possible or use explicit format models when parsing nonuniform strings. This approach ensures downstream presentation is consistently typed as DATE while avoiding runtime failures from malformed data.
- D. Incorrect.
Incorrect. Leaving the column as VARCHAR does not ensure consistent date presentation. Lexicographic sorting of strings does not reliably produce correct chronological ordering across mixed formats such as '2024-01-15' and '01/16/2024'. It also leaves the BI layer to interpret inconsistent string values rather than providing a clean typed DATE column.