How Distinct Count Checks Work

Definition

Asserts that the number of distinct values in a field satisfies a comparison against an expected number.

Overview

The Distinct Count rule measures the cardinality of a single field and compares it against the number you configure, using the operator you pick. It is a dataset-level assertion: the check does not care which values are present, only how many different ones there are.

The count is approximate. The platform uses an approximate distinct-count algorithm so the check stays cheap on large containers, which means the reported number can differ slightly from an exact COUNT(DISTINCT ...). That matters when you assert an exact equality on a high-cardinality column.

Typical use cases:

• Confirm a lookup or dimension table still holds the number of entries it should.
• Detect a category explosion, where a controlled column suddenly gains values.
• Catch a collapsed dimension, where a load left a column with far fewer values than expected.

Field Scope

Single: The rule evaluates exactly one field per check. Every field type is accepted.

General Properties

The filter allows you to define a subset of data upon which the rule will operate.

It requires a valid Spark SQL expression that determines the criteria rows in the DataFrame should meet. This means the expression specifies which rows the DataFrame should include based on those criteria. Since it's applied directly to the Spark DataFrame, traditional SQL constructs like WHERE clauses are not supported.

O_TOTALPRICE > 1000

C_MKTSEGMENT = 'BUILDING'

WHERE O_TOTALPRICE > 1000

WHERE C_MKTSEGMENT = 'BUILDING'

O_ORDERPRIORITY = '1-URGENT' AND O_ORDERSTATUS = 'O'

(L_SHIPDATE = '1998-09-02' OR L_RECEIPTDATE = '1998-09-01') AND L_RETURNFLAG = 'R'

WHERE O_ORDERPRIORITY = '1-URGENT' AND O_ORDERSTATUS = 'O'

O_TOTALPRICE > 1000, O_ORDERSTATUS = 'O'

RIGHT(
    O_ORDERPRIORITY,
    LENGTH(O_ORDERPRIORITY) - INSTR('-', O_ORDERPRIORITY)
) = 'URGENT'

LEVENSHTEIN(C_NAME, 'Supplier#000000001') < 7

RIGHT(
    O_ORDERPRIORITY,
    LENGTH(O_ORDERPRIORITY) - CHARINDEX('-', O_ORDERPRIORITY)
) = 'URGENT'

EDITDISTANCE(C_NAME, 'Supplier#000000001') < 7

O_ORDERSTATUS IN (
    SELECT DISTINCT O_ORDERSTATUS
    FROM {{_qualytics_self}}
    WHERE O_TOTALPRICE > 1000
)

O_ORDERSTATUS IN (
    SELECT DISTINCT O_ORDERSTATUS
    FROM ORDERS
    WHERE O_TOTALPRICE > 1000
)

Specific Properties

Distinct Count has two rule-specific properties:

Anomaly Types

Evaluation Flow

Every Distinct Count check follows the same three-step evaluation flow:

1. Apply the filter clause. If the check has a filter set, only rows matching the filter expression are counted. Rows outside the filter do not contribute to the cardinality.
2. Measure the distinct count. The platform computes the approximate number of distinct values in the field across the filtered rows.
3. Compare against the expected value. The check passes when the measured count satisfies the configured Comparison against Value, and reports a Shape Anomaly when it does not.

The Count Is Approximate

The measurement uses an approximate distinct-count algorithm, which keeps the check inexpensive on large containers at the cost of a small margin of error. Two practical consequences:

• An Equal To comparison on a high-cardinality column can fail even when the exact count matches, because the approximation lands a hair off.
• The looser operators (Greater Than Or Equal To, Less Than Or Equal To) are more robust, and are usually what you want on anything beyond a small, controlled set.

For a small lookup table with a handful of values, the approximation is exact in practice and Equal To is safe.

No Coverage

Distinct Count does not use a coverage threshold. The rule asserts one number about the dataset, so there is no per-row violation rate to tolerate. The coverage field on the API payload is not supported: omit it, or send 1 for backward compatibility, because any other value is rejected with 422 Unprocessable Entity.

NULL Handling

NULL is not counted as a distinct value: the measurement covers the values that are present. A column that is entirely NULL therefore has a distinct count of zero, which fails any comparison expecting more. Pair the check with Not Null when the absence itself is the problem you want reported.

Cardinality Is Not Uniqueness

Distinct Count answers "how many different values are there". Unique answers "does any value repeat". A column can have exactly the expected cardinality and still be full of duplicates; use Unique when no repetition is allowed.

The Filter Clause

The filter clause is a SQL WHERE expression applied before the evaluation. Filtered-out rows are ignored entirely.

When a filter is set, the Shape Anomaly message ends with [filter: <expression>] so the evaluated scope is visible in the alert.

See Also