How Unique Checks Work

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

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
)

Anomaly Types

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

How NULLs Are Handled

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

The platform treats NULL as a real value when grouping rows. Two rows where the selected field is NULL are placed in the same group and are therefore counted as duplicates of each other. The same applies to composite keys: two rows where every selected field is NULL share a key (a tuple of all NULLs) and will be flagged.

This is different from a SQL UNIQUE constraint, where NULL is treated as distinct from every other value, including other NULLs, so a SQL UNIQUE column can hold many NULL rows without violating the constraint. The Unique check is stricter on NULL.

When NULL handling matters

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 → same group → duplicate.

If your data model allows partial NULLs in a composite key and you do not want repeated partial-NULL tuples or the all-NULL tuple 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 and are not counted in the totals reported in the anomaly message.
2. Working around NULL semantics. A filter such as email IS NOT NULL makes the Unique check behave like a SQL UNIQUE constraint on the email column by removing NULL rows from consideration entirely.

The filter is part of the check definition, so the anomaly message includes the filter expression ([filter: <expression>]) when one is set, making it explicit which slice of data was evaluated when the anomaly fired.

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