/

Data

How to reduce BigQuery costs: a guide for data teams

How to reduce BigQuery costs: a guide for data teams

Data

Data

Mahesh Chandran

Mahesh Chandran

CEO

CEO

Guide cover: How to reduce BigQuery costs. Find where the bytes go, then stop paying for work nobody needs.

Which ten queries scanned the most data in your BigQuery project last month, and who still reads their results? BigQuery records the first half of that question for every job, and one query returns it.

On on-demand pricing, BigQuery bills for the bytes each query processes, so most of the work to reduce BigQuery costs comes down to scanning less data. The rest comes from storage settings and from stopping work that nobody uses.

This guide shows where BigQuery spend goes and how to find the queries, users and tables behind it with INFORMATION_SCHEMA. It covers how to make queries scan less, how to cap runaway queries before they run, how to stop paying for data nobody reads, how to cut storage costs and when capacity pricing makes sense. It includes the queries, a worked example and a monthly review routine.

The short answer

Most BigQuery spend on on-demand pricing comes from bytes processed by queries. To reduce BigQuery costs, find the recurring queries that bill the most bytes, partition and cluster the tables they read, select only the columns they need, and cap what a single query or user can scan. Stop refreshing tables nobody reads, set expirations on temporary data, and check whether physical storage billing and capacity pricing would cost less for your workload. Measure spend before and after each change.

Key terms

  • On-demand pricing: billing by the bytes each query processes.

  • Capacity pricing: billing by slots, units of compute, reserved or autoscaled over time.

  • Bytes billed: the bytes a query is charged for after BigQuery’s minimums are applied.

  • Partitioning: splitting a table into segments, usually by date, so queries that filter on that column read only the segments they need.

  • Clustering: sorting a table’s storage blocks by chosen columns, so queries that filter on them skip blocks they do not need.

  • Long-term storage: table data that has not been modified for 90 days, billed at a lower rate.

  • Dry run: a query submitted only to validate it and estimate the bytes it would process, without running it.

The method at a glance

  1. Total the last 30 days of bytes billed by recurring query, by user and by table.

  2. Partition and cluster the large tables those queries read.

  3. Rewrite the heaviest queries to read only the columns and partitions they need.

  4. Cap bytes per query and per user with maximum bytes billed and custom quotas.

  5. Retire or slow down refreshes of tables nobody reads.

  6. Set expirations and time travel windows on temporary data.

  7. Review storage billing and pricing model once the workload is stable.

  8. Repeat the review every month.

Where does BigQuery spend go?

BigQuery bills compute and storage separately. On on-demand pricing, queries cost $6.25 per TiB processed after the first 1 TiB each month, with charges rounded up to the nearest MB, a 10 MB minimum per table referenced and a 10 MB minimum per query. Queries that return an error or are answered from cached results are not charged. On capacity pricing, compute is billed by slot-hours in one of three editions, with options for autoscaling and one-year or three-year commitments.

The same daily query on a 5 TiB table: without a partition filter it reads all 5 TiB, with a date partition filter it reads one day.

Storage is billed separately for active and long-term data, with the first 10 GiB free each month. A table or partition that has not been modified for 90 consecutive days moves to long-term storage, and its price drops by about 50% with no change in performance. Each partition is counted on its own. Loads, DML, CREATE OR REPLACE TABLE, streaming writes and jobs that write to the table reset the 90-day clock. Querying, exporting and copying from the table do not.

Each dataset can be billed on logical or physical bytes. Logical billing, the default, charges for the uncompressed size of the data. Physical billing charges for the compressed size, and charges separately for time travel and fail-safe storage, which logical billing includes in its rate.

Which queries cost the most?

The INFORMATION_SCHEMA.JOBS view records every job in a project for the past 180 days, including the bytes billed and a hash of each query with its literal values removed. Grouping by that hash adds up every run of the same query, so a scheduled job shows its monthly total. Reading the view needs the BigQuery User and BigQuery Resource Viewer roles, or equivalent permissions. Change the region qualifier to match your data.

