Skip to content
Tutorials Center
My learning

Topics

Lesson 27 of 45 ยท Data and Analytics

BigQuery Partitioning, Clustering and Cost Control

Cut BigQuery bills and query time: partitioned and clustered tables, dry runs, avoiding SELECT *, and a pandas model that estimates bytes scanned.

With on-demand pricing, a BigQuery query costs money in proportion to the data it reads. A table of web events can hold a year of history, but the report you run every morning only needs yesterday. If the engine has to read the whole table anyway, you pay for a year each day. The tools in this lesson let BigQuery skip data that cannot match your question: column selection, partitioning and clustering, together with safety nets such as dry runs and billing limits.

The goal is to reduce bytes scanned. Fewer bytes means a smaller bill and, almost always, faster results.

Four ways to read less

Pick columns Never SELECT * from a wide table.
Partition Split a table by date so old days are skipped.
Cluster Sort storage by a column so blocks can be skipped.
Dry run Check the bytes before you pay for them.

Each one removes work before it happens.

1 / 2

Read only the columns you need

BigQuery stores each column separately (a columnar format). A query that mentions two columns reads those two, and nothing else. This is why SELECT * is the most expensive habit: it reads every column, including big text or JSON fields you never look at.

SQL
-- Reads every column of every row
SELECT * FROM `my-project.shop.events`;

-- Reads only two columns
SELECT user_id, event_date FROM `my-project.shop.events`;

-- Star, minus the heavy column
SELECT * EXCEPT (raw_payload) FROM `my-project.shop.events`;

As the previous lesson showed, LIMIT does not help: it limits the rows you get back, not the bytes read. If you only want to look at a table, use the table preview in the console, which is free, instead of SELECT * ... LIMIT 10.

Partitioned tables

A partitioned table is divided into segments, usually by a date or timestamp column. When your query filters on the partition column, BigQuery reads only the matching partitions and ignores the others. This is called partition pruning.

SQL
CREATE TABLE `my-project.shop.events`
(
  event_date DATE,
  country STRING,
  user_id INT64,
  payload STRING
)
PARTITION BY event_date
OPTIONS (require_partition_filter = TRUE);

With require_partition_filter turned on, any query that forgets a filter on event_date is rejected instead of silently scanning everything. That is a cheap guardrail against expensive mistakes.

You can partition by a date or timestamp column (daily, hourly, monthly or yearly granularity), by the time data was ingested, or by an integer range. Each table can have up to a documented maximum number of partitions, so choose a granularity that gives well-sized pieces. Partitions that you do not touch for 90 days also become cheaper to store. For old data you can set an expiration on partitions so they delete themselves.

For pruning to work, the filter must use the partition column in a simple way. A filter such as WHERE event_date BETWEEN '2025-01-01' AND '2025-01-07' prunes; wrapping the column in a function in a way that BigQuery cannot evaluate before running the query may not. When in doubt, run a dry run and look at the bytes.

Clustered tables

Clustering sorts the data inside a table (or inside each partition) by up to four columns that you choose. Storage is split into blocks, and BigQuery keeps the minimum and maximum values of the clustering columns for each block. A filter on a clustering column lets it skip blocks whose range cannot match.

SQL
CREATE TABLE `my-project.shop.events_clustered`
PARTITION BY event_date
CLUSTER BY country, user_id AS
SELECT * FROM `my-project.shop.events`;

Good clustering columns are ones you often filter or aggregate by and that have many distinct values, such as customer ID, country or product category. The order of the columns matters: the first one is sorted first, so filters on it prune best. Unlike partitions, the savings of clustering are not known exactly before a query runs; the dry-run estimate is an upper bound, and the final billed amount can be lower.

Partitioning and clustering work well together: partition by date, then cluster by the column you most often filter on within a day.

Dry runs and billing guards

A dry run asks BigQuery "how many bytes would this scan?" without running the query or charging you. It is available in the console (the number in the editor's corner while you type) and on the command line:

Terminal
bq query --use_legacy_sql=false --dry_run \
  'SELECT user_id FROM `my-project.shop.events` WHERE event_date = "2025-01-07"'

Add a hard stop with maximum bytes billed. If the query would scan more, it fails and costs nothing:

Terminal
bq query --use_legacy_sql=false --maximum_bytes_billed=10000000000 \
  'SELECT user_id FROM `my-project.shop.events`'

At the project and user level you can set custom quotas on bytes processed per day, and you should always have a budget alert on the billing account. Other good habits:

  • Filter early and only join the columns you need.
  • Use APPROX_COUNT_DISTINCT when an estimate is enough.
  • Materialize an expensive intermediate result into a table or a materialized view if many reports reuse it.
  • Do not use the same unfiltered scan to power a dashboard that refreshes every minute; cache, aggregate, or pre-compute.
  • Remember that the result of a query is cached for about a day, and a cached result is free, as long as the underlying data is unchanged.

Stand-in: estimating bytes scanned with pandas

The code below builds a small events table with pandas and counts the bytes each kind of query would read, under simple rules: every column has a size per value, a partitioned table reads only the day-partitions that match, and a clustered table reads only the blocks whose range of country could contain the filter value. It is a model of the idea, not BigQuery's real storage format or its exact accounting. The numbers are from our fake data and will not match a real table, but the proportions show why these features matter.

Python Stand-in: bytes scanned, with and without pruning

Press Run (or Ctrl + Enter) to see the result here.

Program input ยท what you'd type while the program runs

Type the answers before you press Run, one per line. Each time the program reads a line (with input()), it gets the next line from this box.

Read the percentages: picking one column reads about 5% of the data, partitioning by date to read one week out of thirty reads about a fifth, and clustering reads roughly a third of the blocks for one country out of four. The clustered figure is a little above the ideal quarter because blocks at the edge of a country's range also include rows of a neighboring country. Real BigQuery behaves similarly.

Stand-in: a guard before you run

Finally, a tiny model of the dry-run workflow: estimate first, refuse above a limit. The table statistics and the price are invented for the exercise.

Python Stand-in: dry run with a bytes limit

Press Run (or Ctrl + Enter) to see the result here.

Program input ยท what you'd type while the program runs

Type the answers before you press Run, one per line. Each time the program reads a line (with input()), it gets the next line from this box.

The last line is the mistake a require_partition_filter setting prevents: a full year of every column, blocked before it can run.

Check your understanding

Quick check ยท 5 questions

of 5 answered You scored / 5 ยท perfect!

  1. 1. Why is SELECT * discouraged on large BigQuery tables?

    BigQuery is columnar and bills for the columns read. Naming only the ones you need reduces the bytes scanned.
  2. 2. What does partition pruning do?

    Filtering on the partition column lets BigQuery read only the matching partitions.
  3. 3. Which statement about clustering is true?

    Clustering keeps per-block ranges of the chosen columns. Savings depend on the data, so a dry run gives an upper bound.
  4. 4. How do you make sure a query that would scan too much fails instead of costing money?

    With a maximum bytes billed setting, a query that would exceed the limit fails without charge.
  5. 5. What does the option require_partition_filter do?

    It forces every query to include a partition filter, which prevents accidental full scans.

Summary

  • BigQuery on-demand cost follows bytes read, so reduce what is read.
  • Name only the columns you need; LIMIT does not cut cost.
  • Partition by date and require a partition filter; cluster by commonly filtered columns.
  • Use dry runs to see the bytes first and set maximum bytes billed and quotas as a safety net.
  • Cache or pre-aggregate results that many people or dashboards reuse.

Next: Pub/Sub Messaging