COUNT(DISTINCT) where an approximation would do
Exact distinct counts require a full shuffle. APPROX_COUNT_DISTINCT is typically within 1% and dramatically cheaper in slot time.
What it is
COUNT(DISTINCT x) must gather every distinct value in one place to guarantee exactness. APPROX_COUNT_DISTINCT uses a sketch that merges across workers.
Why it costs money
- Bytes billed are unchanged — this is purely a slot-time cost, so it matters under Editions and in query latency.
- On high-cardinality columns the shuffle can dominate the entire query.
- The exactness is usually not needed. Nobody makes a different decision because the number was 1,284,301 rather than 1,284,000.
Worked example
Expensive
SELECT event_date, COUNT(DISTINCT user_id) AS users
FROM `analytics.events`
GROUP BY event_dateCheaper
SELECT event_date, APPROX_COUNT_DISTINCT(user_id) AS users
FROM `analytics.events`
GROUP BY event_dateThe arithmetic. Same bytes scanned. On a table with 200M distinct users the approximate form typically runs several times faster and within roughly 1% of the exact answer.
How to fix it
- 1Use APPROX_COUNT_DISTINCT for dashboards, trends, and anything monitored rather than audited.
- 2Keep exact counts for billing, compliance, and reconciliation.
- 3HLL_COUNT functions let you build re-mergeable sketches when the same distinct count is needed at several grains.
The analyzer detects this one. Paste your query into the BigQuery Query Cost Analyzer and it will point at the exact line.
Fixing one query is satisfying. Fixing the pattern is the win.
Finitizer finds every instance of this across your BigQuery job history, ranks them by what they actually cost, and keeps checking after you have fixed them.