Cutting Your BigQuery Bill Without Cutting Corners

A couple of weeks ago I attended a Google Cloud seminar titled "Optimise BigQuery: Editions, Slots & Savings". I came out with a handful of specific techniques I had not been applying consistently and a few I had not thought about at all. The truth is that BigQuery is one of those services that rewards you enormously when you understand it and quietly punishes you when you don't. You can run the same query on the same data and pay wildly different amounts depending on how your tables are set up. Most of that difference comes down to a few decisions that look minor on the surface. Here is what stood out.

The cost is in what BigQuery reads, not what it returns

This is the foundational thing to understand. BigQuery does not charge you based on the size of your result. It charges you based on the amount of data it scanned to produce that result. So a query that returns ten rows from a table with a billion rows can be extremely cheap or extremely expensive, depending entirely on whether BigQuery had to read all billion rows to get there.

That is where partitioning and clustering come in and why they matter far more than most people give them credit for.

Partitioning divides a table into segments based on a field, most commonly a date. When you query a partitioned table and filter by that partition column, BigQuery skips the segments it does not need entirely. It never reads them. For any table that grows over time, this is the single highest-leverage thing you can do.

Clustering then organises the data within each partition based on the values in one or more columns. If your queries regularly filter by region, product type, or customer segment, clustering on those columns means BigQuery scans far less data within each partition to produce your results.

Use both together. Partition by date, cluster by your most-queried dimension, and a large chunk of your compute costs shrink almost immediately.

Stop guessing why queries are slow

When something is running slowly or costing more than expected, the instinct is to rewrite it. Sometimes that is the right call. Sometimes it is not.

BigQuery has an execution graph that gives you a step-by-step view of exactly how a query ran: which stages ran, how long each one took, how many records were processed, and how many slot-milliseconds were consumed. More usefully, it shows you how each stage compares to historical performance on the same query. If stage three is suddenly taking three times longer than usual, the graph tells you that directly, rather than leaving you to work backwards from a cost report.

Before rewriting any slow query, look at the execution graph first. It usually tells you exactly where the problem is.

Materialised views: the middle ground most teams underuse

A regular view in BigQuery is just a saved query. Every time someone queries the view, BigQuery runs that query fresh against the underlying tables. If your view is doing expensive aggregations or joins, and it is being queried frequently, you are paying for those computations over and over again.

A materialised view precomputes those results and stores them. Subsequent queries read from the stored result rather than reprocessing the base tables. Faster and cheaper.

The part that surprised me in the seminar was how much control you have over refresh behaviour. BigQuery refreshes materialised views automatically, but that refresh is asynchronous, meaning there is a window where the view might be slightly stale. If that matters, you can trigger a manual refresh immediately after your base tables update using

CALL BQ.REFRESH_MATERIALIZED_VIEW('project.dataset.view_name')

For datasets with high write frequency, such as tables receiving multiple batch loads throughout the day, there is an even smarter option. You can pause automatic refresh during the loading window, let all the writes land, and then resume. This avoids the situation where BigQuery refreshes the materialised view mid-load, only to have new data arrive immediately after, triggering another refresh. Wasted compute, twice.

You can also set max_staleness to tell BigQuery how out-of-date you are willing to tolerate before it falls back to a full recompute. For use cases where data from an hour ago is perfectly acceptable, this is a simple way to reduce costs without changing anything a user would notice.

Two storage billing models, and why the difference matters

This is probably the part of the seminar that most people overlook, and it was the most immediately actionable for me. BigQuery gives you two ways to be billed for storage: logical and physical.

Logical billing is based on the uncompressed size of your data. It costs roughly half the rate of physical storage, and it includes time travel and fail-safe storage in that price with no extras.

Physical billing is based on the actual compressed bytes stored on disc. BigQuery typically achieves a compression ratio of 4 to 5 times on most data. So if your uncompressed data is 100GB but compresses down to 22GB, physical billing could be significantly cheaper despite the higher per-GB rate. The catch is that time travel and fail-safe storage are billed separately on top.

Before switching, run this query:

