# Unique Best Practices

Source: https://userguide.qualytics.io/data-quality-checks/unique/deep-dive/best-practices/

Guidelines for getting reliable signal from Unique checks while keeping the noise (and the cost) low.

## Pair with Not Null for true primary-key semantics

Unique skips rows whose selected fields are all NULL, so it never reports NULLs as duplicates, the same as a SQL `UNIQUE` constraint. When the field is a real key that must never be empty, add a [Not Null](https://userguide.qualytics.io/data-quality-checks/not-null/introduction/) check on the same field; together they enforce `UNIQUE` + `NOT NULL`.

## Optional fields need no NULL filter

For an optional identifier that is often empty, no filter is needed: rows with a NULL value are skipped, so only populated values are compared. On a composite key, filter the optional column with `IS NOT NULL` only when repeated `(value, NULL)` tuples should be allowed.

## Keep composite keys narrow

For a composite key, rows group only when the **full tuple** matches, so `(1, NULL)` collides with `(1, NULL)` but not with `(1, 2)`. Most production keys use two to four fields. Going much wider both costs more to evaluate and usually signals that the data model, not the check, is the thing to revisit.

## Scope the check to where uniqueness actually applies

Uniqueness is often scoped: unique per day, per tenant, per active status. Express that with a filter clause rather than accepting the anomalies a global check produces. The filter narrows the grouping work proportionally, and the expression is echoed in the anomaly message so the evaluated scope stays visible.

## Enforce the canonical form separately

Unique compares raw values, so `alice@example.com` and `Alice@Example.com` are two distinct values and neither is a duplicate of the other. When uniqueness should hold on a normalized form, normalize upstream (or with a Computed Field) and check the normalized column, or pair Unique with a [Satisfies Expression](https://userguide.qualytics.io/data-quality-checks/satisfies-expression/introduction/) check that enforces the canonical form on the raw one.

## Choose the right rule for the job

- Use **Unique** when no value (or tuple) may repeat.
- Use [Distinct Count](https://userguide.qualytics.io/data-quality-checks/distinct-count/introduction/) when the *number* of distinct values is itself the rule ("this lookup should always hold exactly seven states").
- Use [Exists In](https://userguide.qualytics.io/data-quality-checks/exists-in/introduction/) or [Not Exists In](https://userguide.qualytics.io/data-quality-checks/not-exists-in/introduction/) for referential integrity across tables. Together with Unique they model the relational core: keys, no duplicates, valid references.

## Expect the check to cost more on wide keys

Uniqueness requires grouping every filtered row by the selected fields. There is no separate optimized path for single-field versus composite checks: the cost comes from how much data is grouped and how wide each key is. Narrowing the key and filtering the rows are the two levers that matter.

## Route the anomalies to the right people

Duplicates usually come from a load that ran twice, a missing upsert key, or a merge that went wrong. Set an **Anomaly Assignee** from the team that owns the ingestion, and record in the description what the key means so whoever triages knows which copy is the one to keep.

## See Also

- **How It Works**

  The complete reference: definition, field scope, single-field and composite semantics, NULL handling, filter behavior, and coverage.

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

  The anomaly message the check produces, what the counts mean, and how duplicate groups appear in Source Records.

  [Anomaly Reporting](https://userguide.qualytics.io/data-quality-checks/unique/deep-dive/anomaly-reporting/)
- **Examples**

  Production scenarios with sample data, duplicate groups, and the resulting anomaly messages.

  [Examples](https://userguide.qualytics.io/data-quality-checks/unique/deep-dive/examples/)
- **Permissions**

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

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

*Last updated: October 16, 2026.*
