The $427/yr cost lesson from BigQuery materialized views

Our on-chain pipeline was costing $1,200/mo in BigQuery queries. Materialised views brought it to $36/mo. That's a 97% reduction.

The mistake: we were querying raw ingestion tables with repeated GROUP BY operations across the same time windows. Every dashboard refresh — every single one — was paying the full scan cost of 72 PB of data. BigQuery charges by bytes processed, and we were processing everything, every time.

The fix was a four-layer materialisation strategy: 1. Raw ingestion → hourly rollups (5-minute windows aggregated) 2. Hourly rollups → daily summaries (per-coin, per-exchange) 3. Daily summaries → weekly snapshots (for ML training windows) 4. Weekly → monthly (for long-term trend queries)

Each layer costs ~1-2% of the layer below it. A query that would scan 50 TB now scans 4 GB. The dashboard that took 14 seconds and cost $2.40 now takes 600ms and costs $0.02.

The principle generalises: if you query the same shape of data more than once, materialise it. This applies to Postgres materialised views, Redis caches, and even memoized API responses. The first time you write the query, you pay full price. Every time after that should be near-free.