Partitioning, clustering, and BI Engine: measuring what each one saves in BigQuery

By the end of this walkthrough you will have two versions of the same table, a repeatable way to price any query before running it, and a query that tells you exactly how many bytes each version billed you. No estimates, no vendor benchmark, just the numbers your own project reports.

I keep running into the same situation: someone turns on partitioning, the bill does not move, and the conclusion becomes "partitioning does not work here". Usually partitioning worked fine and the queries were never written to use it. The only way to settle that is to measure both sides.

What you need

• A Google Cloud project with billing enabled and the BigQuery API on.
• The bq CLI from the Google Cloud SDK, or the BigQuery console if you prefer clicking. Every snippet below is standard GoogleSQL, so both work.
• Permissions to create datasets and tables, plus bigquery.jobs.list if you want the billing history query at the end.
• Around 5 to 10 GiB of scanning budget. On-demand pricing gives you the first 1 TiB per month free and charges $6.25 per TiB after that in the US multi region (Google Cloud, BigQuery pricing documentation), so this exercise costs cents, not dollars.

One warning before you start: the queries that build the optimized table are themselves billed. Dry run them first.

bq mk --location=US --dataset bq_cost_lab

Step 1: Build the baseline

I use the public Stack Overflow dataset because everyone can reproduce it. This copies a slice of it into a plain table with no partitioning and no clustering.

CREATE OR REPLACE TABLE `bq_cost_lab.questions_plain` AS
SELECT
  id,
  creation_date,
  owner_user_id,
  score,
  view_count,
  tags
FROM `bigquery-public-data.stackoverflow.posts_questions`
WHERE creation_date >= TIMESTAMP('2018-01-01');

Note what I did not copy. The body column holds the full HTML of every question and it is the single most expensive column in that table. BigQuery charges according to the data processed in the columns you select, even when you set an explicit LIMIT (Google Cloud, BigQuery pricing documentation), so column selection is the cheapest optimization available and it costs nothing to implement.

Step 2: Price the query before you run it

The dry run flag returns the byte estimate without executing anything and without charging you.

bq query --use_legacy_sql=false --dry_run \
'SELECT owner_user_id, COUNT(*) AS questions
 FROM `bq_cost_lab.questions_plain`
 WHERE DATE(creation_date) BETWEEN "2018-03-01" AND "2018-03-07"
 GROUP BY owner_user_id'

Write down the number it gives you. That is your baseline: [CONFIRMAR: rodar o dry run e anotar o valor observado] bytes. Divide it by 1,099,511,627,776 to get TiB, then multiply by 6.25 for the dollar figure. The date filter in that query saves you nothing yet, because a plain table has no partitions to skip.

Step 3: Partition on the column you actually filter by

Partitioning splits the table into physical blocks, and a qualifying filter on the partitioning column lets BigQuery scan the matching partitions and skip the rest, a process the documentation calls pruning (Google Cloud, introduction to partitioned tables).

CREATE OR REPLACE TABLE `bq_cost_lab.questions_tuned`
PARTITION BY DATE(creation_date)
CLUSTER BY owner_user_id, score
OPTIONS (require_partition_filter = TRUE) AS
SELECT * FROM `bq_cost_lab.questions_plain`;

Three decisions are packed into those four lines.

Daily granularity, not hourly. A partitioned table is capped at 10,000 partitions [CONFIRMAR: conferir o número atual na página Quotas and limits, o limite subiu de 4.000 em 2024] (Google Cloud, BigQuery quotas and limits). Daily partitions give you 27 years of runway. Hourly partitions give you 416 days, and hitting that ceiling in production is a migration, not a config change.

require_partition_filter = TRUE. This rejects any query that does not filter on the partitioning column (Google Cloud, managing partitioned tables). It is one line, it is reversible with an ALTER statement, and it is the difference between a table that saves money and a table that saves money until the first person forgets the WHERE clause. Turn it on unless you have a specific reason not to.

The partition filter has to be a constant expression. Filtering on a column that is not the partition key prunes nothing, and neither does a filter whose value BigQuery cannot resolve before execution. This is where most disappointed partitioning stories end.

Partitioning also has a storage side that rarely gets mentioned. Long term storage drops the rate by roughly half after 90 days without modification, and each partition of a partitioned table is evaluated separately for that discount (Google Cloud, BigQuery pricing documentation). On a plain table, one late arriving row resets the timer for everything. On a partitioned table, it resets one day.

Step 4: Cluster in the order you filter

Clustering sorts storage blocks by the values in the clustered columns, and queries that filter or aggregate on those columns scan only the relevant blocks instead of the full table or partition (Google Cloud, introduction to clustered tables).

You get up to four clustering columns, and the order determines the sort order, so the most frequently filtered column goes first (Google Cloud, creating clustered tables). In my snippet, owner_user_id comes before score because equality filters on user are common and selective, while score usually shows up as a range filter on top of that. Reverse them and the same query prunes worse.

