# Distinct Count Check Examples

Source: https://userguide.qualytics.io/data-quality-checks/distinct-count/deep-dive/examples/

Three real-world scenarios that show how the Distinct Count check is typically used in production: pinning the cardinality of a small lookup, guarding against category growth with a filter, and catching a collapsed dimension. Distinct Count is a Shape-only rule, so every failure is reported as a single Shape Anomaly.

**Tab: Pinned Lookup Cardinality**

**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**

| Field | Value |
| --- | --- |
| Rule | Distinct Count |
| Field | `o_orderstatus` |
| Filter | *(none)* |
| Comparison | Equal To |
| Value | `3` |
| Owner | *(check creator)* |
| Anomaly Assignee | *(Order Management)* |
| Description | The order status column must hold exactly 3 distinct values. |
| Tags | `reference-data`, `cardinality` |
| Additional Metadata | `jira: DATA-9701` |
| Status | Active |

**Payload**

```json
{
    "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**

| Distinct values found | Count |
| --- | --- |
| O, F, P, X | 4 |

**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.

> **Example: Shape Anomaly**
>
> o_orderstatus holds 4 distinct values, which is not equal to the expected 3

**Flowchart**

```mermaid
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**

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

**Tab: Category Growth, with Filter**

**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**

| Field | Value |
| --- | --- |
| Rule | Distinct Count |
| Field | `category` |
| Filter | `status = 'active'` |
| Comparison | Less Than Or Equal To |
| Value | `12` |
| Owner | *(check creator)* |
| Anomaly Assignee | *(Merchandising Data)* |
| Description | Active product lines must not exceed 12 categories. |
| Tags | `catalog`, `cardinality` |
| Additional Metadata | `jira: DATA-9744` |
| Status | Active |

**Payload**

```json
{
    "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'`)

| Scope | Distinct categories | Limit |
| --- | --- | --- |
| `status = 'active'` | 15 | 12 |

**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 names the filter that scoped the measurement.

> **Example: Shape Anomaly**
>
> category holds 15 distinct values, which is not less than or equal to the expected 12 (applied to records where status = 'active')

**Flowchart**

```mermaid
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 names the filter:<br/>applied to records where status = 'active'"]
```

**Equivalent SQL**

```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.
```

**Tab: Collapsed Dimension**

**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**

| Field | Value |
| --- | --- |
| Rule | Distinct Count |
| Field | `region_code` |
| Filter | *(none)* |
| Comparison | Greater Than Or Equal To |
| Value | `5` |
| Owner | *(check creator)* |
| Anomaly Assignee | *(Reporting Data team)* |
| Description | The region dimension must hold at least 5 distinct regions. |
| Tags | `dimension`, `cardinality` |
| Additional Metadata | `jira: DATA-9788` |
| Status | Active |

**Payload**

```json
{
    "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**

| Distinct values found | Count | Minimum |
| --- | --- | --- |
| EMEA | 1 | 5 |

**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. The count has no singular form, so it reads `1 distinct values`. 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.

> **Example: Shape Anomaly**
>
> region_code holds 1 distinct values, which is not greater than or equal to the expected 5

**Flowchart**

```mermaid
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**

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

## See Also

- **How It Works**

  The complete reference: definition, field scope, the comparison operators, approximation, NULL handling, filter behavior, and why coverage does not apply.

  [How It Works](https://userguide.qualytics.io/data-quality-checks/distinct-count/deep-dive/how-it-works/)
- **Anomaly Reporting**

  The anomaly message the check produces, what the counts mean, and why per-row reporting does not apply.

  [Anomaly Reporting](https://userguide.qualytics.io/data-quality-checks/distinct-count/deep-dive/anomaly-reporting/)
- **Best Practices**

  Guidelines for choosing the operator, allowing for approximation, and keeping the signal clean.

  [Best Practices](https://userguide.qualytics.io/data-quality-checks/distinct-count/deep-dive/best-practices/)
- **Permissions**

  The team permission each action needs: view, create, edit, archive, restore, and delete.

  [Permissions](https://userguide.qualytics.io/data-quality-checks/distinct-count/deep-dive/permissions/)

*Last updated: September 22, 2026.*
