Expected Values Best Practices

Guidelines for getting reliable signal from Expected Values checks while keeping the noise (and the maintenance) low.

Use it for vocabularies, not for formats

Expected Values is the right tool when the accepted values are a small, enumerable set that changes rarely: status codes, currencies, country codes, order types. When the accepted values follow a structure rather than a list (SKU-1234, an email, a phone number), use Matches Pattern instead. A list that grows past a few dozen entries is usually a sign the rule should be a pattern or a lookup against a reference table with Exists In.

Keep the list's type aligned with the field's type

The list is interpreted as numeric when every entry is numeric, boolean when every entry is a boolean, and as strings otherwise. A string list on an integer column fails every non-NULL row, because the values cannot be interpreted as numbers. When the check suddenly flags everything, this mismatch is the first thing to look at.

Remember the comparison is exact

Matching is case-sensitive and whitespace-sensitive: "o", " O ", and "O" are three different values. The check form warns about leading and trailing whitespace in the list, but it does not trim it. When the source data is inconsistent, normalize it upstream (or with a Computed Field). When you need case-insensitive matching, use a Satisfies Expression check with lower(field) IN (...).

Pair with Not Null when the value is mandatory

NULL values pass. Expected Values asserts that present values come from the list; it does not require the field to be populated. When the field must also have a value, add a Not Null check on the same field.

Combine with Required Values for full vocabulary control

Expected Values asserts that the column contains nothing outside the list. Required Values asserts that the column contains at least the listed values. They are mirror images: use both when the vocabulary must be exactly the intended set, with nothing missing and nothing extra.

Keep coverage at 100% unless a known backlog exists

At 100% coverage every failing row is reported as a Record Anomaly, which tells you exactly which values are out of vocabulary. Below 100% the check reports a single Shape Anomaly only when the failing fraction crosses the tolerance, and no per-row detail is produced. Lower coverage only when a known fraction of legacy values is expected, and revisit the setting once the cleanup lands.

Scope with a filter instead of loosening coverage

When a vocabulary only applies to part of the table (one product line, one region, rows created after a migration), express that with a filter clause rather than by lowering coverage. The filter removes the out-of-scope rows from evaluation entirely, and the expression is echoed in every anomaly message so the evaluated scope stays visible.

Revisit the list when the business adds a value

An out-of-vocabulary anomaly means one of two things: the data is wrong, or the list is stale. When a new legitimate status appears, updating the check is part of shipping that change. Recording the source of truth for the vocabulary in the check's description (the enum in the application, the reference table, the governance document) makes that update obvious to whoever triages the anomaly.

Route the anomalies to the right people

Out-of-vocabulary values usually come from an application or an integration, not from the warehouse. Set an Anomaly Assignee from the team that owns that producer, and tag the check (vocabulary, reference-data) so related checks are easy to find.