When a financial revenue dashboard diverges from the billing system, begin by clarifying business definitions and operational boundaries before writing investigative SQL queries. Once definitions are aligned, perform step-by-step reconciliations of record counts and aggregate sums across every pipeline layer, from source to bronze, silver, and gold, to locate the exact point where numbers diverge.
Triaging discrepancies between source and gold
Discrepancies in revenue numbers often stem from subtle differences in accounting interpretations rather than corrupted pipeline code. Clarify these fundamentals with finance immediately:
- Gross versus net revenue: Check whether finance is querying gross transaction volume while the dashboard displays net revenue after subtracting payment processor fees, coupons, and sales tax.
- Timezone cutoffs: Billing systems frequently aggregate daily totals using local business hours, such as US Eastern time, whereas warehouse pipelines commonly partition tables on UTC midnight. A three-hour boundary shift moves late-night transactions into adjacent days, creating consistent discrepancies.
- Currency conversions: Verify the exchange rates applied. Does the billing system record the settled conversion at transaction time, while the reporting pipeline applies a daily average exchange rate?
- Refund and chargeback handling: Confirm whether refunds are debited against the original transaction date or appended as negative ledger entries on the day the refund processed.
Tracing reconciliation failures across layers
After aligning on definitions, execute reconciliation queries comparing record counts and total transaction sums across each pipeline stage:
- Layer 1 (Source to Bronze): Compare raw billing system export totals against raw landed files or tables to confirm that no extraction files failed or dropped during ingestion.
- Layer 2 (Bronze to Silver): Verify deduplication logic and filtering conditions. If an inner join drops rows due to missing exchange rates or customer records, record counts will drop between Bronze and Silver.
- Layer 3 (Silver to Gold): Check aggregation logic and dimensional modeling grains in the reporting marts. Look for improper joins that accidentally filter records or group transactions incorrectly.
Billing System --> Bronze (Raw Landing) --> Silver (Cleansed) --> Gold (Reporting Mart) $1,000,000 $1,000,000 $970,000 $970,000 Count: 10,000 Count: 10,000 Count: 9,700 (Drop!) Count: 9,700
Isolating the drop to a specific layer exposes the technical root cause:
- Inspect late-arriving data: If the billing system allows transactions to settle hours after capture, late-arriving events might miss the batch ingestion window, requiring lookback partitions during daily runs.
- Inspect BI dashboard filters: Check the reporting layer for hidden default filters, such as excluding test accounts, filtering out specific payment gateways, or dashboard caching delays.
Documenting standards and permanent guards
Once the discrepancy is diagnosed and resolved, document the agreed revenue calculation formulas, timezone cutoffs, and refund treatments in the shared data catalog. Finally, build an automated daily reconciliation test that compares billing system totals against gold revenue metrics, alerting the data team before business stakeholders open their morning reports.