Data Validation in ETL: Techniques, SQL Queries & Best Practices

A data pipeline can run successfully, report zero errors, and still load completely wrong data into your warehouse. The ETL process completed — but the data is wrong. Revenue is overstated. Customer records are duplicated. Dates are off by a timezone.

Data validation catches these silent failures. It's the practice of systematically verifying that data meets defined quality rules at every stage of the ETL pipeline. This guide gives you the specific techniques, SQL queries, and frameworks to validate data effectively.

What Is Data Validation in ETL?

Data validation in ETL is the process of checking that data conforms to expected rules after extraction, transformation, and loading. It answers three questions:

1. Is the data complete? — Did all records arrive? Are there missing rows or fields?
2. Is the data accurate? — Do values match the source? Were transformations applied correctly?
3. Is the data consistent? — Are formats uniform? Do relationships hold? Are there duplicates?

The 8 Core Validation Techniques

1. Row Count Validation

The most fundamental check — compare the number of records in source vs. target.

SELECT 'Source' AS system, COUNT(*) AS row_count
FROM source_db.orders
UNION ALL
SELECT 'Target', COUNT(*)
FROM warehouse.fact_orders;

2. Column-Level Validation

Verify that individual field values match between source and target. Compare specific columns row by row using a JOIN on the business key.

-- Find mismatches in specific columns
SELECT s.order_id,
       s.customer_name AS source_name,
       t.customer_name AS target_name
FROM source_db.orders s
JOIN warehouse.fact_orders t ON s.order_id = t.order_id
WHERE s.customer_name != t.customer_name;

3. NULL Validation

Check that required fields are never NULL and that optional fields have NULLs only where expected.

-- Multi-column NULL check
SELECT
  SUM(CASE WHEN customer_id IS NULL THEN 1 ELSE 0 END) AS null_customer_id,
  SUM(CASE WHEN email IS NULL OR email = '' THEN 1 ELSE 0 END) AS null_email,
  SUM(CASE WHEN order_date IS NULL THEN 1 ELSE 0 END) AS null_order_date,
  COUNT(*) AS total_rows
FROM warehouse.dim_customer;

4. Duplicate Validation

Ensure business keys are unique where they should be. Duplicates are the most common ETL bug, especially with incremental loads.

SELECT order_id, COUNT(*) AS cnt
FROM warehouse.fact_orders
GROUP BY order_id
HAVING COUNT(*) > 1;

5. Transformation Validation

Verify that business rules were applied correctly by re-computing the expected result from source data and comparing it against the target.

6. Referential Integrity Validation

Every foreign key in a fact table must point to a valid row in the dimension table.

-- Find orphan records
SELECT f.order_id, f.customer_key
FROM warehouse.fact_orders f
LEFT JOIN warehouse.dim_customer d
  ON f.customer_key = d.customer_key
WHERE d.customer_key IS NULL;

7. Aggregate Validation

Compare summary metrics (SUM, AVG, MIN, MAX) between source and target. Catches issues that row-level checks miss.

SELECT 'Source' AS sys,
       SUM(amount) AS total, AVG(amount) AS avg_amt,
       MIN(amount) AS min_amt, MAX(amount) AS max_amt
FROM source_db.orders
UNION ALL
SELECT 'Target',
       SUM(amount), AVG(amount),
       MIN(amount), MAX(amount)
FROM warehouse.fact_orders;

8. Range and Format Validation

Check that values fall within expected ranges and follow correct formats.

-- Out-of-range dates and negative amounts
SELECT * FROM warehouse.fact_orders
WHERE order_date > GETDATE()
   OR order_date < '2000-01-01'
   OR amount < 0;

When to Validate: Stage-by-Stage

Automating Data Validation

Manual validation doesn't scale. Here's how to automate:

1. Build a validation query library. Store parameterized queries in version control. Each query validates one rule.
2. Schedule validation runs. Run queries automatically after every ETL load using your scheduler (cron, Airflow, Control-M).
3. Set thresholds and alerts. Define acceptable tolerance (e.g., row count within 1% of source) and alert when exceeded.
4. Log results to a control table. Track validation outcomes over time to spot trends and regressions.
5. Use AI for anomaly detection. AI agents can flag statistical anomalies that threshold-based checks miss — distribution shifts, new categories, sudden NULL spikes.

Common Validation Failures and Root Causes

For more examples, see What Is ETL Testing? and the ETL Testing Tutorial.

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