Distinct Count Check Examples

The situation: The o_orderstatus column is backed by an enum with exactly three values: O, F, and P. A fourth value appearing means the application shipped a new status without telling the data team. The column is small and controlled, so an exact equality is safe.

Check configuration

Payload

{
    "description": "The order status column must hold exactly 3 distinct values.",
    "rule": "distinctCount",
    "fields": ["o_orderstatus"],
    "container_id": 145,
    "filter": null,
    "properties": {"comparison": "eq", "value": 3},
    "tags": ["reference-data", "cardinality"],
    "additional_metadata": {"jira": "DATA-9701"},
    "template_id": null,
    "status": "Active",
    "owner_id": 7,
    "default_anomaly_assignee_id": 12
}

Sample Data

What gets flagged

A fourth status, X, reached the table. The measured count is 4 against an expected 3, so the check reports a single Shape Anomaly for the container.

Flowchart

graph TD
    A["No filter, count all rows"] --> B["Measure distinct values: 4"]
    B --> C{"Does 4 equal<br/>the expected 3?"}
    C -->|Yes| D["Check passes"]
    C -->|No| E["Report a Shape Anomaly"]

Equivalent SQL

-- The measurement the check performs, exactly.
SELECT count(DISTINCT o_orderstatus) AS distinct_count
FROM orders;
-- Returns 4; the check expected 3.

The situation: Active product lines must not exceed twelve categories: more than that and the merchandising rules downstream stop covering every case. Retired lines are excluded with a filter, and the comparison is bounded so adding a twelfth is still fine.

Check configuration

Payload

{
    "description": "Active product lines must not exceed 12 categories.",
    "rule": "distinctCount",
    "fields": ["category"],
    "container_id": 512,
    "filter": "status = 'active'",
    "properties": {"comparison": "lte", "value": 12},
    "tags": ["catalog", "cardinality"],
    "additional_metadata": {"jira": "DATA-9744"},
    "template_id": null,
    "status": "Active",
    "owner_id": 7,
    "default_anomaly_assignee_id": 22
}

Sample Data (filtered to status = 'active')

Why the filter matters

The filter runs before the measurement, so retired product lines do not inflate the count. Only active rows contribute to the cardinality.

What gets flagged

Fifteen active categories against a ceiling of twelve. The check reports a Shape Anomaly, and the message ends with the filter that scoped the measurement.

Flowchart

graph TD
    A["Apply filter: status = 'active'"] --> B["Measure distinct values: 15"]
    B --> C{"Is 15 <= the<br/>expected 12?"}
    C -->|Yes| D["Check passes"]
    C -->|No| E["Report a Shape Anomaly.<br/>Message ends with<br/>[filter: status = 'active']"]

Equivalent SQL

-- The measurement, scoped by the filter.
SELECT count(DISTINCT category) AS distinct_count
FROM products
WHERE status = 'active';
-- Returns 15; the ceiling is 12.

The situation: A regional dimension should always carry at least five regions. A failed load once left it with a single value, which silently made every regional report show one bucket. The bounded comparison catches the collapse without pinning an exact number as the business expands.

Check configuration

Payload

{
    "description": "The region dimension must hold at least 5 distinct regions.",
    "rule": "distinctCount",
    "fields": ["region_code"],
    "container_id": 733,
    "filter": null,
    "properties": {"comparison": "gte", "value": 5},
    "tags": ["dimension", "cardinality"],
    "additional_metadata": {"jira": "DATA-9788"},
    "template_id": null,
    "status": "Active",
    "owner_id": 7,
    "default_anomaly_assignee_id": 41
}

Sample Data

What gets flagged

The load left a single region in the dimension. One is below the minimum of five, so the check reports a Shape Anomaly. Note that a column left entirely NULL would report a count of zero and fail the same way, since NULL is not counted as a distinct value.

Flowchart

graph TD
    A["No filter, count all rows"] --> B["Measure distinct values: 1"]
    B --> C{"Is 1 >= the<br/>expected 5?"}
    C -->|Yes| D["Check passes"]
    C -->|No| E["Report a Shape Anomaly"]

Equivalent SQL

-- The measurement the check performs.
SELECT count(DISTINCT region_code) AS distinct_count
FROM dim_region;
-- Returns 1; the minimum is 5.