How to Optimize BigQuery Costs (Real Techniques That Work)

Arunkumar Amaran — Wed, 06 May 2026 00:40:36 +0000

I want to start with a number: $140,000.

That's how much a single misconfigured scheduled query cost one of the teams I worked with over a three-month period. Not a rogue machine learning job. Not a massive ETL pipeline. A scheduled query. Running every 15 minutes. Scanning a 4TB table every time. For 90 days before anyone looked at the bill closely enough to notice.

Nobody wrote that query with malicious intent. The engineer who wrote it was experienced. The table had been small when the query was first created. Then the table grew. The query didn't know that. The bill did.

I've spent the better part of the last decade building and optimizing data platforms on GCP — including BigQuery at enterprise scale at Macy's Systems & Technology. This post is what I wish someone had handed me early on. Not the official Google documentation version of cost optimization. The actual production version, including the mistakes that end up in the bill.

First: Understand What You're Actually Paying For

BigQuery has two pricing models and a lot of people mix them up or don't think about which one they're on.

On-demand pricing charges you per byte scanned. As of 2026, that's around $6.25 per TB in most regions. Every query you run, BigQuery measures how much data it touched and charges accordingly. Write a bad query that scans 10TB when it needed to scan 10GB and you've just paid 1,000x more than necessary.

Capacity pricing (flat-rate / editions) charges you for reserved compute slots — basically compute reservations — regardless of how much data you scan. If your workloads are predictable and heavy, this can be dramatically cheaper. If your workloads are bursty and light, on-demand is usually better.

Most teams start on on-demand and never revisit that decision. That's often a mistake. But before you switch, you need to understand your query patterns — and that's what the rest of this post is largely about.

The Single Biggest Lever: Stop Scanning Data You Don't Need

On-demand BigQuery charges by bytes scanned, not bytes returned. That means the columns you don't SELECT and the rows you don't filter still cost money if BigQuery has to read past them.

Never use `SELECT *`

I know you know this. I also know that half the scheduled queries in your project have SELECT * in them right now. Go check.

-- This scans every column in a 2TB table
SELECT * FROM `project.dataset.orders`
WHERE order_date = '2026-01-15'

-- This scans only what you need
SELECT order_id, customer_id, total_amount
FROM `project.dataset.orders`
WHERE order_date = '2026-01-15'

The second query might cost 10-20x less depending on how wide the table is. For a table with 60 columns where you need 4, the math is brutal.

Use partition pruning — and verify it's working

Partitioned tables are one of BigQuery's most powerful cost controls, but they only work if your query filter actually triggers partition elimination.

-- ✅ This WILL prune partitions (uses the partition column directly)
SELECT order_id, total_amount
FROM `project.dataset.orders`
WHERE order_date BETWEEN '2026-01-01' AND '2026-01-31'

-- ❌ This will NOT prune partitions (wraps the column in a function)
SELECT order_id, total_amount
FROM `project.dataset.orders`
WHERE DATE(order_timestamp) BETWEEN '2026-01-01' AND '2026-01-31'

The second query looks like it should be equivalent. It isn't — wrapping the partition column in a function forces a full table scan. This is one of the most common and expensive mistakes I see in production.

How to verify: Run the query and look at the execution details. If you see "Partitions scanned: 1 of 365" — great, it's working. If you see "Partitions scanned: 365 of 365" on a query filtering a single day — your filter isn't hitting the partition column the way you think it is.

Cluster your tables

Partitioning handles time-based filtering. Clustering handles everything else. If you're frequently filtering by customer_id, product_id, store_id, or region — those columns should be in your clustering key.

CREATE TABLE `project.dataset.orders`
PARTITION BY DATE(order_timestamp)
CLUSTER BY customer_id, store_id
OPTIONS (require_partition_filter = TRUE)
AS SELECT * FROM `project.dataset.orders_raw`

That require_partition_filter = TRUE option is worth calling out explicitly. It makes BigQuery reject any query that doesn't include a partition filter. Annoying to developers at first. Saves you from the $140,000 mistake I mentioned at the top.

Materialized Views: The Most Underused Feature in BigQuery

Most people know materialized views exist. Few people use them properly for cost optimization.

Here's the thing about BigQuery materialized views that isn't obvious: they can answer queries even when the query doesn't directly reference them. BigQuery's query optimizer will automatically rewrite a query against a base table to use a materialized view instead, if the view can satisfy the query and is more efficient.

-- Create the materialized view
CREATE MATERIALIZED VIEW `project.dataset.daily_sales_mv`
PARTITION BY sale_date
AS
SELECT
  DATE(order_timestamp)     AS sale_date,
  store_id,
  product_id,
  SUM(quantity)             AS total_units,
  SUM(total_amount)         AS total_revenue,
  COUNT(DISTINCT order_id)  AS order_count
