Build a reconciliation step into every automated report

An automated report should explain how source records become selected records and how selected values become displayed totals. Reconciliation turns that explanation into executable checks that can stop a release when the pieces disagree.

Use controls at several levels. A grand total can match while two regional errors cancel, and two calculations using the same faulty join can agree with each other.

Define the transformation bridge

The original automation lab begins with seven raw event rows. One identical replay is removed, leaving six unique events. Three paid events fall within the configured weekly window, totaling 3,500 paise.

The region totals are North 1,000, South 2,000 and Unknown 500 paise. The unknown region is a visible category; dropping it would make the grouped total smaller than the selected-event total.

The raw-row count, unique count and selected count have different meanings. Record each with its exclusion or deduplication rule rather than expecting every stage to retain the same number of rows.

Verify totals and a known reference independently

import copy
import json
from pathlib import Path
from pipeline import load_source

config = json.loads(Path('report-config.json').read_text())
selected,metrics,evidence = load_source('events.csv','source-manifest.json',config)

def reconcile(rows,report):
    source_total = sum(int(row['amount_paise']) for row in rows)
    grouped_total = sum(group['amount_paise'] for group in report['by_region'].values())
    grouped_count = sum(group['events'] for group in report['by_region'].values())
    if source_total!=report['amount_paise'] or grouped_total!=source_total:
        raise ValueError('amount reconciliation failed')
    if len(rows)!=report['selected_events'] or grouped_count!=len(rows):
        raise ValueError('count reconciliation failed')

reconcile(selected,metrics)
assert [row['event_id'] for row in selected]==['E1','E2','E5']
assert metrics['amount_paise']==3500
assert {region:value['amount_paise'] for region,value in metrics['by_region'].items()}=={
    'North':1000,'South':2000,'Unknown':500}
damaged = copy.deepcopy(metrics)
damaged['by_region']['South']['amount_paise']-=1
try:
    reconcile(selected,damaged)
except ValueError as error:
    assert str(error)=='amount reconciliation failed'
else:
    raise AssertionError('seeded mismatch escaped reconciliation')
print({'reference_total_paise':3500,'seeded_one_paisa_mismatch_detected':True})

The checks compare selected records, headline totals and grouped totals. The explicit expected IDs and regional values provide a golden reference for the synthetic fixture. The mutation demonstrates that a one-paisa presentation error is detected.

Recognize shared-error blind spots

If both selected records and the report are produced by the same wrong eligibility rule, their totals may reconcile perfectly. Internal consistency does not prove that the business definition is correct.

Add independent evidence where available: source-system control totals, separately reviewed eligibility examples, a second implementation of a critical measure or known boundary cases. Explain how independent those controls actually are. Recomputing an “expected” total from the same transformed data is not an independent source check.

Likewise, a matching grand total cannot detect a 100-paise overstatement in North offset by a 100-paise understatement in South. Group-level references and record-level traceability address that different failure mode.

Reconcile amounts at a consistent precision

The fixture stores paise as integers, so exact comparisons are appropriate. For ratios or floating-point calculations, define tolerances based on the calculation and units rather than using an arbitrary large tolerance that hides material errors.

Round for display after calculating and reconciling the underlying measure. If displayed rounded categories no longer sum to a rounded total, explain the rounding convention instead of altering the underlying data to force a visual match.

Currency, tax treatment, refunds and status eligibility must also match across controls. A gross invoice total should not be expected to equal net settled cash without a bridge explaining the difference.

Make failure block the correct downstream step

Run reconciliation before marking an artifact ready for review or distribution. Retain the failed run's diagnostic and source identity so another analyst can reproduce it.

Do not “fix” a mismatch by changing the expected total to the actual total without investigating the cause. If the source was legitimately corrected, create a new reviewed reference and preserve the reason for the change.

Exercise: move 100 paise from South to North while preserving the grand total. Show which checks still pass and add a region-level reference that detects the misallocation.

NeuraPath's Data Analytics with Generative AI course connects data preparation with verifiable reporting. A strong reconciliation design checks both consistency and the business meaning of the values being compared.

Continue learning

This article is part of the Reliable reporting automation sequence. Use the neighbouring tasks when you need the prerequisite or the next application.

• Review the prerequisite or neighbouring task in Retry a failed data extract without duplicating records.
• Continue with Parameterize reports by date and region safely.