# Dqx Define Checks

> Create DQX quality rules (checks) for a PySpark DataFrame or Delta table. Use when the user asks to "add a DQX check", "define a data quality rule", "validate that column X is not null / unique / in a set", or wants checks expressed in YAML/JSON for storage. Covers DQRowRule, DQDatasetRule, DQForEachColRule, built-in check_funcs, filters, user_metadata, custom SQL/Python checks, and the declarative metadata form.

- Skill: `databrickslabs/dqx-define-checks` (Agent Skill)
- Install (CLI): `npx skillmds@latest add databrickslabs/dqx-define-checks`
- Raw SKILL.md: https://api.skillmd.com/api/skills/databrickslabs/dqx-define-checks/raw
- Safety review: pending
- Works with: Claude Code, Claude.ai, OpenAI Codex
- Category: Data & Analytics
- Author: databrickslabs (https://skillmd.com/u/databrickslabs)
- Updated: 2026-09-17
- Page: https://skillmd.com/skills/databrickslabs/dqx-define-checks

---


# DQX — Define quality checks

DQX rules come in two interchangeable forms. **Pick based on where the checks will live.**

- **DQX classes** (`DQRowRule`, `DQDatasetRule`, `DQForEachColRule`) — use when checks are authored in code next to the pipeline. Static typing + IDE autocomplete.
- **Dict / YAML / JSON metadata** — use when checks are loaded from a file, workspace path, volume, or Delta table. Required for the `apply_checks_by_metadata*` path.

Every check has a **`criticality`** of `error` (failing row quarantined) or `warn` (failing row passes but flagged). Default is `error`.

## Minimal — class form

```python
from databricks.labs.dqx import check_funcs
from databricks.labs.dqx.rule import DQRowRule, DQDatasetRule, DQForEachColRule

checks = [
    # row-level: one column
    DQRowRule(
        name="col3_is_not_null",
        criticality="warn",
        check_func=check_funcs.is_not_null_and_not_empty,
        column="col3",
    ),

    # same check across many columns
    *DQForEachColRule(
        columns=["col1", "col2"],
        criticality="error",
        check_func=check_funcs.is_not_null,
    ).get_rules(),

    # dataset-level: uniqueness across a composite key
    DQDatasetRule(
        criticality="error",
        check_func=check_funcs.is_unique,
        columns=["order_id", "line_item_id"],
    ),
]
```

## Minimal — metadata form (YAML)

Load into Python via `yaml.safe_load(...)`, then pass the resulting `list[dict]` to any `apply_checks_by_metadata*` call, or save through a storage config (see `dqx-storage`).

```yaml
- name: col3_is_not_null
  criticality: warn
  check:
    function: is_not_null_and_not_empty
    arguments:
      column: col3

- criticality: error
  check:
    function: is_not_null
    for_each_column: [col1, col2]

- criticality: error
  check:
    function: is_unique
    arguments:
      columns: [order_id, line_item_id]
```

## Common variants

- **Filtered check** — evaluate only when a predicate holds: add `filter="col1 < 3"` (class) or `filter: "col1 < 3"` (YAML).
- **Positional args** — `check_func_args=[[1, 2]]`; **keyword args** — `check_func_kwargs={"allowed": [1, 2]}`.
- **Struct / map / array element** — use `F.try_element_at(...)` or dotted path (`col7.field1`) as the `column` value.
- **User metadata** — annotate the rule with a `user_metadata` dict (e.g. `{"check_type": "completeness"}`) that flows into the result struct.
- **Custom check** — pass any function that returns a PySpark `Column` as `check_func`. For inline SQL, use the fallback section below — only after confirming no built-in fits.
- **Aggregate dataset-level** — `is_aggr_not_greater_than`, `is_aggr_not_less_than`, `is_aggr_equal`, `is_aggr_not_equal`; supply `aggr_type` (`count`, `avg`, `stddev`, `percentile`, `count_distinct`…), optional `group_by`, and `limit`.
- **Uniqueness dataset-level** — `is_unique`, with `columns`, `nulls_distinct` (bool), and optional `row_filter`. Not an aggregate check — no `aggr_type`.

Full reference: <https://databrickslabs.github.io/dqx/docs/reference/quality_checks>.

## Fallback: custom SQL

**Search `check_funcs` first** — the built-ins cover null/empty, range, set membership, regex, referential, aggregate, uniqueness, schema, freshness, comparison, and outlier cases with typed error messages and tested edge handling. Drop down to SQL only when no built-in fits.

- `sql_expression` — **row-level** SQL boolean expression. Use when one row's validity depends on its own columns.
- `sql_query` — **dataset-level** SQL query against `{{ input_view }}`. Use for cross-row aggregates, joins to reference DataFrames, or anything needing `GROUP BY`. Queries are validated by `is_sql_query_safe()` — read-only `SELECT`, no DDL/DML.

```yaml
# row-level: SQL expression evaluated per row
- name: amount_positive_or_refunded
  criticality: error
  check:
    function: sql_expression
    arguments:
      expression: amount > 0 OR refunded = true
      msg: amount must be positive unless refunded

# dataset-level: SQL query, joined back to rows via merge_columns
- name: order_total_matches_lines
  criticality: error
  check:
    function: sql_query
    arguments:
      query: |
        SELECT order_id,
               SUM(line_amount) <> order_total AS condition
        FROM {{ input_view }}
        GROUP BY order_id, order_total
      merge_columns: [order_id]    # row-level: joins back per order_id
      condition_column: condition  # column in query output; true = fail
      # omit merge_columns for dataset-level (one verdict applies to every row)
```

For the equivalent class form, use `DQRowRule(check_func=check_funcs.sql_expression, check_func_kwargs={"expression": "..."})` or `DQDatasetRule(check_func=check_funcs.sql_query, check_func_kwargs={...})`.

## Converting between forms

```python
from databricks.labs.dqx.checks_serializer import serialize_checks, deserialize_checks
checks_metadata = serialize_checks(checks)              # classes → list[dict]
checks_classes  = deserialize_checks(checks_metadata)   # list[dict] → classes
```

## Validation before apply

Catch syntax errors without running the pipeline:

```python
from databricks.labs.dqx.engine import DQEngine
status = DQEngine.validate_checks(checks)   # raises / returns ValidationStatus
```

## Do / Don't

- **Do** give each rule a stable, snake_case `name` — it ends up in result columns and dashboards.
- **Do** put shared rules in a YAML/JSON/Delta file and load them (see `dqx-storage`) — classes are fine for a handful, metadata scales.
- **Don't** use a regex check for null / empty / range / referential / aggregate cases — DQX has a built-in for each; see `check_funcs`.
- **Don't** reach for `sql_expression` / `sql_query` when a built-in covers the case — they bypass typed error messages and security guards. Search `check_funcs` first.
- **Don't** put side effects in a custom `check_func` — it must return a `Column` expression only.

Canonical docs: <https://databrickslabs.github.io/dqx/docs/guide/quality_checks_definition>.