DAA-C01 Question 126
Select 2Identify and analyze data quality issuesA retail analytics team loads daily sales files from multiple stores into a Snowflake table named SALES_RAW. Analysts report that daily revenue is occasionally overstated. Initial investigation shows three recurring data quality problems: duplicate transaction rows from file reprocessing, missing STORE_ID values, and SALE_AMOUNT values stored as text in some files, causing conversion errors. The team wants to identify these issues early and make them visible in downstream reporting with minimal manual review. Which TWO actions would best help the team identify and analyze these data quality issues in Snowflake?
- A
Create a profiling view that measures duplicate counts by transaction business key, null rates for STORE_ID, and TRY_TO_DECIMAL conversion failure rates for SALE_AMOUNT after each load.
- B
Replace all CAST operations on SALE_AMOUNT with TO_DECIMAL so invalid values are converted to 0 and can be included in revenue totals.
- C
Load the files into a staging table first, then use SQL quality checks and exception tables to isolate duplicate rows, null STORE_ID records, and rows where TRY_TO_DECIMAL(SALE_AMOUNT) returns NULL.
- D
Define a clustering key on STORE_ID in SALES_RAW so records with missing or invalid values are automatically grouped and corrected during query execution.
- E
Use a COPY INTO command with ON_ERROR = CONTINUE directly into the reporting table and assume rejected-row counts are sufficient to diagnose duplicates, null business keys, and semantic value issues.
Show answer and explanation
Correct answers: A, C
Explanation
The best answers are options 1 and 3 because they directly address identification and analysis of multiple data quality dimensions: uniqueness, completeness, and validity. In Snowflake, a common best practice is to land source data in a staging/raw layer, then run SQL-based validation checks before promoting records downstream. TRY_CAST-style functions such as TRY_TO_DECIMAL are useful for profiling and exception handling because they return NULL for invalid conversions, allowing bad values to be counted and isolated without aborting queries. Duplicate detection should be based on business keys or expected transaction grain, not load errors alone. Similarly, null required attributes like STORE_ID must be measured explicitly. Snowflake documentation and best practices around staged loading, COPY options, and TRY_* conversion functions support this pattern: use COPY for ingestion, then use SQL transformations and validation logic to assess data quality before trusted consumption.
- A. Correct.
Correct. This is a practical data quality monitoring approach in Snowflake. A profiling view or recurring quality query can quantify the exact issues the team is seeing: duplicate business keys, null rates, and conversion failures. Using TRY_TO_DECIMAL is especially appropriate for analysis because invalid numeric text returns NULL instead of raising an error, making bad records measurable. This helps analysts identify trends by load date and evaluate impact before data reaches trusted reporting layers.
- B. Incorrect.
Incorrect. TO_DECIMAL does not convert invalid values to 0; invalid conversions typically raise an error. More importantly, silently coercing bad values to 0 would hide a data quality issue and distort revenue analysis. The better practice is to preserve bad rows for investigation and use TRY_TO_DECIMAL in validation logic to identify malformed values without failing the entire analysis query.
- C. Correct.
Correct. Staging raw data before promotion to curated/reporting tables is a standard best practice for identifying and analyzing data quality issues. It allows the team to run targeted validation SQL, quarantine exceptions, and measure quality defects such as duplicate transactions, missing required keys, and invalid numeric formats. Using exception tables also supports root-cause analysis and remediation workflows instead of losing visibility into problematic rows.
- D. Incorrect.
Incorrect. Clustering keys are a performance optimization for pruning and micro-partition organization, not a data quality feature. They do not identify, group for remediation, or correct invalid data values. A candidate might choose this option because clustering changes physical organization, but it has no semantic validation capability for duplicates, nulls, or type issues.
- E. Incorrect.
Incorrect. ON_ERROR = CONTINUE can help ingest continue past bad records, but rejected-row counts alone are not enough for the described problems. Duplicate transactions and missing STORE_ID values can still load successfully because they may be syntactically valid rows. Semantic data quality issues require explicit validation logic after loading, usually in staging, rather than relying only on COPY error handling.