Data warehouse reconciliation is the routine that proves a dashboard number matches the records and rules behind it. We start with one agreed metric definition, a declared grain, and layered checks that run on every load. A faster platform or more data will not settle an argument about what a number means.
Why do two dashboards show different numbers?
Finance, sales, and operations often pull from the same systems and still report different totals. In most cases nobody made a mistake. They are counting different events, cutting off on different dates, or treating cancellations and credits in different ways.
A warehouse project should bring those differences into the open before it turns one version into the company number. If it skips that step, it simply automates the argument. This guide is the process we propose to clients. It is a method, not a report of results from a specific project.
Which metric should you reconcile first?
Pick one business decision with a named owner. For example, an operations lead may need to see orders that shipped but were never invoiced. That is easier to test than a goal like "one source of truth."
Write the definition down. Name the business event, the records that count, the states that are excluded, the date boundary, the currency, and how corrections are handled. Name the one person who can change that definition. Keep booked value, invoiced value, and collected cash as separate measures instead of folding them into one word like revenue.
Then build a short set of test cases with agreed answers: a canceled order, a partial shipment, a credit, a reopened record, and a transaction that crosses the month-end cutoff. These cases become acceptance tests for both the pipeline and the dashboard.
What is grain, and why does it break totals?
Grain is what one row in a fact table stands for. An order, an order line, a shipment line, and a payment are four different grains. Microsoft's fact table guidance describes this as the level at which facts are defined, and notes that it is hard to split measures to a finer level later.
Mixed grains are the most common cause of inflated totals we see in reporting reviews. One order can have several lines, several shipments, and several payments. Join them without care and the amounts multiply.
So we write the grain in plain words before anyone designs a report. For a fulfillment question, the fact might be the shipment line, with the source order line kept as a reference. Invoices sit in their own fact and are compared through a documented rule. Then we test joins with awkward cases and confirm that adding a customer or region filter does not change the total.
What load evidence should the warehouse keep?
Every number should be traceable back to where it came from. Keep the source system, the source record ID, the extraction time, the business event time, and the version of the transformation that produced it.
Each load also needs a clear boundary. Record the extraction window, the rows received, accepted, and rejected, and whether the run finished. If a job fails halfway, the next run must know whether it is continuing, replacing, or replaying that batch. Stable keys stop a replay from creating duplicate facts.
Handle deletes and corrections on purpose. A pipeline that only adds new rows can leave canceled orders in a metric for months. Traceability does not mean keeping every field forever, though. Limit personal data, restrict access, and set retention with the teams that own it.
How should history behave when records change?
Customers change territory. Accounts change owners. Products move between categories. For each important attribute, decide whether a report should show the value at the time of the event or the value today.
Microsoft's dimension table guidance covers the two common patterns. Type 1 overwrites the old value and keeps no history. Type 2 inserts a new dated version of the row, and the guidance says to reserve it for attributes where the historical view matters.
Use Type 2 only where a decision needs it. Tracking history on every field adds work without better answers. When users need both views, label them as two separate measures so a hidden filter never changes the meaning.
How do you reconcile in layers?
We check in four layers, in this order:
- Completeness. Did the expected source window arrive in full?
- Structure. Are required IDs present, types valid, states known, and relationships intact?
- Business totals. Do counts and amounts match the agreed source report at the same cutoff?
- Slices. Do they still match by business unit, date, currency, and status?
A matching grand total is not proof on its own. Missing positive and negative records can cancel each other out. That is why the slice check matters.
Keep a reconciliation log with the check name, batch, expected and observed values, tolerance, owner, and resolution. Every tolerance needs a business reason. A rounding tolerance is fine. A tolerance that hides missing rows is not. When a difference is accepted, say why in the log. If unexplained differences keep returning, the metric should not be labeled as trusted.
How do readers know the data is current?
A dashboard can load perfectly while showing stale data. Show the time of the last completed load and the reporting cutoff, not just the time the page opened. Mark partial loads clearly.
Agree in advance what happens when a check fails. The dashboard might show a warning, keep the last accepted result, or hide a sensitive metric until someone reviews it. Also watch for late records. A backdated transaction can change last month even when today's load is healthy, so decide whether closed periods are restated, locked, or adjusted separately.
What does a good first release include?
A strong first release covers one decision, one approved definition, traceability from source to report, automated checks, named owners for exceptions, and a tested recovery step. Track open reconciliation items, freshness misses, manual fixes, and how long it takes to explain a disputed number.
Expand only after the business can use and explain that first metric. The same discipline applies to the feeds that load the warehouse. Our guide to partner API acceptance tests covers how to prove those feeds recover from failure, and our API integration services page explains how we connect source systems.
Frequently asked questions
Usually because of different date cutoffs, included statuses, grains, join logic, or correction rules. Compare the definitions and a few sample records before blaming either tool.
No. Errors can offset each other. Check counts, IDs, relationships, and key slices as well as the total, all at the same cutoff.
No. Keep versions only where a business question needs the view at the time of the event. Overwriting is simpler and fine for most fields.
Usually not. One valuable metric with an accountable owner is easier to validate. Prove reconciliation and support on that, then expand.
Sources
- Microsoft Learn: Dimensional modeling in Microsoft Fabric Data Warehouse, fact tables
- Microsoft Learn: Dimensional modeling in Microsoft Fabric Data Warehouse, dimension tables
- Microsoft Learn: Slowly changing dimension type 2
Two reports that disagree?
Bring both reports and the decision that depends on them. We will map the definitions, the grain, and the checks that make the number explainable.

