Two reports show different revenue totals. Both queries run without errors. Both dashboards look reasonable. Yet one says $482,300 and the other says $497,850.

Do not start by rewriting the SQL. Start by proving that the reports are measuring the same thing, over the same period, at the same level of detail.

Reconciliation is not just matching totals. It is a controlled process for finding exactly where two datasets or reporting outputs diverge—and documenting why.

1. Define what each report is actually measuring

Before comparing numbers, write down the metric definition for both reports. “Revenue” might mean gross sales in one report and net revenue after refunds in another. A shared label does not guarantee a shared definition.

• Metric: gross sales, net sales, recognized revenue, or another measure?
• Time basis: order date, payment date, invoice date, or posting date?
• Population: all orders, completed orders, or only settled transactions?
• Currency: original currency or a converted reporting currency?
• Exclusions: refunds, cancellations, test records, or internal transactions?

If these definitions differ, the discrepancy may be expected. Record the difference instead of forcing the totals to match.

2. Align the reporting window and filters

Check start and end dates, time zones, inclusive versus exclusive end dates, and filters applied in the dashboard layer. A report that includes the final day while another stops at midnight at the start of that day can produce a real gap.

Also compare hidden filters: business unit, region, order status, product type, and data refresh time. Save the filter settings or query parameters so the comparison is reproducible.

3. Compare row counts before totals

Start with the same source tables and the same filters. Compare the number of rows, distinct business keys, null keys, and duplicate keys. If row counts differ, a total comparison alone will not tell you why.

For example, if your transaction table should have one row per transaction, test that assumption:

SELECT
    transaction_id,
    COUNT(*) AS row_count
FROM transactions
WHERE transaction_date >= '2026-10-01'
  AND transaction_date <  '2026-11-01'
GROUP BY transaction_id
HAVING COUNT(*) > 1;

Adapt the dates and key to your dataset. This query identifies transaction IDs appearing more than once in the selected period. Duplicates are not automatically errors—some systems store line items—but they must match the grain expected by the report.

4. Check whether joins are multiplying records

A common cause of mismatched totals is a one-to-many join. If one order matches three rows in a promotion or status table, its order value may be counted three times after the join.

Check the grain of each table and the uniqueness of the join keys. Compare the row count and sum before and after every join, not only at the end of a long query. If the total changes unexpectedly after one join, you have a useful lead.

5. Reconcile at a useful level of detail

When totals differ, break them down by a dimension that can isolate the cause: day, region, source system, transaction status, or business unit. Then compare the same groups across both outputs.

Use a full outer join when you need to identify groups present in one result but missing from the other. The example below assumes each result has already been aggregated to one row per reporting date.

SELECT
    COALESCE(a.report_date, b.report_date) AS report_date,
    a.total_amount AS report_a_amount,
    b.total_amount AS report_b_amount,
    COALESCE(a.total_amount, 0)
      - COALESCE(b.total_amount, 0) AS difference
FROM report_a AS a
FULL OUTER JOIN report_b AS b
    ON a.report_date = b.report_date
WHERE COALESCE(a.total_amount, 0)
   <> COALESCE(b.total_amount, 0)
   OR a.report_date IS NULL
   OR b.report_date IS NULL
ORDER BY report_date;

This shows dates with different totals and dates missing from either side. If your database does not support FULL OUTER JOIN, use an equivalent combination of left joins and a union. For multiple rows per date, aggregate each source first or join on the complete business key.

6. Check nulls, sign conventions, and rounding

Once you isolate the difference, check less obvious causes: null amounts, negative refunds, reversed transactions, decimal precision, currency conversion rates, and rounding at the row level versus the report level.

For example, summing individually rounded line items may not equal rounding the final sum. Agree on the required calculation rule before treating a small difference as a defect.

7. Keep an exception log

Do not close the investigation with “numbers fixed.” Record the expected value, observed value, difference, suspected cause, evidence, owner, resolution, and whether the issue can recur. That turns a one-off investigation into a repeatable control.

A practical reconciliation sequence

1. Confirm the metric definition and reporting grain.
2. Align dates, filters, time zones, and refresh times.
3. Compare row counts, distinct keys, and duplicates.
4. Inspect joins for record multiplication or dropped records.
5. Break the difference down by day or another useful dimension.
6. Check nulls, refunds, sign conventions, currency, and rounding.
7. Document the root cause and add a control to catch it next time.

If the discrepancy remains, reduce the problem to the smallest set of records that reproduces it. A short list of unmatched keys is usually more useful than another full-report rerun.

Related reading

Continue with how to investigate a sudden drop in a dashboard metric, review 10 data validation checks before reporting, or read how to document analytics requirements before touching the data.

Final thought

The goal of reconciliation is not to make two numbers look identical. It is to establish whether they should match, explain why they do or do not, and leave behind enough evidence that another analyst can verify the conclusion.

Want a more structured approach to using AI while keeping your own analytical judgment in control? Explore AI-Assisted Analyst.