BigQuery Cost Optimization: Complete Guide for Data Teams

By Ido Arieli Noga
July 5, 2026 | 5 min read

BigQuery is easy to get started with. Costs, however, are not so easy to control. 

Part of what makes BigQuery so appealing is its serverless model: no infrastructure to manage, no warehouses to size, no clusters to spin up or down. But that same serverless abstraction hides a billing model that punishes inattention. 

A single analyst running an unfiltered scan on a 10 TB table costs $62.50 instantly. Scale that across a team, add in recurring pipelines, and layer on AI-driven workloads generating thousands of autonomous queries, and the monthly bill stops being predictable quickly. 

The standard advice to partition your tables, avoid SELECT*, and using caching is real and worth following. But it only takes you so far. 

The deeper issue here is that even after you’ve done the right things at the query and schema level, you’re still making fundamentally binary decisions about how to pay for compute: on-demand or reservations, pay per buy or commit to slots. That decision, made once at the organization level, applies to every query regardless of if it’s the right call for that specific job. 

This guide covers the full picture: 

  • Where your BigQuery costs actually come from 
  • Basic SQL-level BigQuery optimization tip
  • How to use schema design to improve query costs
  • Advanced BigQuery optimization techniques

Understanding Where BigQuery Costs Actually Come From

Before you start optimizing, you need to know what you’re working with. BigQuery costs fall into three buckets: 

  • Compute
  • Storage
  • Ingestion

Compute: On-Demand Pricing vs. Capacity Pricing

Computes dominate the bill for almost every active data team. BigQuery gives you two ways to pay for query processing: 

  • On-demand pricing: Charges $6.25 per TB scanned (U.S. multi-region). The first 1 TB per month is free. This model makes sense for low-volume or highly unpredictable workloads where you can’t justify a capacity commitment. 
  • Capacity pricing (BigQuery edition): Moves you to a slot-based model. Slots are virtual CPUs. You purchase capacity and queries consumed from that pool. 

The commonly cited break-even point sits around ~400 to 500 TB per month, though the exact threshold depends on discounts and workload patterns. At that level of sustained on-demand usage, capacity pricing typically becomes cheaper. But that number is a blunt instrument. It describes average behavior across your entire environment. 

Storage Costs

Storage pricing is simpler but easier to slip up with. It comes in two parts: 

  • Active storage: Tables or partitions updated in the last 90 days. These cost $0.02 per GB per month. 
  • Inactive storage: Tables untouched for more than 90 consecutive days. These drop to $0.01 per GB per month, a 50% discount that requires no manual action. 

For partitioned tables, this discount applies at the partition level. Historical data in a partitioned table ages into long-term pricing automatically even while you continue appending new data to recent partitions. 

Streaming Inserts 

Streaming inserts cost $0.01 per 200 MB ingested. If you’re loading data that doesn’t need to be immediately available for queries, batch loading is free. Most teams that audit their streaming inserts find a significant portion that could be converted to scheduled batch loads without any operational impact. 

4 SQL-Level BigQuery Cost Optimizations 

These are the fundamentals for BigQuery best practices. They reduce bytes scanned, which reduces costs on on-demand pricing and reduces slot consumption on capacity pricing. Both matter, though they won’t be the big cost-changers in the long run. 

Stop Using SELECT*

BigQuery uses columnar storage. When you SELECT *, it scans every column in the table even if your downstream logic only touches five of them. On a wide enterprise table with 80 or 100 columns, this can increase bytes scanned by an order of magnitude. 

-- Expensive: scans every column
SELECT *
FROM `project.dataset.events`
WHERE event_date = '2026-06-01';

-- Cheap: scans only what you need
SELECT
  user_id,
  event_type,
  session_id,
  event_timestamp
FROM `project.dataset.events`
WHERE event_date = '2026-06-01';

NOTE: LIMIT does not reduce cost. BigQuery still scans the full table and applies the limit after the fact. Use TABLESAMPLE when you need to explore data cheaply: 

-- Explore a 1% sample without paying for the full scan
SELECT *
FROM `project.dataset.events`
TABLESAMPLE SYSTEM (1 PERCENT);

Filter Early and Often 

Apply WHERE clauses as close to the source tables as possible, like before joins and before aggregations. Every row pruned early means less data flowing through the rest of the pipeline. 

-- Less efficient: joins first, then filters
SELECT
  u.user_id,
  u.region,
  SUM(o.order_value) AS total_value
