DAA-C01 Question 128
Single answerHandle erroneous and ambiguous dataA retail analytics team loads point-of-sale data from multiple stores into a Snowflake table. One column, RAW_SALE_TS, contains inconsistent timestamp strings such as "2025-03-04 14:30:00", "03/04/2025 2:30 PM", "2025/04/03 14:30", empty strings, and invalid values like "2025-99-99". Analysts need a reliable SALE_TS column for downstream reporting, but they also want to preserve problematic rows for data quality review instead of failing the pipeline. Which approach best meets these requirements?
- A
Use TO_TIMESTAMP(RAW_SALE_TS) in the transformation step so invalid values raise an error and prevent bad data from entering the curated table.
- B
Use TRY_TO_TIMESTAMP on RAW_SALE_TS, store the parsed result in SALE_TS, and flag rows where SALE_TS is NULL while RAW_SALE_TS is not NULL or empty for separate review.
- C
Cast RAW_SALE_TS to VARCHAR first, then to TIMESTAMP_NTZ, because explicit casting handles mixed timestamp formats better than TRY_ functions.
- D
Set TIMESTAMP_INPUT_FORMAT to AUTO and use TO_TIMESTAMP on RAW_SALE_TS; AUTO guarantees all valid business date formats will parse correctly and ambiguous rows will be standardized automatically.
Show answer and explanation
Correct answer: B
Explanation
The best answer is to use TRY_TO_TIMESTAMP and then identify rows where parsing failed. In Snowflake, the TRY_TO_DATE, TRY_TO_TIME, and TRY_TO_TIMESTAMP functions are specifically designed for handling erroneous or inconsistent source data because they return NULL instead of raising conversion errors. This pattern is widely used in curated-layer transformations: derive the typed column safely, preserve the raw value, and create a quality flag or exception stream for non-null source values that fail parsing. While Snowflake session parameters such as TIMESTAMP_INPUT_FORMAT can influence parsing behavior, they do not eliminate ambiguity or protect the pipeline from invalid values when using strict conversion functions like TO_TIMESTAMP. A practical best practice is to retain the original raw column, use TRY_ conversion for resilience, and route failed parses to data quality review tables or dashboards.
- A. Incorrect.
Incorrect. TO_TIMESTAMP raises an error when it cannot parse a value. That behavior can be useful for strict validation, but it does not satisfy the requirement to preserve problematic rows and keep the pipeline running. In this scenario, the team wants resilient ingestion and explicit review of bad records rather than batch failure.
- B. Correct.
Correct. TRY_TO_TIMESTAMP returns NULL instead of failing when parsing is unsuccessful, which is the standard Snowflake approach for handling erroneous or ambiguous date/time strings in robust pipelines. By storing the parsed value in SALE_TS and separately flagging rows where the source is populated but parsing returned NULL, the team preserves records for investigation while delivering a clean, typed column for downstream analytics.
- C. Incorrect.
Incorrect. Casting to VARCHAR first does not improve parsing of inconsistent formats; the value is already effectively a string input. A direct cast to TIMESTAMP_NTZ can still fail on malformed or unsupported formats. This option reflects a common misconception that extra casting steps make parsing more tolerant, but Snowflake's TRY_TO_TIMESTAMP family is the correct mechanism for fault-tolerant conversion.
- D. Incorrect.
Incorrect. TIMESTAMP_INPUT_FORMAT=AUTO can help Snowflake recognize some common input patterns, but it does not guarantee successful parsing for every business-specific or ambiguous format, and TO_TIMESTAMP still throws an error on invalid values. It also does not inherently provide a review workflow for malformed records. AUTO is helpful, but it is not a substitute for defensive parsing with TRY_ functions and explicit data quality checks.