How to validate data: the complete guide

The six checks that catch most real incidents, where to run them, and how to turn ad-hoc SQL into a versioned data contract that runs on a schedule.

2026-09-10 · 11 min read

To validate data, write each expectation as a query that returns a count of violating rows, run the whole set against the dataset after every load, and fail when a count crosses a threshold you agreed in advance. Six checks catch the overwhelming majority of real incidents: missing values in required columns, duplicate business keys, values outside an allowed set, numbers outside a plausible range, malformed strings, and stale rows. The checks themselves are ordinary SQL — the hard parts are running them where the data lives, keeping them in sync with the schema, and alerting in a way people do not learn to ignore. Everything below is the machinery for doing that reliably.

What data validation actually is

Three things get called validation and only one is the subject of this guide.

Input validation happens at the edge of an application: a form rejects an email address without an @. It is a write-time gate on one record at a time.

Schema validation checks structure — does the table have the columns the loader expects, in the types it expects. It catches a broken contract between systems, not bad values inside a well-formed one.

Data validation checks the contents of a dataset that already exists, in bulk, after it has landed: of the 4.2 million rows we loaded last night, how many break a promise someone is relying on?

That distinction dictates the shape of the solution. You are not rejecting rows; the rows are already there. You are measuring, on a schedule, and deciding what to do about the measurement. A validation run produces a number per rule, a pass or fail per rule, and a history — which is what turns "the data looks off" into "the null rate on customer_id went from 0.02% to 11% at 04:12 last Tuesday". If you want the definition on its own terms, including how validation differs from testing, observability and cleaning, start with what data validation is and come back here for the mechanics.

The six checks every dataset needs

Every check has the same shape. Count the rows that violate the expectation, compare the count to a threshold:

SELECT count(*) AS violations
FROM sales.orders
WHERE order_id IS NULL;

That is the entire pattern. A rule is a predicate, a count and a threshold, and the six that matter are just six predicates:

Two additions earn their place on most tables: a rowCount rule at the table level, because an empty result after a load is a distinct and very common failure, and a referential integrity check on foreign keys, because partial backfills leave orphans that inner joins quietly drop.

The null trap, which everyone hits once

Write the validity check as it appears above and it will lie to you:

-- Wrong: nulls disappear from the count.
WHERE status NOT IN ('pending', 'paid', 'shipped', 'refunded')

-- Right:
WHERE status IS NOT NULL
  AND status NOT IN ('pending', 'paid', 'shipped', 'refunded')

NULL NOT IN (...) evaluates to NULL, not true, in every SQL engine. Without the guard, a column that is 90% null reports perfect conformity. This is the single most common false pass in hand-written data quality SQL, and it is worth checking every rule you inherit for it.

Where to run the checks

Run them in the warehouse, against a read-only connection, and never move the rows.

Pulling data out to validate it is the wrong instinct for three reasons: it is slow, it copies sensitive rows into a second system with a second security review, and it cannot keep up — a check that has to export a billion-row fact table will not run hourly. Pushing the aggregate down to the engine means each check is a single SELECT returning one number, which is a workload every warehouse is built for.

The permission set is genuinely minimal: connect on the database, read the schema catalog, SELECT on the tables you are checking. Nothing else. If a validation tool asks for write access, ask why. Point the connection at a read replica where one exists — these are aggregate scans with no consistency requirement beyond "recent" — and set a query timeout so an unindexed scan cannot pin a connection for an hour.

Manual SQL, a framework, or a managed tool

There are three honest ways to do this, and the right one depends on how many datasets you have and who needs to read the rules.

The failure mode of the first is entropy: within a year you have forty SQL files, six of which reference dropped columns, and nobody willing to delete any of them. The failure mode of the second is scope — dbt tests are excellent, but they only cover models dbt builds, and they run when dbt runs. The failure mode of the third is lock-in, which is the reason the next section exists.

None of these are exclusive. Teams that get this right put fast, cheap assertions in the pipeline where they fail a build, and keep the durable promises somewhere they are reviewed and monitored regardless of which tool wrote the table today.

Data contracts: version the expectations, not the scripts

The durable fix for rule entropy is to stop writing checks as code and start writing them as a document — a declaration of what the dataset promises, stored next to your other source, reviewed in pull requests, and executed by whatever runner you happen to use. That document is a data contract.

The Open Data Contract Standard is the open specification for writing one. It is YAML, it is developed under the Linux Foundation's Bitol project rather than by a vendor, and a minimal contract for the checks above looks like this:

apiVersion: v3.0.0
kind: DataContract
info:
  title: orders
  version: 1.0.0
  owner: data-platform
schema:
  - name: orders
    physicalName: orders
    physicalType: table
    properties:
      - name: order_id
        logicalType: string
        required: true
        primaryKey: true
        quality:
          - rule: nullCount
            dimension: completeness
            severity: error
            mustBe: "0"
          - rule: duplicateCount
            dimension: uniqueness
            severity: error
            mustBe: "0"
      - name: status
        logicalType: string
        quality:
          - rule: validValues
            dimension: conformity
            severity: error
            mustBe: "['pending', 'paid', 'shipped', 'refunded']"
      - name: total_amount
        logicalType: number
        quality:
          - rule: between
            dimension: accuracy
            severity: error
            mustBe: "[0, 100000]"
      - name: created_at
        logicalType: timestamp
        quality:
          - rule: freshness
            dimension: timeliness
            severity: error
            mustBe: "<= 24h"
    quality:
      - rule: rowCount
        dimension: completeness
        severity: warning
        mustBe: "> 0"

