DAA-C01 Question 162
Single answerUse sampling, approximation, and estimation featuresA product analytics team stores clickstream data in a Snowflake table that receives billions of new rows per day. An executive dashboard needs to show the number of distinct users who visited each product category in the last 24 hours. The business accepts a small error rate, but the query must finish much faster than an exact DISTINCT count during peak reporting times. Which approach should the data analyst use to best meet these requirements?
- A
Use APPROX_COUNT_DISTINCT(user_id) grouped by product_category over the last 24 hours
- B
Use COUNT(DISTINCT user_id) grouped by product_category, but add LIMIT 1000 to reduce processing time
- C
Use TABLESAMPLE on the clickstream table, calculate COUNT(DISTINCT user_id) on the sample, and treat that as the dashboard value
- D
Create a materialized view with SELECT DISTINCT user_id, product_category for the last 24 hours and query that view
Show answer and explanation
Correct answer: A
Explanation
The best answer is to use APPROX_COUNT_DISTINCT(user_id) grouped by product_category. In Snowflake, approximation functions are intended for scenarios where speed and scalability are more important than exact precision. DISTINCT counts on very large event tables are a classic example. APPROX_COUNT_DISTINCT provides an efficient estimate of cardinality and is preferable to ad hoc sampling for production KPI reporting because it is a built-in estimation function designed for this purpose. By contrast, COUNT(DISTINCT ...) remains exact and expensive, and adding LIMIT does not reduce the aggregation cost. TABLESAMPLE is valuable for quickly exploring large datasets, but sampled distinct counts are not a substitute for a supported approximate aggregation unless the analyst separately designs and validates an estimation approach. This aligns with Snowflake SQL function guidance on cardinality estimation and best practices for using approximation features when small error is acceptable in exchange for performance.
- A. Correct.
Correct. APPROX_COUNT_DISTINCT is designed for fast, approximate cardinality estimation at scale and is a strong fit when the business accepts a small error rate. In this scenario, the dashboard needs distinct user counts by category on massive, continuously growing data, so using Snowflake's approximation function directly addresses the performance-versus-accuracy tradeoff.
- B. Incorrect.
Incorrect. LIMIT is applied after aggregation and does not reduce the amount of data that must be scanned and processed to compute COUNT(DISTINCT user_id) for each product_category. This is a common misconception: LIMIT can reduce returned rows, but it does not make an expensive DISTINCT aggregation cheaper in this case.
- C. Incorrect.
Incorrect. TABLESAMPLE can be useful for exploratory analysis, but using a raw sample and presenting the resulting COUNT(DISTINCT) as the production dashboard value is unreliable unless the analyst explicitly accepts and manages sampling bias and estimation methodology. Snowflake provides APPROX_COUNT_DISTINCT specifically for approximate distinct-count use cases, making it the better and more defensible option here.
- D. Incorrect.
Incorrect. A materialized view may improve some repeated query patterns, but this option is not the best fit as stated. A view limited to the last 24 hours is problematic because materialized views have restrictions and are not intended to solve this approximate-count requirement directly. More importantly, the requirement explicitly allows approximation for faster execution, and APPROX_COUNT_DISTINCT is the purpose-built feature for that need.