DAA-C01 Question 148
Single answerAggregate and validate the dataA retail analytics team loads point-of-sale transactions into a Snowflake table named SALES_RAW. Analysts need a daily store-level summary table that shows total sales amount and transaction count. Before publishing the summary, they must validate that each store/day aggregate is based only on valid transactions: rows with a non-NULL STORE_ID, a non-NULL TXN_TS, and a non-negative AMOUNT. They also want a quick validation query that highlights any mismatch between the raw valid transaction counts and the published aggregate table. Which approach best meets these requirements?
- A
Create the summary with a GROUP BY on STORE_ID and CAST(TXN_TS AS DATE), filtering valid rows in the WHERE clause, then compare it to a validation query that independently aggregates the same filtered raw data and joins on store and date to find count mismatches.
- B
Create the summary by grouping all rows in SALES_RAW, then use QUALIFY after aggregation to remove groups that contain invalid rows, because QUALIFY is designed to validate aggregated data quality conditions.
- C
Create the summary with APPROX_COUNT_DISTINCT for transaction count and SUM(AMOUNT), then validate by comparing the number of rows in the summary table to the number of rows in SALES_RAW for the same date range.
- D
Create the summary using a window function COUNT(*) OVER (PARTITION BY STORE_ID, CAST(TXN_TS AS DATE)) and store all detailed rows, then validate by checking whether any duplicate windowed counts exist within a partition.
Show answer and explanation
Correct answer: A
Explanation
The key requirement is to aggregate only valid transactions, so validation rules must be enforced before computing summary metrics. In Snowflake, this is typically done with a WHERE clause that excludes rows failing business rules, followed by GROUP BY at the target grain. For example, grouping by STORE_ID and CAST(TXN_TS AS DATE) and computing SUM(AMOUNT) and COUNT(*) produces the daily store-level summary from valid rows only. To validate the published aggregate table, a best practice is to independently recompute expected aggregates from the source using the same validity criteria and compare results by key using joins or set operators. This is more reliable than comparing row counts across tables at different grains. Snowflake documentation and SQL best practices support using WHERE for row filtering before aggregation, GROUP BY for summarization, and QUALIFY primarily for filtering window function results rather than replacing pre-aggregation data quality filtering.
- A. Correct.
Correct. This approach applies row-level validity rules before aggregation, which is the right pattern when the published metric must exclude invalid transactions. GROUP BY on STORE_ID and the transaction date produces the required store/day grain, and an independent validation query against the filtered raw data is a practical way to reconcile counts with the published aggregate table. Joining the two result sets on store and date and checking for mismatches is a common data validation technique.
- B. Incorrect.
Incorrect. QUALIFY filters the results of window functions, not standard grouped aggregates in the way described here. More importantly, grouping all rows first would allow invalid rows to influence totals and counts before any filtering logic is applied. Data quality filters for invalid transactions should be applied before aggregation when invalid rows must be excluded from summary metrics.
- C. Incorrect.
Incorrect. APPROX_COUNT_DISTINCT is not appropriate for transaction count when an exact count of valid transactions is required. Also, comparing only the number of rows in the summary table to the number of rows in SALES_RAW does not validate aggregate correctness; those row counts are at different grains and are not expected to match.
- D. Incorrect.
Incorrect. A window function can calculate per-partition counts while retaining detail rows, but that does not create the required summary table at the store/day grain unless an additional aggregation or deduplication step is performed. Checking for duplicate windowed counts within a partition is also not a meaningful validation of whether invalid rows were excluded or whether aggregate counts are correct.