How do you decide which warehouse metrics may use approximate distinct counts and which may not?

## The decision belongs to the consumer, not the query Engineers tend to make this call at the point of pain: a dashboard query is slow, so someone swaps `COUNT(DISTINCT)` for the approximate function and the dashboard gets fast. That is the wrong place to decide, because the person editing the query rarely knows what the number is eventually used for. Six months later the same figure is feeding an invoice. The framing that survives contact with a real organisation is: **classify the metric by consequence, then implement accordingly.** ## Where exactness is non-negotiable **Money.** Anything that determines what a customer is charged or what a partner is paid — billed seats, billed unique devices, unique events under a usage plan, revenue-share counts. A 0.8% error on a large invoice is a real sum of money and an indefensible one, because there is no story that makes "we estimated your bill" acceptable. **Regulatory and audit reporting.** Filed figures must be reproducible on demand and defensible line by line. An estimate whose value depends on how the cluster happened to parallelise the query is neither. **Anything reconciled against a source of truth.** If the warehouse figure is compared with an operational system's figure, an approximation guarantees a permanent unexplained delta and burns analyst time forever. **Hard thresholds.** A contractual minimum, an SLA breach, a compliance limit — if the decision boundary sits inside the error band, the estimate can flip the decision. Either compute exactly or restructure the rule so the error cannot change the outcome. **Row-level or drill-through requirements.** A sketch answers how many, never which. If the consumer needs the list — for outreach, for investigation, for a cohort — the sketch cannot serve them and an exact path must exist. **Uniqueness and deduplication checks.** A data-quality test asserting that a key is unique is meaningless against an estimate. ## Where approximation is clearly correct Exploratory analysis, data profiling, trend charts, funnels, monitoring panels, capacity signals, A/B dashboards where the effect being measured is far larger than the error, and any cut where the analyst is looking at shape rather than magnitude. In these cases the alternative is not a more accurate number, it is a slower dashboard or no dashboard, and the accuracy nobody uses is not worth the cost. ## Make the choice visible in the metric layer The durable implementation is a property of the metric definition, not of any one query. Each metric in the semantic or metric layer carries a flag: exact or approximate, and if approximate, the error characteristics. Downstream tools show that alongside the number. This solves the two failure modes that actually bite organisations. The first is **cross-tool disagreement**: an approximate figure in one dashboard next to an exact one in another, differing by a percent, generating a support ticket every quarter. The second is **silent implementation drift**: a metric computed exactly for a year, switched to approximate during a cost-reduction push, showing a step change that a downstream team investigates as a pipeline incident. Both are process failures, not technical ones, and both are prevented by declaring and versioning the choice. ## A hybrid that works A pattern worth knowing: serve the fast approximate metric to interactive consumers, and run an exact **certification** job on a slower cadence — nightly or monthly — over the same definition. The certified number is what gets reconciled, filed and billed; the approximate one is what the dashboard shows intraday. Publish both under distinct names so nobody confuses them, and track the observed gap between them over time; a widening gap is a genuine signal that the sketch's precision no longer suits the data's cardinality. ## Consistency traps to warn people about Estimates carry independent error, so they do not respect the relationships people expect of exact numbers. A filtered subset, estimated separately, can come back larger than its parent. Segments that must add to a whole will not add exactly. A month-over-month change smaller than the error band is noise, not a movement. None of these are bugs, but every one of them looks like one to a consumer who was not told the number was approximate — which is why the declaration matters more than the choice. ## Measure before you approximate Finally, do not pay for approximation you do not need. Exact distinct counting is expensive only when the counted column's **cardinality** is large enough to make the deduplication state heavy and the redistribution wide. A distinct count over a few thousand product categories is cheap exactly, regardless of how many billion rows are scanned. Profile the actual cost of the exact version first; if it is acceptable, keep the guarantee. Reserve approximation for the cases where exactness genuinely costs something a business would notice.