Satisfies Expression Check Examples

The situation: Line-item tax is calculated by the pricing service and must never exceed 10% of the extended price. A line above that ratio means the tax rate was applied to the wrong base, and the invoice will be overcharged. No single-field rule expresses a relationship between two columns and a factor, which is what makes this the right rule.

Check configuration

Payload

{
    "description": "The tax on a line must never exceed 10% of the extended price.",
    "rule": "satisfiesExpression",
    "fields": null,
    "container_id": 145,
    "coverage": 1,
    "filter": null,
    "properties": {
        "expression": "l_tax <= l_extendedprice * 0.10"
    },
    "tags": ["business-logic", "pricing"],
    "additional_metadata": {"jira": "DATA-10700"},
    "anomaly_message_field": null,
    "template_id": null,
    "status": "Active",
    "owner_id": 7,
    "default_anomaly_assignee_id": 12
}

Sample Data

What gets flagged

Lines 2 and 4 carry tax above 10% of their extended price: 13.3% and 15%. Lines 1 and 3 sit at 9% and pass; a line exactly at 10% would pass too, because the comparison is inclusive. Coverage is 100%, so each failing row is reported as its own Record Anomaly quoting the expression.

Flowchart

graph TD
    A["Read the row"] --> B["Evaluate<br/>l_tax <= l_extendedprice * 0.10"]
    B --> C{"Is it true?"}
    C -->|Yes| D["Row passes"]
    C -->|No, or returns nothing| E["Flag row and quote<br/>the expression in the message"]

Equivalent SQL

-- Rows the Satisfies Expression check would flag.
SELECT l.*
FROM lineitem l
WHERE NOT (l.l_tax <= l.l_extendedprice * 0.10)
   OR l.l_tax IS NULL
   OR l.l_extendedprice IS NULL;

The situation: Order status depends on priority: urgent and high-priority orders must be open, medium-priority orders may be open or pending, and the rest are unconstrained. A CASE expresses all three branches in one check, with an explicit ELSE TRUE so uncovered rows are not reported by accident.

Check configuration

Payload

{
    "description": "Urgent and high-priority orders must be open; medium-priority orders must be open or pending.",
    "rule": "satisfiesExpression",
    "fields": null,
    "container_id": 512,
    "coverage": 1,
    "filter": null,
    "properties": {
        "expression": "CASE WHEN o_orderpriority IN (\'1-URGENT\', \'2-HIGH\') AND o_orderstatus <> \'O\' THEN FALSE WHEN o_orderpriority = \'3-MEDIUM\' AND o_orderstatus NOT IN (\'O\', \'P\') THEN FALSE ELSE TRUE END"
    },
    "tags": ["business-logic", "workflow"],
    "additional_metadata": {"jira": "DATA-10718"},
    "anomaly_message_field": null,
    "template_id": null,
    "status": "Active",
    "owner_id": 7,
    "default_anomaly_assignee_id": 47
}

Sample Data

What gets flagged

Order 2 is high priority but already finished, which the first branch rejects. Order 3 is medium priority and pending, allowed by the second branch. Order 4 is low priority, so no branch applies and the explicit ELSE TRUE lets it pass. Without that ELSE, order 4 would return nothing and be reported.

Flowchart

graph TD
    A["Read the row"] --> B{"Priority is<br/>1-URGENT or 2-HIGH?"}
    B -->|Yes| C{"Status is O?"}
    C -->|No| F["Flag row"]
    C -->|Yes| E["Row passes"]
    B -->|No| D{"Priority is 3-MEDIUM?"}
    D -->|Yes| G{"Status is O or P?"}
    G -->|No| F
    G -->|Yes| E
    D -->|No| E

Equivalent SQL

-- Rows the check would flag.
SELECT o.*
FROM orders o
WHERE (o.o_orderpriority IN ('1-URGENT', '2-HIGH') AND o.o_orderstatus <> 'O')
   OR (o.o_orderpriority = '3-MEDIUM' AND o.o_orderstatus NOT IN ('O', 'P'));

The situation: A discount audit runs while a pricing cleanup is under way. A line's discounted price must stay at or above half the table's average extended price, a floor that catches discounts entered as an absolute amount instead of a fraction. The comparison needs an aggregate over the same dataset, which the built-in self-reference variable provides, and coverage is lowered to 98% while the cleanup finishes.

Check configuration

Payload

{
    "description": "A line's discounted price must be at least half the table's average extended price.",
    "rule": "satisfiesExpression",
    "fields": null,
    "container_id": 733,
    "coverage": 0.98,
    "filter": null,
    "properties": {
        "expression": "l_extendedprice * (1 - l_discount) >= 0.5 * (SELECT AVG(l_extendedprice) FROM {{_qualytics_self}})"
    },
    "tags": ["pricing", "audit"],
    "additional_metadata": {"jira": "DATA-10736"},
    "anomaly_message_field": null,
    "template_id": null,
    "status": "Active",
    "owner_id": 7,
    "default_anomaly_assignee_id": 41
}

Sample Data

What gets flagged

The average extended price across the four rows is 13,750, so the floor is 6,875. Lines 1 and 3 clear it with discounted prices of 9,500 and 18,000. Lines 2 and 4 hold an absolute amount in a column meant for a fraction, a mistake from the cleanup, which turns l_extendedprice * (1 - l_discount) sharply negative and puts them far below the floor. Note that the aggregate reads only l_extendedprice, the column the cleanup did not touch: averaging the suspect column would move the threshold along with the corruption. Only 50% of the rows pass, below the 98% 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["Read the row"] --> B["Evaluate the expression,<br/>including the subquery over<br/>the scanned data"]
    B --> C{"Is it true?"}
    C -->|Yes| D["Row passes"]
    C -->|No| E["Count the row as failing"]
    D --> F{"Passing fraction<br/>at or above 98%?"}
    E --> F
    F -->|Yes| G["Check passes"]
    F -->|No| H["Shape Anomaly for the dataset"]

Equivalent SQL

-- Rows the check would flag.
SELECT l.*
FROM lineitem l
WHERE NOT (
    l.l_extendedprice * (1 - l.l_discount) >= 0.5 * (SELECT avg(l_extendedprice) FROM lineitem)
);