Data Quality Checks: The 6 Dimensions with SQL Examples

Bad data costs money: wrong invoices, failed campaigns, reports nobody trusts. Data quality checks are the automated and manual tests that catch these problems early. This guide explains the six data quality dimensions, gives a SQL check for each, and shows how testers and data quality analysts put them together into a framework.

What Are Data Quality Checks?

Data quality checks are rules that measure whether data is fit for use: complete, valid, accurate, consistent, unique and on time. They run on source data before loading, on targets after ETL, and on schedule in production to catch issues before business users do.

The 6 Data Quality Dimensions

A SQL Data Quality Check for Each Dimension

-- Completeness: mandatory fields missing
SELECT COUNT(*) AS missing_mobile FROM dim_customer WHERE mobile IS NULL OR TRIM(mobile) = '';

-- Validity: Indian PIN codes must be 6 digits; status from an allowed list
SELECT COUNT(*) FROM dim_customer WHERE LENGTH(pin_code) <> 6;
SELECT COUNT(*) FROM fact_orders WHERE status NOT IN ('NEW','PAID','SHIPPED','CANCELLED');

-- Accuracy: totals agree with the source system
SELECT (SELECT SUM(amount) FROM src.billing WHERE bill_date = '2026-10-01')
     - (SELECT SUM(amount) FROM dw.fact_orders WHERE order_date = '2026-10-01') AS difference;

-- Consistency: same customer, different city across systems
SELECT c.customer_id FROM crm.customers c
JOIN dw.dim_customer d ON d.customer_id = c.customer_id AND d.is_current = 'Y'
WHERE c.city <> d.city;

-- Uniqueness: duplicate business keys
SELECT customer_id, COUNT(*) FROM dw.dim_customer WHERE is_current = 'Y'
GROUP BY customer_id HAVING COUNT(*) > 1;

-- Timeliness: latest data loaded
SELECT MAX(load_ts) AS last_load FROM dw.fact_orders;

Each query should return 0 (or a value within an agreed threshold). Any other result is a data quality issue to log. More validation techniques: data validation in ETL.

A Simple Data Quality Framework

1. Profile the data to learn what "normal" looks like: data profiling.
2. Define rules per critical table and column, mapped to a dimension, with a threshold and an owner.
3. Automate the rules after every load (SQL scripts, Python, dbt tests or Great Expectations).
4. Store results in a results table: rule, run time, failed rows, pass/fail.
5. Alert and report: email or chat alerts on failure and a simple data quality dashboard.
6. Fix at the source and track recurring issues.

# Minimal rule runner: each rule is a SQL query that should return 0
RULES = {
    "completeness_mobile": "SELECT COUNT(*) FROM dim_customer WHERE mobile IS NULL",
    "uniqueness_customer": """SELECT COUNT(*) FROM (SELECT customer_id FROM dim_customer
                              GROUP BY customer_id HAVING COUNT(*) > 1) t""",
}
for name, query in RULES.items():
    failed = run_query(query)[0][0]          # your DB helper
    print(name, "PASS" if failed == 0 else f"FAIL ({failed} rows)")

Data Quality Tools

What Does a Data Quality Analyst Do?

A data quality analyst defines and monitors data quality rules, investigates issues with SQL, works with business owners on fixes and reports quality metrics. The role overlaps strongly with ETL testing: both rely on SQL, profiling and reconciliation. Many ETL testers in India move into data quality analyst or data quality engineer roles. See ETL tester roles and responsibilities.

Common Data Quality Issues

• Duplicates from re-run loads or missing keys
• Leading/trailing spaces and inconsistent case in names and codes
• Wrong date formats (DD/MM vs MM/DD) and time zones
• Default values hiding missing data (for example 01-01-1900)
• Late-arriving data and partial loads

Data testing skills: SQL for testers · data migration testing · Power BI testing · big data testing · ETL tools list · ADF & Databricks testing · database testing interview questions

More guides for Indian students: ETL full form · ETL testing with example · ETL testing jobs in India · ETL testing course in India · ETL training in Hyderabad, Bangalore & Pune · best ETL testing course on Udemy · ETL testing roadmap · ETL tester vs ETL developer · interview questions for freshers · free ETL testing mock test · SQL for testers · QA to data engineer · ETL testing kya hai (Hindi)

Frequently Asked Questions

What are data quality checks?

Data quality checks are rules that measure whether data is complete, valid, accurate, consistent, unique and timely, usually written as SQL or code that runs after each data load.

What are the 6 dimensions of data quality?

Completeness, validity, accuracy, consistency, uniqueness and timeliness. Some frameworks add others such as integrity or conformity.

How do you check data quality with SQL?

Write a query per rule that counts violating rows, for example NULLs in mandatory columns, invalid values, duplicates or total mismatches, and expect 0.

What does a data quality analyst do?

They define and monitor data quality rules, investigate issues with SQL, coordinate fixes with data owners and report quality metrics.

Is data quality part of ETL testing?

Yes. Data quality checks are one of the core ETL testing types, alongside completeness, transformation and reconciliation testing.

AN

Written by

Asim Noaman Lodhi

Certified Google Partner · QA Consultant · 12+ Years IT

QA consultant specializing in ETL testing and data quality. Trained 913+ students to transition into data testing roles through hands-on, real-world instruction.

4.5 Rating 913+ Students Google Partner 79 Lectures