SELECT
  query_info.query_hashes.normalized_literals AS query_hash,
  ANY_VALUE(user_email) AS example_user,
  COUNT(*) AS runs,
  ROUND(SUM(total_bytes_billed) / POW(1024, 4), 2) AS tib_billed,
  ANY_VALUE(query) AS example_query
FROM `region-us`.INFORMATION_SCHEMA.JOBS
WHERE creation_time >= TIMESTAMP_SUB(CURRENT_TIMESTAMP(), INTERVAL 30 DAY)
  AND job_type = 'QUERY'
  AND statement_type != 'SCRIPT'
GROUP BY query_hash
ORDER BY tib_billed DESC
LIMIT 25

SELECT
  query_info.query_hashes.normalized_literals AS query_hash,
  ANY_VALUE(user_email) AS example_user,
  COUNT(*) AS runs,
  ROUND(SUM(total_bytes_billed) / POW(1024, 4), 2) AS tib_billed,
  ANY_VALUE(query) AS example_query
FROM `region-us`.INFORMATION_SCHEMA.JOBS
WHERE creation_time >= TIMESTAMP_SUB(CURRENT_TIMESTAMP(), INTERVAL 30 DAY)
  AND job_type = 'QUERY'
  AND statement_type != 'SCRIPT'
GROUP BY query_hash
ORDER BY tib_billed DESC
LIMIT 25

SELECT
  query_info.query_hashes.normalized_literals AS query_hash,
  ANY_VALUE(user_email) AS example_user,
  COUNT(*) AS runs,
  ROUND(SUM(total_bytes_billed) / POW(1024, 4), 2) AS tib_billed,
  ANY_VALUE(query) AS example_query
FROM `region-us`.INFORMATION_SCHEMA.JOBS
WHERE creation_time >= TIMESTAMP_SUB(CURRENT_TIMESTAMP(), INTERVAL 30 DAY)
  AND job_type = 'QUERY'
  AND statement_type != 'SCRIPT'
GROUP BY query_hash
ORDER BY tib_billed DESC
LIMIT 25

Multiply the TiB billed by your on-demand rate for an estimate in dollars. Group by user_email instead to see which people and service accounts scan the most. Script jobs are excluded because a multi-statement script’s summary row already includes the values of its child jobs, so including both would count them twice.

Which tables cost the most to read?

The same view lists the tables each query referenced. Adding up the bytes of every query that touched a table shows which tables drive the most scanning. Queries that read several tables count toward each of them, so treat the result as a ranking for where to look. Queries answered from cache list no tables.

SELECT
  CONCAT(t.dataset_id, '.', t.table_id) AS table_name,
  COUNT(DISTINCT j.job_id) AS queries,
  ROUND(SUM(j.total_bytes_billed) / POW(1024, 4), 2) AS tib_billed_by_queries
FROM `region-us`.INFORMATION_SCHEMA.JOBS j,
  UNNEST(j.referenced_tables) t
WHERE j.creation_time >= TIMESTAMP_SUB(CURRENT_TIMESTAMP(), INTERVAL 30 DAY)
  AND j.job_type = 'QUERY'
  AND j.statement_type != 'SCRIPT'
GROUP BY table_name
ORDER BY tib_billed_by_queries DESC
LIMIT 25

SELECT
  CONCAT(t.dataset_id, '.', t.table_id) AS table_name,
  COUNT(DISTINCT j.job_id) AS queries,
  ROUND(SUM(j.total_bytes_billed) / POW(1024, 4), 2) AS tib_billed_by_queries
FROM `region-us`.INFORMATION_SCHEMA.JOBS j,
  UNNEST(j.referenced_tables) t
WHERE j.creation_time >= TIMESTAMP_SUB(CURRENT_TIMESTAMP(), INTERVAL 30 DAY)
  AND j.job_type = 'QUERY'
  AND j.statement_type != 'SCRIPT'
GROUP BY table_name
ORDER BY tib_billed_by_queries DESC
LIMIT 25

