Partition pruning

partition-pruning.sql
-- Filtering directly on the partition column lets the engine skip
-- entire partitions before reading any of their data
SELECT * FROM orders
WHERE order_date BETWEEN '2026-08-01' AND '2026-08-10';  -- pruned

-- Wrapping the partition column in a function often defeats pruning --
-- the engine can no longer map the filter directly to partition boundaries
SELECT * FROM orders
WHERE YEAR(order_date) = 2026;  -- may scan every partition
Advertisement

Clustering keys

Clustering keys tell the warehouse how to physically co-locate related rows so queries filtering or joining on those columns scan fewer files or micro-partitions. They pay off most on large tables with a small number of consistently-filtered columns — a smaller table, or one queried unpredictably across many different columns, sees little benefit relative to the ongoing maintenance cost of keeping data clustered.

Avoiding SELECT *

select-star-cost.sql
-- Columnar engines only read the columns a query actually references --
-- SELECT * forces every column to be read regardless of need
SELECT * FROM orders WHERE order_date = CURRENT_DATE;  -- reads all columns

SELECT order_id, amount, status
FROM orders WHERE order_date = CURRENT_DATE;  -- reads only 3 columns

In scan-based billing models such as BigQuery's on-demand pricing, this difference maps directly to bytes scanned — and therefore directly to cost, not just query speed.

Advertisement

Materialized views

materialized-view.sql
CREATE MATERIALIZED VIEW daily_revenue AS
SELECT order_date, SUM(amount) AS total_revenue
FROM orders
GROUP BY order_date;
-- Subsequent reads hit the pre-computed result instead of re-aggregating
-- the full orders table on every single request

This trades a small amount of storage and periodic refresh cost for a large reduction in repeated compute — worthwhile for expensive aggregations queried often, not worthwhile for one-off or rarely-run queries.

Right-sizing warehouses and clusters

An oversized warehouse burns credits or DBUs on capacity the workload never actually uses; an undersized one causes queries to queue and run noticeably slower. The goal is the smallest size that keeps typical workloads running at an acceptable speed — found by monitoring actual query queuing and runtime, not by guessing upward "to be safe."

Common mistakes

  • Wrapping a partition column in a function inside a WHERE clause, silently defeating partition pruning.
  • Using SELECT * in dashboards and scheduled reports that run frequently, compounding the cost of unnecessary column scans.
  • Leaving warehouses at a large fixed size year-round instead of scaling down for predictably lighter periods.
  • Materializing a view that's rarely queried, paying ongoing refresh cost for a query that didn't need pre-computation in the first place.

Key takeaways

  • Filtering directly on partition columns (without wrapping them in functions) is one of the highest-leverage, lowest-effort cost controls.
  • Clustering keys pay off on large, consistently-filtered tables — not universally.
  • SELECT * costs real money in columnar, scan-based billing models, not just query speed.
  • Materialized views trade storage and refresh cost for reduced repeated compute — worth it for frequent, expensive aggregations.
  • Right-sizing compute to actual workload demand, monitored over time, beats guessing at a "safe" larger size.