Stripe data in your warehouse: syncing for reconciliation

The reconciliation join

Charges joined to transfers joined to reversal sums, proportional expectation computed inline:

SELECT c.id,
       ROUND((c.amount_refunded / c.amount) * t.amount) AS expected,
       COALESCE(r.reversed_total, 0)                    AS actual,
       ROUND((c.amount_refunded / c.amount) * t.amount)
         - COALESCE(r.reversed_total, 0)                AS missing
FROM charges c
JOIN transfers t ON t.source_charge = c.id
LEFT JOIN (SELECT transfer_id, SUM(amount) reversed_total
           FROM reversals GROUP BY 1) r USING (transfer_id)
WHERE c.amount_refunded > 0
  AND missing > 2;

Edge cases SQL hides

Standalone fee refunds misread as leaks without net-margin context. Multi-partial accumulation tripping strict comparisons sans tolerance. Currency mixing corrupting sums absent per-currency grouping. Zero-decimal currencies distorting displays. Every clause exists because someone hit it.

Latency is the killer caveat

Sync cadence equals detection latency equals recovery-window decay. Daily-sync warehouses discover findings whose windows died days prior. Warehouse-for-analysis, events-for-recovery is the honest division — each doing what its timing supports.

Feeding scanners from warehouses

NDJSON exports from BigQuery paste directly into scanners — bridging analytical discovery and computational verification without bespoke plumbing.