Data Quality Checks: A Checklist and How to Automate Them
A copy-ready data quality checks checklist and check register, a worked example with SQL, and an AI prompt to draft rules and automate them on a schedule.
For Data & Analytics

What are data quality checks?
Data quality checks are rules that test whether a dataset is fit to use. Each rule asks a specific question: Is this field filled in? Is this ID unique? Did today's data arrive? Does this number make sense? A check returns pass or fail, and a failure tells a named person to act. You run the checks automatically every time data loads or on a fixed schedule, so bad data gets caught before it reaches a report, a model or a customer.
This post gives you the checklist, a register template to copy, a worked example, and a prompt that has AI draft the first version.
TL;DR
- Seven kinds of checks cover most problems: completeness, uniqueness, validity, consistency, timeliness, volume and reasonableness.
- Write each check as a plain-English rule with a threshold, a severity and a named owner. A check with no owner is a log line nobody reads.
- Start with the five to ten checks on your most important tables instead of trying to cover the warehouse.
- Automate in layers: block bad data at load, run scheduled checks in the warehouse, and alert a person on failure.
- AI is good at drafting rules and SQL from a table description. It cannot know your business rules, so a person has to confirm every threshold.
- Engineers, data stewards and governance teams each own a different slice. Writing that down is half the work.
What are the main types of data quality checks?
Almost every data problem fits one of seven categories. Use this table as the menu when you decide what to test.
| Check type | Question it answers | Example |
|---|---|---|
| Completeness | Is required data present? | customer_email is not null on every active customer |
| Uniqueness | Are there duplicates where there shouldn't be? | One row per order_id |
| Validity | Does the value follow the allowed format or range? | Country code is in the approved list; dates are real dates |
| Consistency | Do values agree across fields and systems? | Order total equals the sum of its line items |
| Timeliness | Is the data fresh enough? | The table updated in the last 24 hours |
| Volume | Is the amount of data normal? | Today's row count is within the usual range |
| Reasonableness | Is the value plausible given history? | No order has a negative quantity; average order value hasn't jumped tenfold |
A related term you'll hear is accuracy: does the data match the real world? Accuracy is the hardest to test automatically, because you need a trusted source to compare against. Where you have one (a finance ledger, a payroll system, a signed contract), add a reconciliation check. Where you don't, the other seven categories catch most of the damage.
What does a data quality checklist look like?
Use this per dataset. Work through it once when you onboard a table, then again whenever its source changes.
Data quality checklist (copy and use)
- Purpose and owner. Who uses this dataset, for what decision or report, and who is accountable for it?
- Critical fields. Which five or fewer columns would cause real harm if wrong? Test these first.
- Primary key. Which column or combination identifies one row? Test that it is never null and never duplicated.
- Required fields. Which columns must always be filled in? Test them for nulls and for placeholder junk ("N/A", "TBD", "0000", a single space).
- Allowed values. Which columns have a fixed list, format or range? Test them against it.
- Relationships. Which IDs must exist in another table (customer on an order, product on a line item)? Test for orphans.
- Cross-field logic. Which fields must agree (end date after start date, total equals sum of parts)?
- Freshness. How often should this update, and how late is too late?
- Volume. What does a normal load look like, and what drop or spike should raise an alarm?
- Reasonableness. What would a human reviewer instantly recognize as wrong (negative prices, birth dates in the future)?
- Severity. For each check, does failure block the pipeline, send an alert, or only get logged?
- Response. When a check fails, who is told, where, and what are they expected to do?
- Review date. When will someone confirm these rules still match the business?
What is the data quality check register template?
The checklist tells you what to think about. The register is where you record every check so it has an owner and a response. Copy this table into a spreadsheet, a wiki page or your data catalog.
Data quality check register (copy and use)
| ID | Dataset | Field(s) | Check type | Rule in plain English | Threshold | Severity | Owner | Runs where and when | On failure | Last reviewed |
|---|---|---|---|---|---|---|---|---|---|---|
| DQ-001 | Block / Alert / Log | |||||||||
| DQ-002 | ||||||||||
| DQ-003 |
How to fill in each column:
- Rule in plain English. One sentence a non-engineer can read and agree with. "Every order has a customer ID that exists in the customers table."
- Threshold. Zero tolerance for keys and required fields. For softer checks, a percentage or range based on what the data does today, not a number you guessed.
- Severity. Block means the load stops. Alert means a person is notified but data flows. Log means it's recorded for trend review.
- Owner. A person or a named team. The owner decides whether a failure is a data problem, a pipeline problem or a rule that needs changing.
- Runs where and when. At load time in the pipeline, on a schedule in the warehouse, or at the source system.
- On failure. The action: a Slack message to a channel, a ticket, a call to the source system's owner.
How do you fill it in? A worked example
Say a retailer has an orders table that feeds the weekly revenue report. The finance team's trust in that report depends on it. Here's a filled-in register for the first six checks.
| ID | Dataset | Field(s) | Check type | Rule in plain English | Threshold | Severity | Owner | Runs | On failure |
|---|---|---|---|---|---|---|---|---|---|
| DQ-001 | orders | order_id | Uniqueness | No two rows share an order ID | 0 duplicates | Block | Data engineering | Every load | Stop load, page on-call |
| DQ-002 | orders | customer_id | Completeness | Every order has a customer ID | 0 nulls | Block | Data engineering | Every load | Stop load, page on-call |
| DQ-003 | orders | customer_id | Consistency | Every customer ID exists in customers | 0 orphans | Alert | Customer data steward | Daily 6am | Ticket to steward |
| DQ-004 | orders | order_total | Reasonableness | Order total is greater than zero and below the agreed ceiling | 0 violations | Alert | Finance analyst | Daily 6am | Message to finance channel |
| DQ-005 | orders | updated_at | Timeliness | Newest row is less than 24 hours old | 24 hours | Alert | Data engineering | Hourly | Alert channel |
| DQ-006 | orders | (row count) | Volume | Daily row count is within the normal range for that weekday | Set from 8 weeks of history | Alert | Data engineering | Daily 6am | Alert channel |
The same checks as SQL. Each query returns the bad rows, so a result with zero rows means a pass.
-- DQ-001: duplicate order IDs
SELECT order_id, COUNT(*) AS n
FROM orders
GROUP BY order_id
HAVING COUNT(*) > 1;
-- DQ-002: missing customer ID
SELECT order_id
FROM orders
WHERE customer_id IS NULL;
-- DQ-003: orders pointing to customers that don't exist
SELECT o.order_id, o.customer_id
FROM orders o
LEFT JOIN customers c ON c.customer_id = o.customer_id
WHERE c.customer_id IS NULL;
-- DQ-004: implausible totals (replace 50000 with the ceiling finance agrees to)
SELECT order_id, order_total
FROM orders
WHERE order_total <= 0 OR order_total > 50000;
Notice that the ceiling in DQ-004 is a business decision, not a technical one. That's why finance owns it, and why an AI or an engineer shouldn't pick it alone.
Who owns which data quality checks?
The brief for this post says engineers, stewards and governance teams each own part. Here is a split that works in most large companies.
| Role | Owns | Typical checks |
|---|---|---|
| Data engineers | The pipeline | Schema changes, freshness, volume, duplicates, null keys, load failures |
| Data stewards | The meaning of a dataset | Allowed values, definitions, orphan records, cross-system consistency |
| Analysts and business users | What "reasonable" means | Thresholds, plausibility ranges, reconciliation to reports |
| Governance and compliance | The standard | Which datasets must be covered, severity rules, review cadence, audit evidence |
Put the owner in the register. When a check fails at 6am, the question "whose problem is this?" should already be answered.
How do you automate data quality checks?
Automation means the checks run without anyone remembering to run them, and a failure reaches a person. Build it in this order.
- Pick the critical tables. Start with the datasets behind your most visible reports, models or customer-facing numbers. Five to ten checks each is plenty.
- Write the rules in the register first. Don't open a tool until each rule has a plain-English sentence, a threshold and an owner.
- Turn each rule into a query or test. The pattern above works: a query that returns bad rows, where zero rows means pass. Open-source and commercial tools such as dbt tests, Great Expectations and Soda wrap this pattern in configuration files, and many warehouses and orchestrators have built-in support. Use whatever your data team already runs.
- Choose where each check runs. Run cheap structural checks (null keys, duplicates, schema) in the pipeline so you can block a bad load. Run heavier checks (cross-table, reconciliation, volume trends) on a schedule after the load.
- Set the response. Block, alert or log, as recorded in the register. Send alerts to a channel people watch, with the check ID, the dataset, the count of failing rows and a link to the failing sample.
- Store the results. Write every run's outcome (check ID, time, pass or fail, failing row count) to a table. That history shows trends, proves coverage to auditors and tells you which checks are noisy.
- Review on a schedule. Every quarter, look at the checks that never fail (maybe worthless, maybe your data is clean), the ones that always fail (wrong threshold or a real unfixed problem), and new columns with no checks.
What thresholds should I set?
Use zero tolerance for primary keys, required fields and anything that would break a downstream join. For softer checks such as volume, freshness and averages, start from the data's own history. Pull a few weeks of values, note the normal range including weekday patterns, and set the alert just outside it. Then tighten or loosen based on the first few weeks of alerts.
Resist adding a lot of alerts at once. If a channel fires daily with false alarms, people mute it, and the real failure gets missed. Fewer checks that people trust beat many that nobody reads.
Can AI write data quality checks?
Yes, as a first draft. AI is quick at turning a table description into a list of plausible rules and the SQL for them. It does not know that your "status" column uses legacy codes, that Sunday volume is always low, or that finance caps orders at a certain value. Treat its output as a proposal for your steward and analyst to correct.
Paste this prompt, filling in the brackets. Include column names and types, and a few sample rows with sensitive values removed or masked.
You are helping a data team write data quality checks.
Dataset: [table name]
What it is used for: [report, model or process, and who relies on it]
SQL dialect: [Snowflake / BigQuery / Postgres / etc.]
Columns (name, type, description):
[paste list]
Sample rows (masked):
[paste 5 to 10 rows]
Known business rules: [e.g. order totals are in USD; status can only be open, shipped, cancelled]
Update schedule: [e.g. loaded daily at 2am]
Produce a data quality check register as a markdown table with these columns:
ID, Check type (completeness, uniqueness, validity, consistency, timeliness, volume, reasonableness), Field(s), Rule in plain English, Suggested threshold, Suggested severity (Block / Alert / Log), Suggested owner role.
Then, for each check, write a SQL query that returns the failing rows (zero rows = pass).
Rules:
- Only propose thresholds you can justify from the information above. Where a threshold depends on a business decision, write "NEEDS BUSINESS INPUT" instead of guessing a number.
- List any assumptions you made about the columns.
- Finish with five questions you would ask the data owner before trusting these checks.
What to check in the answer
- Run every query on real data before trusting it. Make sure it returns what you expect on a known-bad example you create on purpose.
- Look for invented thresholds. If a number appears with no basis, replace it with one from history or a business owner.
- Check the joins and null handling. Generated SQL often gets
NULLcomparisons subtly wrong (= NULLinstead ofIS NULL) or forgets that a join can multiply rows. - Look for missing checks. AI tends to produce generic null and format tests and miss cross-table logic that only your team knows.
- Don't paste sensitive data. Mask personal or confidential values, and follow your company's policy on which AI tools are approved for work data.
What are the most common mistakes?
- Testing everything, owning nothing. Hundreds of checks with no owners produce noise. Fewer checks with names beside them get fixed.
- Checking only at the end. A bad value caught in a dashboard has already been used. Catch structural problems at load.
- Guessing thresholds. A number invented in a meeting will either never fire or always fire. Use history.
- Ignoring the source. If the same defect returns every week, the fix belongs upstream in the system or process that creates the data. Fixing it downstream each time only hides the problem.
- Never retiring checks. Business rules change. A rule that no longer matches reality trains people to ignore failures.
Could you build your own data quality tracker?
Many teams run the register in a spreadsheet and the results in a pile of alert messages, so nobody can see at a glance which checks exist, who owns them, or what failed last week. One option is to build a small internal app for it. With Remy, you describe the app in plain English: a check register with owner, severity and threshold fields, role-based sign-in for engineers, stewards and governance reviewers, a results history, scheduled jobs, and an email to the owner when a check fails. Remy builds it as a full-stack app with its own database and deploys it to a live URL. You'd still write and verify the checks themselves, but the register and the failure follow-up live in one place you own.
Frequently Asked Questions
How many data quality checks do I need?
Start with five to ten per critical table: a key check, required-field checks, one or two validity or consistency checks, freshness and volume. Add more only when an actual incident shows a gap. Coverage of the data that matters beats a large count.
What is the difference between data quality checks and data validation?
Validation usually means checking data at the point of entry or load, such as rejecting a malformed email. Data quality checks is the broader term and includes validation plus ongoing monitoring of data already stored, like freshness, volume trends and cross-system consistency. In practice the two overlap, and most teams need both.
Should a failed check always stop the pipeline?
No. Block the load only for failures that would make downstream numbers wrong or unusable, such as duplicate keys or missing required fields. For softer issues like a mild volume dip, alert a person and let the data flow, so you don't halt reporting over a minor anomaly.
Who is responsible for data quality?
Everyone owns a part. Engineers own pipeline health, stewards own definitions and valid values, business users own what is plausible, and governance sets the standard and checks coverage. The register works because it names one owner per check.
How often should data quality checks run?
Match the data's rhythm. Run structural checks at every load, freshness checks as often as the data should update, and heavier reconciliation checks daily or weekly. Review the whole set quarterly to retire stale rules and cover new fields.
Useful for
How we source, write, and correct our articles. Editorial standards
Related articles

Data Dictionary Template + an AI Prompt to Fill It In

Do Serverless Databases Cost Less Than Managed SQL for Startups?

Are managed database services worth it for small teams?
