Asked for different customers this week, an analytics system may sum seven daily counts using perfectly valid SQL—and answer a different question: customer-days. Aggregation has discarded overlap information. This article defines distinct-count contracts and the boundaries of approximate acceleration. Examples are constructed; no production database or HLL performance experiment was run.
Define the objects and population before naming the count
The numerator and denominator contracts for conversion rates require an eligible population and observation scope. Distinct counts need the same foundations. Users, accounts, devices and business customers are different objects. Deduplicating device identifiers and calling the result people—or merging same-named customers across systems—can yield an exact number with the wrong meaning.
Freeze the object key, namespace, identity-mapping version, qualifying event, interval, business time zone, data cutoff and permission scope. For example: different confirmed business customer IDs with at least one valid enquiry in a specified calendar week. Ten enquiries from one customer still contribute one customer; unidentified records are reported separately.
Local identifiers can repeat across tenants. Customer 001 in each of two tenants requires namespace-aware identity if they represent different customers. A group-wide customer measure instead needs a controlled common mapping. Simple string concatenation can also introduce ambiguous boundaries; use typed, unambiguous composite-key encoding.
Do not replace missing IDs with one “unknown customer” in a certified count, collapsing all unidentified objects into one. Nor should exclusion be silent. Return identified distinct customers, records with missing identity and coverage status together. The metric contract determines whether a claim about the entire population is permitted.
Six rows distinguish events, customer-days and distinct customers
Construct six valid enquiries: on day one, search/customer A, search/customer B and event-channel/customer A; on day two, search/customer B, event-channel/customer C and another event-channel/customer C. There are six events. Day one's customers are A and B; day two's are B and C. Both daily distinct counts are two.
The two-day customer set is A, B and C, so the total is three rather than the daily sum of four. Four is a meaningful customer-day measure because B contributes on both dates. Search has customers A and B; the event channel has A and C. Both channel counts are two, but their overall union still has three customers.
At the finer date-by-channel grain, counts are 2, 1, 1 and 1, summing to five. This is neither six events, four customer-days nor three different customers. A changing total under a different grouping exposes discarded overlap information, not a suddenly unreliable COUNT function.
Knowing only that both days contain two customers cannot recover the two-day count. It is two if the sets are identical, four if disjoint and three in this example. For two sets, union size is the sum of their sizes minus intersection size. An unknown intersection must not be replaced with an LLM-invented overlap rate in a certified metric.
Choose exact object sets or mergeable approximate state
The exact path deduplicates object keys within the requested filters and then counts them. In the fixture, retain the six eligible rows and select distinct customer keys, yielding three rows. Daily deduplicated object records can reduce repeated events, but a weekly rollup must still deduplicate across days. Only genuinely disjoint groups permit direct addition of their counts.
For large populations and frequent queries where approximation is acceptable, store HLL or HLL++ sketches. A sketch retains compressed statistical state for estimating cardinality, not a customer list. BigQuery separates initialization, merging and estimate extraction, and notes that the final APPROX_COUNT_DISTINCT number does not offer the same partial aggregation and re-aggregation path.[1]
Trino documents storing daily HyperLogLog state and merging it for a weekly estimate.[3] Merge sketches first and extract one estimate afterward; do not extract seven daily estimates and sum them. Even extremely accurate daily estimates cannot remove cross-day duplicates through addition.
Exact counting suits object-list delivery, reconciliation and decisions that cannot tolerate approximation. Sketches suit explicitly labeled exploration and large repeated rollups. Size alone does not justify approximation, and an estimate that happens to match a small fixture does not establish exactness. Compare cost and error under the intended workload.
Partitions and versions determine the questions a sketch can answer
Store the metric and identity versions, time partition, grouping dimensions, source cutoff, completeness status, engine and serialization version, input type and precision configuration with each sketch. Readable binary state does not establish semantic or format compatibility. Two systems using the name HLL need not share compatible bytes, hashing or parameters.
BigQuery requires merged sketches to have the same underlying input type; mixing precisions downgrades the merge to the lowest participating precision.[1] Lock configuration at construction and validate a manifest at query time, rejecting unapproved mixtures. Whether string 001 and integer 1 identify the same object must be decided before sketch construction.
Partition design limits future queries. A sketch containing all customers for each day cannot later reveal only one product line's customers. That requires source detail or state already partitioned by a suitable dimension. More dimension combinations mean more storage and maintenance, so let actual query demand determine the design rather than materializing every combination.
Authorization must precede merging. A sketch already containing every department cannot become a permitted subset merely by hiding department labels. With frequently changing row permissions, prefer detail that can be filtered under current authorization or rigorously validated permission partitions. Compression is not access isolation.
Append late data, but never repair deletions by subtracting one
Another event from an existing customer should not increase set cardinality; an event from a new customer may. Compatible set sketches represent unions through merging, but completeness still depends on actual upstream delivery. Track readiness for every expected date and source partition. A missing partition is not an empty set.
BigQuery explicitly states that values cannot be deleted from an HLL++ sketch.[2] A revoked event, corrected attribution or changed identity mapping therefore cannot be repaired by subtracting one from the old estimate. The customer may have other eligible events or appear on other dates, while the old sketch still contains the earlier contribution.
Retain controlled source detail sufficient for reconstruction. Rebuild affected date and dimension partitions, then replace dependent weekly or monthly results. Activate a new complete version atomically or through an equivalent complete-switch mechanism. Merging a corrected partition into a cumulative sketch that already contains the old partition does not remove the old contribution; rebuild from the correct partition set.
For a rolling seven-day window, select and merge its seven valid daily sketches rather than subtracting the oldest day's estimate from a lifetime sketch. If source detail has expired and reconstruction is impossible, disclose the affected repair boundary. Do not silently retain known errors to keep a chart continuous.
Test set semantics, completeness and statistical error separately
First, test semantics with the six-row fixture: six events, four customer-days, three overall customers and five as the sum of four date-channel cells. Adding any duplicate event leaves the overall customer count at three. Splitting the same input into different batches and recombining must preserve the intended population. The exact path must return the specified expected values.
Second, test completeness and repair. Omit day two deliberately: the system should report incomplete data, not present day one's two customers as the two-day total. Revoke both customer C events on day two; the exact overall count becomes two. Exercise sketch rebuilds, retirement of old versions and refreshed weekly output. Revoking only one C event leaves three customers—a counterexample to subtract-one logic.
Only then evaluate approximation against exact counts under the same identity, eligibility, filters and cutoff. Stratify by low, medium and high cardinality, duplication, dates and channels. Record absolute errors and, when the exact count is positive, relative errors; test empty sets separately. A single average can hide positive and negative group errors that cancel.
Trino documents a default standard error of 2.3% for approx_distinct, explicitly excluding a guaranteed upper error bound for each individual set.[4] Do not promise every query will fall within 2.3%. Re-running a fixed set generally does not create independent statistical samples. Evaluate multiple representative sets and the actual deployed engine version.
Expose approximation to the answer layer and retain exact fallback
An approximate result may suffice for broad customer-scale trends. A strict eligibility cutoff needs exact evaluation near the decision boundary. Set the error budget from the use case, not an algorithm default. The language model must not turn “approximately ten thousand customers” into a claim precise to the last person.
Return metric version, object definition, time scope, cutoff, exact-or-approximate status, engine and precision, completeness status and appropriate error information. Estimation uncertainty and missing data are different problems: the latter cannot be absorbed into the former's error interval. Undelivered data must not be labeled a complete approximate total.
Compare query resources and latency alongside sketch construction, rebuild costs and the rate of exact fallbacks. Frequent identity-rule changes can erase recurring query savings through reconstruction work. Infrequent analysis may be simpler with exact computation; stable high-frequency measures are stronger candidates for materialized sketches.
Deliver the object contract, replayable fixtures, compatibility manifest, partition-readiness evidence, deletion-rebuild exercise and stratified error report. The six-row example explains set arithmetic, not measured HLL accuracy in a product. Teaching an analytics system when to return to object sets matters more than making it add a column of counts faster.