Now the part that surprises people. Run a dry run against the clustered table and compare it to the previous one:

# replace 12345 with an owner_user_id that exists in your slice
bq query --use_legacy_sql=false --dry_run \
'SELECT owner_user_id, COUNT(*) AS questions
 FROM `bq_cost_lab.questions_tuned`
 WHERE DATE(creation_date) BETWEEN "2018-03-01" AND "2018-03-07"
   AND owner_user_id = 12345
 GROUP BY owner_user_id'

The estimate drops because of partition pruning, but it does not reflect the clustering at all. When you query a clustered table you do not get an accurate cost estimate before execution, because the number of storage blocks to scan is not known until the query runs, and the final cost is based on the blocks actually scanned (Google Cloud, introduction to clustered tables). The dry run on a clustered table is an upper bound. Treating it as the answer is how teams conclude that clustering did nothing.

Step 5: Verify with billed bytes, not estimates

Run both versions for real, then ask the metadata what happened.

SELECT
  job_id,
  creation_time,
  cache_hit,
  total_bytes_processed,
  total_bytes_billed,
  ROUND(total_bytes_billed / POW(1024, 4) * 6.25, 4) AS approx_usd
FROM `region-us`.INFORMATION_SCHEMA.JOBS_BY_PROJECT
WHERE creation_time > TIMESTAMP_SUB(CURRENT_TIMESTAMP(), INTERVAL 1 HOUR)
  AND statement_type = 'SELECT'
ORDER BY creation_time DESC;

total_bytes_billed is the number that reaches your invoice. Two details make it diverge from the estimate in ways worth knowing: charges are rounded up with a minimum of 10 MB per table referenced and 10 MB per query, and you are not charged for queries that fail or that return cached results (Google Cloud, BigQuery pricing documentation). If cache_hit is true, you measured nothing. Change a literal and run it again.

Record the pair. Baseline billed bytes: [CONFIRMAR: valor observado]. Tuned billed bytes: [CONFIRMAR: valor observado]. That ratio is the only savings figure I would put in a report.

This is the same argument the observability people have been making for years with logs, metrics, and traces. Cost is one more signal your platform emits. If nobody queries it, nobody knows what changed.

Step 6: Decide whether BI Engine earns its keep

BI Engine caches table data in memory to accelerate SQL. When it accelerates a query under on-demand pricing, the stage that reads table data is charged for zero scanned bytes (Google Cloud, BigQuery pricing documentation). That sounds like free money until you look at how it bills: $0.0416 per GiB hour, charged per project where you reserved capacity, whether or not anyone runs a query.

So the math is a subscription against a metered service. A 10 GiB reservation runs about $0.42 per hour, close to $304 per month at 730 hours. At $6.25 per TiB, that is the equivalent of roughly 48 TiB of scanning. If the dashboards hitting that reservation do not scan near that volume, the reservation loses.

-- reserve capacity, then confirm queries are actually being accelerated
SELECT
  job_id,
  bi_engine_statistics.bi_engine_mode,
  total_bytes_billed
FROM `region-us`.INFORMATION_SCHEMA.JOBS_BY_PROJECT
WHERE creation_time > TIMESTAMP_SUB(CURRENT_TIMESTAMP(), INTERVAL 1 DAY)
  AND bi_engine_statistics IS NOT NULL
ORDER BY creation_time DESC;

If bi_engine_mode comes back as DISABLED or PARTIAL for most of your traffic, you are paying for memory that is not accelerating anything. Two things commonly cause that: the working set does not fit the reservation, or the queries use operations the accelerator does not cover.

One detail that changes the calculation for larger shops: BigQuery editions commitments bundle BI Engine capacity at no extra cost, starting at 5 GiB for 100 slots and scaling to 100 GiB at 2,000 slots (Google Cloud, BigQuery pricing documentation). If you already hold a commitment, some of that capacity may be sitting unclaimed.

What this does not fix

Partitioning, clustering, and BI Engine all reduce bytes scanned. None of them touch the two costs I see grow fastest.

The first is storage on tables nobody queries. Active logical storage runs $23.55 per TiB per month in the US multi region (Google Cloud, BigQuery pricing documentation), and partition expiration is the cheapest cleanup available. The second is repeated scanning of the same aggregate by twelve dashboards, which is a materialized view problem rather than a partitioning one.

There is a third one that no table setting solves. If the pipeline writes duplicates and someone rebuilds the table twice a week to fix them, you are paying for the rework, not for the analytics. That is a data quality problem, and it gets solved upstream, not in the DDL.

The habit that has saved me the most is boring: dry run first, check total_bytes_billed after, and never accept a savings number that nobody measured. Everything above is just the mechanism.

If you want the follow up, tell me in the comments whether materialized views or slot reservations are the harder call on your side.