Skip to content

Unique Troubleshooting

Common problems when creating and running a Unique check, their causes, and how to resolve them.

Validation Fails When Creating the Check

Clicking Validate returns an error instead of a success message.

Cause: The rule could not run against the selected data. The most common reasons are an invalid Filter Clause expression, a connection problem with the source, or a container that failed to load.

Resolution:

  1. Review the error message: it explains what needs attention.
  2. Check the Filter Clause for typos: it must be a valid SQL WHERE expression for the selected container.
  3. If the message says the container is marked as Unloadable, the container was skipped after repeated operation failures. Follow the steps in Unloadable Container Error.

Repeated NULLs Are Not Reported as Duplicates

Several rows share an empty value in the selected field, and the check passes.

Cause: Rows where every selected field is NULL are skipped, the same as a SQL UNIQUE constraint, so NULLs never count as duplicates.

Resolution: This is expected behavior. When the field should never be empty, pair the check with Not Null.

Values That Look Identical Are Not Flagged

Two rows that a human would call the same value pass the check.

Cause: The comparison is exact. alice@example.com and Alice@Example.com are two distinct values, and so are "ABC" and "ABC ".

Resolution: Normalize the value upstream (or with a Computed Field) and run the check on the normalized column. To also enforce that the raw column stays canonical, pair with a Satisfies Expression check.

A Composite Key Reports Fewer Duplicates Than Expected

Rows that share one column are not reported.

Cause: For a composite key, rows are grouped only when the full tuple matches. (1, NULL) collides with (1, NULL), but not with (1, 2).

Resolution: This is expected behavior. When uniqueness should hold on fewer columns, narrow the key. When a partially populated tuple is the problem, filter the optional column with IS NOT NULL.

The Anomaly Count Looks Doubled

The count reported by the check is higher than the number of "extra" rows you expected to remove.

Cause: The count includes every row in each duplicate group, not only the extras. Two rows sharing a value count as two, not one.

Resolution: This is intentional, so the count matches the rows a query for those duplicate values would return. See What the Numbers Mean.

An Edited Check Keeps Behaving the Old Way

You changed the key or the filter but the anomaly list did not change.

Cause: Edits take effect on the next Scan. Saving the check does not re-evaluate the data, and anomalies raised under the previous configuration are not modified.

Resolution: Run a Scan on the container (or wait for the scheduled one). Old anomalies stay open until you triage them or a Full scan with Auto Resolve clears them. See What Happens to Existing Anomalies.

No Anomalies Although Duplicates Exist

A Scan ran, the table clearly holds duplicates, but nothing was reported.

Cause: One of three configurations is excluding them:

  • The check is in Draft status: Draft checks are not evaluated by Scans.
  • The Filter Clause excludes the duplicate rows before grouping.
  • Coverage is below 100% and the duplicate fraction stayed within the tolerance, so the check passed.

Resolution: Confirm the check is Active, test the filter against the duplicate rows, and review the coverage setting. See Coverage and Tolerance.

A Check on an Array Field Is Not Asserted

A Unique check on an array field shows Not Asserted after a scan, with a reason saying that unique doesn't check the elements within an array.

Cause: The check was saved with element context, which asked it to look for repeated elements inside each array. Unique no longer offers that mode: it only compares values across rows, so a check saved that way is skipped on every scan.

Resolution: Archive the old check, then replace it with the check that matches what you want to catch:

  • To find rows whose whole array repeats another row's, create a new Unique check on the field. It compares whole arrays across rows.
  • To find arrays that repeat an element, create a Satisfies Expression check such as size(tags) = size(array_distinct(tags)).

See Check results for how not-asserted checks are reported.

Expected Per-Row Anomalies, Got a Single One

The check reports one anomaly instead of flagging each duplicate row.

Cause: Unique is a Shape-only rule. Uniqueness is a property of the dataset (a duplicate group), not of one row's value, so the check reports a single Shape Anomaly.

Resolution: This is expected behavior. Open the anomaly's Source Records to see every row in each duplicate group, which is what you need to decide which copy survives.

The Check Is Slow on a Large Table

The scan takes noticeably longer than other checks on the same container.

Cause: Uniqueness requires grouping every filtered row by the selected fields, and the cost grows with the volume grouped and the width of the key.

Resolution: Narrow the key to the fields that really define it, and filter the rows to the scope where uniqueness applies (today's partition, one tenant, active rows).