--- name: data-validation-rules description: > Write, audit and place data validation rules that catch bad data before a human does, across pandas/Pandera, Great Expectations, dbt tests, Pydantic and SQL constraints. Covers the six quality dimensions (completeness, validity, uniqueness, consistency, accuracy, timeliness), which rules are worth writing, the layer each belongs in, severity (warn versus block), NULL semantics that silently defeat CHECK and UNIQUE, dynamic thresholds that avoid alert fatigue, and returning the offending rows. Use it when someone says "validate this data", "add data quality checks", "write a schema", "data contract", "Pandera schema", "Great Expectations suite", "dbt tests", "not_null", "accepted_values", "CHECK constraint", "my test didn't catch it", or "which checks should I add". Do NOT use it to fix bad values once found (see handling-missing-data, duplicate-detection, outlier-detection and the other cleaning skills), to validate a statistical experiment or model, or for app form validation beyond the data layer. license: Free to use and adapt under CC BY 4.0, with credit to Afeez Adebayo. Link afeezadebayo.com, https://www.linkedin.com/in/afeez-adebayo/ and this skill's page at https://afeezadebayo.com/skills-md/data/data-cleaning-preparation/data-validation-rules/. --- # Data validation rules ## When to use this, and when not to Use this skill when you need to decide what to assert about a dataset and how to enforce it, so that a broken assumption is caught by a rule instead of by a stakeholder reading a wrong number. The triggers are a request to validate data, add data quality checks, write a schema or a data contract, set up Pandera, Great Expectations or dbt tests, add SQL constraints (`CHECK`, `NOT NULL`, `UNIQUE`, a foreign key), write a Pydantic model for incoming records, or work out why an existing test let a bad value through. Files that signal the work include a dbt `schema.yml`, a `great_expectations/` directory, a Pandera schema, a Pydantic `BaseModel`, or a `CREATE TABLE` with constraints. Do not use it to repair the values once a rule has flagged them. Filling gaps is `handling-missing-data`, repeated rows are `duplicate-detection`, extreme values are `outlier-detection`, messy strings are `text-standardisation`, dates are `date-parsing`, and wrong types are `data-type-conversion`. This skill flags and blocks; it does not clean. It is also not for validating a statistical experiment or a trained model (that is cross-validation and experiment design), nor for form validation inside an application beyond the data layer. The core idea: a validation rule encodes one assumption about the data so a machine checks it on every load. The value of a rule is not that it runs, but that someone acts when it fails. So write the rules whose failure would actually cost you, place each where it belongs, make each one show the offending rows, and keep the set small enough that every failure gets looked at. A suite nobody reads is worse than no suite, because it manufactures the feeling of safety without the fact of it. ## Inputs the agent must have before starting 1. The schema and the meaning of each field. For every column you plan to check: its type, the range or set of allowed values, the format, and whether a blank is legal. A rule written without knowing what the column means will either miss real errors or reject good data. 2. Which fields are critical. The primary key, the join keys, and the columns that feed money, health, legal or employment decisions. These get rules first; a wide, shallow suite on unimportant columns is how effort is wasted. 3. Where the data is validated and with which tool. At the point of entry (SQL constraints), in the pipeline (dbt, Great Expectations, Pandera), or at an application boundary (Pydantic). The right rule and the right syntax depend on the layer. 4. What should happen on failure. Whether a violation should block the pipeline or only warn, and who receives the alert. A rule with no owner and no consequence is noise. 5. What is already validated upstream. If ingestion already asserts `not_null` on a key, repeating it in three transform models adds cost and no coverage. Ask before adding a rule that may already exist elsewhere. If any of these is unknown, ask before writing rules. Guessing that a column is never null, or that a blocking failure is acceptable, either lets bad data through or halts a production pipeline on a false alarm. ## The workflow, step by step ### 1. Choose what to validate from the cost of being wrong Do not validate everything. Start from the question "what breaks, and who pays, if this value is wrong?" Put a uniqueness and a not-null rule on every primary key, which is the one assertion worth making on every table (dbt documentation). Then add rules where a violation would reach a dashboard, corrupt a model, or change a decision about a real person. Decision rule: if you cannot name what breaks when a rule fails, do not write that rule. "When everything's important, nothing is" (towardsdatascience, "Your data quality checks are worth less than you think"). ### 2. Name the dimension each rule covers Every rule belongs to one of the quality dimensions, and naming it keeps the suite balanced instead of ten variants of the same check (IBM, "What are data quality dimensions?"): - **Completeness:** required values are present. Maps to `not_null`, the percentage of required fields populated. - **Validity:** values follow the rules, formats and ranges. Maps to type checks, range checks (`price >= 0`), format or `pattern`, and `accepted_values`. - **Uniqueness:** each record is distinct. Maps to `unique` and primary keys. - **Consistency:** values agree across columns and systems (a `discount` never exceeds `price`; the same customer has one country). - **Accuracy:** values match a trusted source. Usually a reconciliation against a system of record, not a cheap in-table check. - **Timeliness:** data is current. Maps to a freshness check on a load timestamp and a volume check on row count. Cover the dimensions that matter for the asset before adding depth to any one of them. ### 3. Place each rule in the earliest layer that can own it - **Point of entry (SQL `CHECK`, `NOT NULL`, `UNIQUE`, `FOREIGN KEY`).** Enforced on every insert and update, so bad data never lands. The limit: a `CHECK` cannot reference other rows or other tables, so cross-row and cross-table logic must use `UNIQUE`, `FOREIGN KEY` or a trigger (PostgreSQL documentation, §5.5). - **Pipeline (dbt, Great Expectations, Pandera).** Run at transform or read time against a batch. Best for cross-table relationships, aggregate checks, and anything the warehouse computes. - **Application boundary (Pydantic).** Validates each record as it enters the system, before it is ever written. Decision rule: validate each assumption once, at the earliest layer that can enforce it. Running the same `not_null` at ingestion and again in two transform models adds compute and maintenance without catching anything new (getdbt, "Understanding data validation"; towardsdatascience). ### 4. Make every rule return the offending rows A rule that reports only pass or fail cannot be acted on. Write rules that surface the records that broke them: a dbt data test is a `select` that returns the failing rows, Great Expectations produces validation results per expectation, and Pandera with `lazy=True` raises `SchemaErrors` collecting every failure rather than stopping at the first. Turn on failing-row storage where the tool offers it (`dbt test --store-failures`) so debugging starts from the actual bad data, not from a red tick. ### 5. Get NULL semantics right, because this is where rules silently leak The most common silent hole in a validation suite is a rule that looks correct and quietly ignores nulls. - A SQL `CHECK` passes when its expression evaluates to NULL, not only when it is true. `CHECK (price > 0)` therefore accepts a NULL `price`, because `NULL > 0` is unknown. To forbid the null, add an explicit `NOT NULL` (PostgreSQL documentation). - `UNIQUE` treats two NULLs as distinct by default, so several rows with a NULL in the constrained column are all allowed. Use `NULLS NOT DISTINCT`, or `NOT NULL`, when a single NULL is the most you want (PostgreSQL documentation). - A foreign key under the default `MATCH SIMPLE` is not enforced for a row whose referencing column is NULL. Declare the column `NOT NULL` if the reference must always hold. - In Pandera, a column rejects nulls unless you set `nullable=True`, so state nullability on purpose rather than relying on the default. Decision rule: for every column, decide whether NULL is legal and write the rule that enforces that decision, because "greater than zero" and "not null" are two separate assertions and one does not imply the other. ### 6. Set severity deliberately: warn or block, with thresholds Not every failure should stop the pipeline. Block on primary-key violations, broken referential integrity, and anything feeding money, health, legal or employment outcomes. Warn on softer signals such as a volume dip or a slow freshness breach, so the pipeline keeps moving while a human looks. dbt exposes this through `severity: warn` or `error`, and custom thresholds through `error_if` and `warn_if`; calibrate per rule rather than setting one global strictness (getdbt). Decision rule: a rule allowed to halt production must be one you would wake someone up for. Everything else warns. ### 7. Keep thresholds robust, not brittle Static per-value thresholds are the main source of false positives, and a monitor that cries wolf gets silenced. In the Instagram ML study, a completeness-drop alert set at a fixed 30% produced so many false positives that engineers turned it off, and tuning per-feature thresholds took months and did not transfer between datasets (Shankar, Fawaz, Gyllstrom and Parameswaran, "Moving Fast With Broken Data", Meta and UC Berkeley). For volume and distribution checks, prefer a dynamic baseline, such as a z-score over a rolling window that absorbs weekday and seasonal patterns, over a hardcoded constant. Reserve hard limits for true invariants that cannot drift: a probability in [0, 1], a non-negative count, a date that cannot be in the future (IBM's validity example). ### 8. Deduplicate and prune the suite A large suite is not a good suite. Review long-failing tests on a schedule and fix or remove them, because a wall of permanent red teaches people to ignore all of it (the broken-windows effect in data testing; mikkeldengsoe, "The State of Data Testing in 2024"). Route any alert a team cannot act on to a periodic report rather than a page. In dbt's 2024 analytics-engineering survey, 57% of respondents named alert overload, long-failing tests and poor data quality among their top obstacles, so fewer, sharper rules beat more rules. ### 9. Verify the rules themselves A validation rule is code and can be wrong. Run each rule against known-good data and confirm it passes, then against a seeded violation and confirm it fails and names the bad rows. Check that NULLs behave the way you intended (step 5), that a blocking rule actually blocks, and that failing-row output works. Record coverage: which keys are covered, which dimensions each critical table has, and which rules are warn versus block. A rule that has never been shown to fail on bad data has not been tested. ## Worked example, end to end An `orders` table needs validation before it feeds a revenue dashboard. The quick approach adds two rules and calls it done: ```sql amount numeric CHECK (amount > 0), order_id integer UNIQUE ``` This leaks in four ways. `CHECK (amount > 0)` passes a NULL `amount`, because `NULL > 0` is unknown. `UNIQUE` on `order_id` allows many rows with a NULL id, because NULLs are treated as distinct. `status` has no rule, so a typo like `"shippd"` flows straight to the dashboard. And nothing checks freshness, so a stalled load shows yesterday's revenue as if it were today's. The corrected set names a consequence for each rule, places them at the staging layer, and stores failing rows. In dbt: ```yaml models: - name: stg_orders columns: - name: order_id data_tests: [unique, not_null] # key: blocks; dedupe and joins depend on it - name: amount data_tests: - not_null # completeness: blocks - dbt_utils.accepted_range: arguments: {min_value: 0} # validity: amount >= 0 - name: status data_tests: - accepted_values: arguments: values: ['placed','shipped','completed','returned'] # validity - name: customer_id data_tests: - relationships: arguments: {to: ref('stg_customers'), field: id} # consistency / integrity data_tests: - dbt_utils.recency: config: {severity: warn} # timeliness: warn if loaded_at is stale ``` The same assumptions as a Pandera schema, for a pandas pipeline: ```python import pandera.pandas as pa schema = pa.DataFrameSchema( { "order_id": pa.Column(int, nullable=False, unique=True), "amount": pa.Column(float, pa.Check.ge(0), nullable=False), "status": pa.Column(str, pa.Check.isin(["placed","shipped","completed","returned"])), "customer_id": pa.Column(int, nullable=False), }, strict=True, # reject unexpected columns coerce=False, # do not silently cast; a wrong type is a finding ) schema.validate(df, lazy=True) # collect all failures, not just the first ``` Verifying, each rule fails on a seeded bad row and names it: a NULL `amount` now fails `not_null`, a duplicate `order_id` fails `unique`, `"shippd"` fails `accepted_values`, an `amount` of `-5` fails the range, and a stale load warns without blocking. Every rule is tied to a named consequence, placed once at staging, and the freshness check warns rather than halting a dashboard refresh over a late file. ## Variations you will meet in practice - **Point of entry versus warehouse.** A transactional database can enforce `NOT NULL`, `CHECK` and `FOREIGN KEY` so bad data never lands, while a warehouse usually validates on read with dbt or Great Expectations. Push an invariant as far upstream as the architecture allows. - **Record-level versus aggregate.** Spot-critical data such as financial postings or sensor readings may need record-level handling, for example a dead-letter queue or a flag on each bad row. For data that is only directionally important, such as hand-entered CRM fields, monitoring aggregate metadata (row counts, distinct key counts, min and max, unexpected enumerated values) costs far less and still catches process failures (towardsdatascience). - **Cross-table integrity.** A `CHECK` cannot look at another table, so referential rules belong in a `FOREIGN KEY` or a dbt `relationships` test, not a column check (PostgreSQL documentation; dbt). - **Nullable dtypes.** With pandas nullable types the marker is `pd.NA`, and a foreign key under `MATCH SIMPLE` is skipped when the referencing column is NULL; make such columns `NOT NULL` if the link must always hold. - **Schema evolution.** Rules fall out of date as sources and business logic change. Keep validation code in version control and review it in pull requests like any other code, so a dropped column or a new status value updates the rule rather than silently breaking it (getdbt). ## Templates you can use verbatim ```sql -- SQL point-of-entry constraints: note the explicit NOT NULL beside every CHECK CREATE TABLE orders ( order_id integer PRIMARY KEY, -- unique + not null customer_id integer NOT NULL REFERENCES customers (id), -- integrity, enforced because NOT NULL amount numeric NOT NULL CHECK (amount >= 0), -- completeness + validity status text NOT NULL CHECK (status IN ('placed','shipped','completed','returned')), email text UNIQUE NULLS NOT DISTINCT, -- at most one NULL email loaded_at timestamptz NOT NULL ); ``` ```python # Pydantic v2 record validation at an application boundary from pydantic import BaseModel, Field, field_validator from typing import Literal class Order(BaseModel): order_id: int customer_id: int amount: float = Field(ge=0) # validity: non-negative status: Literal["placed","shipped","completed","returned"] email: str | None = Field(default=None, pattern=r"^[^@]+@[^@]+\.[^@]+$") @field_validator("amount") @classmethod def not_nan(cls, v: float) -> float: if v != v: # NaN check raise ValueError("amount must be a real number") return v ``` ```yaml # Great Expectations: a small, named suite (expectation names are verifiable) # expect_column_values_to_not_be_null(column="order_id") # expect_column_values_to_be_unique(column="order_id") # expect_column_values_to_be_between(column="amount", min_value=0) # expect_column_values_to_be_in_set(column="status", # value_set=["placed","shipped","completed","returned"]) ``` ## Failure modes and anti-patterns 1. **A CHECK that does not reject NULL, or a UNIQUE that allows duplicate NULLs.** The rule looks right and lets nulls through. Spot it by inserting a NULL into the constrained column and watching the rule pass. Fix: add `NOT NULL`, or `UNIQUE NULLS NOT DISTINCT`, alongside the check. 2. **Brittle static thresholds that cause alert fatigue.** A fixed limit fires on normal variation, so people mute the monitor and then miss the real break. Spot it when a check fails most days and everyone ignores it. Fix: use a dynamic baseline for volume and distribution, and keep hard limits only for true invariants. 3. **Duplicate rules across layers.** The same `not_null` runs at ingestion and in two transform models, adding cost and no coverage. Spot it by tracing one assumption through the pipeline. Fix: own each assertion once, at the earliest layer that can enforce it. 4. **Boolean-only rules.** The suite reports a failure but cannot show which rows broke, so no one can act. Spot it when "test failed" comes with nothing to investigate. Fix: write rules that return the failing rows and store them. 5. **Validating everything, or nothing tied to value.** Hundreds of shallow rules bury the few that matter, and users still find the real issues first. Spot it from high test counts and low trust. Fix: cover keys and decision-critical columns first, and tie every rule to a named consequence. ## Quality bar - Every primary key has a uniqueness rule and a not-null rule. - Each critical column states whether NULL is legal, and the rule enforces that decision rather than assuming it. - Each rule is named with the dimension it covers and the consequence of its failure. - Each assumption is validated once, at the earliest layer that can own it, with no duplicate across layers. - Every rule returns or stores the offending rows, not just a pass or fail. - Severity is set per rule: blocking rules are limited to key, integrity and high-stakes checks; everything else warns. - Volume and distribution checks use a dynamic baseline, and hard limits are used only for true invariants. - Each rule has been shown to pass on known-good data and fail, with the bad rows named, on a seeded violation. ## Data handling and safety Validation reads the real data, which often includes personal or sensitive fields (email, income, health, identifiers). Do not log raw failing values to a shared dashboard or send them to a third-party service; store failures in a controlled location and mask sensitive columns in any report. Setting a rule to block a production pipeline affects other people's work and can halt a business process, so confirm with a human before making a rule blocking rather than warning, and never auto-delete or auto-quarantine rows a rule flags without explicit confirmation; default to flagging and reporting. When the data drives money, health, legal standing or employment, validation rules reduce error but do not replace domain review: add a human review step, and treat a passing suite as evidence the known assumptions held, not proof the data is correct. ## Appendix: tool-specific notes (verify before relying on these) - **Pandera:** `DataFrameSchema({col: Column(dtype, checks, nullable, unique)})`; schema-level `checks`, `strict=True` to reject unexpected columns, `coerce` to cast (off by default here so a wrong type is a finding), `ordered` for column order, and `unique=[...]` for jointly unique columns. `validate(df, lazy=True)` runs all checks and raises `SchemaErrors` with every failure; without `lazy` it raises `SchemaError` at the first. Columns reject nulls unless `nullable=True`. Import as `pandera.pandas as pa`. - **Great Expectations:** an Expectation is an assertion (for example `expect_column_values_to_not_be_null`); an Expectation Suite groups them; a Checkpoint runs a suite against a Batch in production and can fail the run; Validation Results and Data Docs record outcomes. Schema validation is necessary but not sufficient, so pair it with distribution and relationship checks. - **dbt:** four built-in generic tests, `unique`, `not_null`, `accepted_values`, `relationships`; a data test is a `select` returning failing rows (zero rows passes); `severity: warn`/`error`, `error_if`/`warn_if` thresholds, and `--store-failures` to persist bad rows. Put `unique` and `not_null` on every model's primary key, and test source data before transforming it. - **Pydantic v2:** `BaseModel` validates on instantiation; `Field(gt=, ge=, lt=, le=, min_length=, max_length=, pattern=)` for constraints; `@field_validator` (with `mode="before"` to run ahead of coercion) and `@model_validator(mode="after")` for cross-field rules; `ValidationError` on failure; `TypeAdapter` to validate lists or non-model data. Use `model_config` for `extra="forbid"` and strictness. - **SQL:** `NOT NULL`, `UNIQUE` (with `NULLS NOT DISTINCT` to treat NULLs as equal), `PRIMARY KEY` (unique and not null), `CHECK` (passes when the expression is true or NULL, so pair with `NOT NULL`), and `FOREIGN KEY` for referential integrity (not enforced on NULL referencing columns under `MATCH SIMPLE`; index the referencing columns yourself). A `CHECK` cannot reference other rows or tables. ## Sources - Pandera documentation, `DataFrameSchema` reference: constructor parameters (`columns`, `checks`, `coerce`, `strict`, `ordered`, `unique`, `report_duplicates`, `add_missing_columns`), `Check` on a `pd.Series`, `validate(..., lazy=True)` raising `SchemaErrors`, and `strict="filter"` behaviour. Verified 1 November 2026. - dbt documentation, "Data tests": the four built-in generic tests, singular versus generic tests, a data test as a `select` returning failing rows, `severity`/`error_if`/`warn_if`, `--store-failures`, and the recommendation to test every primary key. Verified 1 November 2026. - Great Expectations documentation, "Expectation Suite" (v0.18.21): Expectation, Expectation Suite, Batch, Checkpoint and Validator definitions, and suite storage as JSON. Verified 1 November 2026. - PostgreSQL documentation, §5.5 "Constraints": `CHECK` passing when the expression is true or NULL, `NOT NULL`, `UNIQUE` default `NULLS DISTINCT` versus `NULLS NOT DISTINCT`, `PRIMARY KEY` implying unique and not null, and `FOREIGN KEY` behaviour under NULL referencing columns. Verified 1 November 2026. - Pydantic v2 validation reference (field and model validators, `Field` constraints, `ConfigDict`): `BaseModel`, `Field(ge=, le=, min_length=, pattern=)`, `@field_validator` with `mode="before"`, `@model_validator(mode="after")`, and `ValidationError`. Verified 1 November 2026. - IBM, "What are data quality dimensions?": definitions of accuracy, completeness, consistency, timeliness, validity and uniqueness, and how each is measured. Verified 1 November 2026. - Shankar, Fawaz, Gyllstrom and Parameswaran, "Moving Fast With Broken Data" (Meta and UC Berkeley): a fixed 30% completeness-drop alert producing false positives that engineers silenced, per-feature thresholds taking months to tune and not transferring between datasets, that schema validation is necessary but not sufficient, and clustering correlated features and z-scores over rolling windows to cut false alarms. Verified 1 November 2026. - mikkeldengsoe, "The State of Data Testing in 2024": the broken-windows effect of long-failing tests, that 57% of dbt's 2024 analytics-engineering survey respondents named alert overload and poor data quality among their top obstacles, and hygiene tests (`unique`, `not_null`, format, `accepted_values`) belonging in the staging layer. Verified 1 November 2026. - towardsdatascience, "Your data quality checks are worth less than you think": testing each issue once, routing non-actionable alerts to reports, tying every check to a cost, monitoring aggregate metadata for tolerable data, and "when everything's important, nothing is". Verified 1 November 2026. - getdbt, "Understanding data validation": validation as testing assertions about data, validating at multiple lifecycle points, keeping validation in version control, calibrating severity so some failures warn and others block, and the alert-fatigue risk of over-strict thresholds. Verified 1 November 2026. - Existing implementations surveyed and improved on: anthropics/knowledge-work-plugins `data-validation` (a manual pre-delivery checklist with no executable rules or thresholds); wshobson/agents `data-quality-frameworks` (dimensions and tools, but a Great Expectations setup noted as verbose and a quick-start snippet that calls an undefined checkpoint); the `great-expectations-validator` and `data-quality-check` skill listings (tool-bound, no placement or severity guidance). This skill adds the which-rules-to-write decision rule, the layer-placement rule, the NULL-semantics traps, dynamic thresholds and the verify-the-rule step. Verified 1 November 2026.