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 need you to pick a column. It works out which fields are involved from the expression itself and stores that list on the check: it is what the Source Records view highlights and what the API returns under fields.

You can replace the derived list by selecting fields in the form's optional Fields input (or sending fields through the API), for example when the expression builds a column name dynamically or a word in it happens to match a field name. The selected list is then what the check stores and highlights. It does not change what the check evaluates: the expression is evaluated the same way whichever fields are on the list. Clear the selection, or click the re-derive button next to it, to go back to the fields the expression names.

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 only when the expression evaluates to false. When it evaluates to nothing at all, the row passes (see NULL Results Pass).
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

Without an ELSE branch, a row that matches no WHEN returns nothing, and that row passes. Write ELSE FALSE when rows outside every branch should be reported, or ELSE TRUE to make the pass explicit.

NULL Results Pass¶

A row is reported only when the expression is definitely false. If the expression returns nothing, because a column it references is empty, or because a CASE has no matching branch, the row passes.

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

-- Empty values fail
discount IS NOT NULL AND price IS NOT NULL AND discount < price

-- Empty values pass (the default behavior, stated explicitly)
discount IS NULL OR price IS NULL OR 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, the message names it at the end of its first sentence, as (applied to records where <expression>). Record and Shape Anomaly messages both carry it.

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¶