Exists In Best Practices

Guidelines for getting reliable signal from Exists In checks while keeping the noise (and the maintenance) low.

Use it as the foreign key your warehouse does not enforce

Most analytical stores do not enforce referential integrity, so an orphan key is only discovered when a join silently drops rows. This rule is where that constraint lives, and it belongs on every identifier that is supposed to point somewhere.

Point the reference at the smallest authoritative set

The lookup set defines what is valid. Referencing a wide staging table instead of the curated dimension makes the check pass on values nobody considers correct. Prefer the container that is the source of truth, and use the reference filter to exclude retired or draft rows.

Keep the two filters straight

Scoping the target changes what is checked; scoping the reference changes what is allowed. Writing both intentions into the check's description saves the next person from guessing which side a filter belongs to.

Pair with Not Null when presence matters

NULL target values pass, so a column that stops being populated silences the check instead of failing it. Add Not Null when the value must also exist.

Prefer a small vocabulary rule when the set is fixed

When the valid values are a handful of constants that rarely change, Expected Values states the rule inline and costs less than reading another container. Reach for a lookup when the set is large, shared, or maintained elsewhere.

Keep coverage at 100% unless a known backlog exists

At 100% coverage every failing row is reported as a Record Anomaly, which tells you exactly which rows break the rule. Below 100% the check reports a single Shape Anomaly only when the failing fraction crosses the tolerance, and no per-row detail is produced. Lower coverage only while a known set of legacy rows is being cleaned up.

Scope with a filter instead of loosening coverage

When the rule only applies to part of the table (one segment, one channel, one period), express that with a filter clause rather than by lowering coverage. The filter removes the out-of-scope rows from evaluation entirely, and the expression is echoed in every anomaly message.

Route the anomalies to the right people

A failure usually points at the system that produced the value, not at the warehouse. Set an Anomaly Assignee from the team that owns that producer, and tag the check so related checks are easy to find.

See Also