How Not Exists In Checks Work

Definition

Asserts that every value in the selected field does not exist in a field on another container.

Overview

The Not Exists In rule is a referential-integrity check. It reads a reference field on another container and requires that each value in the target field does not exist there. The reference can live in the same datastore or in a different one, and it can be narrowed with its own filter clause, so the lookup covers exactly the values you consider forbidden.

Typical use cases:

• Keep suppressed or opted-out entries out of an active audience.
• Block identifiers that appear on a retired, banned, or reserved list.
• Confirm two datasets that should be disjoint, such as active and archived records, do not overlap.

Field Scope

Single: The rule evaluates exactly one field per check, looked up against exactly one reference field. Every field type is accepted.

Anomaly Types

Evaluation Flow

Every Not Exists In check follows the same four-step evaluation flow:

1. Apply both filter clauses. The target filter scopes the rows that are evaluated; the reference filter scopes the rows that form the lookup set. Each side is narrowed before anything is compared.
2. Build the lookup set. The platform reads the reference field on the filtered reference container. Those values are the forbidden ones.
3. Test each target value. A row passes when its value is absent from the lookup set, and fails when it is present.
4. Apply coverage. At 100% coverage, any failing row causes the check to fail. Below 100% coverage, the check fails only when the passing fraction drops below the threshold (see Coverage and Tolerance).

Two Filters, Two Purposes

The rule carries two independent filter clauses and mixing them up is the most common configuration mistake:

• The Filter Clause on the main form scopes the target rows: which rows get evaluated at all.
• The Filter Clause on the Right Reference panel scopes the reference rows: which values are forbidden.

Narrowing the reference filter makes the check looser, because fewer values are forbidden. Narrowing the target filter makes it quieter, because fewer rows are tested.

NULL Handling

The check passes NULL values in the target field: a row with NULL has no value to look up, so it is not counted as a violation. Not Exists In only asserts something about present values.

If the field must also be populated, pair it with a Not Null check on the same field.

How the Two Sides Are Compared

The lookup is a join, and each reference value is cast to the target field's type before it is compared. A text reference column holding 123 therefore still matches a numeric target value of 123. A reference value that cannot be cast to the target type never matches, which for Not Exists In means the target row passes. When the two columns hold genuinely different kinds of value, normalize one of them upstream with a Computed Field instead of relying on the cast.

The Reference Is Read at Scan Time

The lookup set reflects the reference container as the scan reads it, not as it was when the check was created. A reference table that gains rows makes the check stricter without anyone editing it, and one that loses rows makes it looser, which is worth remembering when a check suddenly starts firing or suddenly goes quiet.

The Filter Clause

The filter clause is a SQL WHERE expression applied before the evaluation. Filtered-out rows are ignored entirely (they cannot trigger a violation and are not counted in the totals).

When a filter is set, both the Record Anomaly and the Shape Anomaly messages end with [filter: <expression>] so the evaluated scope is visible in the alert.

Coverage and Tolerance

Coverage is a fractional value between 0 and 1 that defines the minimum fraction of evaluated rows that must pass:

• 1.0 (100%, default): every row in the filtered set must pass. Any failing row causes the check to fail. This is the strictest setting.
• < 1.0: the check tolerates a fraction of rows failing. The check fails only when the fraction of passing rows drops below the threshold.

Lower coverage values are useful when a small, known fraction of failures is expected. Use coverage carefully: a 0.5% tolerance can mask a real regression that happens to fall just under the threshold.

See Also