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