In this lesson, you will learn how to verify that your dimensional model produces accurate, consistent results by running structured validation queries across different levels of aggregation.
The Logic of Validation
After building your Star Schema or Snowflake model, the final step to ensure your data pipeline is trustworthy is query testing. Even if your joins are technically correct, your data might be double-counted or missing if your schema design doesn’t align with how your facts and dimensions interact.
Validation is the process of checking "bottom-up" consistency. We do this by comparing a highly granular query (e.g., total sales per individual transaction) against a higher-level summary (e.g., total sales per month). If the numbers don't match, you know there is a flaw in your join logic or grain definition.
Practical Query Testing
Imagine you are validating a fact_sales table linked to a dim_product table. You want to ensure that your total revenue matches the source system data. You should run a "sanity check" query to see if the totals align across different grouping levels:
-- Query 1: Total revenue at the most granular level
SELECT SUM(sales_amount) FROM fact_sales;
-- Query 2: Total revenue grouped by product category
SELECT d.category, SUM(f.sales_amount)
FROM fact_sales f
JOIN dim_product d ON f.product_key = d.product_key
GROUP BY d.category;
If the SUM from Query 1 does not equal the sum of the results from Query 2, your model has a "leak." This usually happens because of accidental duplicate joins (many-to-many relationships) or incomplete dimension mapping.
Why This Matters
When you aggregate data, you are looking for "semantic consistency." If your model reports $1,000,000 in revenue for the year but only $800,000 when you break it down by store region, the business users will lose trust in your dashboard. Validation testing forces you to prove that the relationships you built between facts and dimensions hold true, regardless of how much you "zoom in" or "zoom out" of the data.
Key Takeaway: Always validate your schema by comparing granular transaction sums against aggregated dimension totals to ensure your model design produces mathematically consistent results.