BigQuery Cost Optimization — How to Stop Paying for Queries You Do Not Need
BigQuery charges per terabyte scanned. A single analyst running SELECT * on a 10TB table costs around $50. Multiplied across a team running queries all day, unoptimized BigQuery usage can generate surprising bills. These are the practical optimizations that make the biggest difference.
Partitioning — the most impactful change
Partition your tables by date. A query with WHERE event_date = "2026-03-15" on a partitioned table reads only that day's partition — not the full table.
CREATE TABLE myproject.dataset.events
PARTITION BY DATE(event_date)
AS SELECT * FROM source_table;
For a 2-year table queried daily: partitioning reduces bytes scanned by roughly 700x (730 days, querying 1 at a time). This is the single highest-impact optimization for most teams.
Clustering — the second layer of optimization
Clustering co-locates rows with similar values in the same storage blocks. Queries filtering on clustered columns skip entire blocks.
Cluster on columns you frequently filter on after partitioning: user_id, country, product_category.
CREATE TABLE myproject.dataset.events
PARTITION BY DATE(event_date)
CLUSTER BY user_id, country
AS SELECT * FROM source_table;
Combined partitioning and clustering can reduce bytes scanned by 99%+ on well-structured analytical queries.
Never use SELECT *
BigQuery charges for every column in your SELECT. A table with 50 columns — SELECT * reads all 50 columns. SELECT id, revenue, date reads just 3 columns.
Columnar storage means each column is stored separately. Selecting fewer columns directly reduces bytes scanned and cost.
For exploration: use the table preview feature in the BigQuery console — it is free and does not scan any data.
Materialized views and cached results
BigQuery caches query results for 24 hours. An identical query run twice in 24 hours charges only for the first run.
For dashboard queries that run on a schedule: create materialized views. BigQuery maintains them automatically and queries against materialized views scan much less data than the underlying tables.
For recurring aggregations (daily revenue by region): write results to a summary table with a scheduled query rather than re-aggregating the full table on every dashboard load.