Databricks Certified Data Analyst Associate free question 5 / 10 · Domain 4: Executing queries using Databricks SQL and Databricks SQL Warehouses
A dashboard shows daily unique visitors over several billion page views. The analyst considers replacing count(DISTINCT visitor_id) with approx_count_distinct(visitor_id). What does approx_count_distinct return, according to its reference page?
- A range of possible distinct counts, as a lower and an upper bound.
- The exact distinct count of visitor_id, from a sample of page views.
- An estimated number of distinct values of visitor_id in each group.
- The exact distinct count, cached from the last run of the dashboard.
Show the answer and explanation
Answer: C
Why this is correct
The approx_count_distinct page says the function returns the estimated number of distinct values in expr within the group, and that it returns a BIGINT. Its accuracy is governed by the optional relativeSD argument, which defines the maximum relative standard deviation allowed. For a visitor count over billions of rows, where a small error is acceptable, it gives one estimated number per group instead of an exact distinct count.
Why the other options are wrong
"The exact distinct count of visitor_id, from a sample of page views." … The reference describes the result as an estimate, not as an exact figure. Sampling is not how the page describes the function, and an exact count from a sample would be a contradiction in terms.
"The exact distinct count, cached from the last run of the dashboard." … The result is the estimated number of distinct values in expr within the group, so it is computed from the rows of the group when the query runs, and it is defined as an estimate rather than an exact count.
"A range of possible distinct counts, as a lower and an upper bound." … The function returns a single BIGINT, not a pair of bounds. The precision is set through the relativeSD argument, which defines the maximum relative standard deviation allowed.
Using it in practice
approx_count_distinct can also be called as a window function with OVER. Keep the exact count(DISTINCT ...) for figures that must reconcile with other systems, such as billing, and use the estimate where trends matter more than exact values.