Data Quality and Validation

Definition: Data quality and validation is the practice of systematically checking that data conforms to expected rules — correct types, complete fields, plausible ranges, consistent formats, and valid relationships to other data — before it’s trusted for analysis, reporting, or downstream automated decisions. It spans both narrow automated checks run as part of a pipeline and the broader discipline of defining what “correct” even means for a given dataset in the first place. The goal isn’t perfection so much as containment — catching bad data as close to its source as possible, before it compounds into aggregates, dashboards, and decisions that are far more expensive to unwind than the original bad row. In production systems this usually takes the shape of automated checks, commonly called “expectations” or “tests,” that run on a schedule or as a hard gate inside a pipeline.

Architecture

Completeness — are required fields populated, or are nulls showing up where a value is mandatory, a user_id that’s NULL on a fact table row being the canonical example
Accuracy — does the value reflect reality; an order_total of $50 that should be$ 500 due to a decimal-shift bug passes every type and null check while still being flatly wrong
Consistency — does the same entity look the same across systems, a customer’s country stored as "US" in one table and "USA" in another is a consistency failure even though both values are individually valid
Timeliness — is the data fresh enough to be useful; a dashboard built on data that stopped updating six hours ago is a quality failure even if every row it has is perfectly correct
Uniqueness — are there duplicate records that should collapse into one row, commonly from retried writes or at-least-once delivery semantics in systems like Apache Kafka
Validity — does each value fall within an allowed domain, an enum, a regex, a numeric range; the narrowest and most mechanical of the dimensions and usually the first one checked
Data profiling — inspecting the actual distribution, cardinality, and value ranges present in a dataset before writing a single rule, since checks written against assumptions instead of real data tend to be wrong on day one
Schema validation — checks structure before content: column names, types, and nullability against a declared contract, catching a silently renamed or dropped upstream column before it breaks downstream SQL
Statistical/anomaly-based checks — compare today’s data against a historical baseline, row count within roughly X% of the trailing average, null rate within a normal band, to catch rows that are individually valid but collectively wrong
Tooling — Great Expectations and dbt tests dominate the open-source landscape for declaring checks as config or code and running them as steps inside a pipeline scheduled by Apache Airflow

Why It Matters

Bad data that flows silently into dashboards or ML models erodes trust faster than almost anything else — “garbage in, garbage out” at organizational scale, and trust lost this way is slow and expensive to rebuild
Downstream consumers — analysts, ML models, other services — have no independent way to verify upstream data, so validation is roughly the only mechanism standing between a broken pipeline and a wrong business decision
Catching a bad record at ingestion costs a rejected row and a retry; catching the same bad record after it’s fed a quarterly revenue dashboard costs a public correction and a credibility hit
As pipelines feed automated decisions — dynamic pricing, fraud scoring, ad bidding — the loop from bad data to bad outcome tightens, with no human in the middle to catch an obviously wrong number before it acts
Regulatory and compliance regimes in finance and healthcare increasingly require organizations to demonstrate data accuracy rather than merely claim it, turning data quality into an audit requirement, not just an engineering nicety
Data quality compounds — a well-validated core set of tables becomes a trusted foundation that every new pipeline built on top of it inherits for free, while an unvalidated core forces every downstream team to quietly redo the same defensive checks

Under the Hood: How Automated Data Tests Actually Run

Modern data testing frameworks share a common execution model, whether the tool is Great Expectations, dbt tests, Soda, or a hand-rolled SQL check wired into a pipeline. The pattern runs in roughly five stages:

Define an expectation. A human declares a rule in config or code — “order_total should never be null,” “user_id values should be unique,” “status should be one of pending, shipped, cancelled,” or “every order_id in line_items should exist in orders” (referential integrity)
Compile the expectation into a query. The framework translates the declarative rule into SQL or an equivalent — a null check becomes a WHERE column IS NULL count, a range check becomes a BETWEEN, a referential check becomes a LEFT JOIN against the parent table filtered to unmatched rows
Run it against real data, usually as a step inside the pipeline itself — after a load, before a downstream model builds on the table, or on a recurring schedule against a table already serving production traffic
Evaluate against a threshold. Some checks are binary and fail on a single violation; others tolerate a threshold, failing only past roughly 1% of rows violating the rule, which matters because real-world data is rarely perfectly clean
Emit a result and act on it. A pass logs quietly and lets the pipeline continue; a failure can halt the pipeline entirely, quarantine just the offending rows, or log-and-alert without blocking, depending on how severe the team has decided that particular check is

This is why data tests read almost exactly like unit tests to anyone coming from software engineering — the same declare-run-assert shape, except the thing under test is a live, constantly-changing table instead of a fixed function argument.

