What is data validation? Definition, methods and examples

How data validation differs from testing, observability and cleaning, the four methods that cover almost every real check, and where each of them runs.

2026-09-10 · 8 min read

Data validation is the process of checking that data conforms to a defined set of expectations — its structure, its types, its allowed values, its relationships and its freshness — before that data is trusted or used. Each expectation is stated in advance, evaluated against the actual data, and produces a pass or a fail with a count of the records that violated it. Validation does not repair anything; its job is to decide whether the data is fit for the purpose it is about to be used for, and to say precisely what is wrong when it is not.

That definition has three load-bearing parts. Expectations are declared in advance, which is what separates validation from noticing a problem after a dashboard looks wrong. The check is evaluated against real data, not against a sample or a description of the data. And the outcome is a decision — this batch is acceptable, or it is not — rather than a report someone might read later.

What data validation is not

The word gets used loosely for four different activities. Being precise about the boundaries makes it much easier to decide which tool you actually need.

The distinctions matter in practice:

• Validation vs testing. A dbt test is a validation rule that happens to run inside a build. The difference is scope and ownership: tests cover the models the pipeline owns and run when the pipeline runs; validation covers a dataset's guarantees regardless of which pipeline produced it today, and runs on a schedule of its own. A table loaded by Fivetran, transformed by dbt and appended to by a hand-rolled Python job has one set of guarantees and three pipelines.
• Validation vs observability. Observability is unsupervised — it watches row counts, null rates and distributions, and alerts when today looks unlike last week. It finds problems you did not think to look for, and it also fires when the business legitimately changes. Validation is supervised: you said status must be one of four values, and either it is or it is not. Observability gives you recall; validation gives you precision. Mature teams run both.
• Validation vs cleaning. Cleaning mutates data — trimming whitespace, coercing types, deduplicating, imputing. Validation is read-only by design. Conflating them is how silent corruption happens: a coercion step that turns "N/A" into 0 makes a validation rule pass while making the number wrong.

The four methods of data validation

Almost every check in production falls into one of four families.

1. Schema and type checks

Does the dataset have the columns it is supposed to have, in the types it is supposed to have them, with nullability declared correctly? These are the cheapest checks and they catch the most disruptive failures — a renamed column, a type widened from integer to text, a column silently dropped by an upstream migration. Schema drift breaks consumers immediately and loudly, which is why it is worth catching at the boundary rather than in a dashboard.

2. Constraint checks

Row-level rules that a single record either satisfies or violates: a required column is not null, a key is unique, a value belongs to an allowed set, a number sits inside a plausible range, a string matches a pattern, a foreign key resolves. This family covers the bulk of real rules, and each one compiles to a single aggregate query returning a violation count.

SELECT
    count(*) FILTER (WHERE order_id IS NULL)                       AS null_ids,
    count(*) FILTER (WHERE total_amount < 0 OR total_amount > 1e5) AS bad_amounts,
    count(*) FILTER (
        WHERE status IS NOT NULL
          AND status NOT IN ('pending', 'paid', 'shipped', 'refunded')
    )                                                              AS bad_status
FROM sales.orders;

Note the status IS NOT NULL guard. Comparing a NULL to anything yields NULL, never true, so an unguarded NOT IN quietly excludes every null row from its own violation count. A constraint check written this way stays green while the column empties out, which is the standing argument for pairing each constraint rule with a completeness rule on the same column.

3. Statistical checks

Aggregate properties rather than individual rows: row count within a band, null rate below a percentage, mean or median inside a range, cardinality stable, distribution across categories roughly as expected. These catch failures that no row-level rule can see — a partial load where every surviving row is individually valid but a third of the data is missing.

4. Business-rule checks

Cross-column or cross-table logic that encodes a domain invariant: shipped_at is never earlier than ordered_at, an invoice's line items sum to its total, a refund never exceeds the original payment, every active subscription has a billing account. These are the rules that catch genuine business errors rather than plumbing errors, and they are usually the ones a validation tool expresses as custom SQL.

Where validation runs

The same rule means different things depending on where you put it, and most teams need more than one location.

At ingestion. Validate at the boundary, before data lands in a shared table. This is where you reject a malformed CSV, a JSON payload missing a required field or an API response with an unexpected schema. Failing here is cheap: nothing downstream has been computed yet, and quarantining the batch costs you one retry.

In the warehouse, after loading. Validate the landed table itself, on a schedule tied to the load rather than to a round-number cron. This is where most data quality monitoring lives, because it is the only place that sees the data as consumers actually see it — after every pipeline, merge and late-arriving correction has had its say. A daily table validated at 06:00 when the load finishes at 06:40 fails every morning for reasons that have nothing to do with quality.

At consumption. Validate immediately before a high-stakes use — a regulatory report, a customer-facing metric, a training set for a model. These checks are narrower and stricter than the general ones, because the tolerance is different: a 0.1% null rate might be fine for an internal dashboard and unacceptable in a financial filing.

A practical rule: reject at ingestion, monitor in the warehouse, gate at consumption.

From ad-hoc checks to versioned expectations

Hand-written validation SQL works until there are forty rules across a dozen tables, at which point it develops the usual problems. The rules drift out of sync with the schemas they check. Nobody can answer "what do we actually guarantee about this table?" without reading a script. A threshold change leaves no trace of who changed it or why.

The fix is to make the expectations a declarative artefact rather than code — a document that states what the dataset promises, versioned in the same repository as everything else, and compiled to SQL by a runner. That document is a data contract, and the Open Data Contract Standard is the open format for writing one. Catalyst takes this route: rules are ODCS YAML, the YAML is the source of truth, and each rule compiles to the engine-specific SQL shown above.

Organising rules by data quality dimension — completeness, uniqueness, validity, consistency, accuracy, timeliness — is what turns a pile of checks into a coverage view. For the mechanics of writing and scheduling the checks themselves, see the guide to validating data.

Frequently asked questions

What is data validation in simple terms?

Data validation is checking that data matches the rules you decided it should follow — the right columns and types, no missing values where they are required, no duplicate keys, values inside the allowed set or range, and data recent enough to be useful. Each rule is checked against the real data and returns a pass or a fail with a count of the offending records.

What is the difference between data validation and data verification?

Validation asks whether the data satisfies the rules defined for it — is it structurally and semantically acceptable? Verification asks whether the data faithfully matches its source — did it arrive intact and unaltered, for example by comparing row counts or checksums between the source system and the destination. A record can be perfectly verified and still fail validation if the source itself contained bad values.

What are the main types of data validation?

Four families cover almost everything: schema and type checks (the right columns, in the right types), constraint checks (not null, unique, allowed values, ranges, patterns, referential integrity), statistical checks (row counts, null rates, distributions) and business-rule checks (cross-column and cross-table invariants specific to your domain).

When should data validation run?

At three points, for different reasons. At ingestion, to reject malformed data at the boundary before it lands. In the warehouse after loading, on a schedule triggered by the load, to monitor the tables consumers actually query. And at consumption, as a stricter gate before a high-stakes use such as a regulatory report or a model training run.

Does data validation change my data?

No. A validation check is a read-only query that returns a count of violations. Modifying records is data cleaning, a separate step that should happen after validation has told you what is wrong — never as a side effect of the check itself, which would hide the problem instead of surfacing it.