DWH Testing: Data Warehouse Testing Explained with SQL

Your data warehouse is the single source of truth for the entire organization — financial reports, executive dashboards, and machine learning models all depend on it. When the data inside is wrong, everything downstream is wrong too — silently. Data warehouse testing — often shortened to DWH testing — is how you prevent that. This guide covers data warehouse ETL testing end to end, with SQL you can run today.

What is DWH Testing?

DWH testing (data warehouse testing) validates that:

• Data was loaded correctly from all source systems
• Transformation logic was applied accurately (business rules, calculations, lookups)
• Data is complete — no records lost, no duplicates created
• The schema is correct — right tables, columns, data types, and relationships
• Query performance meets SLA requirements

The 5 Types of DWH Tests

1. ETL / Pipeline Testing

Validates data movement from source → staging → target. Key checks: row count reconciliation, transformation accuracy, null handling, duplicate detection, and rejection record capture.

2. Schema Testing

Validates the physical structure of warehouse tables.

-- Verify column data types (SQL Server / PostgreSQL)
SELECT COLUMN_NAME, DATA_TYPE, IS_NULLABLE
FROM INFORMATION_SCHEMA.COLUMNS
WHERE TABLE_NAME = 'fact_sales'
ORDER BY ORDINAL_POSITION;

3. Data Quality Testing

Validates the content of the data itself — nulls, duplicates, out-of-range values.

-- Null check on mandatory columns
SELECT COUNT(*) AS null_count
FROM fact_sales
WHERE order_id IS NULL OR customer_id IS NULL;

-- Duplicate check
SELECT order_id, COUNT(*) AS cnt
FROM fact_sales
GROUP BY order_id
HAVING COUNT(*) > 1;

-- Value range check
SELECT * FROM fact_sales
WHERE quantity < 0 OR price < 0;

4. Performance Testing

Validates that ETL jobs complete within SLA and key queries return within acceptable time. Use ETL monitoring dashboards and SQL execution plans (EXPLAIN ANALYZE in PostgreSQL).

5. Regression Testing

After any ETL change or deployment, re-run all critical tests to confirm existing data was not broken. This is where a maintained test case library pays off.

Critical SQL Validation Patterns

Pattern 1: Row Count Reconciliation

-- Source count
SELECT COUNT(*) FROM source_db.orders
WHERE created_date = CURRENT_DATE - 1;

-- Target count (must match)
SELECT COUNT(*) FROM dw.fact_orders
WHERE etl_load_date = CURRENT_DATE - 1;

Pattern 2: Source-to-Target Comparison

SELECT order_id, customer_id, total_amount
FROM source_db.orders
EXCEPT
SELECT order_id, customer_id, total_amount
FROM dw.fact_orders;
-- Returns records in source but missing/different in target

Pattern 3: Transformation Validation

-- Rule: final_price = unit_price * quantity * (1 - discount)
SELECT order_id,
       ROUND(unit_price * quantity * (1 - discount), 2) AS expected,
       final_price AS actual
FROM dw.fact_orders
WHERE ABS(unit_price * quantity * (1 - discount) - final_price) > 0.01;
-- Should return 0 rows

Pattern 4: SCD Type 2 Validation

-- Only one active record per customer
SELECT customer_id, COUNT(*) AS active_records
FROM dim_customer
WHERE is_active = 1
GROUP BY customer_id
HAVING COUNT(*) > 1;
-- Should return 0 rows

Pattern 5: Referential Integrity

-- Every order must have a valid customer in dim_customer
SELECT f.order_id
FROM fact_orders f
LEFT JOIN dim_customer c ON f.customer_id = c.customer_id
WHERE c.customer_id IS NULL;
-- Should return 0 rows

Test Case Template

Tools for DWH Testing

Where Most Teams Go Wrong

• Testing only on day one. Data quality degrades over time as source systems change. Run your test suite after every ETL cycle.
• Skipping regression tests. An ETL fix for one table often silently breaks another. Always run the full suite after changes.
• Only checking counts. A matching row count does not mean the data is correct. Always validate transformation logic and data quality separately.

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