DAA-C01 Question 125
Single answerIdentify and analyze data quality issuesA retail analytics team loads daily order files from multiple regional systems into a Snowflake table named ORDERS_RAW. Analysts report that weekly revenue dashboards are inconsistent because some records have duplicate ORDER_ID values, some rows have NULL CUSTOMER_ID values, and the ORDER_DATE in a subset of records is later than the SHIP_DATE. The team wants to identify these data quality issues quickly and make the findings available for downstream monitoring without rewriting the ingestion pipeline. Which approach is the MOST appropriate in Snowflake?
- A
Create a dynamic table that selects from ORDERS_RAW and calculates data quality flags such as duplicate ORDER_ID counts, missing CUSTOMER_ID indicators, and invalid date-order checks, then use that dynamic table for monitoring and analysis.
- B
Replace ORDERS_RAW with an external table so Snowflake automatically removes duplicate rows, fills NULL CUSTOMER_ID values, and corrects invalid date relationships during refresh.
- C
Create a secure view on ORDERS_RAW because secure views enforce data quality rules and prevent invalid rows from being queried by analysts.
- D
Enable Search Optimization Service on ORDERS_RAW because it detects data quality problems such as duplicates, missing values, and invalid date sequences during query execution.
Show answer and explanation
Correct answer: A
Explanation
The best answer is to create a dynamic table that derives data quality metrics and flags from ORDERS_RAW. In Snowflake, dynamic tables are well suited for maintaining a continuously refreshed query result based on source data, making them useful for operationalized data quality analysis when you want to preserve the raw ingestion layer. For example, the team can define logic to identify duplicate ORDER_ID values, detect NULL CUSTOMER_ID records, and flag rows where ORDER_DATE occurs after SHIP_DATE. This allows downstream users and monitoring processes to query a current, curated quality-assessment layer.
The other options misuse Snowflake features. External tables expose externally stored data but do not perform cleansing or validation. Secure views help with data protection, not quality enforcement. Search Optimization Service is for query performance acceleration, not issue detection. A common Snowflake best practice is to keep raw data intact and implement validation, profiling, and business-rule checks in derived layers such as views, dynamic tables, or transformation models. This approach supports traceability, repeatability, and easier monitoring of data quality over time.
- A. Correct.
Correct. A dynamic table is a practical Snowflake option for continuously deriving and refreshing a result set from base tables using query logic. In this scenario, it can compute quality indicators such as duplicate detection using ORDER_ID aggregations, NULL checks on CUSTOMER_ID, and rule-based comparisons like ORDER_DATE > SHIP_DATE. This supports ongoing monitoring and analysis without changing the original ingestion process. It aligns with a common best practice of separating raw ingestion from quality assessment logic.
- B. Incorrect.
Incorrect. External tables allow querying data stored externally, but they do not cleanse data, remove duplicates, populate missing values, or validate business rules automatically. This option reflects a common misconception that storage format or table type inherently fixes data quality issues. External tables are about access to externally stored data, not automated data quality remediation.
- C. Incorrect.
Incorrect. Secure views are designed to protect sensitive logic and data exposure, particularly for data sharing and governance scenarios. They do not enforce data quality rules on the underlying data. While a secure view could expose filtered results, it is not itself a mechanism for detecting and managing quality issues for monitoring purposes. The misconception here is confusing security/governance features with data quality controls.
- D. Incorrect.
Incorrect. Search Optimization Service improves performance for selective queries on supported predicates, especially when locating small subsets of data in large tables. It does not identify, validate, or correct data quality issues. Someone might choose this if they associate faster querying with better diagnostics, but it is a performance feature, not a data validation capability.