# How Unique Checks Work

Source: https://userguide.qualytics.io/data-quality-checks/unique/deep-dive/how-it-works/

## Definition

*Asserts that each field's value is unique across rows. When multiple fields are selected, the combination of values across those fields must be unique (composite key).*

## Overview

The Unique rule behaves in two modes depending on how many fields you select:

- **Single field**: every value in the field must appear only once across all rows. Equivalent to a SQL `UNIQUE` constraint on that column.
- **Multiple fields (composite key)**: the *combination* of values across the selected fields must appear only once. Individual fields may repeat values, but the combined values across the selected fields must be unique on every row. This is equivalent to enforcing a composite/compound primary key.

Typical use cases: enforcing primary keys, detecting duplicates, and enforcing composite-key uniqueness.

## Field Scope

**Multiple:** Accepts one or more fields. With two or more fields, uniqueness is evaluated on the tuple of values across all selected fields.

**Accepted Types**

| Type | Supported |
| --- | --- |
| `Date` | Yes |
| `Timestamp` | Yes |
| `Integral` | Yes |
| `Fractional` | Yes |
| `String` | Yes |
| `Boolean` | Yes |

## General Properties

**Tab: Details**

| Name | Supported |
| --- | --- |
| Filter  Allows the targeting of specific data based on conditions | Supported |
| Coverage Customization  Allows adjusting the percentage of records that must meet the rule's conditions | Supported |

**Tab: Filter Guide**

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.

> **Example: Examples**
>
> **Direct Conditions**
>
> Simply specify the condition you want to be met.
>
> > **Success: Correct usage" collapsible="true**
> >
> > ```sql
> > O_TOTALPRICE > 1000
> > ```
> >
> > ```sql
> > C_MKTSEGMENT = 'BUILDING'
> > ```
>
> > **Failure: Incorrect usage" collapsible="true**
> >
> > ```sql
> > WHERE O_TOTALPRICE > 1000
> > ```
> >
> > ```sql
> > WHERE C_MKTSEGMENT = 'BUILDING'
> > ```
>
> **Combining Conditions**
>
> Combine multiple conditions using logical operators like `AND` and `OR`.
>
> > **Success: Correct usage" collapsible="true**
> >
> > ```sql
> > O_ORDERPRIORITY = '1-URGENT' AND O_ORDERSTATUS = 'O'
> > ```
> >
> > ```sql
> > (L_SHIPDATE = '1998-09-02' OR L_RECEIPTDATE = '1998-09-01') AND L_RETURNFLAG = 'R'
> > ```
>
> > **Failure: Incorrect usage" collapsible="true**
> >
> > ```sql
> > WHERE O_ORDERPRIORITY = '1-URGENT' AND O_ORDERSTATUS = 'O'
> > ```
> >
> > ```sql
> > O_TOTALPRICE > 1000, O_ORDERSTATUS = 'O'
> > ```
>
> **Utilizing Functions**
>
> Leverage Spark SQL functions to refine and enhance your conditions.
>
> > **Success: Correct usage" collapsible="true**
> >
> > ```sql
> > RIGHT(
> >     O_ORDERPRIORITY,
> >     LENGTH(O_ORDERPRIORITY) - INSTR('-', O_ORDERPRIORITY)
> > ) = 'URGENT'
> > ```
> >
> > ```sql
> > LEVENSHTEIN(C_NAME, 'Supplier#000000001') < 7
> > ```
>
> > **Failure: Incorrect usage" collapsible="true**
> >
> > ```sql
> > RIGHT(
> >     O_ORDERPRIORITY,
> >     LENGTH(O_ORDERPRIORITY) - CHARINDEX('-', O_ORDERPRIORITY)
> > ) = 'URGENT'
> > ```
> >
> > ```sql
> > EDITDISTANCE(C_NAME, 'Supplier#000000001') < 7
> > ```
>
> **Using scan-time variables**
>
> To refer to the current dataframe being analyzed, use the reserved dynamic variable `{{_qualytics_self}}`.
>
> > **Success: Correct usage" collapsible="true**
> >
> > ```sql
> > O_ORDERSTATUS IN (
> >     SELECT DISTINCT O_ORDERSTATUS
> >     FROM {{_qualytics_self}}
> >     WHERE O_TOTALPRICE > 1000
> > )
> > ```
>
> > **Failure: Incorrect usage" collapsible="true**
> >
> > ```sql
> > O_ORDERSTATUS IN (
> >     SELECT DISTINCT O_ORDERSTATUS
> >     FROM ORDERS
> >     WHERE O_TOTALPRICE > 1000
> > )
> > ```
>
> While subqueries can be useful, their application within filters in our context has limitations. For example, directly referencing other containers or the broader target container in such subqueries **is not supported**. Attempting to do so will result in an error.
>
> > **Warning: Important Note on {{_qualytics_self}}**
> >
> > The `{{_qualytics_self}}` keyword refers to the dataframe that's currently under examination. In the context of a full scan, this variable represents the entire target container. However, during incremental scans, it only reflects a subset of the target container, capturing just the incremental data. It's crucial to recognize that in such scenarios, using `{{_qualytics_self}}` may not encompass all entries from the target container.

## Anomaly Types

| Type | Supported |
| --- | --- |
| Record Flag inconsistencies at the row level | Not supported |
| Shape Flag inconsistencies in the overall patterns and distributions of a field | Supported |

## How the Check Evaluates Uniqueness

Every Unique check follows the same four-step evaluation flow, regardless of how many fields you select:

