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.

Rows With NULL Are Reported as Duplicates

An optional column with many empty values is flagged, even though a database would accept it.

Cause: The check treats NULL as a real value when grouping, so every NULL row lands in the same group. This is stricter than a SQL UNIQUE constraint, where NULLs are all distinct from one another.

Resolution: Add a filter such as external_ref IS NOT NULL so only populated values are compared, which reproduces SQL UNIQUE semantics. When the field should never be empty, pair the check with Not Null instead.

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.

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