Comparison: Schema Validation vs Statistical/Anomaly Checks vs Business Rule Checks

	Schema Validation	Statistical / Anomaly Checks	Business Rule Checks
What it catches	Wrong types, missing columns, unexpected nulls, silently renamed fields	Row counts, null rates, or distributions drifting from a historical baseline	Domain-specific logic violations, e.g. a negative `order_total` or a `ship_date` before `order_date`
When it runs	Immediately at ingestion, before anything else touches the data	After load, comparing against a rolling window of prior runs	After schema checks pass, once the data is structurally trustworthy
Typical tooling	dbt schema tests, Great Expectations, JSON Schema, Avro/Protobuf contracts	Great Expectations, Soda, Monte Carlo, custom SQL against historical snapshots	dbt custom/singular tests, Great Expectations custom expectations, SQL assertions
Catches “individually valid, collectively wrong” rows	No	Yes — this is its entire purpose	No
Domain knowledge required to write	Low, mostly mechanical	Medium, needs a sensible baseline window	High, needs to know what “correct” means for the business
Typical failure mode if skipped	Pipeline crashes downstream with a cryptic type error	A quiet, gradual drift nobody notices until a report looks “off”	Individually plausible rows that are still wrong, e.g. a valid but impossible date
Example check	`status` column is `varchar`, not null	Daily order count within 20% of the trailing 7-day average	`discount_amount` never exceeds `order_total`

Common Pitfalls

Validating only at ingestion and never downstream, so a transformation bug introduced mid-pipeline — a bad join, a wrong aggregation — ships to production with nothing left to catch it
No alerting wired to failed checks, so a check quietly fails in a CI log or a dashboard nobody watches, and the team only learns about it when a stakeholder complains
Checks set too strict, flagging normal data variance as a failure and causing frequent false-positive pipeline halts that teams eventually learn to route around or silence
Checks set too loose, technically “passing” while catching nothing that would actually matter — checking that a column merely exists but never checking whether its values are sane
No clear ownership of who fixes a failed check, so failures pile up in a queue nobody is accountable for clearing, the same organizational failure mode as unowned alerts in Observability and Monitoring
Treating every validation failure as equally severe, causing the alert fatigue that eventually makes teams start ignoring real issues along with the noise
Validating structure but never freshness — a table can pass every schema and business-rule check while being eight hours stale, which is itself a quality failure nothing above caught

Worked Example

A single order row arrives with order_total = -49.99 — a refund processed through the wrong code path, tagged as a normal order instead of a return. Tracing it through a validation-gated pipeline:

Step	Stage	What happens
1	Ingestion	Row lands in the raw `orders` staging table, captured via Change Data Capture (CDC) from the source database
2	Schema check	Passes — `order_total` is a valid decimal, no type or nullability violation
3	Business rule check	Fails — the `order_total >= 0` expectation evaluates false for this row
4	Quarantine	The single offending row routes to a dead-letter table instead of the clean `orders` table; the other 49,999 rows in the batch proceed normally
5	Alert	An on-call data engineer gets paged, naming the failed check, the row’s primary key, and the offending value
6	Triage	Engineer traces the row back to source and finds a refund-processing bug tagging returns as regular orders
7	Resolution	Source bug fixed upstream, the quarantined row is manually reclassified and reprocessed, and that day’s revenue dashboard is never contaminated

Real-World Use

Pre-load validation gates in ETL/ELT pipelines, rejecting or quarantining bad batches before they reach a warehouse like Snowflake
Contract testing between microservices that produce and consume data, so a producer team can’t silently change a field’s type or meaning without breaking a documented agreement
Monitoring dashboards tracking data freshness, volume, and schema drift over time, distinct from one-off pass/fail checks — see Observability and Monitoring
Regulatory and compliance requirements in finance and healthcare, where data accuracy has to be demonstrable to an auditor rather than simply assumed
Machine learning training pipelines, where silently corrupted or drifted input data degrades model quality in ways that are far harder to debug than an outright pipeline crash
Streaming validation on data in motion, checking messages against a schema registry before they’re even written to a topic — see Batch vs Stream Processing

Best Practices

Validate as early in the pipeline as possible — catching a bad record at ingestion is roughly orders of magnitude cheaper than catching it after it’s fed a dashboard
Alert on failures rather than silently dropping bad records; a silent drop just relocates the “garbage in, garbage out” problem instead of actually solving it
Track data quality metrics over time — pass rate, null rate trend, row count trend — rather than treating checks as a one-time binary pass/fail gate
Assign clear ownership for each check, so a failure has a named person or team responsible for triage rather than a shared queue nobody feels accountable for
Quarantine bad rows instead of blocking an entire batch where possible, so one bad record doesn’t hold the other 99.9% of a load hostage
Version and review validation rules the same way as application code, since a check that’s silently wrong is roughly as dangerous as having no check at all
Write checks against a representative sample of real historical data before trusting a threshold, rather than guessing at what “normal” looks like