SELECT
  CONCAT(t.dataset_id, '.', t.table_id) AS table_name,
  COUNT(DISTINCT j.job_id) AS queries,
  ROUND(SUM(j.total_bytes_billed) / POW(1024, 4), 2) AS tib_billed_by_queries
FROM `region-us`.INFORMATION_SCHEMA.JOBS j,
  UNNEST(j.referenced_tables) t
WHERE j.creation_time >= TIMESTAMP_SUB(CURRENT_TIMESTAMP(), INTERVAL 30 DAY)
  AND j.job_type = 'QUERY'
  AND j.statement_type != 'SCRIPT'
GROUP BY table_name
ORDER BY tib_billed_by_queries DESC
LIMIT 25

The top tables from this query are usually the ones where partitioning and clustering pay off first.

How do you estimate a query’s cost before running it?

A dry run validates a query and estimates the bytes it would process, without running it. Dry runs use no slots and are not charged. Run one before scheduling any new query against a large table:

bq query \
  --use_legacy_sql=false \
  --dry_run \
  'SELECT customer_id, SUM(amount) FROM analytics.invoices WHERE invoice_date = CURRENT_DATE() GROUP BY customer_id'
bq query \
  --use_legacy_sql=false \
  --dry_run \
  'SELECT customer_id, SUM(amount) FROM analytics.invoices WHERE invoice_date = CURRENT_DATE() GROUP BY customer_id'
bq query \
  --use_legacy_sql=false \
  --dry_run \
  'SELECT customer_id, SUM(amount) FROM analytics.invoices WHERE invoice_date = CURRENT_DATE() GROUP BY customer_id'

The estimate has limits. For clustered tables, the number of blocks skipped is only known after the query runs, so the estimate is an upper bound. Dry runs on tables with row-level security return 0 bytes, so they cannot be used for those.

How do you make queries scan less?

BigQuery stores data by column, so a query is billed for the columns it reads in the partitions and blocks it scans. Every change that narrows either one cuts the bill.

  • Partition large tables by date. A query that filters on the partition column reads only the matching partitions. Requiring a partition filter on the table stops queries that would otherwise read all of it.

  • Cluster on the columns people filter by. Clustering lets BigQuery skip storage blocks that cannot match the filter, and skipped blocks are not billed. A table can have up to four clustering columns, and BigQuery reclusters automatically at no charge.

  • Select only the columns needed. SELECT * reads every column. Naming the columns reads only those.

  • Use the table preview to look at data. Previewing a table in the console does not run a query, so it costs nothing.

  • Follow the recommender. BigQuery’s partitioning and clustering recommender looks at up to 30 days of your workload and suggests which tables to partition or cluster, on which column, with estimated monthly savings.

CREATE TABLE analytics.events_partitioned
PARTITION BY DATE(event_timestamp)
CLUSTER BY customer_id, event_type
OPTIONS (require_partition_filter = TRUE)
AS SELECT * FROM

CREATE TABLE analytics.events_partitioned
PARTITION BY DATE(event_timestamp)
CLUSTER BY customer_id, event_type
OPTIONS (require_partition_filter = TRUE)
AS SELECT * FROM

CREATE TABLE analytics.events_partitioned
PARTITION BY DATE(event_timestamp)
CLUSTER BY customer_id, event_type
OPTIONS (require_partition_filter = TRUE)
AS SELECT * FROM

The recommender needs the Recommender API enabled and a role that can view its recommendations. Its savings estimates assume the next month looks like the last one, so they can be high. It also cannot change a table’s partitioning in place, which is why the example above builds a new table. Swap readers over to it, check the bytes billed fall, then drop the old table.

Common patterns that scan too much

  • Dashboards that refresh on a timer. A BI dashboard set to refresh every few minutes runs its queries whether or not anyone is looking. Refresh it as often as the data changes.

  • Exploration on full tables. Analysts exploring a large table can scan all of it many times a day. Give them a sampled or recent-days table to explore.

  • Joins against unpartitioned history. A daily query that joins today’s events to an unpartitioned history table reads all of the history every day.

How do you cap runaway queries?

