In this lesson, you will learn how to design automated reconciliation tests that ensure your fact table metrics perfectly mirror your source system data.
Why Reconciliation Matters
A fact table is the "source of truth" for your business analytics. If your report says you made 1.1M, stakeholders will lose trust in the entire data platform. Automated reconciliation is the practice of running "balance checks" between your source data and your modeled fact table to catch discrepancies before they reach your end users.
Designing the Reconciliation Test
To validate integrity, you must compare the aggregated totals from your raw source data against the aggregated totals in your final fact table.
Imagine you are loading sales_fact. You should write a script that calculates the total sales_amount for a specific day in the source database and compares it to the sum of the same metric in your sales_fact table.
-- Automated Reconciliation Query
WITH source_totals AS (
SELECT SUM(price * quantity) as total_revenue
FROM raw_transactions
WHERE transaction_date = '2023-10-01'
),
fact_totals AS (
SELECT SUM(sales_amount) as total_revenue
FROM sales_fact
WHERE date_key = '20231001'
)
SELECT
CASE WHEN s.total_revenue = f.total_revenue THEN 'PASS' ELSE 'FAIL' END as status
FROM source_totals s, fact_totals f;
Ensuring Accurate Business State
Beyond raw totals, you must validate the "business state." For example, if your fact table uses an accumulating snapshot to track order status, your reconciliation test should verify that the count of "Open" orders in the fact table matches the current count in the source ERP system.
If the numbers don't match, your test should trigger an alert. This allows you to investigate issues—like missing late-arriving records or join errors—immediately rather than waiting for a business user to find a "wrong number" in their dashboard. By treating these reconciliation checks as a mandatory part of your data pipeline, you ensure that every row in your fact table accurately reflects the atomic business process it represents.
Key Takeaway: Automated reconciliation acts as a financial-style audit for your data, guaranteeing that the metrics in your fact tables match the source systems through systematic, repeatable balance checks.