DAA-C01 Question 127
Single answerHandle erroneous and ambiguous dataA retail analytics team loads clickstream data from CSV files into a Snowflake staging table. The raw files contain a column named EVENT_TS that should represent the event timestamp, but the source systems send inconsistent values such as "2025-01-15 14:33:09", "01/15/2025 2:33 PM", empty strings, and invalid text like "not_available". Analysts need a downstream view that preserves all rows for investigation while exposing a reliable timestamp column for reporting. Which approach best handles this erroneous and ambiguous data in Snowflake?
- A
Create the reporting view with TRY_TO_TIMESTAMP(EVENT_TS) AS EVENT_TIMESTAMP and a separate flag such as IFF(EVENT_TS IS NULL OR TRY_TO_TIMESTAMP(EVENT_TS) IS NULL, TRUE, FALSE) AS IS_INVALID_TS
- B
Create the reporting view with TO_TIMESTAMP(EVENT_TS) AS EVENT_TIMESTAMP so bad values fail immediately and prevent ambiguous records from entering the view
- C
Cast EVENT_TS to VARCHAR in the reporting view because keeping the original string avoids any ambiguity and is sufficient for timestamp-based reporting
- D
Use COALESCE(TO_TIMESTAMP(EVENT_TS), CURRENT_TIMESTAMP()) AS EVENT_TIMESTAMP in the reporting view so reporting queries always have a non-NULL timestamp
Show answer and explanation
Correct answer: A
Explanation
The best answer is to use TRY_TO_TIMESTAMP (or a related TRY_ conversion function) so that invalid or ambiguous source values become NULL instead of causing query failure. This is especially important when analysts must retain bad records for root-cause analysis, remediation, or source-system feedback. A companion data quality flag makes the issue explicit and supports downstream filtering, exception reporting, or dashboard warnings.
In Snowflake, the TRY_TO_DATE, TRY_TO_TIME, and TRY_TO_TIMESTAMP family is specifically designed for resilient parsing of imperfect data. By contrast, TO_TIMESTAMP is appropriate when strict parsing is required and failed conversion should stop processing. For ambiguous timestamp formats, teams should also consider standardizing input formats earlier in the pipeline or applying explicit parsing rules if source formats are known and consistent by source. However, for a mixed-quality reporting view, a safe conversion plus quality indicators is the most practical design.
This approach follows common data engineering and analytics best practices: preserve raw values, create typed columns for trusted use, and make data quality status visible rather than masking errors with invented defaults.
- A. Correct.
Correct. TRY_TO_TIMESTAMP returns NULL instead of raising an error when parsing fails, which is the appropriate choice when the business requirement is to preserve all rows while identifying problematic data. Adding a quality flag allows analysts to separate valid timestamps from invalid or missing values without losing investigatory context. This pattern is aligned with Snowflake best practices for handling messy source data in transformations and analytical views.
- B. Incorrect.
Incorrect. TO_TIMESTAMP raises an error for unparseable values, which is useful in strict validation pipelines but does not meet the stated requirement to preserve all rows for investigation. A single invalid string could break the query or transformation logic, making it unsuitable for downstream reporting over imperfect source data.
- C. Incorrect.
Incorrect. Keeping the value as VARCHAR does preserve the original data, but it does not provide a reliable timestamp column for reporting, filtering, time-window analysis, or joins on temporal attributes. The misconception is that preservation alone solves ambiguity; in reality, the requirement is to both preserve rows and expose a usable typed timestamp when possible.
- D. Incorrect.
Incorrect. Replacing invalid timestamps with CURRENT_TIMESTAMP() hides data quality issues and introduces misleading facts into reports because the substituted value represents load/query time rather than event time. This can distort trend analysis, sessionization, and SLA reporting. The misconception is that non-NULL values are always better for analytics; in data quality scenarios, fabricated defaults are often worse than explicit NULLs.