How Satisfies Expression Checks Work

Definition

Evaluates the given expression, any valid Spark SQL, for each record.

Overview

The Satisfies Expression rule runs a Spark SQL expression once per row. The expression must evaluate to true or false, and a row passes when it evaluates to true.

Because the expression can reference any column of the container, combine them, branch with CASE, or call SQL functions, the rule covers logic that no dedicated rule expresses: relationships between three fields, conditions that only apply to part of the data, or a formula the business defines rather than the schema.

That flexibility is also its cost. A dedicated rule states its intent in its name, validates its inputs, and produces a message anyone can read. Reach for this rule when no other one fits, not as a first choice.

Typical use cases:

• Validate business logic that spans several fields at once.
• Apply a condition only to the records where it makes sense, using CASE.
• Express a formula or threshold the business defines rather than the schema.

Field Scope

Calculated: the rule does not pick a column. It works out which fields are involved from the expression itself, so the form shows no field picker. Whatever columns the expression names are the columns the check reads, and that derived list is stored on the check: it is what the Source Records view highlights and what the API returns under fields.

Anomaly Types

Evaluation Flow

Every Satisfies Expression 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. Resolve any check variables. Placeholders such as {{batch_id}} and the built-in ones are replaced with their values before the expression runs.
3. Evaluate the expression on the row. The row passes when the expression evaluates to true. It fails when the expression evaluates to false, and also when it evaluates to nothing at all (see NULL Results Fail).
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 Expression Must Return True or False

The rule tests a condition, not a value. total_amount is not a valid expression; total_amount > 0 is. A CASE statement is valid as long as every branch returns true or false:

CASE
    WHEN priority IN ('1-URGENT', '2-HIGH') AND status <> 'O' THEN FALSE
    WHEN priority = '3-MEDIUM' AND status NOT IN ('O', 'P') THEN FALSE
    ELSE TRUE
END

Writing the ELSE TRUE branch explicitly matters: without it, every row that matches no WHEN returns nothing, and those rows fail.

NULL Results Fail

This is the behavior that surprises people most, and it is the opposite of most other rules. A row passes only when the expression is definitely true. If the expression returns nothing, because a column it references is empty, or because a CASE has no matching branch, the row is reported.

SQL comparisons involving an empty value return nothing rather than false, so discount < price reports every row where either column is empty. Decide what you want and say it:

-- Empty values pass
discount IS NULL OR price IS NULL OR discount < price

-- Empty values fail (the default behavior, stated explicitly)
discount IS NOT NULL AND price IS NOT NULL AND discount < price

Check Variables and the Scanned Data

The expression is resolved before it runs, so any Check Variable defined for the scan can appear in it as {{variable_name}}.

Three variables are always available:

The first is the useful one day to day: it lets an expression compare a row against an aggregate of the same dataset.

l_discount <= (SELECT MAX(l_discount) FROM {{_qualytics_self}})

Every Row Runs the Expression

The expression is evaluated once per row that passes the filter. A subquery inside it therefore runs in that context too, which makes an expression with a correlated subquery far more expensive than a simple comparison.

When a check is slow, narrow it with a Filter Clause first: the filter runs before the expression, so scoping the rows scopes the work.

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

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 failures is expected. Use coverage carefully: a 0.5% tolerance can mask a real regression that happens to fall just under the threshold.

See Also