FROM `project.dataset.users` u
JOIN `project.dataset.orders` o ON u.user_id = o.user_id
WHERE o.created_date >= '2026-01-01'
  AND u.region = 'US-West';

-- More efficient: filter before joining
WITH recent_orders AS (
  SELECT user_id, order_value
  FROM `project.dataset.orders`
  WHERE created_date >= '2026-01-01'
),
us_west_users AS (
  SELECT user_id, region
  FROM `project.dataset.users`
  WHERE region = 'US-West'
)
SELECT
  u.user_id,
  u.region,
  SUM(o.order_value) AS total_value
FROM us_west_users u
JOIN recent_orders o ON u.user_id = o.user_id
GROUP BY 1, 2;

Set Maximum Bytes Billed as a Safety Net

This is one of the highest-value guardrails available. Set a hard cap on bytes billed per query. If a query exceeds the threshold, it fails without incurring cost. This doesn’t slow anything down. It only prevents accidental runaway scans from expensive pipelines or analysts working against production tables. 

-- Set at query level via bq command line
bq query \
  --maximum_bytes_billed=10737418240 \  # 10 GB cap
  --use_legacy_sql=false \
  'SELECT user_id, event_type FROM project.dataset.events WHERE event_date = "2026-06-01"'

PRO TIP: You can also set this at the project level using custom quotas or enforce it through IAM policies for specific service accounts running automated pipelines. 

Use Dry Runs Before Executing Expensive Queries 

The BigQuery dry run estimates bytes processed before execution. Make this a team habit for any ad-hoc query against large tables. 

from google.cloud import bigquery

client = bigquery.Client()

query = """
SELECT user_id, COUNT(*) as event_count
FROM `project.dataset.events`
WHERE event_date BETWEEN '2026-01-01' AND '2026-06-01'
GROUP BY user_id
"""

job_config = bigquery.QueryJobConfig(dry_run=True, use_query_cache=False)
job = client.query(query, job_config=job_config)

print(f"Estimated bytes processed: {job.total_bytes_processed:,}")
print(f"Estimated cost (on-demand): ${job.total_bytes_processed / 1e12 * 6.25:.4f}")

How to Use Schema Design to Make Data Cheaper to Query

SQL optimization reduces waste at query time. Schema decisions reduce waste structurally by making it harder to accidentally scan too much data. Let’s take a look at how you can improve your BigQuery setup by adjusting your schema usage with these three steps. 

Step One: Partitioning 

Partitioning splits a table into physical segments based on column value, typically a DATE or TIMESTAMP. When a query filters on the partition column, BigQuery skips the partitions that don’t match – no bytes scanned, no cost. 

-- Create a partitioned table on event_date
CREATE TABLE `project.dataset.events`
(
  user_id STRING,
  event_type STRING,
  event_timestamp TIMESTAMP,
  event_date DATE
)
PARTITION BY event_date
OPTIONS (
  require_partition_filter = TRUE  -- Forces WHERE clause on partition column
);

The require_partition_filter option is worth enabling for large production tables. It forces any query to include a filter on the partition column, making full-table scans impossible by default. 

A properly partitioned table queried for a single month of five years of data only scans the relevant partitions, significantly reducing total bytes processed.

Step Two: Clustering 

Clustering organizes data within each partition based on the values of one to four columns. When a query filters on clustered columns, BigQuery uses block-level metadata to skip irrelevant data blocks entirely. 

-- Partition by date, cluster by the columns most commonly used in filters
CREATE TABLE `project.dataset.events`
(
  user_id STRING,
  event_type STRING,
  campaign_id STRING,
  region STRING,
  event_timestamp TIMESTAMP,
  event_date DATE
)
PARTITION BY event_date
CLUSTER BY campaign_id, region;

PRO TIP: Clustering is most effective on high-cardinality columns that appear frequently in WHERE clauses such as user_id, campaign_id, account_id, and region. It compounds with partitioning, so you get partition pruning at the segment level and block pruning within each segment. 

Step Three: Use INFORMATION_SCHEMA to Find Your Highest Cost Queries 

Before reorganizing schema or rewriting queries, identify which jobs are actually driving spend. The top 10-20 most expensive recurring queries typically account for the majority of your monthly compute costs. Optimize those first. 

-- Find the most expensive queries over the last 30 days
SELECT
  job_id,
  user_email,
  query,
  total_bytes_processed,
  ROUND(total_bytes_processed / POW(1024, 4) * 6.25, 4) AS estimated_cost_usd,
  creation_time,
  total_slot_ms