Two controls stop a single mistake from becoming a large bill.

  • Maximum bytes billed per query. Setting maximum bytes billed on a query, or as a default in a tool or service account, makes BigQuery fail the query before it runs if it would bill more than the limit.

  • Custom quotas per project and per user. Custom quotas limit the bytes processed per day across a project or for each user and service account. The default project limit is 200 TiB a day and the default per-user limit is unlimited, so most teams benefit from setting both. A query larger than the remaining quota fails with a quotaExceeded error, and daily quotas reset at midnight Pacific Time. Quotas apply only to on-demand pricing, and Google describes them as approximate.

bq query \
  --use_legacy_sql=false \
  --maximum_bytes_billed=100000000000 \
  'SELECT customer_id, SUM(amount) FROM analytics.invoices WHERE invoice_date = CURRENT_DATE() GROUP BY customer_id'
bq query \
  --use_legacy_sql=false \
  --maximum_bytes_billed=100000000000 \
  'SELECT customer_id, SUM(amount) FROM analytics.invoices WHERE invoice_date = CURRENT_DATE() GROUP BY customer_id'
bq query \
  --use_legacy_sql=false \
  --maximum_bytes_billed=100000000000 \
  'SELECT customer_id, SUM(amount) FROM analytics.invoices WHERE invoice_date = CURRENT_DATE() GROUP BY customer_id'

Set per-user quotas with the heaviest legitimate users in mind, using the JOBS query grouped by user. A quota set below what a scheduled pipeline needs will fail that pipeline at the worst time, so give service accounts that run Tier 1 work enough headroom.

How do you attribute costs to teams?

Costs that belong to nobody do not get cut. Make each team’s share of the bill visible.

  • One service account per workload. Give each pipeline, BI tool and team its own service account. The JOBS query grouped by user_email then splits spend by workload without any extra setup.

  • Separate projects for large teams. Running a team’s work in its own project puts its spend on its own line of the bill.

  • Labels on jobs. The bq tool’s --label flag attaches key-value labels to a query job, such as a team or cost center. Like table labels, keys and values can contain only lowercase letters, numbers, underscores and dashes.

Send each team its monthly total with its five most expensive recurring queries. People act on costs they can see and recognize as theirs.

How do you stop paying for data nobody reads?

Every scheduled query that rebuilds a table costs bytes whether or not anyone reads the result. How to find and retire unused data pipelines includes a BigQuery query that lists tables written in the last 90 days and never read, and shows how to retire them in stages. How to decide which datasets your business depends on ranks the rest, so that Tier 3 tables can move to slower refreshes.

Rebuilding a large table in full every run is a common cost. Where only recent data changes, an incremental model that processes the latest partitions reads far less. It also leaves older partitions untouched, so they move to long-term storage pricing.

Check refresh schedules against when the data is read. A table refreshed hourly and read once a morning can be refreshed once a day. How to set data freshness SLAs shows how to set a ready-by time for each dataset, which tells you how often it really needs to run.

How do you cut storage costs?

Storage costs can be cut without touching any query.

  • Let long-term pricing work. Partitions that are not modified for 90 days are billed at the lower rate. Pipelines that rewrite whole tables reset that clock for every partition, while partitioned incremental loads leave old partitions alone.

  • Set expirations on temporary data. Google’s storage best practices recommend expirations at the dataset, table and partition level, so data you no longer need deletes itself. A dataset’s default expiration applies to tables created after it is set.

  • Shorten time travel on staging data. Time travel keeps changed and deleted data for 2 to 7 days, with 7 as the default, followed by 7 days of fail-safe storage. On physical billing both are charged, so a shorter window on staging and scratch datasets saves money.

  • Compare logical and physical billing. Physical billing can cost less for data that compresses well. The TABLE_STORAGE view shows both sizes for each table, and Google’s documentation for the view includes an example that forecasts the price difference per dataset.

-- Expire scratch tables after 7 days
ALTER SCHEMA scratch
SET OPTIONS (default_table_expiration_days = 7);

