# Satisfies Expression Check Examples

Source: https://userguide.qualytics.io/data-quality-checks/satisfies-expression/deep-dive/examples/

Three real-world scenarios that show how the Satisfies Expression check is typically used in production: enforcing a ratio between two columns, applying a condition only where it makes sense with `CASE`, and comparing a row against an aggregate of the same dataset. The first two run at 100% coverage and report Record Anomalies; the third lowers coverage and reports a Shape Anomaly instead.

**Tab: A Ratio Between Two Columns**

**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**

| Field | Value |
| --- | --- |
| Rule | Satisfies Expression |
| Table / File | `lineitem` |
| Filter | *(none)* |
| Custom Anomaly Description | Off |
| Expression | `l_tax <= l_extendedprice * 0.10` |
| Coverage | 100% |
| Owner | *(check creator)* |
| Anomaly Assignee | *(Pricing Engineering)* |
| Description | The tax on a line must never exceed 10% of the extended price. |
| Tags | `business-logic`, `pricing` |
| Additional Metadata | `jira: DATA-10700` |
| Status | Active |

**Payload**

```json
{
    "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**

| l_orderkey | l_extendedprice | l_tax |
| --- | --- | --- |
| 1 | 10000 | 900 |
| 2 | 15000 | 2000 |
| 3 | 20000 | 1800 |
| 4 | 10000 | 1500 |

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

> **Example: Record Anomaly**
>
> This record does not satisfy the expression: l_tax <= l_extendedprice * 0.10

**Flowchart**

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

**Equivalent SQL**

```sql
-- Rows the Satisfies Expression check would flag.
SELECT l.*
FROM lineitem l
-- A row where either column is empty passes.
WHERE NOT (l.l_tax <= l.l_extendedprice * 0.10);
```

**Tab: A Condition That Only Applies to Some Rows**

**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**

| Field | Value |
| --- | --- |
| Rule | Satisfies Expression |
| Table / File | `orders` |
| Filter | *(none)* |
| Custom Anomaly Description | Off |
| Expression | *(the `CASE` statement below)* |
| Coverage | 100% |
| Owner | *(check creator)* |
| Anomaly Assignee | *(Order Platform team)* |
| Description | Urgent and high-priority orders must be open; medium-priority orders must be open or pending. |
| Tags | `business-logic`, `workflow` |
| Additional Metadata | `jira: DATA-10718` |
| Status | Active |

**Payload**

```json
{
    "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**

| o_orderkey | o_orderpriority | o_orderstatus |
| --- | --- | --- |
| 1 | 1-URGENT | O |
| 2 | 2-HIGH | F |
| 3 | 3-MEDIUM | P |
| 4 | 5-NOT URGENT | F |

**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 still pass; the `ELSE TRUE` only makes that intent visible.

> **Example: Record Anomaly**
>
> This record does not satisfy the 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

**Flowchart**

```mermaid
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**

```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'));
```

**Tab: Comparing a Row Against the Dataset**

**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**

| Field | Value |
| --- | --- |
| Rule | Satisfies Expression |
| Table / File | `lineitem` |
| Filter | *(none)* |
| Custom Anomaly Description | Off |
| Expression | *(the subquery below, using the self-reference variable)* |
| Coverage | 98% |
| Owner | *(check creator)* |
| Anomaly Assignee | *(Pricing Engineering)* |
| Description | A line's discounted price must be at least half the table's average extended price. |
| Tags | `pricing`, `audit` |
| Additional Metadata | `jira: DATA-10736` |
| Status | Active |

**Payload**

```json
{
    "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**

| l_orderkey | l_extendedprice | l_discount |
| --- | --- | --- |
| 1 | 10000 | 0.05 |
| 2 | 15000 | 14200 |
| 3 | 20000 | 0.10 |
| 4 | 10000 | 11500 |

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

> **Example: Shape Anomaly**
>
> 2 of 4 asserted records do not satisfy the expression: l_extendedprice * (1 - l_discount) >= 0.5 * (SELECT AVG(l_extendedprice) FROM {{_qualytics_self}})

**Flowchart**

```mermaid
graph TD
    A["Read the row"] --> B["Evaluate the expression,<br/>including the subquery over<br/>the scanned data"]
    B --> C{"Is it false?"}
    C -->|No, or returns nothing| D["Row passes"]
    C -->|Yes| 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**

```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)
);
```

## See Also

- **Best Practices**

  Guidelines for writing expressions that stay readable, handling NULLs deliberately, and knowing when a dedicated rule is better.

  [Best Practices](https://userguide.qualytics.io/data-quality-checks/satisfies-expression/deep-dive/best-practices/)
- **Permissions**

  The team permission each action needs: view, create, edit, archive, restore, and delete.

  [Permissions](https://userguide.qualytics.io/data-quality-checks/satisfies-expression/deep-dive/permissions/)
- **How It Works**

  The complete reference: definition, field scope, what the expression must return, check variables, NULL handling, filter behavior, and coverage.

  [How It Works](https://userguide.qualytics.io/data-quality-checks/satisfies-expression/deep-dive/how-it-works/)
- **Anomaly Reporting**

  The anomaly messages the check produces, what the numbers mean, Source Records behavior, and Custom Anomaly Description.

  [Anomaly Reporting](https://userguide.qualytics.io/data-quality-checks/satisfies-expression/deep-dive/anomaly-reporting/)

*Last updated: October 19, 2026.*