FROM `region-us`.INFORMATION_SCHEMA.JOBS
WHERE
  creation_time >= TIMESTAMP_SUB(CURRENT_TIMESTAMP(), INTERVAL 30 DAY)
  AND job_type = 'QUERY'
  AND state = 'DONE'
  AND error_result IS NULL
ORDER BY total_bytes_processed DESC
LIMIT 25;

NOTE: This query gives you a ranked list of jobs by bytes processed, with estimated on-demand cost. Run this once a week. The results tell you exactly where to focus optimization effort. 

Advanced BigQuery Cost Optimization Techniques

Now that we’ve covered the basic schema and SQL tricks your teams should be using to keep costs down, let’s take a look at more situation-specific tactics you can apply. 

Managing AI-Drive Query Spikes

AI agents and automated pipelines introduce a cost pattern that traditional optimization approaches simply aren’t designed to handle. 

A human analyst running 50 queries per day is manageable. An AI agent generating 5,000 queries per hour is a different problem entirely. Without workload management, these jobs can rapidly overwhelm reservation capacity and trigger unexpected on-demand costs that don’t show up when the bill arrives. 

The standard tools for managing this, like query quotas, maximum bytes billed, and custom budgets, are all reactive. They cut off queries that exceed thresholds. They don’t proactively route workloads to keep costs down while maintaining throughput. 

Real-time routing handles AI workloads differently. Instead of imposing hard limits that block query execution, it continuously balances jobs across available compute based on current load and cost efficiency. When an AI agent generates a burst of queries, optimization tools [link] like Yuki dynamically manages them across on-demand and reservation billing in real time, keeping costs predictable without throttling the workload that is driving business value. 

These types of tools are becoming increasingly important as more data teams rely on AI agents for exploratory analysis, automated reporting, and customer-facing features. The query volumes generated by these systems scale in ways that manual cost controls simply cannot track. 

Storage Optimization

Compute dominates most BigQuery bills, but storage optimizations compute quietly and are often worth implementing as part of any cost review. Here’s how to manage it. 

Set Table Expiration on Staging and Temporary Data

Development datasets, staging tables, and temporary pipeline artifacts accumulate quickly. Set expiration dates at the dataset level for any data that doesn’t need long-term retention. 

-- Set dataset-level default table expiration (in milliseconds)
-- 7 days = 604800000 ms
ALTER SCHEMA `project.staging_dataset`
SET OPTIONS (
  default_table_expiration_days = 7
);

Evaluate Physical vs. Logical Storage Billing

By default, BigQuery bills are based on logical (uncompressed) storage. If your data compresses well, opting into physical storage billing can reduce costs. Check your compression ratios before switching – physical rates are higher than per GB, so this only saves money if compression is significant. 

Avoid Unnecessary Table Copies

BigQuery’s federated query model lets you query external data sources directly without loading them first. For data that changes frequently and doesn’t need to be stored long-term in BigQuery, this eliminates duplicate storage costs. 

Use Batch Loading Instead of Streaming Inserts

Do this when latency allows for it. Batch loading is free. Streaming inserts at $0.01 per 200 MB add up at scale. Audit which pipeline uses streaming inserts and if sub-minute data availability is actually required for each one. 

Building a Monitoring Habit 

You’ve probably noticed by now that cost optimization isn’t a one-time project. Query patterns change, new pipelines get added, and teams grow. The teams that keep costs under control long-term are the ones that treat INFORMATION_SCHEMA as a regular reporting surface. 

NOTE: For environments running thousands of jobs per day across multiple projects, manual monitoring stops being efficient and becomes insufficient. This is where continuous automated analysis – watching slot consumption, storage utilization, and query patterns in real-time – becomes the difference between managing costs and reacting to them. 

Combining It On One Platform

BigQuery cost optimization has two layers, and most organizations only focus on the first one: 

Layer one is query and schema efficiency: 

  • Avoid SELECT*
  • Filter early 
  • Partition and cluster your tables
  • Use incremental models
  • Leverage caching 

These optimizations reduce bytes scanned and slot consumption across the board. They’re genuinely valuable and should be implemented as standard practice. 

Layer two is compute routing. It’s making sure that every query runs in the billing model that’s actually cheapest for that specific job, at that specific moment. This is where significant additional savings live – and where manual approaches fall apart at scale. 

Combining these layers is what moves BigQuery cost reduction from incremental improvements into the 30-60% range that teams using automated routing actually see. But before we get into just how much you can save by adjusting your strategy, let’s cover the one last pivotal part of this equation: how the choice between on-demand versus reservation is more than what you think it is. 

