DAA-C01 Question 18
Single answerQuery tables in Snowflake to assess:A retail analytics team stores daily sales data in a Snowflake table named SALES_FACT with the columns ORDER_DATE, STORE_ID, PRODUCT_ID, QUANTITY, and REVENUE. Analysts suspect that some ETL loads are introducing duplicate business records for the same ORDER_DATE, STORE_ID, and PRODUCT_ID combination. They need a query that assesses the table for duplicate business keys and shows only the duplicate key combinations with the number of occurrences. Which query best meets this requirement?
- A
SELECT ORDER_DATE, STORE_ID, PRODUCT_ID, COUNT() AS OCCURRENCES FROM SALES_FACT GROUP BY ORDER_DATE, STORE_ID, PRODUCT_ID HAVING COUNT() > 1;
- B
SELECT DISTINCT ORDER_DATE, STORE_ID, PRODUCT_ID, COUNT() AS OCCURRENCES FROM SALES_FACT WHERE COUNT() > 1 GROUP BY ORDER_DATE, STORE_ID, PRODUCT_ID;
- C
SELECT ORDER_DATE, STORE_ID, PRODUCT_ID, ROW_NUMBER() OVER (PARTITION BY ORDER_DATE, STORE_ID, PRODUCT_ID ORDER BY ORDER_DATE) AS OCCURRENCES FROM SALES_FACT WHERE OCCURRENCES > 1;
- D
SELECT ORDER_DATE, STORE_ID, PRODUCT_ID, COUNT(UNIQUE *) AS OCCURRENCES FROM SALES_FACT GROUP BY ALL HAVING OCCURRENCES > 1;
Show answer and explanation
Correct answer: A
Explanation
To assess a Snowflake table for duplicate business keys, the appropriate approach is to aggregate by the columns that define uniqueness for the business process and then filter aggregated groups with HAVING COUNT(*) > 1. In this scenario, ORDER_DATE, STORE_ID, and PRODUCT_ID define the suspected business key, so grouping on those columns reveals whether multiple records exist for the same key combination. Snowflake follows standard SQL behavior here: WHERE filters rows before aggregation, while HAVING filters groups after aggregation. Window functions such as ROW_NUMBER() are useful when you need to identify or remove specific duplicate rows, but they are not the simplest choice when the requirement is to assess duplicate key combinations and report occurrence counts. This aligns with Snowflake SQL best practices for aggregation, GROUP BY, HAVING, and duplicate analysis.
- A. Correct.
Correct. This query uses GROUP BY on the business key columns and COUNT(*) to measure how many rows exist for each key combination. The HAVING clause correctly filters aggregated results so that only combinations appearing more than once are returned. This is the standard SQL pattern in Snowflake for assessing duplicate keys in a table.
- B. Incorrect.
Incorrect. Although DISTINCT and GROUP BY are both used for deduplication-related tasks, this query is invalid because COUNT(*) cannot be used in the WHERE clause; aggregate filters must be placed in HAVING after grouping. Adding DISTINCT is also unnecessary because GROUP BY already produces one row per key combination.
- C. Incorrect.
Incorrect. ROW_NUMBER() can be useful to identify individual duplicate rows, but this query is not valid as written because the alias OCCURRENCES from the SELECT list cannot be referenced directly in the WHERE clause at the same query level. Also, ROW_NUMBER() assigns a sequence number per row, not the total number of occurrences for the duplicate key combination, so it does not directly satisfy the requirement to show the count of duplicates per key.
- D. Incorrect.
Incorrect. Snowflake supports COUNT(DISTINCT expr), but COUNT(UNIQUE *) is not valid syntax. In addition, GROUP BY ALL is not the appropriate pattern here for clearly assessing duplicate business keys, and HAVING OCCURRENCES > 1 depends on an invalid aggregate expression. This option reflects a common misconception that there is a shorthand aggregate for counting unique rows across all columns.