-- Keep 2 days of time travel on staging data
ALTER SCHEMA staging
SET OPTIONS (max_time_travel_hours = 48)

-- Expire scratch tables after 7 days
ALTER SCHEMA scratch
SET OPTIONS (default_table_expiration_days = 7);

-- Keep 2 days of time travel on staging data
ALTER SCHEMA staging
SET OPTIONS (max_time_travel_hours = 48)

-- Expire scratch tables after 7 days
ALTER SCHEMA scratch
SET OPTIONS (default_table_expiration_days = 7);

-- Keep 2 days of time travel on staging data
ALTER SCHEMA staging
SET OPTIONS (max_time_travel_hours = 48)

This query shows logical and physical sizes by dataset, including the bytes held for time travel and fail-safe:

SELECT
  table_schema,
  ROUND(SUM(active_logical_bytes) / POW(1024, 4), 2) AS active_logical_tib,
  ROUND(SUM(long_term_logical_bytes) / POW(1024, 4), 2) AS long_term_logical_tib,
  ROUND(SUM(active_physical_bytes) / POW(1024, 4), 2) AS active_physical_tib,
  ROUND(SUM(long_term_physical_bytes) / POW(1024, 4), 2) AS long_term_physical_tib,
  ROUND(SUM(time_travel_physical_bytes + fail_safe_physical_bytes) / POW(1024, 4), 2) AS tt_and_failsafe_tib
FROM `region-us`.INFORMATION_SCHEMA.TABLE_STORAGE
GROUP BY table_schema
ORDER BY active_logical_tib DESC

SELECT
  table_schema,
  ROUND(SUM(active_logical_bytes) / POW(1024, 4), 2) AS active_logical_tib,
  ROUND(SUM(long_term_logical_bytes) / POW(1024, 4), 2) AS long_term_logical_tib,
  ROUND(SUM(active_physical_bytes) / POW(1024, 4), 2) AS active_physical_tib,
  ROUND(SUM(long_term_physical_bytes) / POW(1024, 4), 2) AS long_term_physical_tib,
  ROUND(SUM(time_travel_physical_bytes + fail_safe_physical_bytes) / POW(1024, 4), 2) AS tt_and_failsafe_tib
FROM `region-us`.INFORMATION_SCHEMA.TABLE_STORAGE
GROUP BY table_schema
ORDER BY active_logical_tib DESC

SELECT
  table_schema,
  ROUND(SUM(active_logical_bytes) / POW(1024, 4), 2) AS active_logical_tib,
  ROUND(SUM(long_term_logical_bytes) / POW(1024, 4), 2) AS long_term_logical_tib,
  ROUND(SUM(active_physical_bytes) / POW(1024, 4), 2) AS active_physical_tib,
  ROUND(SUM(long_term_physical_bytes) / POW(1024, 4), 2) AS long_term_physical_tib,
  ROUND(SUM(time_travel_physical_bytes + fail_safe_physical_bytes) / POW(1024, 4), 2) AS tt_and_failsafe_tib
FROM `region-us`.INFORMATION_SCHEMA.TABLE_STORAGE
GROUP BY table_schema
ORDER BY active_logical_tib DESC

After changing a dataset’s billing model, you must wait 14 days before changing it again, and the change takes about a day to take effect. Tables already in long-term storage stay there when the model changes. Changes from expirations can take up to a day to appear in the view, so check it again before deciding.

Should you switch to capacity pricing?

Capacity pricing bills for slots used over time, so it rewards steady, predictable workloads and removes the link between bytes scanned and cost. On-demand pricing suits spiky or light workloads. Capacity pricing also groups slots into reservations, which can keep production and ad hoc work apart. Autoscaled slots are billed with a one-minute minimum by default.

Compare the last few months of on-demand spend with what the same workload would use in slot-hours before switching. The JOBS view’s total_slot_ms column gives slot usage per job. Fix the heaviest queries first, because they cost more under either model.

How do you keep costs from creeping back?

