Validating a report against its source

Why this matters

A report's numbers pass through many hands before reaching a reader: extracted, joined, filtered, aggregated, formatted and visualised. An error at any step reaches the page looking exactly like a correct figure. Validation is the work of proving, before release, that the report says what the source data says.

The exam's objective here is to validate reporting accuracy and solve reporting issues through validation and review. This lesson covers recomputing headline numbers independently, control totals, end-to-end spot checks, validating after every change, and recording what was checked.

The lesson

Recomputing a report's headline numbers independently

The strongest check of a figure is to calculate it a second way and compare. If a dashboard shows total 2025 revenue, recompute it directly from the source with a separate query, written without looking at the dashboard's logic, and check that the two agree.

Independence matters. Re-running the report's own query only proves it gives the same answer twice. A different path, such as a direct SQL query against the source instead of the BI tool's model, or a total from the finance system instead of the sales database, will expose errors in the report's joins, filters and calculations.

Which numbers to recompute:

• the headline KPIs, because they are what readers act on;
• at least one figure at each level of detail, such as a total, a regional subtotal and one individual value;
• any figure that looks surprising, whether unexpectedly good or bad.

When the recomputed figure differs, the reconciliation techniques from domain 3 find why: break both down by the same dimensions until the difference is located.

Row counts and control totals through the pipeline

A control total is a sum or count calculated at one stage and checked at the next, to prove nothing was lost or duplicated in between. It is a standard technique in finance and applies equally to data pipelines.

Typical controls include:

• Row counts at each stage: records in the source, extracted, loaded, transformed, and shown in the report.
• Totals of key measures, such as total quantity or total revenue, which should be preserved by steps that only reshape data.
• Distinct counts of keys, such as the number of distinct customers, which should not change unexpectedly.
• Hash totals: sums of a field with no business meaning, such as the sum of order IDs, used purely to detect missing or altered records.

Each control is compared with its expected value, and any difference is explained before the report is released. The course's practice data offers an easy one: 2,000 orders in the source should mean 2,000 order rows after joining to customers, and the total quantity should be identical before and after.

Spot-checking individual records end to end

Totals can match while individual records are wrong, for example when two errors cancel out. Spot checks follow individual records all the way through.

Pick a handful of records, some at random and some chosen deliberately, such as the largest value, an edge case, or a record known to be unusual, and trace each from the source system, through every transformation, to where it appears in the report. Check that the values, categories and dates are right at every step.

Records worth choosing deliberately include:

• boundary cases: an order placed at 23:59 on the last day of a month, or at midnight in another time zone;
• special cases: refunds, cancellations, zero values, missing fields;
• recently changed records, whose history may be handled differently.

Spot checks are also the best way to validate definitions: tracing one customer through a "churned customers" report shows exactly what the report means by churned.

Validating after every change, not only the first release

Reports are validated carefully when they are first built and then trusted indefinitely. But reports change, and so does everything around them: the source schema, the pipeline, the definitions, the BI tool.

Validation should be repeated whenever something changes:

• after a change to the report's logic, measures or filters;
• after a change to the source data or pipeline, such as a new field, a renamed column or a new source system;
• after an upgrade of the reporting tool or libraries;
• periodically, even when nothing is known to have changed, because something may have.

Automating the checks makes this practical. Control totals and reconciliations to a trusted source can run every time the data refreshes, with an alert when they fail, so a problem is found by a check rather than by a reader. Regression testing, comparing a report's output after a change with its output before, shows whether the change affected anything it should not have.

Recording what was checked and by whom

Validation that is not recorded cannot be relied on, because nobody can tell what was checked, when, or how thoroughly.

A simple validation record for each release notes:

• what was validated: the figures recomputed, the control totals checked, the records spot-checked;
• the results, including any differences found and how they were resolved;
• the data as of which the validation was done;
• who performed it, and who reviewed or approved it.

The record serves several purposes. It gives readers and auditors evidence that the report was checked. It shows reviewers what has and has not been covered. And it makes the next validation faster, because the checks, and the expected results, are already written down. For reports used in regulated or financial decisions, such records are often required.