How Exists In Checks Work

Definition

Asserts that every value in the selected field exists in a field on another container.

Overview

The 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 exists 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 set you consider valid.

Typical use cases:

• Enforce a foreign key between a fact table and its dimension.
• Restrict a code column to the values held in a reference table.
• Confirm that a migrated dataset only references entities that made it across.

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 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.
3. Test each target value. A row passes when its value exists the lookup set, and fails when it does not exist.
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 count as valid.

Narrowing the reference filter makes the check stricter, because fewer values are acceptable. 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. 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.

Both Sides Must Be Comparable

The target value and the reference value are matched as values of the same kind. A numeric column looked up against a text column will not match, even when the printed values look identical. Keep the two columns on the same type, or normalize one of them upstream with a Computed Field.

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 loses rows makes the check stricter without anyone editing it, which is usually what you want from referential integrity, and is worth remembering when a check suddenly starts firing.

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