The Deeper Problem: On-Demand vs. Reservations Isn’t a One-Time Decision

The on-demand versus reservations decision is where most organizations end up leaving money on the table. 

The standard advice is this: if your monthly BigQuery spend exceeds a certain threshold, evaluate if reservations make sense. Run that math, buy some slots, then move on. But this treats compute pricing as an organization-level decision made once and applied uniformly to everything. 

But that’s not how actual workloads behave. 

Within any BigQuery environment, you have queries that scan 50 MB and queries that scan 5 TB. You have lightweight dashboard refreshes and multi-hour dbt model runs. You have ad-hoc analyst queries and scheduled pipeline jobs. You have AI agents generating thousands of small queries per hour. 

The correct billing model for a 50 MB on-demand query might be very different from the correct billing model for a 5 TB pipeline job – even if they run in the same project, in the same hour, and from the same team. 

On-demand pricing is often cheaper for small queries. Reservation slots provide better economics for large, sustained workloads. But most teams pick a model and apply it everywhere, which means they’re overpaying for some workloads and undershooting capacity on others. 

Here’s how you should actually solve this problem. 

Why Reservations Alone Don’t Solve Anything

Reservations give you a pool of committed slots. Queries that run under reservation billing consume from that pool instead of paying per byte scanned. 

The problem is that reservation capacity is often either underutilized or overwhelmed: 

  • Idle slots consume money even when nothing is running. If your workloads are bursty, you’ll have periods where you’re paying for reserved capacity that isn’t being used. 
  • Overflow to on-demand can happen unexpectedly. When reservation slots are fully consumed, queries queue or spill to on-demand, sometimes without clear visibility into when or why. 
  • Long-term commitment locks you in. Three-year commitment pricing is attractive on paper, but committing to a fixed slot count based on current workloads becomes expensive when those workloads change. 

The result is that teams with reservations are still met with unpredictable costs… they’ve just moved that unpredictability from the per-query level to the capacity management level. 

The Right Frame: Per-Query Routing

The right question isn’t “Should we use on-demand or reservations?” It’s “Which compute model is cheapest for this specific query right now?” 

That’s a per-query decision, and it needs to be made in real time based on: 

  • How many bytes the query will scan
  • If reservation slots are currently available 
  • Current system load across projects
  • The cost difference between on-demand and reservation billing for this specific job

Making this decision manually – across thousands of queries per day, across multiple pipelines and teams – isn’t feasible. This is a problem Yuki’s BigQuery optimization platform was built to solve [link]. 

Yuki acts as an intelligent routing layer between your workloads and BigQuery’s compute infrastructure. It analyzes each job in real time and routes it to the most cost-efficient compute option – on-demand or reservations – based on bytes scanned, current slot availability, and system load. 

No code changes, no query rewrites, no manual tuning. Queries continue running normally. Yuki just decides when they execute. 

The result is that large scans that benefit from reservation economics run on reservation slots when capacity is available, while small queries that would overpay for a reservation model run on-demand. Every query pays the right rate for its workload profile. 

See What You’re Actually Spending – And What You’re Wasting

The fastest way to understand your BigQuery cost profile is to look at it. Yuki offers a free BigQuery cost analysis that reviews your query patterns and compute utilization to identify inefficiencies and estimate savings.

No commitment required. Get your free BigQuery cost analysis and see exactly what Yuki can do for your environment.

By Ido Arieli Noga
Ido Arieli Noga is the CEO and Co-Founder of Yuki, where he helps businesses cut Snowflake spend through smart warehouse scaling and DevOps-driven optimization. He brings over 12 years of experience across data storage, BI, and FinOps, including nearly four years as Head of Data at Lightico and five years managing large-scale virtual environments in the government sector. Ido holds a degree in Computer Science and is passionate about building scalable, cost-efficient data infrastructures. Since founding Yuki in 2023, he’s focused on helping teams reduce costs without changing queries or code. Find more of his insights on Medium or LinkedIn.

Table of Contents

Free cost analysis

Take 5 minutes to learn how much money you can save on your Snowflake account.

By clicking Submit you’re confirming that you agree with our Terms and Conditions.

Follow us on LinkedIn

Related posts

Free cost analysis

Take 5 minutes to learn how much money you can save on your Snowflake account.

By clicking Submit you’re confirming that you agree with our Terms and Conditions.

Skip to content