FAQ

Is data validation the same thing as data testing? Mostly interchangeable in practice — “testing” tends to emphasize the software-engineering-style automated check, “validation” the broader practice of defining and enforcing correctness, but most teams use the two terms loosely.

Should a failed check block the pipeline or just alert? Depends on severity — a broken primary key or a failed schema check usually should block, since anything downstream is probably wrong; a soft anomaly like a slightly elevated null rate is often better logged and alerted without halting anything.

What’s the difference between data quality and data observability? Data quality checks assert specific rules against specific data; data observability is the broader practice of monitoring pipeline health — freshness, volume, schema, lineage — to catch problems nobody thought to write an explicit check for.

Do I need a framework like Great Expectations, or can I just write SQL? Either works — frameworks add reusability, a standard way to declare thresholds, and built-in alerting and reporting, but a hand-rolled SELECT COUNT(*) WHERE ... gated inside a pipeline step is a legitimate, common starting point.

How strict should a threshold be? Strict enough to catch real problems, loose enough to tolerate the normal noise in real data — a reasonable rule of thumb is to start looser than feels comfortable and tighten it based on what actually breaks in production.

Can validation catch every kind of bad data? No — it only catches what someone thought to check for; a value that’s wrong but structurally, statistically, and logically plausible, a typo that still lands inside a valid range, sails through every gate untouched.

Where should validation live — in the pipeline, in the warehouse, or both? Both, ideally — pre-load checks in the pipeline catch structurally bad data before it lands anywhere, while in-warehouse checks like dbt tests catch problems introduced by transformations that only exist after the data has already loaded.

Does adding more checks always improve data quality? Not automatically — checks that are redundant, untuned, or that nobody reviews just add maintenance burden and noise; a smaller set of well-chosen, well-owned checks usually beats a sprawling suite nobody trusts.

History

Data quality as a formal discipline traces back to traditional data warehousing and master data management practice in the 1990s, where “single source of truth” projects made bad data an expensive, highly visible problem
Academic work on Total Data Quality Management in the early 1990s was among the first to treat data quality as a measurable, manageable property rather than an incidental side effect of ETL
Data profiling tools emerged alongside enterprise data warehouses through the 2000s, giving teams a way to inspect a dataset’s actual shape before writing rules against it
Open-source testing frameworks — dbt tests built into dbt from its early releases, and Great Expectations first released around 2017 — brought software-engineering-style automated testing to data teams, replacing ad hoc SQL scripts and spreadsheet audits
The “data contracts” movement gained traction in the early 2020s, pushing validation upstream to the producer side, treating a schema and quality agreement as something a service commits to before publishing data rather than something a downstream consumer discovers by breaking
Data observability platforms, emerging from roughly the mid-to-late 2010s onward, extended the discipline from “did this specific check pass” to “is this pipeline behaving normally overall,” borrowing concepts directly from software reliability engineering
Cloud data warehouses and the ELT pattern shifted a huge share of transformation logic into SQL run directly against the warehouse, which is part of why dbt tests, defined alongside that same SQL, became the default rather than a bolt-on afterthought

Common Interview Questions

“How would you design a data validation strategy for a new pipeline?” — expect an answer covering schema checks at ingestion, business rule checks after transformation, and alerting tied to severity, not just “write some tests”
“What’s the difference between a hard failure and a soft failure in a data quality check?” — expect a distinction between blocking checks like a failed schema or primary key, and logged/alerted checks tolerant of some statistical noise
“How do you handle a validation check that’s too noisy?” — expect discussion of threshold tuning, distinguishing genuine anomalies from normal variance, and the alert-fatigue risk of leaving it unfixed
“Explain ‘garbage in, garbage out’ using a pipeline you’ve actually worked on” — expect a concrete example of how one unvalidated bad value propagated and got more expensive to fix at each downstream stage
“How would you test referential integrity between two tables in a warehouse?” — expect a LEFT JOIN-and-check-for-nulls query pattern or a framework’s relationship expectation, plus a plan for what to do with orphaned rows
“What’s the difference between data quality and data governance?” — expect a distinction between quality’s focus on correctness of the data itself versus governance’s broader scope of access control, policy, cataloging, and ownership

Example

A pipeline checks that a user_id column is never null, that order_total is always non-negative, and that every order_id in a line_items table exists in the parent orders table. The first two run as simple column-level expectations immediately after load; the third is a referential integrity check that runs once both tables are populated. A failure on any of them halts the nightly load, quarantines the offending rows into a dead-letter table, and pages the on-call data engineer rather than letting a partially-broken dataset reach the next morning’s revenue dashboard.

Referenced by

Dig deeper

Spot an error in this note?