Data Quality Checks Every Pipeline Needs
Running data quality checks is the difference between a pipeline that’s probably fine and one you can actually trust. The dangerous failures are rarely loud crashes — they’re a job that ran green while quietly loading yesterday’s rows, or silently dropping half the orders. This guide covers the seven checks every pipeline needs, where they run, and how to stop bad data before it reaches a dashboard.
- What data quality checks actually are
- The seven checks every pipeline needs
- Freshness and volume: is the data recent and complete?
- Schema, nulls, and uniqueness: is each row well-formed?
- Referential integrity and distribution: does it make sense together?
- Where checks run (and warn vs block)
- Common pitfalls to avoid
- FAQ
What data quality checks actually are
A data quality check is an assertion about the data that runs automatically at or around each pipeline step. If it fails, you know immediately — not three days later when a stakeholder spots a wrong number.
The critical insight is that a green run only means the code executed without an exception. A job can run successfully every night while loading yesterday’s rows, inserting duplicates, or skipping an entire customer segment, and your orchestrator still shows a green tick. Checks are the smoke detectors that catch what the job log misses.
The seven checks every pipeline needs
These seven categories cover the failure modes that surface repeatedly in real pipelines. Build one of each on your most important tables and you’ve addressed most of the silent failures.
| Check | What it catches | Example |
|---|---|---|
| Freshness | Unexpectedly stale data | max(updated_at) is more than 26 hours old |
| Volume | Row counts far above or below normal | Today’s load brought 40% fewer rows than yesterday |
| Schema | Column renames, drops, or type changes | The amount column changed silently from INT to TEXT |
| Null | Required fields that are empty | user_id is null on 400 order rows |
| Uniqueness | Duplicate primary or business keys | The same order_id appears twice in the orders table |
| Referential integrity | IDs that reference nothing | 5,000 product_id values in orders have no match in products |
| Distribution | Values outside expected ranges or valid sets | A revenue column contains negatives; a status field holds an unknown string |
None of these require a dedicated tool; plain SQL assertions running after each load are enough to start.
Freshness and volume: is the data recent and complete?
A freshness check queries the most recent timestamp and compares it to now. If the latest event is older than your refresh interval plus a buffer — say, 26 hours for a daily pipeline — it fails, catching source outages and connector failures before they go unnoticed for days. A volume check compares today’s row count against a baseline. A sudden 40% drop usually means something upstream stopped sending data; a spike often means a replication ran twice. Neither is automatically a crisis, but both deserve attention before the numbers reach a dashboard.
Schema, nulls, and uniqueness: is each row well-formed?
A schema check compares actual columns — names, order, and types — against what you expect. Source teams rename fields and change types more often than they announce it; catching that at ingestion is cheaper than debugging it in a BI tool weeks later. A null check asserts that columns which make a row meaningless when blank — foreign keys, timestamps, amounts — are never empty. A uniqueness check asserts a key column has no duplicates: a duplicated order ID will silently inflate every revenue metric that reads from the table, with no error anywhere in the pipeline run.
Referential integrity and distribution: does it make sense together?
A referential integrity check looks for orphan records — rows whose foreign key points to an ID that doesn’t exist in the referenced table. Orphans are common when deletions in the source don’t replicate downstream, or when two tables are on different sync schedules. Left unchecked, they cause silent attribution gaps: revenue that can’t be tied to a customer, events that can’t be tied to a session. A distribution check tests whether values fall within an expected range or a known set. Revenue should never be negative; a latitude should be between –90 and 90; a status column should only hold known strings. These catch upstream bugs that structural checks miss entirely.
Where checks run (and warn vs block)
Checks fit two natural points in any data pipeline: at ingestion, as raw data lands, and post-transformation, before modeled data reaches BI tools. Ingestion-time checks (freshness, volume, schema) catch broken source feeds early. Post-transform checks (null, uniqueness, referential integrity, distribution) run after the SQL has done its work. In dbt, the four built-in generic tests — unique, not_null, accepted_values, and relationships — cover those four types from YAML. Source freshness uses dbt’s separate source freshness feature — it is not a generic test. Volume checks typically need a custom singular test.
The warn-vs-block decision matters as much as the check itself. A null on a join key or a duplicated primary key will corrupt every downstream metric — block the run and alert. A minor schema drift on a non-critical column might only warrant a warning. Define the response when you write each check; revisit whenever a warn fires repeatedly without anyone acting on it.
Common pitfalls to avoid
Four patterns erode a check suite until it’s quietly ignored:
- Alert fatigue. If checks fire every morning without a real problem, engineers start filtering alerts. A check that fires during a known maintenance window trains your team to ignore the inbox.
- Testing everything equally. Focus on the tables and fields that drive decisions. A check on a non-critical lookup table adds maintenance without protection.
- No clear owner. Every failing check needs a named owner and a defined next step. Tie alerts to runbooks, not just a Slack channel nobody watches.
- Checks with no action path. If a check can’t trigger a block, an alert, or a ticket, it’s just a log entry. Define the action before you implement the assertion.
Frequently asked questions
What’s the difference between data quality and data observability?
Data quality checks are discrete assertions you define and run — “this column must never be null,” “row count must stay within 20% of yesterday’s.” Data observability is the broader practice of continuously monitoring data systems for anomalies you didn’t think to test for. Think of checks as unit tests and observability as production monitoring. Both matter; checks are the right place to start.
Should a failed check stop the pipeline or just warn?
Decide by blast radius. If a failure corrupts every downstream number — a null join key, a duplicate primary key — block the run and alert immediately. If it’s cosmetic, like a renamed non-critical column or a small volume wobble, a warning your team reviews next standup is enough. Set the response per check, not globally.
Where do dbt tests fit into this picture?
dbt’s four built-in generic tests — unique, not_null, accepted_values, and relationships — cover uniqueness, null, valid-set distribution, and referential integrity from YAML. Source freshness is a separate feature run with dbt source freshness and is not a generic test. See dbt for beginners for a working setup.
How many checks is too many?
If your check suite takes longer to run than your transformation layer, or if you ignore more than a quarter of the alerts, you have too many. Start with one check of each of the seven types on your most critical table, then add only when a real failure slips through. A small, maintained suite beats a large ignored one.
Data quality checks rarely make conference talks, but they’re what keeps people trusting the numbers. Add the seven types above, define the response for each, and bad data will stop at the pipeline. For context on where checks fit in the broader stack, start with The Modern Data Stack Explained.
Last updated: July 6, 2026
Comments
Post a Comment