← SQL roadmap

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.

Architect / SpecialistSpecialist · 8–10+ yearsWarehouse architecture

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?
Practise SQL with the AI mentor

Can't find a SQL topic you need for exams or placements? Request it and we'll add it.