Your team is doing great work – you’ve just released a new analytics feature. Usage is growing. All is well until your cloud bill lands and finance wants to understand why BigQuery represents a significant portion of your GCP spend – and why it keeps getting larger.
BigQuery spend is one of the most common pinch points for scaling data teams. Its serverless model makes it easier to get started and overspend silently. There’s no cluster to provision, no infrastructure to babysit. But that same invisibility means costs can compound behind the scenes for weeks before anyone notices, and by the time your bill arrives, the damage has been done.
This guide for engineering managers and technical leaders looking to learn the BigQuery optimization techniques that get costs under control. We’ll cover:
- Why BigQuery can get so expensive
- Six BigQuery optimization techniques that actually reduce your bill
- How to set up guardrails to prevent past problems from returning
Why BigQuery Costs Get Out of Hand So Quickly
BigQuery’s pricing model is deceptively simple at first glance: under on-demand pricing, you pay about $6.25]] per terabyte of scanned data, depending on region and edition. Storage adds one more small cost on top. And that’s it. There’s no servers, no clusters, no maintenance workloads.
There’s a big problem here though: “data scanned” is doing a lot of work that’s hard to track. BigQuery doesn’t charge you for the data you return. It charges you for the data it reads in the process of getting there.
Running a query that touches a 10TB table but only returns 500 rows? You just paid for 10TB of compute.
Add 10 analysts running exploratory queries against the same table throughout the day, and you’ve multiplied that cost by however many times they ran it.
For analytics-heavy workloads, compute often represents the majority of BigQuery spend, frequently far exceeding storage costs. And unlike your traditional warehouse costs that scale with infrastructure, BigQuery costs scale with behavior. That means the queries your team writes, how often dashboards refresh, and how your tables are structured all impact your monthly bill. That’s why BigQuery cost control is as much an engineering and governance problem as it is a billing one.
There’s some good news here though: the same changes that reduce cost almost always impact performance. Less data scanned means faster queries. These two objectives rarely conflict in BigQuery, which is why optimization is easy to justify and even easier to prioritize.
BigQuery Optimization Techniques That Actually Reduce Costs
Now that you understand the power of investing in useful BigQuery optimization techniques, let’s break down six tactics you can use to completely transform your tech stack – and bring a smile to your finance team’s face the next time they get your BigQuery bill.
Technique #1: Only Read the Data Your Query Actually Needs
BigQuery is a columnar database. That means it stores and reads data column by column, not row by row. This architecture is the foundation of BigQuery’s performance. Understanding how it works can save you money… so long as your queries are written to take advantage of it.
There’s a couple things your team should focus on here:
First, be careful when using SELECT. When a query selects every column, BigQuery has to scan every column, even the ones your query doesn’t use in a meaningful way. On a table with 40 or 50 columns, you may be processing 10x the data you actually need.
How do you fix this? Just list only the columns required. Yes, it sounds obvious, but it’s often skipped in exploratory work and data science notebooks, and those bad habits carry over into production pipelines. And they cost you.
Another common mistake here: LIMIT does not reduce cost under on-demand pricing when the query scans a full table. BigQuery scans the full table first, then applies the limit to what it returns. A ‘SELECT * FROM events LIMIT 100’ against a 5TB table costs the same as ‘SELECT * FROM events’.
If your team wants to preview data, they should use BigQuery’s built-in table preview in the console. It’s free, and it also doesn’t trigger a scan.
The same logic applies to filtering as well. WHERE clauses reduce what BigQuery reads, but only when they’re applied before expensive operations like JOINs. While BigQuery’s optimizer often pushes filters down automatically, poorly structured queries can prevent that optimization and lead to unnecessary scans. Filtering in an outer query after a JOIN has already scanned everything saves much less than filtering before the JOIN runs.
Technique #2: Design Your Tables Around Your Querying
Query-level changes are how you can cut back on the costs of individual queries. Table design is where you’ll see the biggest savings because you’re reducing what every query against that table costs – permanently. How does one do that? With partitioning and clustering.
Partitioning divides a table into segments based on a column like a date or timestamp. When a query filters on the partition column, BigQuery reads only the relevant partitions.
For example, a table holding two years of event data, queried with a 30-day filter, might see a 95% reduction in bytes billed after partitioning by date.
Here’s what this code might look like:
CREATE OR REPLACE TABLE `project.dataset.events`
PARTITION BY DATE(event_timestamp)
OPTIONS (require_partition_filter = true)
AS SELECT * FROM `project.dataset.events_raw`;NOTE: The ‘require_partition_filter = true’ option is a very useful guardrail because it means queries without a partition filter fail outright instead of triggering an accidental full-table scan. This is especially important for large, high-traffic tables.
Clustering physically organizes data within partitions based on the values in one or more columns. When a query filters on a clustered column, BigQuery skips blocks of data that don’t match, which reduces scan volume further. This trick is most effective on tables over 1GB where queries regularly filter on the same dimensions, like region, product category, or user segment.
It’s best to combine both of these techniques. You can partition by date and cluster by the one or two high-cardinality columns your team filters on most. This combination usually results in the largest ongoing reduction in bytes billed for analytical workloads – and it’s a one-time schema change that pays dividends on each subsequent query.
NOTE: Avoid date-sharded tables that use date suffixes like ‘events_20240901’- in favor of time-partitioned tables. Shared tables require BigQuery to maintain separate metadata and schema for each table, which can add overhead, degrade query performance, and make data overall harder to manage over time.
Technique #3: Pre-Compute What Gets Queried Repeatedly
Not all queries are one-off explorations. Dashboards refresh on a schedule, BI tools run the same aggregations dozens or hundreds of times a day, and ETL pipelines re-aggregate the same raw tables on every run. Each of these repeated queries scans the same data and leaves you with the same cost – unless you pre-compute the result and let subsequent queries read from that instead.
That’s exactly what materialized views do. BigQuery computes and stores the result of a defined query, then automatically keeps it in sync as the underlying data changes. Queries against the materialized view run against the pre-computed result – leaving you with a dramatically faster and cheaper result than re-aggregating raw data each time.
Here’s what this looks like:
CREATE MATERIALIZED VIEW `project.dataset.daily_revenue_mv`
AS
SELECT
DATE(order_timestamp) AS sale_date,
region,
product_category,
SUM(revenue) AS daily_revenue
FROM `project.dataset.orders`
GROUP BY sale_date, region, product_category;Dashboard queries that once aggregated millions of rows on each refresh can return in milliseconds from a pre-computed result set. Organizations that run high-frequency BI tools against large tables find materialized views are usually the fastest path to a meaningful cost reduction.
Pro tip: Incremental pipeline updates also work. For large tables that receive daily updates, refreshing the whole data on each pipeline is expensive and unnecessary. Incremental patterns – where you identify and process only new or changed records – can dramatically reduce compute for ETL workloads. This requires your pipeline tables to be partitioned on the incremental key, which is another reason partitioning pays compounding dividends.
Technique #4: Fix How Your Queries Handle JOINs
JOINs are probably the culprit behind your spiking BigQuery costs. And these problems are usually invisible until you start looking at execution plans.
The most straightforward fix is filtering before joining. When you filter inside a subquery before the JOIN runs, you can reduce the size of the tables being joined. When you filter after the JOIN in an outer ‘WHERE’ clause, BigQuery has already done the expensive work of joining the full tables. There’s a pretty notable difference in costs between these two approaches when it comes to working on large tables.
BigQuery automatically chooses between broadcast joins and shuffle joins depending on table size and query structure. When one table is small enough, BigQuery may broadcast it to each slot processing the larger table.
Basically, this means it sends the smaller table to each slot processing the larger one. This is efficient when one table is significantly smaller than the other, but isn’t the best when both are large. Avoid unnecessary costs here by being explicit about filtering your tables down before they join to keep JOINs fast and cheap regardless of size.
If your team can handle schema changes, there’s another structural fix you can try: BigQuery’s native support for arrays and structs allows you to store related records together in a denormalized structure. This means you can avoid expensive runtime JOINs entirely for data that’s always queried together.
Technique #5: Match Your Pricing Model to Your Workload
This trick is one of the highest leverage. And it has nothing to do with query syntax. It’s all a matter of what pricing model you’re on.
Most teams will default to on-demand pricing when they start with BigQuery, and stick with that structure until it stops making economic sense.
Before we continue, let’s break down the two pricing models you can pick from here:
- On-demand pricing: Charges per terabyte scanned.
- Capacity-based pricing: Charges per slot-hour, regardless of how much data is processed.
On-demand pricing makes the most sense for when utilization is slow and unpredictable. That includes tasks such as:
- Sporadic workloads
- Dev and test environments
- Teams and running infrequent queries against manageable data volumes
Capacity pricing makes the most sense for work like slot utilization. It removes per-query cost variability that makes on-demand bills so hard to forecast. Workloads it aligns well with include:
- Production analysis pipelines
- BI dashboards with high refresh frequency
- ETL jobs that run continuously
BigQuery Editions support autoscaling reservations, allowing you to define a baseline slot commitment and a maximum capacity ceiling. It means you don’t need to commit to a fixed block of slots you pay for whether or not they’re used. You can set a baseline (the minimum slots always allocated) and a maximum, and BigQuery scales between the two.
For most organizations, starting with a low baseline commitment and setting the maximum to handle peak concurrent load will get you the cost benefits of capacity pricing without over-provisioning.
NOTE: Before committing to an annual plan, run a monthly flat-rate reservation and use ‘INFORMATION_SCHEMA.JOBS’ to analyze your actual slot usage patterns. Review daily and hourly consumption data. That will tell you if your workload justifies the switch, and what reservation size makes the most sense for your organization.
Technique #6: Govern Who Can Scan What
We’ve covered techniques that tell you how to fix queries that you know are a problem. This final trick of governance is how you can address those tricky queries that sneak past you. These are the dashboards that refresh every 15 minutes against unpartitioned data, or that notebook that re-runs a heavy aggregation on each save.
Here’s how to apply this technique:
- Set maximum bytes billed limits. BigQuery lets you cap the bytes a query can scan before it’s allowed to execute. Queries that exceed the limit fail with an error instead of running, which eliminates any scenario where a single query generates an unexpected large charge.
- Label everything. Without query labels, it’s nearly impossible to understand which teams, pipelines, or use cases drive costs. Take advantage of BigQuery supporting custom labels, and you’ll get a huge cost visibility boost.
- Identify your most expensive queries before optimizing. First, run an INFORMATION_SCHEMA.JOBS query to find your top 20 most expensive queries by bytes. That’s the best place to start.
- Use BI Engine for dashboard-heavy workloads. BigQuery’s BI Engine is an in-memory service that caches frequently accessed data and accelerates dashboard queries significantly. Teams running high-frequency queries from Looker, Tableau, or Metabase will find BI Engine can reduce latency for dashboard workloads and, in some cases, reduce overall compute consumption for frequently repeated queries.
The Real Cost of BigQuery DIY Optimization
All of these techniques work. When implemented correctly, you’ll see a reduction – often significant – in your BigQuery spend. The drawback is that your team will have to dedicate time to auditing existing queries and table schemas, implementing structural changes on live tables, establishing and enforcing governance policies across every team that touches your warehouse, and monitoring new inefficiencies on an ongoing basis.
For a lot of engineering teams, that amount of work doesn’t align well with your product roadmap. And we’re not talking about one-off optimizations. This is ongoing work, adapting as your query patterns change, new tables are created, and new analysts join and bring old, unoptimized habits with them.
Here lies the real decision for engineering leaders: where is your team’s time best spent?
At Yuki Data, we work with engineering teams in industries across fintech, gaming, ecommerce, and cybersecurity who are done trading product velocity for optimized infrastructure. With our plug-and-play solutions, past customers have:
- Saved an average of 37.6% on compute costs
- Run 500 million daily queries more efficiently
- Reduced infrastructure management overhead by up to 30%
All without engineers having to own the optimization problem.
If your BigQuery bill is growing faster than your confidence in what’s driving it, talk to our team. We’ll show you exactly where your spend is going – and what it would take to bring it down while maintaining operations.
