Unique 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 treats NULL as a real value: two rows with NULL in the selected field land in the same group and are reported as duplicates. That is stricter than a SQL UNIQUE constraint, where NULLs are all distinct. When the field is a real key, add a Not Null check on the same field; together they enforce UNIQUE + NOT NULL.

Filter out the NULLs when the field is genuinely optional

For an optional identifier that is often empty, a Unique check on its own reports every NULL row as a duplicate of every other. Add a filter such as external_ref IS NOT NULL so only populated values are compared. That reproduces SQL UNIQUE semantics, where NULLs never collide.

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 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 when the number of distinct values is itself the rule ("this lookup should always hold exactly seven states").
Use Exists In or Not Exists In 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.