Approximate distinct counts need visible error bounds

Data Modeling

Approximate distinct counts are acceptable when the decision can tolerate estimation error and the metric is labeled as an estimate. They become dangerous when a dashboard shows an estimated unique count with exact-looking precision, no error bound, and no explanation of what decisions the number can safely support.

Why approximate distinct counts exist

Exact distinct counts are expensive because the system has to determine how many unique values exist after duplicates are removed. For small tables, this is ordinary work. For very large event streams, high-cardinality identifiers, or interactive dashboards over long time windows, exact deduplication can become slow or costly.

Approximate distinct count algorithms solve a practical problem: they trade a controlled amount of accuracy for lower storage, faster aggregation, and easier merging across partitions. Common examples include sketch-based approaches such as HyperLogLog. The important point for metric owners is not the algorithm name. The important point is that the output is an estimate, not an exact count.

A good approximate metric answers a slightly different question from an exact metric. Instead of saying there were exactly 1,240,000 unique users, it says the estimated number of unique users is about 1.24 million, with an expected error range that is acceptable for this decision.

When an approximate count is acceptable

An approximate distinct count is usually acceptable when the metric is used for monitoring, directional comparison, capacity planning, anomaly detection, or product usage analysis where small percentage errors do not change the decision.

It is usually not acceptable when the number is used for billing, contractual reporting, regulatory reporting, financial settlement, customer entitlements, or any process where one record changes the outcome. In those cases, the business meaning of the metric demands exactness, auditability, or reproducibility beyond what an approximate estimate can provide.

The practical test is simple: define the decision tolerance before choosing the counting method. If a 2% or 5% estimation error would not change the decision, approximation may be reasonable. If the decision turns on a narrow threshold, an exact count or a higher-precision process is safer.

What an error bound does and does not mean

An error bound is a statement about the likely size of estimation error under the assumptions of the estimator. It is not a promise that every result is inside the bound. Many approximate distinct count methods describe accuracy using relative standard error. That means the typical error scales with the size of the estimate.

For example, an estimate of 1,000,000 with 2% relative standard error has a standard error of about 20,000. Under a rough normal approximation, a 95% interval is often estimated as about 1.96 standard errors, or about 39,200. That would be communicated as approximately 1,000,000, with a rough 95% interval of about 960,800 to 1,039,200.

The phrase rough 95% interval matters. It tells the reader that the range comes from estimator assumptions, not from a complete verification of every unique identifier. It also prevents the estimate from being mistaken for an exact number with decorative uncertainty.

Calculation rules for communicating estimation error

Use explicit rules so metric owners, analysts, and dashboard readers do not improvise different interpretations of the same estimate.

1. Start with the estimate. Example: estimated unique users equals 1,240,000.
2. Convert relative error to a decimal. Example: 1.5% relative standard error becomes 0.015.
3. Calculate the standard error. Multiply the estimate by the relative standard error: 1,240,000 × 0.015 = 18,600.
4. Choose the communication interval. For a rough 95% interval under normal-style assumptions, multiply by 1.96: 18,600 × 1.96 = 36,456.
5. Build the interval. Lower bound: 1,240,000 - 36,456 = 1,203,544. Upper bound: 1,240,000 + 36,456 = 1,276,456.
6. Round honestly. Do not display 1,240,000 as if every unit is meaningful. A better presentation is: estimated unique users: about 1.24 million, rough 95% range about 1.20 million to 1.28 million.
7. Compare the bound to the decision tolerance. If the business needs accuracy within 10,000 users, this estimate is not sufficient. If the business needs accuracy within 100,000 users, it may be sufficient.

These rules do not make the estimate exact. They make the uncertainty visible enough for the reader to decide whether the number is fit for purpose.

Two numeric counterexamples that expose bad interpretations

Counterexample one: an exact-looking estimate. A dashboard shows 1,020,417 unique visitors. The underlying metric is approximate with about 2% relative standard error. A reader may compare it to yesterday's 1,004,982 and conclude that unique visitors increased by 15,435.

That conclusion is not supported. A 2% relative standard error on roughly 1 million users is about 20,000 for one standard error, and a rough 95% range is close to plus or minus 40,000. The displayed day-to-day difference is smaller than the estimation noise. The dashboard should not invite readers to interpret the last few digits.

Counterexample two: a threshold crossed only by the estimate. Suppose a program triggers manual review when unique accounts exceed 1,250,000. The estimate is 1,240,000 with a rough 95% range of 1,203,544 to 1,276,456. The estimate itself is below the threshold, but the upper range is above it.

