Topic 46 of 52 · Expert
How would you reduce the cost of a daily billion-row warehouse query?
Start with measured bytes scanned, slot usage or warehouse time, shuffle volume and output grain. BigQuery bills on bytes under on-demand pricing; capacity pricing and Snowflake credits have different cost models.
MySQL 8-compatible SELECT syntax; sample queries also execute in SQLite 3. Window functions require MySQL 8+.
Detailed answer
Start with measured bytes scanned, slot usage or warehouse time, shuffle volume and output grain. BigQuery bills on bytes under on-demand pricing; capacity pricing and Snowflake credits have different cost models.
Filter partition columns with explicit ranges, project needed columns, cluster on selective frequently filtered keys and pre-aggregate facts before high-cardinality joins. Validate the plan rather than assuming SQL brevity means lower cost.
Materialize reusable increments with a watermark and a documented late-data correction window. Reconcile incremental totals against a periodic full recomputation; retries and replays must not double-count.
Nested data can avoid joins, but UNNEST multiplies rows. BigQuery clustering prunes blocks, not individual lookup rows; Snowflake micro-partition pruning depends on metadata and data organization.
Internal working
Distributed stages scan, shuffle and aggregate partitions. A skewed key can dominate one stage; partition pruning cuts input but cannot fix incorrect join cardinality or skew.
Practical scenario
A dashboard recomputes three years of orders every morning. Maintain daily summaries, rescan only recent correction partitions and audit historical reconciliation.
-- BigQuery Standard SQL (warehouse only)
SELECT customer_id, SUM(amount) revenue
FROM `analytics.orders`
WHERE order_date >= DATE '2026-01-01' AND order_date < DATE '2026-02-01'
GROUP BY customer_id;Common mistakes
- Using SELECT * on large facts.
- Ignoring late arrivals in incremental jobs.
Key takeaways
- Measure the pricing model.
- Prune, project, then aggregate.
- Reconcile increments against trusted totals.
Follow-up challenges
- How do you define a correction window?
- When can materialization cost more than recomputation?