1. **Apply the filter clause.** If the check has a `filter` set, only the rows that match the filter expression continue to the next step. Rows that fall outside the filter are ignored and cannot cause a violation.
2. **Group the remaining rows by the selected fields.** For a single-field check, rows are grouped by that one column's value. For a multi-field check, rows are grouped by the tuple of values across all selected columns.
3. **Find groups with more than one row.** Any group of size 2 or larger represents a duplicate: every row in that group has the same key (or tuple of keys) as at least one other row in the filtered set.
4. **Report every row in every duplicate group as a Shape Anomaly.** This means the violation count reflects *all* rows that participate in a duplicate, not just the "extra" copies. If `customer_id = 1001` appears on rows 1 and 3, both rows are reported.

The order of operations matters: the filter is applied **before** the grouping, so rows that the filter excludes cannot contribute to a duplicate group or to the violation count.

## Single-Field vs. Composite-Key Semantics

The Unique check supports two modes, switched by the number of fields you list in `fields`:

### Single Field

Every row's value in the selected column must be distinct from every other row's value in that column. This is the textbook uniqueness constraint and is equivalent to a SQL `UNIQUE` constraint on a single column (with the NULL caveat described below).

Typical use: enforcing a primary key (`customer_id`, `order_id`, `sku`) on a dimension or fact table where the column is the identifier.

### Multiple Fields (Composite Key)

The **combination** of values across all selected fields must be unique on each row. Individual columns may freely repeat, but the tuple as a whole must not. This is equivalent to a composite `UNIQUE` constraint in SQL.

Typical use: enforcing uniqueness on a junction or line-item table where neither column alone is a key (`(order_id, line_number)` on `order_items`, `(student_id, course_id)` on enrollments, `(user_id, event_type, event_date)` on daily event aggregates).

> **Note: Field order doesn't affect duplicate detection**
>
> `["a", "b"]` and `["b", "a"]` flag the same rows as duplicates. The anomaly message reflects the order you specified (the field names are joined in the order they appear in `fields`).

## How NULLs Are Handled

This is a common source of confusion with the Unique check, so it deserves its own section.

**Rows where every selected field is NULL are left out of the evaluation.** On a single-field check, a NULL value is never reported as a duplicate, however many rows hold it. On a composite key, a row whose selected fields are all NULL is skipped the same way. This part matches a SQL `UNIQUE` constraint, which accepts many NULL rows.

**A partially NULL tuple is compared like any other value.** On a composite key, a row with at least one non-NULL selected field is grouped by its full tuple, and NULL counts as a value inside the tuple. This part is stricter than a SQL `UNIQUE` constraint, where a tuple containing NULL never collides with another.

### When NULL handling matters

| Situation | What the check does |
| --- | --- |
| A required field that should never be NULL | Unique does not flag NULLs. Combine it with a [Not Null](https://userguide.qualytics.io/data-quality-checks/not-null/introduction/) check: together they enforce true primary-key semantics (`UNIQUE` + `NOT NULL`). |
| An optional field where many NULLs are expected | No filter is needed: NULL rows are skipped, so only populated values are compared. |
| A composite key where one column is optional | Repeated `(value, NULL)` tuples are reported as duplicates. If they should be allowed, filter the optional column with `IS NOT NULL`. See "Composite keys and partial NULLs" below. |

### Composite keys and partial NULLs

For a composite key like `(a, b)`, a row is grouped with another row only when the **full tuple** matches. So:

- `(1, NULL)` and `(1, NULL)` → same group → duplicate.
- `(1, NULL)` and `(1, 2)` → different groups → not a duplicate.
- `(NULL, NULL)` repeated → skipped, because every selected field is NULL → not a duplicate.

If your data model allows partial NULLs in a composite key and you do not want repeated partial-NULL tuples to register as duplicates, scope the check with a filter that excludes those rows.

## The Filter Clause

The filter clause is a SQL `WHERE` expression that the platform applies before the uniqueness evaluation. It serves two purposes:

1. **Scoping the check.** Restrict uniqueness to a subset of the data (for example, `status = 'active'`, `event_date = current_date()`, or `tenant_id = 42`). Rows outside the scope cannot trigger a violation, but they still count toward the total in the anomaly message. See [What the Numbers Mean](https://userguide.qualytics.io/data-quality-checks/unique/deep-dive/anomaly-reporting/#what-the-numbers-mean).
2. **Allowing partial-NULL tuples.** On a composite key, a filter such as `line_number IS NOT NULL` keeps repeated `(value, NULL)` tuples from counting as duplicates. A single-field check needs no NULL filter, because NULL rows are already skipped.

When a filter is set, the Shape Anomaly message names it at the end of its first sentence, as `(applied to records where <expression>)`.

## Coverage and Tolerance

Coverage is a fractional value between `0` and `1` that sets the threshold for what counts as a violating shape:

- **`1.0` (100%):** every row must be part of a singleton group. Any duplicate triggers a Shape Anomaly. This is the default and the most common setting.
- **`< 1.0`:** the check tolerates a fraction of records appearing in duplicate groups before flagging an anomaly. For example, `0.995` allows up to 0.5% of rows to participate in duplicates without firing.

Lower coverage values are useful when a small, known fraction of duplicates is expected (legacy data that has not been deduplicated yet, slow-running migrations, or a tolerated overlap between data sources). Use coverage with care: lowering it by, say, 0.5% means a real regression introducing duplicates in up to 0.5% of rows will look identical to the tolerated baseline and won't fire.

## See Also

- **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/)
- **Best Practices**

  Guidelines for NULL handling, composite keys, scoping, and pairing rules.

  [Best Practices](https://userguide.qualytics.io/data-quality-checks/unique/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/unique/deep-dive/permissions/)

*Last updated: October 16, 2026.*
