DAA-C01 Question 161
Single answerUse sampling, approximation, and estimation featuresA retail analytics team stores clickstream events in a very large Snowflake table named EVENTS with columns including USER_ID, SESSION_ID, EVENT_TS, and DEVICE_TYPE. Product managers need a dashboard tile that shows the approximate number of distinct users who visited during the last 7 days, and they want the query to return quickly even as data volume grows. The team can tolerate a small estimation error, but they do not want a solution that depends on reading only a subset of rows because user activity is heavily skewed by device type and time of day. Which approach is the most appropriate?
- A
Use APPROX_COUNT_DISTINCT(USER_ID) over the filtered 7-day dataset to return an estimated distinct user count without relying on row sampling.
- B
Use COUNT(DISTINCT USER_ID) on a TABLESAMPLE of 10% of EVENTS for the last 7 days, then multiply the result by 10 to estimate the total distinct users.
- C
Use SAMPLE BERNOULLI (10) on the last 7 days of EVENTS and run COUNT(USER_ID), because simple row counts scale linearly to distinct user counts.
- D
Use NTILE(10) to divide the last 7 days of EVENTS into groups and count distinct users in one tile, because each tile represents 10% of the total users.
Show answer and explanation
Correct answer: A
Explanation
The best choice is to use Snowflake's approximation capability directly: APPROX_COUNT_DISTINCT(USER_ID) on the filtered 7-day dataset. For dashboards and large-scale analytics, this is a practical pattern when the business accepts a small estimation error in exchange for better performance and scalability. Snowflake documents APPROX_COUNT_DISTINCT as an approximate distinct-count function based on probabilistic estimation, making it more suitable than naive sampling for cardinality problems. By contrast, TABLESAMPLE and SAMPLE return subsets of rows and are useful for data exploration or reducing scan volume in some cases, but they do not provide a mathematically reliable way to estimate distinct counts by simple scaling. This distinction is important for exam scenarios: use approximation functions for approximate cardinality, and use sampling only when the analytical goal tolerates subset-based analysis rather than inferred full-population distinct estimates.
- A. Correct.
Correct. APPROX_COUNT_DISTINCT is designed for fast, approximate cardinality estimation and is appropriate when a small error is acceptable. It avoids the flawed assumption that a subset of rows can be scaled up to estimate distinct users accurately. This is especially important in skewed datasets, where sampling can undercount or overcount unique users depending on behavior patterns.
- B. Incorrect.
Incorrect. TABLESAMPLE can be useful for exploratory analysis, but distinct counts do not scale linearly from a sample. Multiplying a sampled COUNT(DISTINCT USER_ID) by 10 is statistically unsound because the relationship between sampled distinct values and full-population distinct values is not proportional, particularly when user activity is unevenly distributed.
- C. Incorrect.
Incorrect. COUNT(USER_ID) returns the number of rows, not the number of unique users. Even if sampling is applied correctly, row counts cannot be converted into distinct user counts in a reliable way. This option reflects a common misconception that counting events is a valid proxy for counting unique users.
- D. Incorrect.
Incorrect. NTILE is a window function used to assign rows into ranked buckets after ordering; it is not a sampling or estimation feature. Counting distinct users from a single NTILE bucket does not produce a valid estimate for the total population, especially when row ordering and user repetition influence bucket composition.