Rules reference¶

Every check airflow-dq can run, with exact semantics: what it checks, its parameters, the SQL it generates, and the gotchas you'd otherwise learn in production. Sourced from the compiler and executor code — where behavior is surprising, it's documented here rather than smoothed over.

How rules become checks¶

A contract carries rules in two places:

Schema flags — required: true compiles to a notNull check, unique: true to a unique check. Both compile with severity warning (see the severity gotcha below).
Explicit quality rules — ODCS v3.1 custom blocks with engine: airflow-dq (the shape the editor emits), attached to a schema property or object:

- name: amount
  logicalType: number
  quality:
  - type: custom
    engine: airflow-dq
    implementation:
      rule: valueRange
      params:
        mustBeGreaterThan: 0
        warnIfPercentGreaterThan: 5
        failIfPercentGreaterThan: 50
    severity: blocking

ODCS library blocks (metric: <rule> + arguments) and sql blocks (query: → an expression rule) are accepted too, as is the pre-0.2 legacy dialect. Quality entries airflow-dq cannot execute (foreign engines, unknown rules) are preserved verbatim on round-trip, never silently stripped.

The compiler turns each rule into one CheckSpec → one mapped Airflow task, named <dataset>.<column>.<rule> (or <dataset>.<rule> for table-level checks; expression rules use their name param). Additionally one schema drift check is emitted per dataset (severity blocking), and slaProperties compile into sla.* checks.

Check names dedupe — last one wins

Specs are deduplicated by check name, later definitions win. That's a feature (an explicit scoped orders.user_id.notNull rule overrides the schema-derived one, letting you attach severity/tolerances/scope) — and a trap: two same-type rules on one column, or two expression rules without distinct name params, silently collapse into one check.

For row-level rules, one failing predicate selects the bad rows, and the count query, bad-row sample and quarantine are all derived from it — so they can never disagree. Table-level checks (rowCount, reconciliation, schema, max-age freshness) have no per-row predicate: they report rows_scanned=1 (or the column count for schema), a synthetic rows_failed of 0/1, and produce no sample and no quarantine.

Every row-level rule additionally accepts the cross-cutting params where (partition scoping) and tolerances (warnIfGreaterThan etc.) — documented once, below.

notNull¶

Flags rows where the column is NULL. Dimension: completeness.

Param	Required	Meaning
`where`	no	scope the check to a partition

Generated SQL (failing predicate):

SELECT count(*) AS rows_scanned,
       count(*) FILTER (WHERE col IS NULL) AS rows_failed
FROM dataset [WHERE <where>]

Gotchas

The schema-derived variant (from required: true) has severity warning — it records and alerts but never gates. Add an explicit notNull rule with severity: blocking on the same column to make it gate (same check name → it overrides the derived one).
Full-table scan on every run unless you add a where scope.

unique¶

Flags duplicated values in one column. Dimension: uniqueness.

Param	Required	Meaning
`where`	no	scope both the count and the duplicate lookup

Generated SQL — the count uses aggregate math, the sample a self-lookup:

-- count
SELECT count(*) AS rows_scanned,
       count(*) - count(DISTINCT col) AS rows_failed
FROM dataset [WHERE <where>]
-- sample / quarantine predicate
col IN (SELECT col FROM dataset [WHERE <where>] GROUP BY col HAVING count(*) > 1)

Gotchas

Single-column only. Composite uniqueness isn't expressible with this rule — use an expression rule instead.
NULLs count as failures but never appear in samples/quarantine: count(DISTINCT) ignores NULLs, so each NULL row inflates rows_failed, while the IN (...) sample predicate can never match NULL. If the column can be NULL, pair with notNull (or the required flag) so the discrepancy can't confuse triage. This is the one rule where count and sample use different math.
All copies of a duplicated value are flagged (and quarantined), not just the extras.
Schema-derived variant (unique: true) is severity warning — same gating caveat as notNull.

valueRange¶