SELECT table_schema, table_name, 
ROUND(total_logical_bytes / POW(1024,3), 2) AS logical_gb, ROUND(total_physical_bytes / POW(1024,3), 2) AS physical_gb FROM your_project.region-eu.INFORMATION_SCHEMA.TABLE_STORAGE ORDER BY logical_gb DESC;

If your physical_gb is meaningfully smaller than your logical_gb for the larger datasets, physical billing is worth a proper look. You switch at the dataset level in the console under Dataset Settings.

Long-term storage, time travel, and a few things that quietly save money

A few other storage behaviours worth understanding.

Any table or partition that goes unmodified for 90 consecutive days automatically drops to long-term storage pricing, which is about half the active rate. This is automatic and free. The key word is "modified". Querying a table, exporting from it, or creating a view does not reset the 90-day clock. Loading new data, running DML, or streaming inserts does. Partitioning is useful here too because each partition's timer is tracked independently. One updated partition does not reset the rest.

Time travel, the ability to query your data as it existed at any point in the past seven days, is included at no extra charge under logical billing. Under physical billing, it is charged at active storage rates. If you are on physical billing and do not need seven days of history, you can reduce the time travel window to as little as two days in Dataset Settings. For high-churn datasets, this can make a meaningful difference.

For data you only need temporarily, set a time-to-live on the dataset or table. BigQuery handles the deletion automatically on the expiration date. For backup scenarios where you need to retain a point-in-time copy beyond the seven-day time travel window, table snapshots do the job cheaply. You are only billed for data that has changed or been deleted from the base table since the snapshot was taken, not for the full copy.

How to actually apply all of this

Knowing these techniques is one thing. Here is exactly where to go in BigQuery to put them into practice.

Add partitioning and clustering to a new table

In the BigQuery console, when creating a table, look for "Partition and cluster settings". Pick your partition field (typically a DATE or TIMESTAMP column) and up to four clustering columns. In SQL:

CREATE TABLE my_dataset.my_table PARTITION BY DATE(created_at) CLUSTER BY region, product_category AS SELECT * FROM my_dataset.old_table;

Note: You cannot add partitioning to an existing table in place. You create a new one and populate it.

Read the execution graph

Run your query in the console, then open the query job from history. Select the "Execution graph" tab at the top. Click into any stage for duration, bytes processed, records shuffled, and slot-milliseconds. The graph surfaces deviations from historical averages automatically.

Create a materialised view

Go to your dataset in the console, click "Create", and select "Materialised view". Set your defining query and refresh options. In SQL:

CREATE MATERIALIZED VIEW my_dataset.my_mv 
OPTIONS ( 
refresh_interval_minutes = 60, 
max_staleness = INTERVAL "4:0:0" HOUR TO SECOND) AS SELECT region, COUNT(*) AS total FROM my_dataset.events GROUP BY 1;

To trigger a manual refresh after a base table load:

CALL BQ.REFRESH_MATERIALIZED_VIEW('my_project.my_dataset.my_mv');

Check your compression ratios before switching billing models.

Run the INFORMATION_SCHEMA.TABLE_STORAGE query above against your datasets. If the numbers justify it, switch via Dataset Settings in the console. You can mix logical and physical billing across different datasets in the same project.

Reduce the time-travel window

In the console, open the dataset, click "Edit dataset", and adjust the "Time travel window" slider. Reducing from seven to two days lowers storage costs under physical billing.

Set time-to-live on transient data

When creating or editing a dataset or table, look for the "Default table expiration" field. Set a duration and BigQuery handles deletion automatically.

Take a longer-term snapshot

Open the table in the console, click "Export", and select "Snapshot". Or in SQL:

CREATE SNAPSHOT TABLE my_dataset.my_snapshot 
CLONE my_dataset.my_table 
OPTIONS (expiration_timestamp = TIMESTAMP_ADD(CURRENT_TIMESTAMP(), INTERVAL 90 DAY));

You are billed only for data that differs from the base table, making this a cost-effective alternative to duplicating tables for backup.

None of this requires infrastructure changes or large migrations. Most of it is configuration. The opportunity cost of not doing it, though, adds up faster than most people expect.