Not Exists In Best Practices

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

Name the exclusion in the description

"Must not be on the suppression list" and "must not reuse a retired code" are very different rules with the same shape. Whoever triages the anomaly needs to know which one they are looking at, and what the correct fix is: remove the row, or change the value.

Point the reference at the authoritative forbidden set

The lookup set defines what is forbidden. Referencing a wide staging table instead of the curated blocklist flags values nobody meant to prohibit; referencing too narrow a slice lets prohibited values through. Prefer the container that is the source of truth for the exclusion, and use the reference filter to keep the blocked set to the entries the rule is actually about.

Keep the two filters straight

Scoping the target changes what is checked; scoping the reference changes what is forbidden. 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.

State a small blocklist inline instead of reading a container

When the forbidden values are a handful of constants that rarely change, a Satisfies Expression check with a NOT IN (...) predicate states the rule on the check itself and costs less than reading another container. Reach for the lookup when the blocked 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