Flags numeric values outside an open interval. Dimension: validity.

Param	Required	Meaning
`mustBeGreaterThan`	at least one of the two	lower bound (exclusive)
`mustBeLessThan`		upper bound (exclusive)

Generated failing predicate:

col <= <mustBeGreaterThan> OR col >= <mustBeLessThan>

Gotchas

Bounds are exclusive — a value exactly on a bound fails. mustBeGreaterThan: 0 rejects 0 itself. This regularly surprises contract authors (and it's why profiler-suggested ranges from observed min/max need widening before they'll pass their own data).
NULLs silently pass (NULL <= x is not true). Combine with notNull if NULL isn't valid.
Configuring neither bound is a runtime error: the check ends with status error ("No count SQL for rule ..."), not a pass.

acceptedValues¶

Flags non-NULL values outside an allow-list. Dimension: validity.

Param	Required	Meaning
`allowedValues`	yes	list of allowed literals (strings, numbers, booleans)

Generated failing predicate:

col IS NOT NULL AND col NOT IN ('USD', 'EUR', 'GBP')

Gotchas

Case-sensitive string comparison — 'usd' fails against ['USD'] (the demo seed exploits exactly this).
NULLs are explicitly ignored (notNull's job).
An empty allowedValues list yields no predicate → the check errors at runtime instead of failing every row.
Literals are rendered inline; there is no list-size cap — keep the list small (it's SQL text).

pattern¶

Flags non-NULL values that do not match a regex (format checks: emails, ID shapes). Dimension: validity.

Param	Required	Meaning
`pattern`	yes	the regular expression

Generated failing predicate (Postgres shown; this is the most dialect-divergent rule):

col IS NOT NULL AND col !~ 'pattern'

Backend	Operator	Regex flavor / matching
Postgres	`!~`	POSIX ERE, partial match
DuckDB	`NOT regexp_matches()`	RE2, partial match
Snowflake	`NOT (col REGEXP ...)`	implicitly anchored — full-string match
BigQuery	`NOT REGEXP_CONTAINS()`	RE2, partial match
Databricks	`NOT (col RLIKE ...)`	Java regex, partial match
Trino	`NOT regexp_like()`	Java/RE2 dialect, partial match

Gotchas

Partial match by default — anchor with ^...$ when you mean the whole value. Exception: Snowflake REGEXP always full-matches, so the same contract can pass on Snowflake and fail on Postgres (or vice versa). Anchoring explicitly makes the behavior uniform.
Regex syntax is per-dialect (POSIX vs RE2 vs Java) — exotic constructs (lookaheads, backreferences) are not portable across backends.
A malformed regex is a warehouse SQL error → check status error.
NULLs are ignored.

referentialIntegrity¶

Flags non-NULL values that don't exist in a referenced column (foreign-key consistency). Dimension: integrity.

Param	Required	Meaning
`refDataset`	yes	referenced table
`refColumn`	yes	referenced column

Generated failing predicate:

col IS NOT NULL AND col NOT IN (SELECT ref_col FROM ref_table WHERE ref_col IS NOT NULL)

Gotchas

Single-column FKs only.
The referenced side is never scoped — it is fully materialized in the subquery on every run. Fine for dimension tables; think twice before referencing a billion-row fact table.
Both tables must be reachable over the same backend connection.
NULL FK values pass (use notNull for mandatory FKs).

freshness¶

Is the data recent enough? Two very different modes. Dimension: freshness (or sla when compiled from slaProperties).

Param	Required	Meaning
`mode`	no	`max_age` (default) or `row_age`
`unit`	no	`day` (default) or `hour` (aliases `d/days/h/hr/hours`)
`mustBeLessThan`	no (default 1)	max allowed age in `unit`s
`mustBeAtLeast`	no, `max_age` only	minimum age — retention semantics ("oldest row must be at least N units old", used with `aggregate: min`)
`aggregate`	no, `max_age` only	`max` (default) or `min`
`asOfDate`	no	`YYYY-MM-DD` reference date — injected automatically from the run's `logical_date`; only set manually for ad-hoc evaluation
`where`	no	scope the aggregate / the rows

max_age (default) — one aggregate: the age of max(col) (or min(col)) relative to the as-of reference must be under mustBeLessThan (or at least mustBeAtLeast):

SELECT CAST(<as_of> AS date) - CAST(max(col) AS date)         AS age_days,
       FLOOR(EXTRACT(EPOCH FROM (<as_of_ts> - max(col))) / 3600) AS age_hours
FROM dataset [WHERE <where>]

Table-level: rows_scanned=1, rows_failed 0/1, no sample, no quarantine. The observed value renders as e.g. max(created_at) age: 0d 5h.

row_age (legacy, pre-0.2 behavior) — a per-row predicate: every row whose age ≥ mustBeLessThan counts as failed:

(CAST(<as_of> AS date) - CAST(col AS date)) >= <mustBeLessThan>   -- unit: day

Row-level: gets samples, quarantine and tolerances like any other row rule.

Gotchas

row_age is permanently red on any table that keeps history — every historical row older than the threshold fails, forever, unless you add a where scope. That noise is exactly why max_age became the default in 0.2 (breaking change).
Backfill-safe by design: the operator injects the run's logical_date as asOfDate, so re-running last month's interval measures age as of last month. But manual and asset-triggered runs have no logical_date — the check silently falls back to CURRENT_DATE/CURRENT_TIMESTAMP ("is it fresh now?"). Correct for monitors, a semantic shift worth knowing about for gates.
Tolerances are ignored in max_age mode — it's binary pass/fail(warn) by severity (there is one aggregate, not a row share). They apply in row_age mode.
An empty table (or all-NULL column) in max_age mode fails: no age to measure is stale.
DATE and TIMESTAMP columns both work (both sides are cast); unit: hour is exact on Postgres/DuckDB, best-effort on the experimental dialects (Snowflake DATEDIFF counts hour-boundary crossings, not elapsed hours).

rowCount¶

Table must contain more than N rows. Dimension: volume.

Param	Required	Meaning
`mustBeGreaterThan`	no (default 0)	minimum exclusive row count
`where`	no	count only a partition — this is how "did data arrive this interval?" is expressed

SELECT count(*) AS rows_scanned,
       CASE WHEN count(*) > <min> THEN 0 ELSE 1 END AS rows_failed
FROM dataset [WHERE <where>]

Gotchas

Lower bound only — there is no mustBeLessThan; catch row-count spikes with reconciliation against a reference table or an anomaly/expression rule.
rows_failed is synthetic (0/1) — tolerances are technically accepted but semantically meaningless here; don't set them.
No sample/quarantine (nothing row-shaped failed).

expression (custom SQL predicate)¶

The low-code escape hatch: you write a boolean SQL predicate that flags bad rows; count, sample, quarantine and tolerances come for free because it plugs into the same failing-predicate machinery. Dimension: validity.

Param	Required	Meaning
`expression`	yes	boolean SQL — true = row fails
`name`	strongly recommended	names the check (`<dataset>.<name>.expression` would otherwise collapse with other unnamed expressions)
`where`	no	partition scope

rule: expression
params:
  name: refunds_not_positive
  expression: status = 'refunded' AND amount > 0

Gotchas

The guard is a keyword blocklist, not a SQL parser: max 500 chars, no statement terminators or comments, and the words drop delete insert update alter create grant revoke truncate copy merge call execute into vacuum are rejected anywhere — including inside string literals (status = 'updated' is rejected; rephrase). Subselects that only read are allowed.
Rows where the predicate evaluates to NULL are not flagged (SQL three-valued logic) — guard nullable columns explicitly (col IS NULL OR col < 0) if NULL should fail.
Unrendered {{ ... }} tokens are rejected at runtime with a clear error (see template tokens).
Trust model: contract authors are trusted like dbt-test authors. The predicate runs with the backend connection's read privileges — use a read-only warehouse role.

anomaly¶

Unsupervised statistical outliers: flags rows whose value is more than sigma standard deviations from the column mean (z-score), entirely in push-down SQL — no data leaves the warehouse. Dimension: distribution.

Param	Required	Meaning
`sigma`	no (default 3)	the z-score threshold
`baselineWhere`	no	scope the mean/stddev baseline (e.g. exclude known-noisy segments)
`where`	no	scope which rows are tested

ABS(col - (SELECT avg(col) FROM dataset [WHERE <baseline>])) >
  <sigma> * (SELECT stddev_samp(col) FROM dataset [WHERE <baseline>])

Baseline resolution: explicit baselineWhere wins; otherwise, when where is set the baseline becomes NOT (where) — a leave-current-out approximation, so a bad new partition is judged against history that excludes itself and cannot mask itself by dragging the mean. With neither param the baseline is the full table (and a large bad batch can self-mask — scope it).

Gotchas

Fewer than 2 baseline rows → stddev_samp is NULL → the comparison is NULL → zero rows flagged, silently. New/tiny tables pass by construction.
Roughly 3 scans/aggregate passes per run (count + sample + quarantine each re-evaluate the subqueries) — scope large tables.
Use baselineWhere to keep the baseline honest: the demo contract excludes bot traffic (event_type <> 'bot') so bots can't widen sigma until they mask themselves.
This is the row-level half of anomaly detection. Independently, the server flags whole runs whose failing-row totals spike vs. a trailing window (persisted to dq.dq_metric_anomalies, shown as red points on the trend chart, alertable) — that one needs ≥5 runs of history and excludes the current run from its own baseline.

reconciliation¶

Cross-table consistency: an aggregate of this dataset must match the same aggregate of another table within a tolerance (bronze vs silver row counts, sum of amounts vs the ledger, ...). Dimension: consistency.

Param	Required	Meaning
`against`	yes	the reference table
`metric`	no (default `rowCount`)	`rowCount` \| `sum` \| `avg` \| `min` \| `max`
`column`	for non-rowCount metrics	the column to aggregate (both sides)
`tolerancePercent`	no (default 0)	allowed relative difference in percent
`where`	no	scope this side
`againstWhere`	no	scope the reference side (defaults to `where`)

SELECT sum(amount) FROM dataset  WHERE <where>;
SELECT sum(amount) FROM against  WHERE <againstWhere>;
-- pass iff ABS(a - b) / ABS(b) * 100 <= tolerancePercent

Gotchas

The difference is normalized by the against side — the comparison is asymmetric. Put the trusted/reference table in against.
NULL aggregates (empty side) coerce to 0, and a zero reference side divides by 1.0 — an empty reference table won't crash, but the percentage loses meaning; pair with rowCount.
Binary outcome (rows_failed 0/1): graded warn/fail tolerances do not apply — tolerancePercent + severity decide pass vs fail/warn. No sample, no quarantine.
Both tables must be reachable over the same backend connection.

schema (drift check)¶

Compiled automatically — one per dataset, severity blocking — whenever the contract has a schema. Compares live information_schema columns/types against the contract and flags:

missing required columns, and
type-family mismatches — comparison is deliberately coarse, by family (string / number / temporal / boolean), so decimal vs numeric doesn't false-positive while string↔number drift is caught.

Dimension: schema. Table-level: rows_scanned = expected column count, rows_failed = problem count, the observed value lists the problems.

SELECT column_name, data_type FROM information_schema.columns
WHERE table_schema = current_schema() AND table_name = 'orders'
-- 'analytics.orders' filters table_schema = 'analytics' instead

Gotchas

Not flagged: extra columns in the table, missing optional columns, and columns whose type maps to family "other" on either side (jsonb, arrays, enums, ... drift freely).
Schema-qualify the dataset (analytics.orders) in multi-schema warehouses; a bare name filters on the connection's current schema.
It also carries compile warnings: slaProperties entries that couldn't compile are recorded in this check's params.sla_skipped.
Not authorable in the editor (it's derived); tolerances don't apply.

SLA checks (`slaProperties`)¶

ODCS v3.1 slaProperties compile into real checks named <dataset>.sla.<property>, dimension sla, surfaced as badges on the Overview page. Severity defaults to warning; add a severity key on the entry to change it.

slaProperties:
  - property: latency          # newest row must be younger than 24h
    value: 24
    unit: h
    element: created_at
  - property: frequency        # data must arrive every interval
    value: 1
    unit: d
    element: created_at
  - property: retention        # history must reach back at least 365 days
    value: 365
    unit: d
    element: created_at

Property	Compiles to	Semantics
`latency`	`freshness` (`mode: max_age`, `mustBeLessThan: value`, `unit`)	age of `max(element)` must be under value units
`frequency`	`rowCount` (`mustBeGreaterThan: 0`, `where: element >= '{{ data_interval_start }}'`)	at least one row must exist in the current run's interval
`retention`	`freshness` (`mode: max_age`, `aggregate: min`, `mustBeAtLeast: value`)	the oldest row must be at least value units old

Gotchas

sla.frequency is interval-scoped via a template token — it requires a scheduled or backfill run. On a manual/asset-triggered run there is no data interval, the token can't render, and the check fails closed with status error (see below).
Units: d/day and h/hour only.
Anything else (availability, completeness, entries missing element/value/unit) is skipped with a compile warning, recorded in the schema check's params.sla_skipped — never silently enforced as a no-op.
element may be qualified (orders.order_date) or bare (order_date).

Severity × on_fail: what actually gates¶

Two independent knobs decide what happens when a check finds bad rows:

The rule's severity (blocking | warning) + tolerances determine the check's final status (pass / warn / fail / error).
The task's on_fail policy (fail | warn | quarantine, set per build_contract_checks() call) determines what that status does.

Final status, first:

Situation	Final status
no failing rows	`pass`
failing rows, severity `blocking`, no tolerances	`fail`
failing rows, severity `warning`, no tolerances	`warn`
any tolerance param set	graded by the thresholds — severity is ignored
executor/SQL exception (store outage, bad regex, unrendered token, ...)	`error`

Then what the status does, per on_fail:

Final status	`on_fail="fail"` (gate)	`on_fail="warn"` (monitor)	`on_fail="quarantine"`
`pass`	task succeeds	succeeds	succeeds — tolerated rows are not quarantined
`warn`	task succeeds (recorded)	succeeds	succeeds, failing rows quarantined
`fail`	task fails, downstream blocked	succeeds (recorded)	succeeds, failing rows quarantined
`error`	task fails (original exception re-raised)	succeeds (recorded as `error`)	succeeds (recorded, nothing to quarantine)

Alerts fire for fail and error in every mode — one grouped alert per (run, contract), retry-safe.

The two gating traps

warning-severity rules never gate — including every schema-derived notNull/unique. A gate full of warning rules is a gate that never closes. Make the rules that matter blocking (or give them failIf... tolerances).
Tolerances override severity. A blocking rule with only warnIf... thresholds can never reach fail — you've silently un-gated it. Conversely a warning rule with a failIf... threshold can gate. If you set tolerances, the thresholds are the whole truth.

Tolerances: graded pass/warn/fail¶

Real data is never 100% clean; a check that goes red on a single NULL gets disabled. Any row-level rule accepts, in params:

Param	Meaning
`warnIfGreaterThan`	status `warn` when failing count exceeds this
`failIfGreaterThan`	status `fail` when failing count exceeds this
`warnIfPercentGreaterThan`	status `warn` when failing share of scanned rows (%) exceeds this
`failIfPercentGreaterThan`	status `fail` when the failing share (%) exceeds this

Setting any of them switches the check to graded mode: fail if over a fail threshold, else warn if over a warn threshold, else pass. Absolute and percent thresholds combine with OR.

Gotchas

The severity-override trap above is the big one.
Nothing validates warn ≤ fail — a warn threshold above the fail threshold just never fires.
Percent thresholds are relative to rows_scanned after where scoping.
Not applicable to schema, reconciliation, and max_age freshness (binary checks); meaningless on rowCount.
Rows within tolerance (final status pass) are not quarantined.

`where` scoping and template tokens¶

Every row-level rule accepts a where param — a guarded boolean predicate that scopes rows_scanned, rows_failed, the sample and quarantine identically (they share the rendered scope, so they can never disagree). This is how you check only the current partition instead of re-scanning history:

rule: notNull
property: user_id
severity: blocking
params:
  where: "created_at >= DATE '{{ data_interval_start }}'"

Before execution, the operator substitutes these tokens inside where and baselineWhere with ISO-formatted values from the run's context (simple string substitution, not Jinja — no filters, no expressions):

Token	Value
`{{ ds }}`	the logical date, `YYYY-MM-DD`
`{{ data_interval_start }}`	ISO timestamp of the interval start
`{{ data_interval_end }}`	ISO timestamp of the interval end
`{{ logical_date }}`	ISO timestamp of the logical date

Because a backfill run carries its interval, a scoped check re-run for last month checks last month's slice — backfill-safe by construction.

Gotchas

Manual and asset-triggered runs have no data interval. A where containing {{ data_interval_start }} cannot render there, and airflow-dq fails closed: the check reports status error ("unrendered template placeholder ...") instead of silently scanning the whole table. Under on_fail="fail" that errors the gate. Templated scopes belong to scheduled/backfill runs.
where predicates pass through the same guard as expression (read-only, ≤500 chars, keyword blocklist).
Freshness uses a different mechanism for backfill safety (asOfDate injection) — its where only scopes which rows are aggregated.

Bad-row samples and quarantine¶

Samples. Whenever a row-level check fails, up to DQ_SAMPLE_LIMIT (default 10) offending rows are captured via SELECT * FROM dataset WHERE (<where>) AND (<failing predicate>) LIMIT n and stored with the result — that's what expands under a failing check in the dashboard. Full rows: if the table holds PII, the sample holds PII (in the results store and API responses). Size the limit — or your reader audience — accordingly.

Quarantine (on_fail="quarantine"). For every check whose final status is fail or warn with rows_failed > 0, up to DQ_QUARANTINE_LIMIT (default 1000) failing rows are copied — same predicate — into the results store's dq.dq_quarantine (two-connection pattern: rows are read over the warehouse connection, written over DQ_RESULTS_DSN, so it works on every backend and the warehouse needs no DQ tables). Notes:

Retry-safe: rows are keyed by dag_id|run_id|task_id|check_name; a task retry replaces its own rows instead of duplicating them.
Tolerated passes don't quarantine; table-level checks (rowCount, schema, reconciliation, max-age freshness) never quarantine (no per-row predicate).
Quarantine copies rows aside — it does not delete or rewrite anything in your table. Triage in Data Quality → Quarantine (filter, inspect payloads, bulk Mark resolved — audited with resolved_by/resolved_at) or via the API.
Quarantine writes are best-effort: if they fail, the check result records quarantine failed: ... in sample_ref and the pipeline continues.

Rules reference¶

How rules become checks¶

notNull¶

unique¶

valueRange¶

acceptedValues¶

pattern¶

referentialIntegrity¶

freshness¶

rowCount¶

expression (custom SQL predicate)¶

anomaly¶

reconciliation¶

schema (drift check)¶

SLA checks (slaProperties)¶

Severity × on_fail: what actually gates¶

Tolerances: graded pass/warn/fail¶

where scoping and template tokens¶

Bad-row samples and quarantine¶

SLA checks (`slaProperties`)¶

`where` scoping and template tokens¶