BigQuery Cost Optimization: A Practical Guide for Data Engineers

If you have worked with BigQuery for any reasonable amount of time, you have probably hit that moment where you look at the billing dashboard and think — wait, that cannot be right. BigQuery makes it incredibly easy to run queries on terabytes of data, and that ease comes with a downside: it is equally easy to burn through your budget without noticing.

In this article, let us go through the cost optimization basics for BigQuery workloads. We will cover the two pricing models, how to figure out where your money is actually going, practical query-level optimisations you can apply today, and what changes when you move from a small project to a production setup. This is not a theoretical overview — these are things I have applied on real workloads and seen the difference in the billing reports.

Understanding the Two Pricing Models

BigQuery gives you two ways to pay for queries: on-demand and capacity-based (slot reservations). Most teams start with on-demand and eventually move to slot-based pricing once the workload becomes predictable. Here is a quick comparison:

Aspect	On-Demand	Slot Reservations
Pricing basis	$6.25 per TB scanned	Fixed monthly/annual cost for dedicated slots
Good for	Low or unpredictable query volume	Steady, predictable workloads
Concurrency	Shared pool, can be throttled	Guaranteed slots, no queuing
Cost predictability	Variable — depends on queries	Fixed — you know the monthly bill
Free tier	1 TB/month free	No free tier

For a small team running ad-hoc queries, on-demand makes sense. You pay for what you use and the first terabyte each month is free. The problem starts when you have scheduled pipelines running every hour, analysts querying dashboards, and someone decides to run a SELECT * on a 200 TB table. At that point, the variable cost stops being a feature and becomes a liability.

If your monthly scanned data is consistently above 20-30 TB, switching to slot reservations usually saves money. You can start with flex slots (pay-as-you-go commitments of 60 seconds or longer) to test the waters before committing to an annual reservation.

Find Where the Money Is Going

Before you optimise anything, you need to know what is costing you. BigQuery exposes query costs through the INFORMATION_SCHEMA.JOBS views. Here is a query I keep handy to identify the top spenders by user and by table:

SELECT
  user_email,
  SUM(total_bytes_processed) / POWER(1024, 4) AS tb_processed,
  SUM(total_bytes_billed) / POWER(1024, 4) AS tb_billed,
  COUNT(DISTINCT job_id) AS query_count
FROM `region-us`.INFORMATION_SCHEMA.JOBS
WHERE
  creation_time >= TIMESTAMP_SUB(CURRENT_TIMESTAMP(), INTERVAL 30 DAY)
  AND statement_type = 'SELECT'
GROUP BY user_email
ORDER BY tb_billed DESC;

This alone will often surface surprises. I once found that a single Looker dashboard was scanning the same 50 TB fact table on every page load because the explore was set up without a default date filter. No one noticed because the dashboard finished in under 10 seconds. BigQuery is fast enough to hide expensive patterns.

You can take this further and break it down by referenced table:

SELECT
  referenced_tables,
  SUM(total_bytes_processed) / POWER(1024, 4) AS tb_processed,
  COUNT(*) AS job_count
FROM `region-us`.INFORMATION_SCHEMA.JOBS,
  UNNEST(referenced_tables) AS referenced_tables
WHERE
  creation_time >= TIMESTAMP_SUB(CURRENT_TIMESTAMP(), INTERVAL 30 DAY)
GROUP BY referenced_tables.table_id
ORDER BY tb_processed DESC
LIMIT 20;

Run these every couple of weeks. The output tells you exactly where to focus your optimisation efforts.

Query-Level Optimisations That Actually Matter

Once you know which queries and tables are eating your budget, it is time to tighten things up. Here are the techniques that have given me the biggest cost reductions in practice.

Partition and Cluster Your Tables

This is the single highest-impact thing you can do. A non-partitioned table forces every query to scan the entire thing. A partitioned table lets BigQuery prune down to only the relevant partitions.

CREATE TABLE my_dataset.events_partitioned
PARTITION BY DATE(created_at)
CLUSTER BY user_id, event_type
AS SELECT * FROM my_dataset.events;

Pick your partition column based on how your queries filter the data. For most event-style tables, using a date column works well. For slowly changing dimensions, partitioning might not help much — but clustering on the join key can still make a difference.

A partitioned table with clustering columns means a query like this only scans the relevant date range and reads far fewer blocks:

