DATA QUALITY
Data Quality Checks: Data Quality Control, Data Quality Tests, and What to Automate
The checks that actually catch things, the SQL to run them, the trap hiding in each one, and an honest line between what you should hand-write and what you should let a monitor do.
Alerted #data-eng 0.8s ago.
Downstream impact · consumers at risk
What are data quality checks?
Data quality checks are tests that assert something must be true about your data: no nulls in a required column, no duplicate order IDs, revenue equal to price times quantity, a table loaded within the last six hours. They run as SQL, dbt tests, Soda or Great Expectations assertions, or PySpark jobs, and they fail loudly when the assertion breaks. The catch is that a check only catches what somebody thought to assert, so most teams pair hand-written checks for business rules with automated monitoring for the failures nobody predicted.
Last updated July 2026
Side by side
Data quality checks compared
| Check | What it catches | Typical SQL | The trap in it |
|---|---|---|---|
| Null / completeness | Missing values in a column that must be populated | SELECT COUNT(*) FROM orders WHERE customer_id IS NULL | Empty strings, whitespace, and the literal text "NULL" all pass. So does a column that is 100 percent null because the load never ran at all. |
| Uniqueness | Duplicate keys from a replayed or double-run load | SELECT id, COUNT(*) FROM orders GROUP BY id HAVING COUNT(*) > 1 | On a wide table this is a full scan. Run it on every build across 300 tables and the check burns more warehouse credits than the incident would have cost. |
| Row volume | A load that ran green but delivered a fraction of the rows | SELECT COUNT(*) FROM orders WHERE loaded_at > CURRENT_DATE - 1 | Static thresholds break on Black Friday and break again in January. A count that is healthy on Tuesday is an incident on Monday. |
| Freshness | A stale table nobody noticed for three days | SELECT MAX(updated_at) FROM orders | This reads when a row claims it was updated, not when the table was actually written. If the sync stalled, updated_at is frozen and the check has no idea. |
| Accepted values | A new enum value shipped by an upstream team | SELECT DISTINCT status FROM orders WHERE status NOT IN (...) | It fires the day product adds a legitimate new status, which is correct behavior and also exactly why people mute it and forget. |
| Referential integrity | Orphan rows pointing at a parent that is gone | LEFT JOIN customers c ON o.customer_id = c.id WHERE c.id IS NULL | A late-arriving dimension looks identical to a real orphan, so you get paged for a race condition that resolves itself 20 minutes later. |
| Business rule | Totals that do not reconcile | WHERE ABS(total - price * quantity) > 0.01 | The only check here that no tool can generate for you, and usually the one that catches a real money bug. Keep writing these by hand. |
| Distribution shift | A currency or unit field that quietly changed meaning | Compare today AVG and STDDEV to a trailing 30-day window | Hand-rolling this means implementing seasonality yourself. You will get it wrong for a quarter before you get it right. |
Positioning and pricing models are summarized in good faith from each vendor's public pages, July 2026. Verify current terms with the vendor.
What you get
Where hand-written checks win, and where they run out
Write the checks only you can write
A rule that says net_revenue equals gross minus discounts encodes logic no algorithm can infer from your data. Those checks are cheap, precise, and belong in your repo next to the model that produces the column. Nothing on this page argues otherwise.
Automate the failures nobody predicted
Nobody writes a test for a vendor silently dropping a column, or a Fivetran sync pausing over Thanksgiving, or a source switching cents to dollars. Monitors that learn each tables normal freshness, row count, null rate, and distribution catch those with no assertion written in advance.
Coverage past the ten tables you tested
Hand-written checks cluster on the tables one engineer was worried about in 2024. Dataobservability profiles every table you connect, including the 200 nobody has looked at since the migration, which is exactly where silent failures live.
One incident instead of 200 test failures
When an upstream table breaks, 40 downstream dbt tests fail in the same minute. Related failures group into a single incident with the column-level blast radius attached, routed to Slack or PagerDuty by severity.
How it works
From connected to caught
Pick the tables that actually matter
Start with what feeds the revenue dashboard, the customer sync, and anything a production model reads. Ten tables you can name beats 400 you cannot. Coverage of everything comes later, and it comes from automation, not from typing.
Write the checks a machine cannot guess
Uniqueness on keys, accepted values on enums, referential integrity between facts and dimensions, and your genuine business rules. Put them in dbt tests or SodaCL in the repo so they run on every build and get reviewed like code.
Let monitors handle the statistical checks
Freshness, row volume, null rate, and distribution are learnable from warehouse metadata. Do not hand-tune thresholds for them. You will spend the next year chasing seasonality and tuning numbers that a baseline model fits in an afternoon.
Route by severity, then measure the noise
Hard failures that should block a pipeline go to PagerDuty and fail the build. Soft anomalies go to a Slack channel someone reads. After two weeks, count true positives per check. Anything under half gets fixed, downgraded, or deleted.
Data quality checks in SQL, and the trap in each one
Every check in the table above is four lines of SQL, which is why teams start there and why they underestimate it. The null check misses empty strings and the string "NULL", and it cannot tell a genuinely null column from a load that never ran. The uniqueness check is a GROUP BY over the whole table, which is fine on one table and a real credit line item across three hundred. The freshness check reads MAX(updated_at), a value the source system controls, so when a sync stalls the timestamp simply stops moving and the check sees nothing wrong. The volume check needs a threshold, and any threshold you pick by hand is wrong on Black Friday and wrong again in the first week of January. None of this means do not write SQL checks. It means know what each one is blind to, because the blind spots are where the incidents come from.
Data quality checks in dbt, Snowflake, and Databricks
In dbt, the four built-in tests (unique, not_null, accepted_values, relationships) plus dbt_utils cover the assertion layer well, and dbt-expectations extends it to distribution and pattern checks. They run on every build, they live in version control, and they fail the job. That is the right shape for business rules. Snowflake gives you data metric functions that attach a metric to a table and run on a schedule, and Databricks has expectations in Delta Live Tables that can warn, drop, or fail on violating rows. Both are useful and both are per-table configuration you write and maintain by hand. The pattern is the same everywhere: the platform gives you a place to put assertions, and you still have to know which assertions to write. Warehouse-native monitoring is the layer that does not ask you that question first, because it derives the baseline from what the table already does.
Data quality checks in a data pipeline: Airflow, PySpark, and where to put the gate
The decision people get wrong is placement, not tooling. A check inside the pipeline (an Airflow task, a Great Expectations validation step, a PySpark job asserting on a DataFrame before write) is a gate: it can stop bad data from landing at all. Use gates for anything where a bad row is worse than a late table, like a customer-facing export or a payments reconciliation. A check after the load, against the warehouse, is a detector: bad data has already landed, and you are racing to catch it before a stakeholder does. Detectors are cheaper, they cover far more tables, and they never block the business at 2am over a false positive. Most teams need both and only build one. The rough rule: gate the handful of tables where wrong beats late, and detect everywhere else.
Data quality checks automation: what to write and what to buy
Assertion-based testing is cheap to start and expensive to keep. Every new table needs new rules, every schema change breaks the old ones, and coverage stalls wherever attention stopped. Teams tend to hit the wall around a few hundred tables: tests rot, the suite gets slow, someone adds a global mute, and the failures that actually hurt were never asserted anyway. The honest split is this. Hand-write anything that encodes a rule from your business, because a monitor cannot invent your revenue definition. Automate anything statistical, because a baseline model is better at seasonality than you are and it does not need you to name the table first. Dataobservability sits on the second half: connect a read-only role and monitors generate for freshness, volume, schema, distribution, and lineage across every table, with column-level impact on every alert. Starter is 99 dollars a month, Team is 299, Scale is 799, with a 14-day trial and no credit card. Checked July 2026.
Data quality checks best practices, short version
Check where the data enters, not only where it is consumed, because a check on the mart tells you something broke and a check on the source tells you what. Version your checks with your models so a reviewer sees the rule change next to the logic change. Give every check an owner and a severity, and be ruthless: a check nobody acts on is worse than no check, since it teaches the team that red is normal. Test the checks themselves by breaking data on purpose in staging. Watch warehouse spend on the suite, because full-table scans on every build is a bill people discover in a quarterly review. And review the noise monthly. The metric that matters is not how many checks you have, it is what percentage of alerts led to a fix.
Questions buyers ask
Data quality checks FAQ
What are data quality checks?
Data quality checks are automated tests that assert something must be true about your data: no nulls in a required field, no duplicate keys, values inside an accepted set, a table refreshed within a set window. They run in SQL, dbt, Soda, Great Expectations, or PySpark, and they fail when the assertion breaks so bad data does not reach a dashboard unnoticed.
What are examples of data quality checks?
The common ones are null checks on required columns, uniqueness checks on primary keys, row-volume checks against an expected range, freshness checks on load time, accepted-values checks on enums, referential-integrity checks between facts and dimensions, format or pattern checks, and business-rule checks like total equals price times quantity. Most teams run six to eight types across their critical tables.
How do you perform data quality checks in SQL?
Write a query that returns rows only when the rule is broken, then alert or fail when the count is above zero. A null check is SELECT COUNT(*) FROM orders WHERE customer_id IS NULL. A duplicate check groups by the key and keeps groups with COUNT(*) greater than one. Schedule them, and watch the scan cost on wide tables.
What are data quality checks in dbt?
dbt ships four generic tests: unique, not_null, accepted_values, and relationships. You declare them in YAML next to the model and they run on every dbt build, failing the job when they break. dbt_utils and dbt-expectations add row counts, ranges, and distribution assertions. They cover business rules well and do not catch anything you did not assert.
What is data quality control?
Data quality control is the operational side of data quality: running checks, catching violations, and correcting or blocking bad records before they spread. Database quality control usually means the same thing scoped to one system, using constraints, validation rules, and scheduled checks. Quality assurance is the wider process (standards, ownership, definitions) that decides which controls exist in the first place.
Do I still need data quality checks if I have data observability?
Yes, and fewer of them. Observability learns each tables normal freshness, volume, null rate, and distribution and flags deviations without an assertion, so you can stop hand-tuning statistical thresholds. It cannot infer your business rules. Keep the checks that encode your revenue logic, your key definitions, and your reconciliation math, and let monitors cover the rest of the warehouse.
Catch broken data before your stakeholders do
Connect your warehouse and get data quality checks live in 15 minutes. Transparent pricing, no credit card.