DAA-C01 Question 17
Single answerQuery tables in Snowflake to assess:A data analyst needs to quickly assess the quality of a newly loaded SALES_TRANSACTIONS table in Snowflake before publishing a dashboard. The analyst wants to identify whether key columns contain missing values, whether there are unexpected duplicate transaction IDs, and whether any rows have transaction dates in the future. Which query is the most appropriate to perform this assessment in a single pass over the table?
- A
SELECT COUNT(*) AS total_rows, COUNT(TRANSACTION_ID) AS non_null_transaction_id, COUNT(CUSTOMER_ID) AS non_null_customer_id, COUNT(TRANSACTION_DATE) AS non_null_transaction_date, COUNT(DISTINCT TRANSACTION_ID) AS distinct_transaction_ids, COUNT_IF(TRANSACTION_DATE > CURRENT_DATE()) AS future_dated_rows FROM SALES_TRANSACTIONS;
- B
SELECT * FROM SALES_TRANSACTIONS WHERE TRANSACTION_ID IS NULL OR CUSTOMER_ID IS NULL OR TRANSACTION_DATE > CURRENT_DATE() GROUP BY TRANSACTION_ID;
- C
SELECT SYSTEM$CLUSTERING_INFORMATION('SALES_TRANSACTIONS');
- D
SELECT MIN(TRANSACTION_DATE), MAX(TRANSACTION_DATE), AVG(AMOUNT) FROM SALES_TRANSACTIONS;
Show answer and explanation
Correct answer: A
Explanation
To assess a table in Snowflake for common data quality issues, analysts often use aggregate profiling queries. COUNT(*) returns total row count, while COUNT(column) counts only non-NULL values, allowing nulls to be inferred. COUNT(DISTINCT key_column) is a standard way to check whether a supposed unique identifier contains duplicates when compared against row counts. COUNT_IF(condition) is a Snowflake-supported aggregate function that efficiently counts rows matching a predicate, such as dates greater than CURRENT_DATE(). This approach aligns with practical Snowflake data assessment patterns and avoids unnecessary row-by-row inspection when a summary is needed. Snowflake documentation for aggregate functions, including COUNT, COUNT DISTINCT, and COUNT_IF, supports this type of quality validation workflow.
- A. Correct.
Correct. This query uses aggregate functions to assess multiple quality dimensions efficiently in one scan: COUNT(*) gives total rows, COUNT(column) helps infer null counts by comparing with total rows, COUNT(DISTINCT TRANSACTION_ID) helps detect possible duplicates when compared with total rows or non-null transaction IDs, and COUNT_IF(TRANSACTION_DATE > CURRENT_DATE()) identifies future-dated records. This is a practical Snowflake pattern for table assessment.
- B. Incorrect.
Incorrect. This query mixes SELECT * with GROUP BY TRANSACTION_ID, which is not valid unless all selected non-aggregated columns are grouped. Even if corrected, it would only return problematic rows rather than provide a concise assessment summary. It also does not quantify duplicates reliably across the full table.
- C. Incorrect.
Incorrect. SYSTEM$CLUSTERING_INFORMATION returns metadata about clustering depth and micro-partition organization, which is useful for performance tuning, not for assessing missing values, duplicates, or future dates in business data.
- D. Incorrect.
Incorrect. MIN, MAX, and AVG can help profile date and amount ranges, but this query does not assess null completeness, duplicate transaction IDs, or explicitly count future-dated rows. It provides only partial profiling, not the requested quality assessment.