SELECT * FROM my_dataset.events_partitioned
WHERE DATE(created_at) BETWEEN '2026-05-01' AND '2026-05-07'
  AND user_id = 'abc123';

Without partitioning, the same query scans the full table. With partitioning and clustering, it might only scan 1% of the data. That directly translates to a 99% cost reduction for that query.

Avoid SELECT *

It sounds obvious, but I still see it in production pipelines all the time. Every column you do not need is bytes you are paying to scan. BigQuery is a columnar store — reading fewer columns means reading less data, which means a lower bill.

-- Bad: scans all columns
SELECT * FROM my_dataset.large_table;

-- Better: only what you need
SELECT id, created_at, event_type, amount
FROM my_dataset.large_table;

In one project, cutting SELECT * from five scheduled queries reduced the daily scanned bytes by nearly 40%. That is real money on the billing report.

Materialize Intermediate Results

If you have a query or a CTE that gets reused across multiple pipelines, materialize it into a table. Do not run the same expensive aggregation five times a day when you can run it once.

-- Instead of this CTE in every query:
WITH daily_aggregates AS (
  SELECT DATE(created_at) AS dt, user_id, COUNT(*) AS events
  FROM events_partitioned
  WHERE DATE(created_at) >= '2026-01-01'
  GROUP BY dt, user_id
)

-- Create it once:
CREATE OR REPLACE TABLE my_dataset.daily_user_events
PARTITION BY dt
AS SELECT DATE(created_at) AS dt, user_id, COUNT(*) AS events
FROM events_partitioned
WHERE DATE(created_at) >= '2026-01-01'
GROUP BY dt, user_id;

Downstream queries then hit a small pre-aggregated table instead of the raw events table every time.

Set Query-Level Cost Controls

BigQuery lets you set a maximum bytes billed per query. This acts as a circuit breaker — if a query would scan more than the limit, it fails immediately instead of running and charging you.

-- In the BigQuery console or API, set:
-- Maximum bytes billed: 1 TB

In your scheduled queries or dbt project config, you can set this at the project or query level. For a team with junior analysts, I would recommend setting a reasonable default at the project level — something like 500 GB or 1 TB per query — so no one accidentally kicks off a monster scan.

Production Considerations

The things above work well for a single project or a small team. When you move to a larger production setup, a few additional patterns become important.

Separate projects for compute and storage. Keep your raw data in one project and run queries in another. This makes it easier to track costs per environment (dev, staging, prod) and apply different cost controls per project.

Use BI Engine for dashboards. If you have Looker or Data Studio dashboards hitting BigQuery, BI Engine reserves memory for caching query results. The queries served from cache do not incur additional query costs. For dashboard-heavy workloads, the cost of BI Engine capacity is often less than the query costs it displaces.

Monitor with budget alerts and quotas. Set up GCP budget alerts at 50%, 75%, and 90% of your monthly budget. Configure per-user quotas so a single person or pipeline cannot consume all your slots. These are set in the IAM & Admin section under Quotas.

Review table expiration. Set default table expiration on your datasets so temporary and staging tables do not accumulate storage costs forever. Storage at $0.02 per GB per month does not sound like much until you realise you have 200 TB of forgotten intermediate tables sitting around.

Things to Be Careful About

A few gotchas I have run into:

• Partition pruning only works with literal dates or date functions on the partition column. If you write WHERE DATE(created_at) >= CURRENT_DATE() - 7, it prunes correctly. If you wrap the column in a function not recognised by the pruning logic, it falls back to a full scan. Test with the execution details panel in the BigQuery console to confirm pruning is happening.
• Streaming inserts do not have a free tier for storage. The first 10 GB of storage is free for batch-loaded data, but streaming inserts start billing for storage immediately.
• Flat-rate pricing commits are annual. If your workload drops midway through the year, you are still paying for those slots. Start with flex slots or a short-term commitment before going all in.

Wrapping Up

BigQuery cost optimisation is not a one-time exercise. It is something you build into your workflow — checking the INFORMATION_SCHEMA views every sprint, reviewing query patterns when new pipelines go live, and revisiting your pricing model as the workload grows.

The biggest wins almost always come from partitioning and clustering. After that, it is the discipline of only scanning what you need and not repeating expensive computations. None of this is complicated, but it takes someone on the team paying attention. That someone might as well be you.