Exists In Check Examples

The situation: Every order carries a o_custkey that must exist in the CUSTOMER dimension. Orphan keys come from a load that ran before the customer batch and break every join downstream.

Check configuration

Payload

{
    "description": "Every order must reference an existing customer.",
    "rule": "existsIn",
    "fields": ["o_custkey"],
    "container_id": 145,
    "coverage": 1,
    "filter": null,
    "properties": {"ref_container_id": 178, "field_name": "c_custkey"},
    "tags": ["integrity", "orders"],
    "additional_metadata": {"jira": "DATA-9901"},
    "anomaly_message_field": null,
    "template_id": null,
    "status": "Active",
    "owner_id": 7,
    "default_anomaly_assignee_id": 12
}

Sample Data

What gets flagged

Order 2 points at a customer key that does not exist in the dimension. Order 4 has no key at all, 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 o_custkey"]
    B --> C{"Is value NULL?"}
    C -->|Yes| D["Row passes"]
    C -->|No| E{"Is o_custkey in<br/>the lookup set?"}
    E -->|Yes| D
    E -->|No| F["Flag row.<br/>Record Anomaly per failing row."]

Equivalent SQL

-- Rows the Exists In check would flag.
SELECT o.*
FROM orders o
WHERE o.o_custkey IS NOT NULL
  AND o.o_custkey NOT IN (SELECT c_custkey FROM customer);

The situation: New invoices may only use a currency that is still active. Historical invoices keep currencies that were retired, so the target filter limits the check to recent rows, and the reference filter limits the valid set to active currencies.

Check configuration

Payload

{
    "description": "Recent invoices must use an active currency.",
    "rule": "existsIn",
    "fields": ["currency_code"],
    "container_id": 512,
    "coverage": 1,
    "filter": "created_at >= '2026-01-01'",
    "properties": {"ref_datastore_id": 22, "ref_container_id": 803, "field_name": "code", "ref_filter": "is_active = true"},
    "tags": ["integrity", "billing"],
    "additional_metadata": {"jira": "DATA-9944"},
    "anomaly_message_field": null,
    "template_id": null,
    "status": "Active",
    "owner_id": 7,
    "default_anomaly_assignee_id": 29
}

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

Why the filter matters

The two filters do different jobs. The target filter keeps historical invoices out of the evaluation; the reference filter removes retired currencies from the valid set, which is what makes the check strict about active ones.

What gets flagged

I-02 uses a currency that still exists in the reference table but is no longer active, so it is excluded from the lookup set by the reference filter. 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 currency_code in the<br/>active-currency lookup 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 invoices.
SELECT i.*
FROM invoices i
WHERE i.created_at >= DATE '2026-01-01'
  AND i.currency_code IS NOT NULL
  AND i.currency_code NOT IN (SELECT code FROM currency WHERE is_active = true);

The situation: A legacy warehouse_code column holds values from a retired coding scheme. A remapping job is rewriting them against the current warehouse list, and a small fraction is expected to stay unmapped until it finishes, so the check tolerates up to 0.5% failures.

Check configuration

Payload

{
    "description": "Warehouse codes must exist in the warehouse reference.",
    "rule": "existsIn",
    "fields": ["warehouse_code"],
    "container_id": 733,
    "coverage": 0.995,
    "filter": null,
    "properties": {"ref_container_id": 905, "field_name": "code"},
    "tags": ["integrity", "legacy"],
    "additional_metadata": {"jira": "DATA-9988"},
    "anomaly_message_field": null,
    "template_id": null,
    "status": "Active",
    "owner_id": 7,
    "default_anomaly_assignee_id": 41
}

Sample Data

What gets flagged

Shipments S-02 and S-03 still carry codes from the retired scheme. S-04 is NULL, so it 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 warehouse_code"]
    B --> C{"Is value NULL?"}
    C -->|Yes| D["Row passes"]
    C -->|No| E{"Is warehouse_code in<br/>the lookup set?"}
    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 s.*
FROM shipments s
WHERE s.warehouse_code IS NOT NULL
  AND s.warehouse_code NOT IN (SELECT code FROM warehouse);