Savings fade as new queries and tables arrive. A few controls keep them in place:

  • Dry-run new scheduled queries in code review. Add a step to continuous integration that dry-runs each new or changed scheduled query and flags any that would process more than an agreed number of bytes.

  • Require partition filters on new large tables. Make require_partition_filter the default for any table expected to grow large.

  • Set maximum bytes billed in every tool. BI tools, notebooks and service accounts should all have a default limit, so a mistake fails fast.

  • Review new tables monthly. Tables created in the last month with no reads are early candidates for retirement, before anyone starts depending on them.

A worked example

Here is how a first review might go. The details are invented to show the method.

A data team runs the JOBS query and finds that one scheduled query accounts for the largest share of bytes billed. It rebuilds a daily summary from a raw events table that is not partitioned, and it reads every column. The table query shows the raw events table at the top, read by that query and by a BI dashboard that refreshes every 15 minutes.

The team partitions the events table by event date and clusters it on customer ID, with a required partition filter. They rewrite the scheduled query to read the previous day and the five columns it uses, and change the dashboard to refresh hourly. A dry run of the new query shows it would process a small fraction of the bytes. They set a per-user quota for analysts and a maximum bytes billed default for the BI tool’s service account.

A month later, the team runs the same JOBS query and reports the change in TiB billed for the summary query, the dashboard and the project as a whole.

What does a monthly cost review look like?

A short routine keeps the bill from drifting back up:

  1. Run the JOBS query by query hash and by user, and compare with last month.

  2. Look at the five most expensive new queries and check they partition-filter and name their columns.

  3. Run the table query and check the top tables are partitioned and clustered.

  4. Check the recommender for new partitioning or clustering suggestions.

  5. Run the unused-table check and start retiring what nobody reads.

  6. Check storage by dataset, including time travel and fail-safe bytes.

  7. Share the per-user report, so people can see what their queries cost.

Questions people ask

Does LIMIT reduce BigQuery costs?

Not on its own. A LIMIT clause reduces the rows returned, but BigQuery still reads the columns in every partition and block the query scans. Filtering on the partition column is what reduces bytes billed.

How much can these changes save?

It depends on how the workload reads data today, and there is no reliable industry figure to borrow. Run the JOBS query before making changes and again a month later, and report the difference.

Do queries on cached results cost anything?

Queries answered from cached results are not billed, and cached result tables carry no storage charge. Cached results are not used for every query, for example when the underlying tables have changed.

Are dry runs free?

Yes. Dry runs use no slots and are not charged, so they are a safe way to check a query against a large table.

Who should be able to see BigQuery costs?

Everyone who runs heavy queries. Sharing the JOBS report by user each month shows each person what their queries cost.

Does clustering cost extra?

Automatic reclustering is free. Building a new clustered copy of an existing table is a normal query, billed for the bytes it reads.

Does deleting data reduce storage costs straight away?

Not entirely. Deleted and changed data stays in time travel for the dataset’s window and then in fail-safe for 7 days. On physical billing, both are charged, so deleting a large table keeps costing money for up to two weeks.

Should every table be partitioned?

Partition large tables that are queried by a date or a range. Small tables gain little, because queries against them bill few bytes anyway, and each table referenced still bills at least 10 MB.

What should I fix first?

The recurring queries at the top of the JOBS report. A scheduled query runs every day, so cutting its bytes saves money every day. One-off expensive queries are worth a conversation with whoever ran them, but they rarely repeat.

How far back can I see query costs?

The JOBS view keeps job history for the past 180 days. Save the monthly report to a table if you want to compare over longer periods.

Where Dataring fits

We built Dataring to rank every dataset by how much the business depends on it, then act on that ranking. The datasets the business relies on get the resources to stay reliable, and the pipelines nobody depends on are retired within limits each company sets, under its supervision. The BigQuery bytes that went to data nobody reads go to the data the business runs on.

Book a free call to see it on your data estate. For the wider process this guide fits into, read How to audit your data estate in seven steps.

Newsletter

Product news by email. We use your address for nothing else.

© 2026 ShareData Inc.Last updated October 29, 2026