Key takeaway
Record the counting expression and input scope. An integer output does not establish that every distinct identity was counted exactly.
Ask how the number was produced
A dashboard reports a whole number of unique jobs. A file delivery contains a different whole number, and the discrepancy is treated as a missing-record problem. Before tracing individual jobs, inspect the dashboard expression. A function that estimates distinct identities answers a different question from an exact distinct count. Both can return integers and look identical in a presentation.
BigQuery documents APPROX_COUNT_DISTINCT as a statistical estimate of COUNT(DISTINCT expression). Its result need not equal the actual distinct count. BigQuery also documents an INT64 return type for that estimator. The storage type therefore cannot resolve the acceptance question. The receiving agreement needs the operation, population and intended use alongside the reported value.
Work through five invented rows
Use this hypothetical input: A, A, B, C and one null job identifier. There are five rows, four non-null identifier occurrences and three distinct non-null identifiers. Those are three useful but different quantities. The table completes the exact side of the reconciliation; it deliberately supplies no invented output for an approximate query.
Run the intended engine on an approved synthetic fixture and retain its actual output separately. An approximate result that happens to equal three on this tiny input does not become a generally exact algorithm. Nor would a disagreement establish which production records were absent. It establishes the need to compare like definitions before deciding whether the transfer failed.
| Operation on the fictional input | Expected interpretation | Value or decision |
|---|---|---|
| COUNT(*) | All supplied rows | 5 |
| COUNT(job_id) | Non-null identifier occurrences | 4 |
| COUNT(DISTINCT job_id) | Distinct non-null identifiers | 3 |
| APPROX_COUNT_DISTINCT(job_id) | Estimated distinct identifiers | Record actual run; do not invent it |
Keep two acceptance paths
For a package-completeness check, state which identities must be present and use a suitable exact reconciliation within the approved environment. Record filters, grouping, null handling and identity comparison rules. An exact count alone still cannot prove that the correct identities arrived: two different sets can have the same size. Where identity-level reconciliation is permitted, retain the set-difference finding as a separate result.
For a planning dashboard, an estimate may be an appropriate deliberate tradeoff. Label it as an estimate and document why the decision tolerates approximation. If the use requires an error bound, obtain the actual algorithm and applicable evidence for that implementation; do not attach a familiar percentage from another estimator. The cited function description does not supply a universal accuracy promise for this receiving decision.
Keep exact package acceptance and estimated planning totals as separately named measures. A later analyst should be able to identify which one a chart uses without reconstructing its author’s intent. The cost or convenience of a computation does not change the definition of the quantity it reports.
Make the reconciliation record portable
The completed record for the invented input says: five supplied rows, four non-null occurrences, three exact distinct identities, approximate output not yet observed. It retains the query expression, engine/version, input version, filters, null policy, grouping and run time. If comparison uses collation or normalization, name that policy too. Distinct identity must not silently change between the dashboard and the receiving table.
Then assign the discrepancy to an actual boundary. A definition mismatch goes back to the metric owner; an unapproved approximation goes back to the acceptance owner; a confirmed missing identity goes back to extraction. This prevents an export engineer from “repairing” a correct file to match an estimate. Preserve both original results and their explanations rather than overwriting whichever number looks inconvenient.
Accept the description before the count
An owner can initially describe the counting method, covered period and unresolved reconciliation in metadata. The receiving party can decide whether it needs exact identity coverage before any real records are shared. The useful outcome is a precise acceptance question, not a larger inventory total. Distinct-count methodology establishes neither uniqueness of business meaning nor a right to disclose the underlying identities.