DAA-C01 Question 147
Single answerAggregate and validate the dataA retail analytics team loads point-of-sale transactions into a Snowflake fact table named SALES_FACT with columns ORDER_ID, STORE_ID, SALE_TS, AMOUNT, and LOAD_BATCH_ID. Analysts produce a daily revenue dashboard by aggregating AMOUNT by store and calendar date. Recently, a pipeline issue caused some files to be loaded twice, and leadership noticed inflated daily totals. The team wants a SQL-based validation approach in Snowflake that both identifies duplicate-driven inflation and compares raw row-level totals with business-level aggregates before publishing the dashboard. Which approach best meets this requirement?
- A
Create a validation query that groups by STORE_ID, CAST(SALE_TS AS DATE), and ORDER_ID to detect repeated business keys, then compare SUM(AMOUNT) from the raw table to SUM of a deduplicated result set using QUALIFY ROW_NUMBER() OVER (PARTITION BY ORDER_ID ORDER BY LOAD_BATCH_ID) = 1 before publishing
- B
Use APPROX_COUNT_DISTINCT(ORDER_ID) by day and store as the dashboard source because approximate distinct counts eliminate duplicate rows while improving performance
- C
Cluster the SALES_FACT table by STORE_ID and SALE_TS, then assume duplicate inflation is resolved because clustering reorganizes the table for accurate aggregation
- D
Create a materialized view that aggregates SUM(AMOUNT) by STORE_ID and date; materialized views automatically remove duplicate source records during refresh
Show answer and explanation
Correct answer: A
Explanation
The best answer is to validate at both the row level and the aggregate level using SQL. In Snowflake, duplicate loads are typically addressed through business-key validation and deduplication logic rather than relying on storage optimizations or approximate functions. A common pattern is to identify duplicates with GROUP BY on the expected business grain, then use window functions such as ROW_NUMBER() with QUALIFY to isolate the surviving row per key. After that, compare raw aggregate totals versus deduplicated totals to quantify inflation before downstream publication. This aligns with Snowflake best practices for data quality validation using standard SQL constructs, especially GROUP BY, window functions, and QUALIFY. Relevant Snowflake documentation includes topics on GROUP BY aggregation, window functions, QUALIFY filtering, and data quality checks implemented in SQL rather than through physical design features like clustering or through approximate aggregate functions.
- A. Correct.
Correct. This approach directly validates the business grain and the aggregate output. Grouping by STORE_ID, date, and ORDER_ID helps identify duplicate business events contributing to inflated totals. Using a deduplication pattern such as ROW_NUMBER with QUALIFY lets the team retain one record per business key and compare raw totals against deduplicated totals before publishing. This is a practical Snowflake SQL pattern for validating aggregated metrics after accidental duplicate loads.
- B. Incorrect.
Incorrect. APPROX_COUNT_DISTINCT estimates the number of unique ORDER_ID values, but it does not deduplicate rows for SUM(AMOUNT). It is useful for cardinality estimation, not for producing financially accurate revenue totals. Choosing this option reflects a misconception that distinct-count functions can correct duplicated measure values in aggregate calculations.
- C. Incorrect.
Incorrect. Clustering can improve pruning and query performance, but it does not change the logical contents of the table and does not remove duplicates. Aggregations over duplicated rows would still be inflated. This distractor targets the common misunderstanding that physical optimization features also enforce data quality.
- D. Incorrect.
Incorrect. A materialized view can precompute aggregates for performance, but it does not automatically detect or remove duplicate source records unless the view definition explicitly handles deduplication. If the source table contains duplicated transactions, the materialized view's SUM(AMOUNT) would also be inflated.