How Any Not Null Checks Work

Definition

Asserts that at least one of the selected fields must hold a value.

Overview

The Any Not Null rule evaluates a set of fields together and requires that, on every row, at least one of those fields is populated. A row passes as soon as any selected field carries a non-NULL value, and fails only when every selected field is NULL. The rule is useful when several fields are individually optional but the row is considered incomplete if none of them are present.

Typical use cases:

• Require at least one contact method (phone, email, or mobile).
• Ensure that alternative identifier fields such as national_id or passport_number are not all missing.
• Guarantee that at least one fallback value column is populated when a primary source is optional.

Field Scope

Multiple: The rule is designed for two or more fields per check. A row passes if at least one of them holds a non-NULL value.

Accepted Types

Any Not Null accepts every field type. NULL is treated the same across types, so the check does not restrict which columns can be included.

Anomaly Types

Evaluation Flow

Every Any Not Null check follows the same four-step evaluation flow:

1. Apply the filter clause. If the check has a filter set, only rows matching the filter expression continue to the next step. Rows outside the filter are ignored and cannot contribute to the violation count.
2. Read the selected fields. The platform reads every field listed under fields for the current row, without any type conversion.
3. Test at least one non-NULL. The row passes if at least one of the selected fields carries a non-NULL value. It fails only when every selected field is NULL.
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).

The evaluation is symmetric across the selected fields: their order does not matter. Empty strings, zeros, and false are treated as values, so they satisfy the check. Only SQL NULL counts as missing.

NULL Handling

Any Not Null is a completeness rule, so NULL is the value it looks for. Row-level behavior:

• All selected fields NULL → the row fails.
• At least one selected field non-NULL → the row passes, even when the other selected fields are NULL.

Because a NULL in some fields (but not all) does not fail the row, Any Not Null is a weaker constraint than a per-field Not Null on every field. Use Any Not Null when the row is considered complete as long as at least one alternative is present.

Fields are inspected as-is; there is no transformation or comparison against sentinel values. If your source data uses a placeholder such as "" or "N/A" to mean "missing", either normalize it upstream with a Computed Field or add a companion rule that treats those placeholders as violations.

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).

Common uses:

• Restricting the check to a subset of the dataset (tenant_id = 42, status = 'active').
• Excluding known-incomplete legacy rows that are tracked by a separate clean-up task.
• Scoping the check to a partition (event_date >= '2025-12-01').

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 rows is expected to fail (for example, during a slow backfill). Use coverage carefully: a 0.5% tolerance can mask a real regression that happens to fall just under the threshold.

See Also