How Expected Values Checks Work

Definition

Asserts that the value in the selected field is contained in a predefined list of accepted values.

Overview

The Expected Values rule enforces a closed vocabulary on a single column. The check compares each row's value against a list configured on the rule and flags every row whose value is not in the list. Typical use cases include:

• Enforcing status enums (order_status, payment_state).
• Restricting a country, currency, or locale code to an allowed set.
• Validating short categorical fields (tier, channel, segment).
• Asserting that an array column contains only elements from a known vocabulary.

NULL values pass the check. The rule only evaluates non-NULL values against the list, so an Expected Values check on a nullable field never fires on missing values. Pair it with a Not Null check on the same field when missingness is also a violation.

Field Scope

Single: The rule evaluates one field per check.

Accepted Types

Every field type except Struct is accepted. For Date and Timestamp, the list is compared as strings against the column's rendered value; see Type Coercion and Matching Rules for the recommended formats. Array fields are evaluated element-wise, as described under Array Fields.

General Properties

The filter allows you to define a subset of data upon which the rule will operate.

It requires a valid Spark SQL expression that determines the criteria rows in the DataFrame should meet. This means the expression specifies which rows the DataFrame should include based on those criteria. Since it's applied directly to the Spark DataFrame, traditional SQL constructs like WHERE clauses are not supported.

O_TOTALPRICE > 1000

C_MKTSEGMENT = 'BUILDING'

WHERE O_TOTALPRICE > 1000

WHERE C_MKTSEGMENT = 'BUILDING'

O_ORDERPRIORITY = '1-URGENT' AND O_ORDERSTATUS = 'O'

(L_SHIPDATE = '1998-09-02' OR L_RECEIPTDATE = '1998-09-01') AND L_RETURNFLAG = 'R'

WHERE O_ORDERPRIORITY = '1-URGENT' AND O_ORDERSTATUS = 'O'

O_TOTALPRICE > 1000, O_ORDERSTATUS = 'O'

RIGHT(
    O_ORDERPRIORITY,
    LENGTH(O_ORDERPRIORITY) - INSTR('-', O_ORDERPRIORITY)
) = 'URGENT'

LEVENSHTEIN(C_NAME, 'Supplier#000000001') < 7

RIGHT(
    O_ORDERPRIORITY,
    LENGTH(O_ORDERPRIORITY) - CHARINDEX('-', O_ORDERPRIORITY)
) = 'URGENT'

EDITDISTANCE(C_NAME, 'Supplier#000000001') < 7

O_ORDERSTATUS IN (
    SELECT DISTINCT O_ORDERSTATUS
    FROM {{_qualytics_self}}
    WHERE O_TOTALPRICE > 1000
)

O_ORDERSTATUS IN (
    SELECT DISTINCT O_ORDERSTATUS
    FROM ORDERS
    WHERE O_TOTALPRICE > 1000
)

Specific Properties

Specify the predefined list of values that the field is allowed to contain.

Anomaly Types

How the Check Evaluates Values

Every Expected Values check follows the same evaluation flow:

1. Apply the filter clause. If the check has a filter set, only the rows that match the filter expression continue to the next step. Rows outside the filter are ignored and cannot cause a violation.
2. Skip NULL values. Rows where the selected field is NULL pass the check automatically and are never reported as violations.
3. Compare each non-NULL value to the list. A row passes when the value is exactly equal to one of the list entries (same type, same case, same whitespace). A row fails when the value is non-NULL and not in the list.
4. Decide Record or Shape anomaly. On a scalar field this follows coverage: at 100% coverage every failing row becomes a Record Anomaly, and below 100% coverage the violation is rolled up into a single Shape Anomaly when the failing fraction exceeds the configured threshold (see Coverage and Tolerance). On an Array field the check is evaluated at field level and always reports a Shape Anomaly (see Array Fields).

The order of operations matters: the filter is applied before the value comparison, so rows that the filter excludes cannot contribute to the violation count.

NULL Handling

NULL values pass the Expected Values check. The engine evaluates column IS NULL OR column IN (list), so a NULL is treated as compliant regardless of what is in the list. This is intentional: Expected Values asserts membership in a vocabulary, not presence.

If missing values are also a violation, pair Expected Values with a Not Null check on the same field. Together they enforce "this field must be present and must match the vocabulary."

Type Coercion and Matching Rules

The list is interpreted according to its contents and the column type:

• All entries numeric → the list is treated as a numeric list. Integers and decimals are compared numerically. 100 matches 100.0.
• All entries boolean (true, false) → the list is treated as a boolean list. The column must be of Boolean type.
• Anything else (including mixed or empty) → the list is treated as strings and the column value is compared as a string.

A few practical consequences worth knowing:

• Case-sensitive. "O" does not match "o". If you need case-insensitive matching, normalize the source data upstream or use a Satisfies Expression check with lower(field) IN (...).
• Whitespace-sensitive. " O " does not match "O". The Authored Check modal warns when an entry has leading or trailing whitespace, but it does not trim it for you.
• Type mismatches fail. If the column is integer and the list is ["O", "F", "P"] (strings), the platform cannot interpret "O" as an integer; every non-NULL row will fail the check. Keep the list's type aligned with the field's type.
• Date and Timestamp. There is no native date list type; values are compared as strings against the column's rendered form. Prefer the ISO-8601 representation that matches how your engine serializes the column (YYYY-MM-DD for Date, YYYY-MM-DD HH:MM:SS for Timestamp).

Array Fields

When the check targets an Array[String] field, the platform automatically switches to element-wise evaluation:

• The check tests every element of the array against the list, rather than the array as a whole.
• Every element of the array must be in the list. A row fails as soon as one element is outside the vocabulary, even if the other elements pass.
• An empty array passes (no elements to evaluate). A NULL array also passes (NULL handling rules above).
• The evaluation runs as a field-level check, so a failure is reported as one Shape Anomaly for the field, never as per-row Record Anomalies, whatever the coverage is set to.

Element-wise evaluation is supported only for string element lists today. Array fields with numeric or boolean lists fall through to scalar comparison and are not currently recommended. See Complex Data Types for the broader picture of how rule types interact with arrays.

The Filter Clause

The filter clause is a SQL WHERE expression applied before the value comparison. Use it to:

1. Scope the check. Restrict the vocabulary requirement to a subset of the data (for example, region = 'NA' to enforce a North-American country code vocabulary only on orders from that region).
2. Exclude legacy or pending rows. A filter such as created_at >= '2024-01-01' keeps the check from flagging values that were valid under an earlier vocabulary.

The filter is part of the check definition, so the anomaly message includes the filter expression ([filter: <expression>]) when one is set, making it explicit which slice of data was evaluated when the anomaly fired.

Coverage and Tolerance

Coverage is a fractional value between 0 and 1 that controls whether failing rows produce Record anomalies (one per row) or a single Shape anomaly (the dataset-level rollup):

• 1.0 (100%, default): every non-NULL row must match the list. Each failing row produces a Record Anomaly.
• < 1.0: the check tolerates a fraction of records failing the comparison. When the actual failing fraction exceeds the threshold, a single Shape Anomaly fires for the dataset. No Record Anomalies are produced in this mode.

Lower coverage values are useful when a small known fraction of out-of-vocabulary values is expected (a slow migration, a deprecation window, a legacy import). Use it with care: lowering coverage by 0.5% means a regression introducing failures in up to 0.5% of rows will look identical to the tolerated baseline and won't fire.

See Also