In this lesson, you will learn how to leverage INTERSECT and EXCEPT to programmatically validate that your data warehouse mirrors your source system with absolute precision.
When moving data from a transactional source into a data warehouse, simple row counts aren’t enough to guarantee integrity. You need to ensure that the specific rows (and their values) in the source match those in the destination. Set operations are the most efficient way to perform this "diffing" at scale.
Detecting Missing or Mismatched Rows
The EXCEPT operator (or MINUS in some dialects) compares two result sets and returns rows from the first query that do not appear in the second. This is your primary tool for identifying data gaps.
To reconcile a transactions table between your source and your warehouse, you can execute:
-- Find records in source that failed to load into the warehouse
SELECT transaction_id, amount, customer_id
FROM source_transactions
EXCEPT
SELECT transaction_id, amount, customer_id
FROM warehouse_transactions;
If this query returns any rows, your reconciliation has failed. The output gives you the exact records that are missing or corrupted in your warehouse.
Validating Exact Alignment
While EXCEPT identifies missing records, INTERSECT helps you verify the "happy path." It returns only the rows that appear in both the source and the warehouse. If you calculate the count of an INTERSECT and it matches the count of your source_transactions table, you have mathematical proof that every single record in your source has been successfully ingested into your warehouse.
Why use Set Operations over Joins?
You might be tempted to use a LEFT JOIN with WHERE warehouse.id IS NULL, but set operations are often more performant and readable for reconciliation tasks. They automatically handle distinct row comparisons and are optimized by the engine to compare entire row structures rather than manual column-by-column join conditions.
By treating your tables as mathematical sets, you can quickly isolate discrepancies without writing complex, brittle join logic.
Key Takeaway: Use EXCEPT to find missing records during ETL validation and INTERSECT to confirm the total integrity of your data subset compared to the source.