Distinct Count Best Practices

Guidelines for getting reliable signal from Distinct Count checks while keeping the noise (and the maintenance) low.

Prefer a bounded comparison over an exact equality

Because the count is approximate, Equal To is fragile on anything but a small, controlled column. Greater Than Or Equal To ("we must still have all our categories") and Less Than Or Equal To ("the vocabulary must not grow") express most real rules and tolerate the margin of error.

Use it on controlled columns, not on identifiers

A column of identifiers has a cardinality that grows with the table, so a fixed expected count goes stale immediately. Distinct Count belongs on columns whose set of values is governed: statuses, regions, product lines, currencies.

Pair with Expected Values when the values themselves matter

Distinct Count notices that the number changed; Expected Values notices which value is wrong. Together they catch both a new category appearing and an existing one drifting.

Scope with a filter when cardinality is per-segment

"Each region should have exactly seven statuses" is not a single-number rule for the whole table. Use a filter to scope the check to one segment, and create one check per segment when the rule genuinely applies that way.

Record where the expected number comes from

A bare number invites the wrong fix. Noting in the description that it mirrors an enum in the application, or a reference table, tells the next reader whether an anomaly means bad data or an outdated check.

Route the anomalies to the right people

A failure usually points at the system that produced the value, not at the warehouse. Set an Anomaly Assignee from the team that owns that producer, and tag the check so related checks are easy to find.

See Also