skip to content

In BigQuery's on-demand pricing model, what determines how much a query costs?

level: juniorimportance: must knowfreq 82%

answer

  1. you pay for reads, not results
  2. columnar storage decides what gets read
  3. row limits do not shrink the bill
  4. logical size, not compressed size on disk
  5. cache hits bill zero bytes

basics

~10 s

Under BigQuery on-demand pricing you pay per byte processed: the uncompressed size of the columns the query reads, after partition and cluster pruning. The number of rows returned is irrelevant, so LIMIT changes nothing.

solid answer

~40 s

On-demand BigQuery bills **bytes processed**, meaning bytes read from storage, at a published rate per TiB. Because storage is columnar, only the columns a query references are read, so `SELECT *` on a wide table is the classic cost bug and `SELECT user_id, event_ts` may be a hundred times cheaper. Bytes are counted with BigQuery's logical data-type sizes, not the compressed size on disk, so compression lowers storage cost but never query cost. `LIMIT` and `ORDER BY` do not reduce the bill because the engine still had to read the data. What genuinely reduces it: fewer columns, a constant filter on a partitioning column, clustering, and the result cache — a cache hit bills zero bytes. Failed queries and dry runs are not billed, and you can cap a job with `maximum_bytes_billed`.

code

sql · 7 lines
sql
-- Expensive: reads every column of a wide table
SELECT * FROM `proj.ds.events` WHERE event_date = '2026-08-01' LIMIT 100;

-- Cheap: two columns, constant filter on the partitioning column
SELECT user_id, event_name
FROM `proj.ds.events`
WHERE event_date = '2026-08-01';

go deeper

for a junior

Recall the one-line rule: on-demand BigQuery bills bytes read, driven by which columns and partitions the query touches. Be ready to say why SELECT * is discouraged and why LIMIT does not help.

for a middle

Explain the mechanics: columnar projection, logical data-type sizes rather than compressed size, and the fact that pruning is the only filter that removes bytes before they are read.

for a senior

Show how you find the expensive jobs after the fact with INFORMATION_SCHEMA.JOBS, attribute them to a user, dashboard or model, and fix the pattern rather than the single query.

for a principal

Own the framing that query cost, storage cost and ingestion cost are separate meters, and decide whether per-byte billing is even the right model for the organization's workload shape.

## What the meter actually counts BigQuery's on-demand (per-query) pricing bills each query for **bytes processed** — the volume of table data the engine had to read in order to answer it — at a published rate per TiB. It is a read meter, not a result meter. A query that returns a single row can be the most expensive query of the day, and a query that returns ten million rows can be nearly free. All of the engineering work in controlling on-demand cost is therefore work on making the number of bytes read small. ## How the byte number is computed Three properties of BigQuery storage decide it. **Columnar layout.** Table data is stored column by column, so a query reads only the columns it references. On a table with 300 columns, `SELECT event_id, user_id FROM t` reads two columns; `SELECT * FROM t` reads all 300 and can easily cost two orders of magnitude more. `SELECT * EXCEPT(raw_payload)` is a cheap way to drop the one fat column while keeping the rest. **Logical, uncompressed sizes.** Bytes processed are computed from BigQuery's documented data-type sizes, not from what the compressed blocks weigh on disk. An `INT64` counts eight bytes per value even if the column compressed to almost nothing; a `STRING` counts two bytes plus the length of its UTF-8 encoding. Compression makes storage cheaper; it never makes a query cheaper. **Pruning.** Partition elimination on a partitioned table and block pruning on a clustered table remove data before it is read, and those savings do appear in the bill. This is why a date filter written as a constant expression on the partitioning column is the single highest-leverage cost change available. ## What does not reduce the number `LIMIT 10` is the most common misconception: the rows are limited after the data is read, so the bill is unchanged. `ORDER BY` likewise. A `WHERE` clause on an ordinary column that is neither a partitioning key nor a cluster key does not help either — the engine must read that column across the table to evaluate the predicate, plus every other column in the select list. Logical views are inlined into the calling query, so a view defined as `SELECT *` hands the caller the full-width scan. Subqueries and CTEs do not change the accounting; what matters is the set of columns and partitions ultimately touched. Queries that fail are not billed, and a dry run is free. ## What does reduce it Project fewer columns. Partition the table on the column real filters use and write those filters as constants. Cluster on high-selectivity filter columns. Let the result cache work: an identical query text against unchanged tables is served from cache and bills zero bytes for roughly a day. Materialized views let repeated aggregations read a small precomputed table instead of the base table. For exploration, `TABLESAMPLE SYSTEM (1 PERCENT)` reads a fraction of the blocks and bills accordingly. ## Minimums, and reading the bill afterwards BigQuery applies a small per-query and per-table-referenced minimum, so trivial queries are not free but are negligible. After the fact, `INFORMATION_SCHEMA.JOBS` is the source of truth: `total_bytes_processed`, `total_bytes_billed`, `cache_hit`, `user_email` and the query text per job. The processed and billed numbers differ exactly where the minimums and the cache apply — a cache hit reports zero billed bytes. ```sql SELECT user_email, SUM(total_bytes_billed) / POW(1024, 4) AS tb_billed FROM `region-us`.INFORMATION_SCHEMA.JOBS_BY_PROJECT WHERE creation_time >= TIMESTAMP_SUB(CURRENT_TIMESTAMP(), INTERVAL 7 DAY) AND job_type = 'QUERY' GROUP BY user_email ORDER BY tb_billed DESC; ``` ## Where it bites in production The expensive queries are rarely the ones an engineer wrote by hand. They are BI dashboards refreshing an unfiltered `SELECT *` view every few minutes, transformation models built on views that widen as the schema grows, and tables that grow so that last quarter's cheap query is this quarter's expensive one — the same query text costs more every month because the table is bigger. Cost review therefore has to be continuous, not a one-off audit. ## The rest of the bill Query cost is only one meter. Stored bytes are billed separately and are unaffected by queries, and streaming ingestion is billed by ingested bytes. When someone says "the BigQuery bill went up", the first question is which of those meters moved.

  • Does adding LIMIT 100 ever reduce the bytes billed?
    As a rule, no — the limit is applied after the data is read, so the bill is identical. The exception people cite is a clustered table, where block-level pruning can let the engine stop early and read fewer blocks. Never treat `LIMIT` as a cost control; project fewer columns and filter on the partitioning column instead.
  • Why do total_bytes_processed and total_bytes_billed differ for the same job?
    Billed bytes apply the per-query and per-table minimums, so tiny queries bill more than they processed. And when a query is served from the result cache, billed bytes are zero while the processed figure still reflects what the query would have read. Both are exposed per job in `INFORMATION_SCHEMA.JOBS`.
  • A table is heavily compressed. Why doesn't that make queries cheaper?
    Bytes processed are computed from BigQuery's logical data-type sizes — eight bytes for an `INT64`, two bytes plus the UTF-8 length for a `STRING` — not from the compressed bytes on disk. Compression reduces what you pay to store the table; query cost tracks the logical volume of the columns read.

It is metered like a warehouse pick fee: you are charged for every aisle the picker walks down, not for how many items end up in the box.

saying these in an interview costs you the question

  • Says LIMIT 10 makes an expensive query cheap
  • Thinks on-disk compression lowers bytes billed
  • Believes any WHERE clause reduces the scan
  • Claims BigQuery bills per row returned
  • Assumes SELECT * is harmless because storage is cheap

context

resolving links…