How BigQuery bills a query

On-demand BigQuery charges for the bytes in the columns a query reads, not for rows returned or time spent. LIMIT doesn't lower the estimate and only lowers the bill on a clustered table, but selecting fewer columns always does.

With on-demand pricing, BigQuery charges for the bytes stored in the columns your query reads. The price is $6.25 per TiB in the US multi-region, and the first 1 TiB each month is free. The number of rows you get back, how long the query runs and how complex the SQL is don’t change the bill. Only the columns matter.

The other pricing model, capacity pricing, charges for slots over time instead. It is covered in On-demand vs capacity pricing. Everything below is about on-demand.

BigQuery stores columns, not rows

A BigQuery table is stored column by column. To run a query, BigQuery opens only the columns the query mentions and reads each of them from top to bottom. The bill is the total size of those columns.

bigquery-public-data.samples.shakespeare has four columns: word, word_count, corpus and corpus_date. Reading all of them costs 6.1 MB:

SELECT *
FROM `bigquery-public-data.samples.shakespeare`

Reading only word costs 1.3 MB, about a fifth of that:

SELECT word
FROM `bigquery-public-data.samples.shakespeare`

The estimate is a dry run: BigQuery plans the query and reports its size without running it or charging for it. The BigQuery console shows the same number before you press Run, and so does Querylab.io.

What doesn’t reduce the bytes

LIMIT

LIMIT 10 returns ten rows, but on a table that isn’t clustered BigQuery still reads each column in full first. The estimate is identical to the query without it:

SELECT *
FROM `bigquery-public-data.samples.shakespeare`
LIMIT 10

A clustered table is the exception. Its rows are stored in blocks sorted by the clustering columns, so a query with no ORDER BY, aggregation or join can stop reading once it has enough rows. You are billed only for the blocks it read. The dry run still shows the full size, because the saving happens while the query runs. On a clustered table in Clustering, LIMIT 10 had a 363 MB estimate and the job read 196 MB.

To look at a few rows for free, open the table’s Preview tab in Querylab.io (see Table information) or in the BigQuery console. A preview reads the stored data through the table data API and is not billed.

WHERE on an ordinary column

A filter doesn’t skip data in a table that isn’t partitioned or clustered. BigQuery has to read the column to evaluate the filter, so the filter column is added to the bill:

SELECT word
FROM `bigquery-public-data.samples.shakespeare`
WHERE corpus = 'hamlet'

That is the same 3.6 MB as reading word and corpus with no filter. A filter only cuts bytes on a partitioned or clustered table, when it is on the partitioning column or the clustering columns.

What is free or cheaper than it looks

COUNT(*)

COUNT(*) with no filter reads no columns. BigQuery answers it from table metadata:

SELECT COUNT(*)
FROM `bigquery-public-data.samples.shakespeare`

The 10 MB minimum below still applies when the query actually runs.

Reading a table twice

A column is billed once per query, however many times the query reads it. This self-join reads word and corpus from two aliases of the same table and costs exactly as much as reading those two columns once:

SELECT a.word, b.corpus
FROM `bigquery-public-data.samples.shakespeare` AS a
JOIN `bigquery-public-data.samples.shakespeare` AS b USING (word)

The same applies to a CTE used several times: you pay for the columns it reads, once.

Cached results

BigQuery keeps the result of each query for about 24 hours. Running the exact same query text again returns the cached result and costs nothing, unless the underlying tables changed or the query uses a non-deterministic function like CURRENT_TIMESTAMP(). See Query cache.

Queries that fail

A query that returns an error is not billed.

The 10 MB minimum

Billed bytes are rounded up to the next MB, with a minimum of 10 MB for each table the query references. So a query against a 1 KB table is billed as 10 MB, and a join of three small tables as 30 MB. At $6.25 per TiB that is a fraction of a cent, but it explains why the billed number on a job can be larger than its dry-run estimate. Querylab.io’s Query history shows the processed and billed bytes of each job in separate columns.

Columns have fixed sizes

The size of a column is the sum of its values, using these sizes per value:

A STRING column of long text is often the most expensive column in a table. Leaving it out of SELECT * is the quickest saving.

Nested fields are separate columns

Each field inside a STRUCT is stored as its own column. Selecting address.city reads only the city field, not the whole address struct. The same holds for fields of a repeated struct that you read through UNNEST. See Nested and repeated data.

Keeping the bill under control

• Name the columns you need instead of SELECT *. SELECT * EXCEPT (big_text_column) works when you need almost everything.
• Partition large tables by date and filter on the partition column. Set require_partition_filter so nobody can forget the filter.
• Set maximum bytes billed on a query. A query that would read more fails before it starts, and failed queries are free.
• Set custom quotas to cap bytes per user or per project per day.

In Querylab.io the estimate updates as you type, and cost limits warn you before a query over your limit runs. See Cost limits.