The honest interpretation is not we are safely below the threshold. It is the estimate is below the threshold, but the uncertainty range crosses it. If the threshold has operational or contractual consequences, use an exact count or a more precise counting process for that decision.

Do not add distinct estimates across partitions

Distinct counts are not additive in the same way simple row counts are. If Monday has 100,000 unique users and Tuesday has 100,000 unique users, the two-day unique user count is not necessarily 200,000. The same person may appear on both days.

The same caution applies to approximate distinct counts. Do not sum daily approximate uniques to get monthly approximate uniques. That produces a different metric: the sum of daily unique counts. It overstates monthly uniques whenever users appear on multiple days.

If the approximate method supports mergeable sketches, the safer pattern is to merge the sketches for the desired reporting window and then estimate the distinct count from the merged sketch. If only final estimates are stored, the original set information has already been compressed too far for reliable union counting.

Model approximate counts as estimates, not facts

A modeled table should make approximation visible. If a table stores estimated cardinalities, include enough metadata for a downstream analyst to understand the number without reverse engineering the pipeline.

• Metric name: use names such as estimated_unique_users instead of unique_users when the value is approximate.
• Counting method: record the estimator or sketch family when relevant.
• Precision setting: store the configured precision or documented relative standard error.
• Input definition: document the identifier, filters, time boundary, and deduplication grain.
• Estimate timestamp: distinguish when the estimate was produced from the business period it describes.
• Interval fields: consider storing lower_bound and upper_bound when the metric is regularly consumed by dashboards or automated checks.
• Exactness flag: use a field such as is_estimated or count_method so exact and approximate counts are not silently mixed.

The goal is not to clutter every table. The goal is to prevent a downstream model, dashboard, or export from stripping away the uncertainty and turning an estimate into a fake exact value.

Presentation rules for dashboards and metric reviews

The user interface should match the reliability of the number. If the metric has a possible error of tens of thousands, showing the ones digit is misleading even if the database returns an integer-looking value.

Use rounded values, visible labels, and plain-language notes. A useful dashboard label might say Estimated unique users: about 1.24M, rough range 1.20M to 1.28M. A bad label says Unique users: 1,240,000 with no mention of approximation.

For comparisons, do not overstate small differences. If two estimates have overlapping uncertainty ranges, a practical dashboard note can say that the apparent change is within estimation noise. That is not a formal statistical test, but it is a useful guardrail against false precision in operational reporting.

Common failure modes

Approximate distinct counts usually fail socially before they fail technically. The algorithm may be working as designed, while the organization reads the output as exact.

• False precision: displaying too many digits and encouraging exact comparisons.
• Hidden method changes: changing the estimator or precision setting without marking a metric definition change.
• Mixed exact and approximate series: backfilled exact counts appear beside recent approximate counts with no visible distinction.
• Summed distincts: adding daily distinct counts to create weekly or monthly distinct counts.
• Threshold misuse: using an approximate estimate to make a decision that depends on a hard boundary.
• Unclear identifier: counting device IDs, user IDs, emails, or account IDs interchangeably and blaming the estimator for a definition problem.
• Ratio hiding: placing an approximate distinct count inside a conversion rate or retention metric without documenting that the denominator is estimated.

A checklist for deciding whether the estimate is good enough

Before publishing an approximate distinct metric, answer these questions in the metric definition or dashboard documentation.

1. What entity is being counted: users, accounts, devices, sessions, orders, or something else?
2. Is the count exact or approximate?
3. What estimator or sketch method is used?
4. What is the documented relative error or precision setting?
5. What is the expected error range at the scale where the metric is normally used?
6. What decisions will this metric support?
7. What amount of error would change those decisions?
8. Are there thresholds where an exact count is required?
9. Can sketches be merged for larger windows, or are only final estimates stored?
10. How will the dashboard label, rounding, and notes prevent false precision?

If these questions cannot be answered, the metric may still be useful for exploration, but it is not ready to be treated as a trusted operational number.

Key takeaways

• Approximate distinct counts are useful when speed, scale, or storage matter and the decision can tolerate controlled estimation error.
• The estimate should be communicated with its relative error, a practical interval, and rounding that matches the uncertainty.
• Do not present approximate estimates as exact counts, especially near thresholds or in formal reporting.
• Distinct counts are not additive across partitions; merge compatible sketches before estimating larger windows.
• A metric definition should record the counting method, precision, entity being counted, time boundary, and whether the value is estimated.

Next step

Pick one approximate unique-count metric in a dashboard or model. Calculate a rough interval from its documented relative error, compare that interval with the decision the metric supports, and rewrite the label so a reader cannot mistake the estimate for an exact count.