Not Exists In Check Examples

The situation: Contacts who opted out are recorded on a suppression list. Any address that appears there must not be present in the active mailing audience, or the next campaign sends mail someone explicitly refused.

Check configuration

Payload

{
    "description": "Active contacts must not appear on the suppression list.",
    "rule": "notExistsIn",
    "fields": ["email"],
    "container_id": 145,
    "coverage": 1,
    "filter": null,
    "properties": {"ref_container_id": 178, "field_name": "email"},
    "tags": ["compliance", "integrity"],
    "additional_metadata": {"jira": "DATA-10001"},
    "anomaly_message_field": null,
    "template_id": null,
    "status": "Active",
    "owner_id": 7,
    "default_anomaly_assignee_id": 12
}

Sample Data

What gets flagged

Contact K-02 is on the suppression list and should not be in the active audience. K-04 has no address, so there is nothing to look up and the row passes. Coverage is 100%, so the failing row is reported as a Record Anomaly.

Flowchart

graph TD
    A["Build the lookup set<br/>from the reference field"] --> B["Read email"]
    B --> C{"Is value NULL?"}
    C -->|Yes| D["Row passes"]
    C -->|No| E{"Is email absent from<br/>the suppression set?"}
    E -->|Yes| D
    E -->|No| F["Flag row.<br/>Record Anomaly per failing row."]

Equivalent SQL

-- Rows the Not Exists In check would flag.
SELECT c.*
FROM contacts c
WHERE c.email IS NOT NULL
  AND c.email IN (SELECT email FROM suppression_list);

The situation: New products may not reuse a code that was retired. Legacy products keep theirs, so the target filter limits the check to recent rows, and the reference filter limits the blocked set to codes retired in the current policy window.

Check configuration

Payload

{
    "description": "New products must not reuse a retired code.",
    "rule": "notExistsIn",
    "fields": ["product_code"],
    "container_id": 512,
    "coverage": 1,
    "filter": "created_at >= '2026-01-01'",
    "properties": {"ref_container_id": 803, "field_name": "code", "ref_filter": "retired_at >= '2020-01-01'"},
    "tags": ["catalog", "integrity"],
    "additional_metadata": {"jira": "DATA-10044"},
    "anomaly_message_field": null,
    "template_id": null,
    "status": "Active",
    "owner_id": 7,
    "default_anomaly_assignee_id": 22
}

Sample Data (target filtered to created_at >= '2026-01-01', reference filtered to retired_at >= '2020-01-01')

Why the filter matters

The target filter keeps legacy products out of the evaluation. The reference filter narrows the blocked set to recently retired codes, so a code retired decades ago can legitimately be reused.

What gets flagged

SKU-02 reuses a code retired in the current policy window. Coverage is 100%, so the failure is reported as a Record Anomaly, and the message ends with the target filter that scoped the evaluation.

Flowchart

graph TD
    A["Apply target filter: created_at >= '2026-01-01'"] --> B["Build the lookup set<br/>from the reference field"]
    B --> C{"Is product_code absent from<br/>the retired-code set?"}
    C -->|Yes| D["Row passes"]
    C -->|No| E["Flag row.<br/>Anomaly message ends with<br/>[filter: created_at >= '2026-01-01']"]

Equivalent SQL

-- Rows the check would flag among recent products.
SELECT p.*
FROM products p
WHERE p.created_at >= DATE '2026-01-01'
  AND p.product_code IS NOT NULL
  AND p.product_code IN (SELECT code FROM retired_code WHERE retired_at >= DATE '2020-01-01');

The situation: Two systems were merged and a handful of identifiers ended up in both the active table and the archive, which must stay disjoint. A deduplication job is resolving them, so the check tolerates up to 0.5% failures while it runs.

Check configuration

Payload

{
    "description": "Active records must not also exist in the archive.",
    "rule": "notExistsIn",
    "fields": ["record_id"],
    "container_id": 733,
    "coverage": 0.995,
    "filter": null,
    "properties": {"ref_container_id": 905, "field_name": "record_id"},
    "tags": ["integrity", "legacy"],
    "additional_metadata": {"jira": "DATA-10088"},
    "anomaly_message_field": null,
    "template_id": null,
    "status": "Active",
    "owner_id": 7,
    "default_anomaly_assignee_id": 41
}

Sample Data

What gets flagged

Records R-1002 and R-1003 exist in both tables, which the merge was supposed to prevent. The NULL row passes without firing an anomaly but still counts in the scanned total. Only 50% of the rows pass, which is below the 99.5% coverage threshold, so the check reports a single Shape Anomaly for the dataset with a sample of the offending rows. Coverage below 100% does not produce Record Anomalies.

Flowchart

graph TD
    A["Build the lookup set<br/>from the reference field"] --> B["Read record_id"]
    B --> C{"Is value NULL?"}
    C -->|Yes| D["Row passes"]
    C -->|No| E{"Is record_id absent<br/>from the archive?"}
    E -->|Yes| D
    E -->|No| F["Flag row.<br/>Passing rate falls below the 99.5%<br/>coverage, so one Shape Anomaly is reported."]

Equivalent SQL

-- Rows the check would flag.
SELECT a.*
FROM active_records a
WHERE a.record_id IS NOT NULL
  AND a.record_id IN (SELECT record_id FROM archive);