Start with objectives and priority: quickly determine whether the 20% gap is systemic (ongoing) or one-off, and whether it’s timing, scope, or calculation. Triage in this order: scope & definitions, timing/recognition, data pipeline errors, adjustments (refunds/chargebacks), and aggregation/duplication.Likely root causes (prioritized)- Different revenue definitions (booked vs recognized, gross vs net, invoice vs cash)- Recognition timing/window mismatch (finance uses month-of-invoice; analytics uses event time)- Refunds/chargebacks and credit memos applied after analytics extract- Currency conversion and FX forward/backdated rates- Timezone misalignment leading to different invoice dates- Double-counting or missing joins in ETL (order lines vs invoice lines)- Exclusions/filters (internal test accounts, discounts, partner passes)Immediate checks & queries (run first)1) Verify totals by definition and period:sql
-- Analytics view totals by day
SELECT date_trunc('day', event_time) as day, SUM(amount) as analytics_rev
FROM analytics.payments
WHERE event_time BETWEEN '2025-10-01' AND '2025-10-31'
GROUP BY 1;
sql
-- Finance GL totals by day
SELECT posting_date, SUM(amount) as finance_rev
FROM finance.gl_entries
WHERE account IN ('4000','4010') AND posting_date BETWEEN '2025-10-01' AND '2025-10-31'
GROUP BY 1;
sql
-- Join invoices to payments to find unmatched/inconsistent rows
SELECT i.invoice_id, i.invoice_date, i.currency, i.total AS invoice_total, p.sum_payments
FROM finance.invoices i
LEFT JOIN (
SELECT invoice_id, SUM(amount) as sum_payments FROM analytics.payments GROUP BY invoice_id
) p ON i.invoice_id = p.invoice_id
WHERE i.invoice_date BETWEEN '2025-10-01' AND '2025-10-31';
sql
SELECT date_trunc('day', refund_date), SUM(refund_amount) FROM finance.refunds
WHERE refund_date BETWEEN '2025-10-01' AND '2025-11-15' GROUP BY 1;
sql
-- Compare converted totals using analytics FX vs finance FX table
SELECT cur, SUM(amount) AS local_sum, SUM(amount * fx.rate) AS usd_sum
FROM analytics.payments a JOIN finance.fx_rates fx ON a.currency = fx.currency AND fx.date = date_trunc('day', a.event_time)
WHERE a.event_time BETWEEN ...
GROUP BY cur;
sql
SELECT payment_id, COUNT(*) FROM analytics.payments GROUP BY payment_id HAVING COUNT(*)>1;
