BigQuery query cache

BigQuery keeps each query result for about 24 hours and returns it for free when the same query runs again. What must match, which functions and tables turn caching off, and how to check whether a job hit the cache.

When you run a query, BigQuery stores the result in a hidden temporary table for about 24 hours. If the same query runs again and nothing it reads has changed, BigQuery returns the stored result. The repeat is free (it bills 0 bytes) and finishes quickly. The cache is on by default. Whether other users share your cached results depends on the pricing model, as described below.

“The same query” is strict. Many queries that look repeatable never hit the cache.

What a cache hit looks like

We ran SELECT word FROM bigquery-public-data.samples.shakespeare LIMIT 3 twice in a project on on-demand pricing and looked at the job statistics:

The first run read 1,332,943 bytes and was billed 10 MiB, which is the per-table minimum from How a query is billed. The second returned the stored result and cost nothing.

To check any job yourself, look at statistics.query.cacheHit in the job, or the cache_hit column of INFORMATION_SCHEMA.JOBS:

SELECT cache_hit, COUNT(*) AS jobs, SUM(total_bytes_billed) AS bytes_billed
FROM `region-us`.INFORMATION_SCHEMA.JOBS_BY_PROJECT
WHERE creation_time >= TIMESTAMP_SUB(CURRENT_TIMESTAMP(), INTERVAL 1 DAY)
  AND job_type = 'QUERY'
GROUP BY cache_hit;

In Querylab.io the status bar says cached next to the bytes after a cache hit, the Info view of the results grid lists cache hit for the job (results grid), and the execution plan summary shows Cache when the result came from BigQuery’s cache (execution plan). Query history can filter on cache.

The query text has to match exactly

BigQuery compares the text of the query, character by character. These each miss the cache after the original has run:

-- Original
SELECT word FROM `bigquery-public-data.samples.shakespeare` LIMIT 3

-- Lowercase keywords
select word from `bigquery-public-data.samples.shakespeare` limit 3

-- Two spaces after SELECT
SELECT  word FROM `bigquery-public-data.samples.shakespeare` LIMIT 3

-- A trailing comment
SELECT word FROM `bigquery-public-data.samples.shakespeare` LIMIT 3 -- again

We ran each variant right after the original had been cached. All three were cache misses and were billed in full, and each one was then cached under its own text. Google’s docs list whitespace and comments as breaking the match.

This matters for dashboards and tools that build SQL. If a BI tool adds a timestamp comment, a request ID or a different alias to each query, it never gets a cache hit. Generate the same text for the same request.

Functions that turn caching off

A query is not cached if it calls a function that returns a different value each time. We ran each of these twice and none hit the cache:

SELECT CURRENT_DATE() AS d;
SELECT CURRENT_DATE('UTC') AS d;
SELECT CURRENT_TIMESTAMP() AS ts;
SELECT RAND() AS r;
SELECT GENERATE_UUID() AS id;
SELECT SESSION_USER() AS who;

Giving CURRENT_DATE an explicit time zone doesn’t help. By contrast, a query with only constants, like SELECT 41 + 1 AS x, was cached on the second run even though it reads no table.

The usual trap is a dashboard filter written as WHERE order_date = CURRENT_DATE(). Every run is a cache miss, all day. If the result can be reused, compute the date in your tool and put it in the query as a literal.

Other things that turn caching off

Google’s docs list these cases. We tested the destination table, changed table and wildcard ones.

• A destination table is set on the job. We ran a cached query with --destination_table. It re-ran and was billed in full. A later plain run of the same query hit the cache again.
• A referenced table changed. We queried a table, inserted a row, and queried again: the second run missed the cache, and the one after it hit.
• A wildcard table in the query, such as gsod19*. We ran one twice with a _TABLE_SUFFIX filter and neither hit the cache.
• Streaming inserts. A table that recently received streaming inserts is not cached, even if no new rows have arrived since.
• External data sources other than Cloud Storage. Queries over Cloud Storage can be cached.
• Row-level security. Results over a table protected by row-level security are not cached. With column-level security or data masking they might not be.
• Expiry. The usual lifetime is 24 hours, and the cache is best effort, so a result can disappear sooner.

Views count as part of the query: if a logical view the query reads has changed, the cache is invalid.

Who can use a cached result

Each user’s results are stored in a private, hidden dataset (an anonymous dataset, with a name that starts with an underscore). By default, only the user who ran a query can get its cached result.

Cross-user caching is available on the Enterprise and Enterprise Plus editions. There, if someone else in the project ran the same query and you have permission to run it, you get their cached result, copied into your own anonymous dataset for 24 hours. On-demand and Standard use single-user caching. On those, if twelve people open the same dashboard, each of them pays for their own first run.

You can’t share a cached result or write to one with DML. Google also advises against using cached result tables as input to other jobs. To keep a result, write it to a named table.

Turning it off on purpose

The cache is a problem when you are measuring performance, because the second run is fast for the wrong reason. In Querylab.io, click cached in the status bar and choose Run again without cache for a one-off rerun, or put -- querylab:cache=off in the tab (the “Use cached results” checkbox in the status bar’s ⚙ popover writes that line for you) to keep the cache off for every run of that tab. In the console, clear “Use cached results” under query settings. With bq, use --nouse_cache. In the API, set useQueryCache to false in the job configuration.

A run with the cache off recomputes the result and bills you for it. It also replaces the stored result. When we ran a query with --nouse_cache and then ran it normally, the normal run hit the cache and billed 0.

A dry run ignores the cache

A dry run reports what the query would read if it ran. It does not tell you whether the real run will be served from the cache. We ran a dry run of a query whose result was already cached, and it still reported the full number of bytes:

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

The real run of that query billed 0 bytes. Treat the dry-run figure as the most the query can cost.

Billing and quotas

A cache hit is not billed, and cached results sit in temporary tables that cost nothing to store. Queries served from the cache still count toward quotas.