SQL Data Quality Checks You Need to Know

Bad data can make a good dashboard, report, or Machine Learning model produce bad results.
Data Quality is not just about removing NULLs and duplicates. Before data reaches your analytics pipeline, you need to know whether it is complete, valid, consistent, unique, and up to date.
Here are 10 practical SQL data quality checks you can use to catch common problems early. 🔍

1. NULL Check​

Missing values are not always a problem, but they can be serious when an important column is expected to contain a value.
For example, you can check whether orders are missing a customer ID:

SELECT COUNT(*)
FROM orders
WHERE customer_id IS NULL;

If the result is greater than zero, investigate why those records are missing a customer.

---

2. Uniqueness Check​

IDs such as order_id and customer_id are often expected to be unique.
To find duplicate order IDs:

SELECT order_id, COUNT()
FROM orders
GROUP BY order_id
HAVING COUNT() > 1;

Unexpected duplicates can cause incorrect counts, double-counting, and unreliable reports.

---

3. Referential Integrity Check​

Relationships between tables should point to valid records.
For example, every customer_id in orders should normally exist in the customers table:

SELECT o.customer_id
FROM orders o
LEFT JOIN customers c
ON o.customer_id = c.customer_id
WHERE c.customer_id IS NULL;

Any returned rows represent orders whose customer reference does not exist in the customer table.

---

4. Range Validation​

Some values should stay within a reasonable range.
For example, a product price should not normally be negative:

SELECT *
FROM products
WHERE price < 0;

The same idea can be applied to quantities, percentages, scores, ages, temperatures, and other numeric fields.

---

5. Accepted Values Check​

Columns such as status often have a predefined set of valid values.
You can find unexpected status values with:

SELECT DISTINCT status
FROM orders
WHERE status NOT IN ('Pending', 'Shipped', 'Delivered', 'Cancelled');

This can catch spelling mistakes, unexpected values, and changes introduced by an upstream system.

---

6. Volume Check​

A sudden change in the number of records can indicate a problem in an ETL or data ingestion pipeline.
For example:

SELECT order_date, COUNT(*) AS orders
FROM orders
GROUP BY order_date
ORDER BY order_date;

You can compare daily volumes to historical patterns and investigate unusual drops or spikes.
📌 A volume check is especially useful for detecting failed imports, partial loads, and unexpected changes in source systems.

---

7. Outlier Check​

Some records may be technically valid but still look unusual compared with the rest of the dataset.
For example, an unusually large transaction might indicate a legitimate business event, a data entry mistake, or a pipeline issue.
Depending on your database and use case, you can use statistical functions such as AVG() and STDDEV(), or percentile-based methods, to identify unusually high or low values.
The important point is that an outlier is not automatically bad data. It should be investigated in context.

---

8. Data Freshness Check​

A dataset can be complete and internally consistent but still be outdated.
You can check the latest update timestamp with:

SELECT MAX(updated_at)
FROM orders;

Compare the result with the time the data was expected to be refreshed.
For example, if the pipeline should update every hour but the latest record is several hours old, there may be an ingestion or ETL problem. ⏱️

---

9. Date Consistency Check​

Dates should follow the logical order expected by the business process.
For example, an order should not normally be shipped before it was created:

SELECT *
FROM orders
WHERE shipped_at < created_at;

Similar checks can be applied to:

• created_at vs. updated_at
• Start dates vs. end dates
• Payment dates vs. order dates
• Delivery dates vs. shipping dates

These checks can reveal incorrect timestamps or problems in the source data.

---

10. Data Type and Format Validation​

Data can also be wrong even when it is not NULL, duplicated, or outside a numeric range.
Common examples include:

• 📧 Invalid email formats
• 📱 Incorrect phone number formats
• 📅 Dates stored as text
• 🔢 Numeric fields containing unexpected non-numeric values
• 🏷️ Inconsistent text formats or casing
• 🌍 Invalid or unexpected country or region codes

The exact validation method depends on the database system and the data format.

---

Why Data Quality Checks Matter​

Data Quality should not be treated as a final step after the analysis is finished.
It should be checked throughout the data pipeline:
Source → ETL → Data Warehouse → Dashboard → ML/AI
A problem introduced at the source can move through every downstream system and eventually affect business decisions.
For example, if an ETL process accidentally duplicates orders, the dashboard may report inflated sales. A Machine Learning model trained on the same data can also learn from those incorrect records.
💡 A beautiful dashboard built on bad data is still a bad source for decision-making.

---

A Practical Data Quality Checklist​

Before using a dataset for analytics, ask:

• ✅ Are required fields populated?
• ✅ Are unique identifiers actually unique?
• ✅ Do foreign keys reference valid records?
• ✅ Are numeric values within valid ranges?
• ✅ Are categorical values valid?
• ✅ Does the data volume look normal?
• ✅ Are there unusual outliers that need investigation?
• ✅ Is the data fresh enough for the use case?
• ✅ Are dates logically consistent?
• ✅ Are data types and formats correct?

These checks can be built into SQL queries, ETL pipelines, data validation processes, and automated data quality monitoring.
The goal is simple: find bad data before bad data becomes a bad decision. 🔎

---

Frequently Asked Questions​

----------------------

What is a data quality check in SQL?​

A SQL data quality check is a query used to identify problems such as missing values, duplicates, invalid relationships, incorrect ranges, unexpected values, stale data, or inconsistent dates.

Why are SQL data quality checks important?​

They help detect problems before incorrect data reaches dashboards, reports, data warehouses, or Machine Learning systems.

What are the most common data quality checks?​

Common checks include NULL validation, uniqueness, referential integrity, range validation, accepted values, volume, outliers, freshness, date consistency, and data format validation.

Can SQL automatically validate data quality?​

Yes. SQL queries can be used for many automated checks, and the results can be integrated into ETL pipelines, scheduled jobs, monitoring systems, or data quality tools.

Is an outlier always bad data?​

No. An outlier may represent a legitimate event. It should be investigated based on the business context before being treated as an error.