FROM `project.dataset.orders`
GROUP BY 1, 2, 3;

Now here's where it gets interesting. A dashboard query that does:

SELECT store_id, SUM(total_amount) AS revenue
FROM `project.dataset.orders`
WHERE DATE(order_timestamp) = '2026-01-15'
GROUP BY store_id

...might get automatically rewritten to query the materialized view instead of the full orders table — even though the query never mentioned the materialized view. That's BigQuery doing the cost optimization for you, provided you've set things up right.

The maintenance overhead is minimal. The cost reduction on heavily queried aggregations can be 80-95%.

Query Cost Estimation Before You Run

This one sounds obvious but is skipped constantly: estimate cost before running unfamiliar queries, especially on large tables.

In BigQuery console, every query shows a bytes-to-be-processed estimate in the top right before execution. Pay attention to it.

In code, you can do a dry run:

from google.cloud import bigquery

client = bigquery.Client()

job_config = bigquery.QueryJobConfig(dry_run=True, use_query_cache=True)

query = """
    SELECT *
    FROM `project.dataset.large_table`
    WHERE region = 'US'
"""

job = client.query(query, job_config=job_config)

# Bytes processed if this query ran
bytes_processed = job.total_bytes_processed
gb_processed = bytes_processed / (1024 ** 3)
estimated_cost = (bytes_processed / (1024 ** 4)) * 6.25  # $6.25 per TB

print(f"This query would scan: {gb_processed:.2f} GB")
print(f"Estimated cost: ${estimated_cost:.4f}")

Build this into your data pipeline development workflow. Make developers run a dry-run estimate before merging new queries into production. It takes 30 seconds. It occasionally saves you $10,000.

Slots, Reservations, and When Flat-Rate Actually Makes Sense

The on-demand vs. flat-rate decision is one that most teams make once and never revisit. That's a mistake because the right answer changes as your workload matures.

On-demand is usually right when:

Your query volume is unpredictable or spiky
You're early-stage and still figuring out your workload patterns
Your monthly BigQuery bill is under ~$2,000

Flat-rate (Editions) is usually right when:

You have consistent, heavy query workloads running throughout the day
You're running large-scale batch processing jobs on a schedule
Your on-demand bill has stabilized and is consistently above ~$3,000/month

The crossover point depends on your region and edition tier, but a rough rule: if you're consistently buying more than 500 slot-hours per month at on-demand prices, flat-rate reservations are worth modeling out.

One trap to avoid: Buying flat-rate capacity and then not using it. Idle reserved slots cost the same as busy ones. If you're going to commit to flat-rate, you need to actually fill those slots — which means scheduling your heavy batch jobs during off-peak hours to maximize slot utilization.

-- Check your slot utilization in INFORMATION_SCHEMA
SELECT
  TIMESTAMP_TRUNC(period_start, HOUR)     AS hour,
  project_id,
  SUM(period_slot_ms) / (1000 * 3600)    AS slot_hours_used
FROM `region-us`.INFORMATION_SCHEMA.TIMELINE_BY_PROJECT
WHERE period_start >= TIMESTAMP_SUB(CURRENT_TIMESTAMP(), INTERVAL 7 DAY)
GROUP BY 1, 2
ORDER BY 1 DESC

Run this before committing to any reservation purchase. If you're averaging 40 slot-hours per hour during business hours and near-zero overnight, a flat-rate reservation sized for your peak will be mostly idle.

INFORMATION_SCHEMA: Your Cost Debugging Best Friend

BigQuery's INFORMATION_SCHEMA views are where you find the expensive queries. Most people don't use them nearly enough.

Find your most expensive queries from the last 30 days

SELECT
  job_id,
  user_email,
  ROUND(total_bytes_processed / POW(1024, 4), 4)  AS tb_processed,
  ROUND((total_bytes_processed / POW(1024, 4)) * 6.25, 2) AS estimated_cost_usd,
  SUBSTR(query, 0, 200)                            AS query_preview,
  creation_time
FROM `region-us`.INFORMATION_SCHEMA.JOBS_BY_PROJECT
WHERE
  creation_time >= TIMESTAMP_SUB(CURRENT_TIMESTAMP(), INTERVAL 30 DAY)
  AND job_type = 'QUERY'
  AND state = 'DONE'
ORDER BY total_bytes_processed DESC
LIMIT 25

Run this query once a week. The top 5 results are almost always where your money is going. In my experience, fixing the top 3 queries on this list typically cuts the bill by 30-50%.

