An analyst must report the number of distinct visitor identifiers in a clickstream table that holds many billions of rows. An exact figure is not required, and the team wants the count to return as quickly and cheaply as possible while accepting a small margin of error. Which function should the analyst use to satisfy this requirement?
- ACOUNT(DISTINCT visitor_id), because computing the exact count of unique identifiers is the only supported way to measure cardinality across billions of rows in Snowflake
- BCOUNT(visitor_id) with a DISTINCT clause in the surrounding GROUP BY, because grouping first reduces the rows so the final count is both exact and fast
- CAPPROX_COUNT_DISTINCT(visitor_id), because it estimates the number of distinct values using a sketch and runs far faster and cheaper than an exact distinct count Correct
- DAPPROX_PERCENTILE(visitor_id, 0.5), because the median identifier value is an accepted proxy for how many unique visitors appear in the table
Why A is wrong: An exact COUNT(DISTINCT) must track every unique value, which is memory heavy and slow on billions of rows, so it conflicts with the stated goal of a fast, cheap approximate count.
Why B is wrong: Adding a GROUP BY still forces Snowflake to materialise every distinct group, so it does not avoid the cost of an exact distinct count and remains slow at this scale.
Why C is correct: APPROX_COUNT_DISTINCT uses a HyperLogLog sketch to estimate cardinality with low memory in a single pass, returning a close approximate count quickly, which matches the requirement exactly.
Why D is wrong: APPROX_PERCENTILE estimates a percentile of a numeric distribution, not the number of distinct values, so its result has nothing to do with visitor cardinality.