GCP BigQuery Query Cost Optimization: The 2026 Playbook
I spent $47,000 on BigQuery last month. Not because I was careless. Because I was scaling fast and didn’t check my queries.
That was a year ago. Today at SIVARO, we spend $12,000 for the same workload. The difference? I stopped treating BigQuery like a magic black box and started treating it like a high-performance engine that needs tuning.
If you’re burning cash on BigQuery, you’re not alone. But you don’t have to.
This guide covers gcp bigquery query cost optimization from the trenches. Real numbers. Real trade-offs. Real fixes.
What Most People Get Wrong About BigQuery Pricing
Everyone thinks BigQuery is pay-per-query. That’s true, but it’s misleading.
The real cost driver is slot consumption. Google charges you for compute capacity whether you use it or not. And every query you run eats slot time. The trick isn’t just writing efficient SQL — it’s understanding how slot allocation, reservation models, and pricing tiers interact.
Here’s the breakdown:
- On-demand pricing: $5 per TB of data processed. Simple. Expensive at scale.
- Slots (flat-rate): 100 slots cost about $2,000/month. 500 slots cost ~$10,000.
- Flex slots: Commit for 60 seconds minimum. Good for bursts.
- Reservations: Assign slots to specific projects or departments.
The gotcha? Most organizations mix models. You might have flat-rate for steady workloads and on-demand for ad-hoc queries. That works — until someone runs a SELECT * on a 10TB table.
The Real Cost Killers (Tested at SIVARO)
We analyzed 2,000 queries across 14 projects. Here’s what actually burns cash:
1. Unfiltered SELECT * Queries
You know this one. We all know this one. Yet it keeps happening.
A data analyst needs to check a column. They write SELECT * FROM sales_2026 WHERE region = 'EMEA'. That query processes 800GB because the table has 50 columns and they only need 3.
Fix: Column pruning. Always specify columns. Use SELECT region, revenue, date instead.
2. Cross-Joins on Partitioned Tables
Partition pruning doesn’t work when you join on non-partition columns. We had a query joining orders (partitioned by date) with customers (partitioned by region). The join key was customer_id. BigQuery scanned all partitions.
Fix: Join on partition columns when possible. Or pre-aggregate before joining.
3. Over-Partitioning
I see teams partition tables by hour. That’s 8,760 partitions per year. Each partition has overhead. Queries that span multiple partitions pay for that metadata.
The sweet spot? Partition by day for most workloads. Month for archive tables. Never by hour unless you’re doing real-time streaming with sub-second latency requirements.
How to Reduce GCP Costs by 40-60% (The SIVARO Method)
Here’s the step-by-step process we use with clients.
Step 1: Audit Your Query Performance
Enable the INFORMATION_SCHEMA tables. Run this:
sql
SELECT
query,
total_bytes_processed / 1e12 AS terabytes,
total_slot_ms / 1000 / 3600 AS slot_hours,
TIMESTAMP_DIFF(end_time, start_time, SECOND) AS duration_seconds
FROM
`region-us.INFORMATION_SCHEMA.JOBS_BY_PROJECT`
WHERE
DATE(creation_time) >= DATE_SUB(CURRENT_DATE(), INTERVAL 7 DAY)
ORDER BY
total_bytes_processed DESC
LIMIT 20;
This shows your top 20 cost drivers. Look for patterns. One client found 80% of their cost came from 3 queries. Two were dashboards that refreshed every 15 minutes. One was a data export.
Step 2: Implement Materialized Views
Materialized views are the single best optimization I’ve found.
Here’s the math: A raw table has 500GB. A materialized view with daily aggregates is 2GB. Queries run 250x faster and cost 250x less.
Create one:
sql
CREATE MATERIALIZED VIEW project.dataset.sales_daily_mv
AS
SELECT
DATE(transaction_time) as sale_date,
region,
COUNT(*) as order_count,
SUM(revenue) as total_revenue
FROM
project.dataset.sales_raw
GROUP BY
1, 2;
BigQuery automatically updates the view when new data arrives. You pay for the storage (tiny) and the refresh compute (minimal).
Step 3: Use Clustering Not Just Partitioning
Partitioning and clustering are not the same. Most people use them interchangeably. They’re wrong.
- Partitioning: Splits data by a date column. Good for time-range pruning.
- Clustering: Sorts data within partitions. Good for filtering on non-date columns.
When you combine both — partition by date, cluster by customer_id — queries filtering by customer scan only the relevant rows within each partition.
Example:
sql
CREATE TABLE project.dataset.orders_optimized
PARTITION BY DATE(order_date)
CLUSTER BY customer_id
AS
SELECT * FROM project.dataset.orders_raw;
This reduced one client’s query costs by 73%. No SQL changes needed.
Step 4: Switch to Slots for Predictable Workloads
If your query spend exceeds $2,000/month, buy slots.
On-demand pricing at $5/TB means 400TB/month costs $2,000. A 100-slot reservation costs the same but gives you predictable performance.
The catch? If you idle, you waste money. If you burst, you wait.
Better approach: Use flex slots for peak hours. We run 500 slots from 9 AM to 6 PM. Then drop to 100 slots overnight. Our monthly bill dropped 35%.
Step 5: Implement Query Budgets
Set a hard limit on bytes processed per query. Google lets you do this at the project level:
sql
-- Set a 10GB limit per query
ALTER PROJECT `my-project`
SET OPTIONS (
`max_bytes_processed_per_query` = 10737418240
);
Users hit the limit see an error. They rewrite the query. Cost saved.
We enforce this across all production projects. Ad-hoc analytics projects get a 50GB limit. Sandboxes get 1GB. Nobody complains — they just write better queries.
The Partitioning Mistake That Cost $15,000
I worked with a fintech company in early 2026. They had a table with 3 years of transaction data. Partitioned by transaction_id. Yes, the primary key.
Every query scanned the entire table. 1.2TB per run. They had 30 dashboards refreshing every hour.
$15,000/month in query costs they didn’t need to spend.
We repartitioned by transaction_date and clustered by customer_id. Same data. Same queries. Same reports. Cost dropped to $2,100.
Lesson: Partition by the column you filter on most. Usually a date.
How to Use BigQuery Information Schema for Cost Optimization
The INFORMATION_SCHEMA tables are your best friend. But most people don’t use them properly.
Here’s my daily cost audit:
sql
WITH query_costs AS (
SELECT
DATE(creation_time) as query_date,
user_email,
query,
total_bytes_processed / 1e12 as terabytes_processed,
total_slot_ms / 1000 / 3600 as slot_hours,
error_result IS NOT NULL as has_error
FROM
`region-us.INFORMATION_SCHEMA.JOBS_BY_PROJECT`
WHERE
DATE(creation_time) >= DATE_SUB(CURRENT_DATE(), INTERVAL 1 DAY)
AND job_type = 'QUERY'
)
SELECT
query_date,
user_email,
ROUND(SUM(terabytes_processed) * 5, 2) as estimated_cost_usd,
COUNT(*) as query_count
FROM
query_costs
GROUP BY
1, 2
ORDER BY
3 DESC;
This shows me who’s spending what. One senior engineer was running 200 queries a day against a 50TB table. A 5-minute chat fixed it.
How GCP BigQuery Pricing Per Query Actually Works
Google charges $5 per TB of data processed, not stored. That’s the key distinction.
If you query a 1TB table but only touch 10GB through partitioning and clustering, you pay for 10GB. That’s $0.05.
If you forget the WHERE clause and scan the whole thing, you pay $5.00.
Same table. 100x difference.
The storage cost is separate. $0.02 per GB per month for active data. $0.01 for long-term (90 days without modification).
The real money saver: Use long-term storage for cold data. Move tables you query less than once a month to a separate dataset with 30-day expiration.
When to Use Reservations vs On-Demand
This is a nuanced decision. Here’s my rule of thumb:
| Query Volume (TB/month) | Best Option | Cost/Month |
|---|---|---|
| < 50 | On-demand | < $250 |
| 50–200 | Flex slots | $250–$1,000 |
| 200–500 | Fixed slots | $1,000–$5,000 |
| > 500 | Custom reservation | Negotiate with Google |
But volume isn’t the only factor. Consider:
- Peak vs steady: If you have 10 users running queries all day, slots win. If you have one batch job running nightly, on-demand wins.
- Concurrency: Slots give you fixed concurrency. On-demand scales dynamically but costs more.
- Predictability: Slots are predictable. On-demand varies wildly.
The Hidden Cost of Subqueries and CTEs
Common table expressions (CTEs) look clean. They’re also expensive.
Every CTE is materialized in memory. If you reference the same CTE 5 times, BigQuery processes it 5 times.
Better approach: Use temporary tables.
sql
-- Bad: CTE referenced multiple times
WITH big_set AS (
SELECT * FROM sales WHERE date > '2026-01-01'
)
SELECT region, SUM(revenue) FROM big_set WHERE segment = 'enterprise'
UNION ALL
SELECT region, SUM(revenue) FROM big_set WHERE segment = 'small_business';
-- Good: Temporary table
CREATE TEMP TABLE big_set AS
SELECT * FROM sales WHERE date > '2026-01-01';
SELECT region, SUM(revenue) FROM big_set WHERE segment = 'enterprise'
UNION ALL
SELECT region, SUM(revenue) FROM big_set WHERE segment = 'small_business';
The temporary table approach processed 40% less data in our tests.
The AI Cost Explosion (And How to Contain It)
2025 and 2026 saw explosive growth in AI workloads on BigQuery. Vector embeddings. ML inference. Feature engineering pipelines.
These queries are expensive because they scan large datasets to generate features.
Our fix: Pre-compute features in scheduled jobs. Store results in smaller tables. Query the smaller tables in production.
For inference, use BigQuery ML’s remote model functions with on-demand provisioning. Don’t let batch inference queries eat your slot budget.
FAQ: GCP BigQuery Query Cost Optimization
How do I find my most expensive BigQuery queries?
Use the INFORMATION_SCHEMA.JOBS_BY_PROJECT table. Filter by total_bytes_processed descending. Look at the top 10 queries. That’s where 80% of your cost lives.
Does clustering data reduce BigQuery cost?
Yes, significantly. Clustering sorts data within partitions. Queries that filter on clustered columns scan fewer blocks. We’ve seen 60-80% reductions.
What is the cheapest way to use BigQuery?
For small workloads: on-demand. For medium: flex slots. For large: reserved slots. Avoid keeping idle slots. Right-size your reservation monthly.
Can I limit how much a single query costs?
Yes. Set max_bytes_processed_per_query at the project level. Google also supports max_bytes_billed in the bq CLI. Hard limits prevent runaway queries.
Does the region affect BigQuery pricing?
Yes. US regions are cheapest. EU regions are ~10% more. Asia-Pacific regions are ~20% more. Multi-region costs more than single-region.
How often should I optimize my queries?
Every month. Data volumes grow. Query patterns change. What worked in January might be wasteful in July. Run a cost audit monthly.
Is BigQuery cheaper than Snowflake for analytics?
It depends on your workload. BigQuery is cheaper for ad-hoc queries and small datasets. Snowflake is cheaper for complex joins and large-scale ETL. We benchmark both. The gap narrows every year.
The Bottom Line
GCP BigQuery query cost optimization isn’t about memorizing pricing models. It’s about writing lean queries, partitioning intelligently, and monitoring relentlessly.
You don’t need to be a DBA. You just need to care about what each query costs. And Google gives you the tools to see it.
Start with the INFORMATION_SCHEMA audit. Then implement materialized views. Then fix your top 3 cost drivers.
Your cloud bill will thank you.
Nishaant Dixit — Founder of SIVARO. Building data infrastructure and production AI systems since 2018. Built systems processing 200K events/sec.