Find queries run by service accounts that are scanning too much

SELECT
  user_email,
  COUNT(*) AS query_count,
  ROUND(SUM(total_bytes_processed) / POW(1024, 4), 2) AS total_tb_processed,
  ROUND(SUM(total_bytes_processed) / POW(1024, 4) * 6.25, 2) AS total_cost_usd
FROM `region-us`.INFORMATION_SCHEMA.JOBS_BY_PROJECT
WHERE
  creation_time >= TIMESTAMP_SUB(CURRENT_TIMESTAMP(), INTERVAL 30 DAY)
  AND job_type = 'QUERY'
GROUP BY user_email
ORDER BY total_tb_processed DESC

Service accounts running scheduled queries are frequently the biggest offenders because nobody's watching them. A dashboard refresh that runs every 15 minutes against an unpartitioned table is 2,880 queries per month. Do the math on what each of those costs and multiply.

Caching: Free Is Good, When It Works

BigQuery caches query results for 24 hours. Identical queries (same SQL, same referenced tables, no changes to underlying data) return cached results at zero cost.

This sounds great. It has a few gotchas:

Caching doesn't work if your query uses non-deterministic functions like CURRENT_TIMESTAMP(), NOW(), or RAND()
Caching doesn't work if you explicitly disable it
Caching doesn't work across projects by default

The biggest practical implication: don't put CURRENT_DATE() or CURRENT_TIMESTAMP() directly in scheduled queries if you can help it. Pass the date as a parameter instead. An identical parameterized query against a table that hasn't changed since the last run will hit the cache. A query with an inline CURRENT_DATE() won't.

-- ❌ This will never hit cache (current_date() makes it non-deterministic)
SELECT store_id, SUM(revenue)
FROM `project.dataset.daily_sales`
WHERE sale_date = CURRENT_DATE()
GROUP BY store_id

-- ✅ Pass the date as a scripting variable or scheduled query parameter
DECLARE run_date DATE DEFAULT DATE('2026-01-15');

SELECT store_id, SUM(revenue)
FROM `project.dataset.daily_sales`
WHERE sale_date = run_date
GROUP BY store_id

The Cost Optimization Mindset Shift

Here's the thing that took me the longest to internalize: BigQuery cost optimization is not a one-time project. It's a continuous practice.

Tables grow. Query patterns change. New analysts join the team and write queries the way they're used to writing queries in other systems. A dashboard that was cheap six months ago isn't necessarily cheap today because the underlying table is now 10x larger.

The teams that control their BigQuery costs over the long term are the ones that have made it a habit — weekly cost reviews, dry-run estimates as part of code review, INFORMATION_SCHEMA queries on a dashboard that someone actually looks at.

The teams that get surprised by the bill are the ones that set it up and assumed it would stay efficient on its own.

It won't. But with the right habits, it's absolutely manageable — and the savings compound faster than you'd expect.

Quick Reference Checklist

Before you ship any new BigQuery query or table to production, run through this:

[ ] No SELECT * — only columns you need
[ ] Table is partitioned on the most common filter column
[ ] Partition filter is on the raw column, not wrapped in a function
[ ] Clustering keys match your most common WHERE and JOIN conditions
[ ] require_partition_filter = TRUE for large tables
[ ] Dry-run estimate reviewed before merging
[ ] Materialized view considered for any aggregation queried more than daily
[ ] No CURRENT_TIMESTAMP() inline if caching matters
[ ] Scheduled query frequency matches actual business need (not "every 15 minutes because that was the default")

Wrapping Up

The $140,000 story at the top of this post isn't the worst one I've heard. It's not even close. BigQuery cost disasters tend to be quiet — they accumulate over weeks or months before anyone notices, and by then the money is gone.

The good news: almost every expensive pattern is avoidable, and most of them are avoidable with techniques that also make your queries faster. Partition pruning, clustering, materialized views, proper column selection — these aren't just cost controls. They're good engineering.

Start with INFORMATION_SCHEMA. Find your top 5 most expensive queries. Fix them. Repeat monthly. You'll be surprised how quickly the bill responds.

Have a BigQuery cost story of your own? Drop it in the comments — the good ones are always educational (and occasionally horrifying).

Arunkumar Amaran — Tech Manager, Data Engineering & Architecture at Macy's Systems & Technology. 20+ years building enterprise data platforms on GCP, AWS, and Azure. IEEE Senior Member. Writes about the things that don't make it into the official docs.

🔗 linkedin.com/in/arunkumar-amaran · 📧 arunkumar.amaran@ieee.org

Opinions are my own and do not represent my employer.

Forem: Arunkumar Amaran