Three properties make this worth the trouble.

It is portable. nullCount means the same thing everywhere; the runner compiles it to that engine's dialect. Migrating warehouses stops being a rules migration.

It is reviewable. A threshold change is a diff with an author and a reason. Whether the bound moved because the business changed or because someone got tired of the alert becomes recoverable, which it never is in a vendor UI.

It is yours. Rules written in an open standard are not an asset of whoever runs them today.

Catalyst is built on ODCS directly: the YAML is the storage format rather than a rendering of a database row, so a rule edited in the visual builder produces a one-line diff and the contract reviewed in a pull request is byte-for-byte the one that executes.

Scheduling and alerting

Two rules, both learned the hard way.

Trigger from the load, not from a clock. A daily table validated at 06:00 when the ELT finishes at 06:40 fails every morning for a reason that has nothing to do with quality. Match the cadence to the data: hourly freshness on a streaming table, one run after the nightly batch for everything else. Running checks more often than the data changes produces noise and, on usage-billed warehouses, an invoice.

Alert on transitions, not on state. "Orders started failing" is actionable. "Orders is still failing", repeated hourly, is how a channel gets muted — and a muted channel is worse than no alerting, because it looks like coverage.

Use severity to separate the two populations of rule. Reserve error for checks that should genuinely block a downstream consumer: a dashboard refresh, a reverse-ETL sync, a finance report. Everything else — pattern checks on human-entered fields, row count drift, distributional oddities — belongs at warning, where it is a trend line rather than a page.

Which datasets to validate first

You cannot validate everything, and trying is how the project dies. Rank by blast radius:

1. Anything feeding a number an executive reads. Revenue, headcount, pipeline. A wrong number here costs credibility that takes months to rebuild.
2. Anything feeding an automated decision. Pricing, credit limits, ML features, reverse-ETL into a CRM. These act on bad data before a human sees it.
3. Anything with an external consumer. A partner feed or a regulatory submission has a cost of failure you do not control.
4. Joins at the heart of your model. The dimension tables everything joins to, where an orphaned key silently drops rows from every downstream query.

Start with five to ten rules on one such table rather than three rules on forty. Coverage that is shallow everywhere tells you nothing; coverage that is deep on the tables that matter catches the incidents you would otherwise hear about from someone else.

Then read the guide for your engine — the six checks are universal but the SQL, the traps and the cost model are not:

• PostgreSQL — real regex, honest nulls, and the risk of scanning your transactional primary
• MySQL — implicit coercion, zero dates and collation-dependent equality
• SQL Server — no regex operator, COUNT_BIG and collation traps
• BigQuery — validation is a cost problem first; partition filters are the whole game
• Amazon Redshift — unenforced constraints the planner trusts anyway, plus WLM contention
• Microsoft Fabric — Entra ID auth, a narrower T-SQL surface, and endpoint metadata lag
• CSV, JSON and Excel files — where most defects are parsing problems, not rule violations

Catalyst implements exactly the workflow above — import the schema, propose a baseline contract, compile each rule to your engine's SQL, run it on a schedule, and track pass/warn/fail history per rule — across all of those connections, with a free tier to try it on one dataset (PostgreSQL and MySQL) before deciding anything (pricing).

Frequently asked questions

What counts as a data validation check?

A data validation check is one agreed expectation about a dataset's contents, expressed as a query that returns a count of violating rows — no missing values in a required column, no duplicate keys, values within an allowed set or a plausible range, correctly formatted strings, or rows recent enough to be useful. The check fails when that count crosses a threshold you set in advance. Unlike input validation, it runs in bulk against data that has already landed.

What are the main types of data validation checks?

Six cover most real incidents: completeness (nulls in required columns), uniqueness (duplicate business keys), validity (values outside an allowed set), accuracy (numbers outside a plausible range), conformity (strings that do not match a required format), and timeliness (rows or partitions that are stale). Table-level row counts and referential integrity between tables are the two most common additions.

Do I need to write SQL to validate data?

Not for the standard checks. Declaring the expectation — required, unique, allowed values, numeric range, pattern, maximum age, foreign key — lets a runner compile it to the correct SQL for your engine, which also removes the dialect differences and the null-guard traps. Hand-written SQL is worth reserving for genuinely bespoke business logic, through a custom SQL rule.

How often should data validations run?

Match the schedule to the data's own cadence and trigger from the job that produces it, not from a fixed clock. Streaming tables want hourly freshness checks; batch tables want one run after the load completes. Running more often than the data changes only produces noise, and on usage-billed warehouses it produces a bill as well.

Is data validation the same as a data contract?

No. Validation is the act of running checks; a data contract is the versioned document that says what the checks should be. The contract also carries ownership, descriptions and service levels, which is what makes it reviewable by the consumers of the data rather than only by the team that wrote the pipeline. A standard like ODCS is what keeps the contract portable between runners.

Does data validation need write access to my database?

No. Every check described here is a SELECT returning a single number, so read access on the tables plus the ability to read the schema catalog is the complete permission set. Where a read replica or readable secondary exists, point the connection at it — the checks have no consistency requirement beyond "recent", and moving them off the primary removes the main operational objection to running them often.