BigQuery Query Cache Invalidation in dbt: Why Your Cache Never Hits

BigQuery Query Cache Invalidation in dbt: Why Your Cache Never Hits

BigQuery's query result cache is one of the most powerful free cost-saving mechanisms in the platform — but in practice, most dbt projects never benefit from it. If your BigQuery bill keeps climbing even after you've cleaned up your SELECT * patterns and added partitioning, cache invalidation is almost certainly the silent culprit.

BigQuery offers a powerful built-in cost-saver: it caches query results for 24 hours at zero cost. If a query is repeated within this window and the underlying data remains unchanged, BigQuery serves the result instantly without scanning a single byte. That means zero compute cost. On an on-demand project where the rate is $6.25 per TiB, the savings potential is real and compounding. But here is the thing nobody tells you when you first set up dbt: almost every common dbt pattern is a cache invalidation event waiting to happen.

This post explains exactly how BigQuery's cache works, which dbt patterns silently kill it, and what you can do right now to recover those savings.

---

How Does BigQuery Query Caching Actually Work?

BigQuery maintains a cache of query results for 24 hours. When you execute a query, BigQuery first checks if an identical query has been run recently. If it finds a match and the underlying data hasn't changed, it returns the cached result instantly — no bytes processed, no charges incurred.

The key word is identical. BigQuery does not fuzzy-match queries. The query string must be byte-for-byte the same. The referenced tables must not have been modified since the cached result was written. The query must not use functions that return non-deterministic results. And the user must be running under the same project billing configuration.

BigQuery's query cache is one of those features that can dramatically improve application performance — when you understand how it works. Unlike traditional database caching, BigQuery's approach is automatic, intelligent, and can save you both time and money. But if you don't know the rules, you'll miss out on these benefits entirely.

There are also two layers of caching worth understanding. There are two types of caches in BigQuery: a query cache and a table cache. The query cache stores the results of recently executed queries in memory, while the table cache stores the results of frequently accessed tables on disk. By using both caches, BigQuery can provide fast and efficient data access, resulting in improved query performance and reduced costs.

For Enterprise and Enterprise Plus projects, the story gets better. In BigQuery's Enterprise and Enterprise Plus editions, the Cross-user Result Cache speeds up query responses by allowing users with the right permissions to access cached results from other users within the same project. This is significant for BI tool environments where multiple analysts are hitting the same underlying models.

---

Why dbt Projects Silently Invalidate the Cache on Every Run

Here is where the gap between theory and delivery experience lives. In our work with early-stage fintech and e-commerce companies on BigQuery, we see the same cache-killing patterns repeated across projects — not because engineers are careless, but because dbt's defaults are optimised for correctness, not cache efficiency.

1. Materialised tables invalidate on every dbt run

The most common issue is straightforward: when a model is materialised as a table, every dbt run drops and recreates it. A recreated table is a modified table. A modified table breaks the cache entirely — even if the underlying source data did not change. If your BI dashboards query a model that refreshes on a schedule, and that model is a table, no cache hit will ever survive between runs.

This is a particularly expensive trap. Teams often materialise staging and intermediate models as tables for performance reasons — and then watch BigQuery re-scan identical result sets on every dashboard load because the table's metadata timestamp has changed. For more on the trade-offs in materialisation choices, it is worth reviewing how dbt models materialised as table in BigQuery create cost leaks — the interaction with cache behaviour compounds the problem significantly.

2. CURRENT_TIMESTAMP(), CURRENT_DATE(), and NOW() in models or queries

Any query that includes a non-deterministic function — CURRENT_TIMESTAMP(), CURRENT_DATE(), RAND(), NOW() — cannot be cached. BigQuery treats these as volatile by design. If you have a dashboard query that appends WHERE created_at >= CURRENT_DATE() - 7, that query will never hit the cache. Ever. It is a fresh scan every time.

This one is counterintuitive. Teams often add dynamic date filters to reduce data scanned — a reasonable optimisation in isolation. But it simultaneously disqualifies every query containing it from cache eligibility. The right approach is to push the date boundary into your dbt model logic, materialise the bounded dataset, and let downstream queries run against a stable, cacheable result.

3. Whitespace and comment changes in query strings

BigQuery's cache key includes the full, normalised query text. A single added space, a changed alias, or a reformatted CTE will produce a cache miss. This matters more than it sounds. If your BI tool generates dynamic SQL — as many do — small differences in generated query structure between sessions or users will prevent cache hits even when the logical intent is identical.

The fix here is to ensure that queries hitting BigQuery directly from your BI layer are as deterministic and stable as possible. Templated queries, named views, and well-governed semantic layer definitions are the right controls. This is one of the reasons we push clients toward a proper SQL semantic layer rather than ad-hoc BI tool SQL generation.

4. Incremental models that touch the target partition on every run

Incremental models are generally the right strategy for large, append-heavy tables. But if your incremental model's merge logic touches the same partition every run — for example, always re-processing "today's" partition — the target table is modified on every run, and any cached queries against it are invalidated. The fix is precise partition targeting: ensure your incremental logic only writes to partitions that genuinely have new data, and consider using is_incremental() filters that are tight enough to avoid touching stable partitions unnecessarily.

5. CTE patterns that defeat BigQuery's internal optimiser

BigQuery handles CTEs differently from most SQL engines. In some configurations, if a staging model feeds multiple downstream marts, recomputation can quietly increase BigQuery costs. Persisting these models as views allows BigQuery to reuse cached results and significantly reduce repeated scans. But if a CTE is defined and referenced multiple times within the same query, BigQuery may re-evaluate it each time — bypassing both CTE reuse and result cache mechanics. This interacts badly with complex mart models that reference the same intermediate transformations repeatedly. We have covered the CTE re-evaluation trap in detail in this post on BigQuery CTEs re-evaluated every time — it is worth reading alongside this one, because the cost impacts stack.

---

How to Audit Your Cache Hit Rate Right Now

Before you change anything, measure. BigQuery's INFORMATION_SCHEMA.JOBS view is your source of truth.

Run this query against your project:

SELECT
  cache_hit,
  COUNT(*) AS job_count,
  ROUND(SUM(total_bytes_processed) / POW(1024, 4), 4) AS total_tb_processed,
  ROUND(SUM(total_bytes_processed) / POW(1024, 4) * 6.25, 2) AS estimated_cost_usd
FROM
  `region-us`.INFORMATION_SCHEMA.JOBS
WHERE
  creation_time >= TIMESTAMP_SUB(CURRENT_TIMESTAMP(), INTERVAL 30 DAY)
  AND job_type = 'QUERY'
  AND state = 'DONE'
GROUP BY
  cache_hit
ORDER BY
  cache_hit DESC;

This gives you a direct breakdown of cached vs. non-cached jobs over the last 30 days, with associated bytes processed and estimated cost for the non-cached workload. If your cache_hit = FALSE row is dominating, you have a fixable problem.

The BigQuery console provides detailed execution details for every query through the job history interface. Users can view processing time, data scanned, and associated costs. Query costs appear immediately after execution completion. The job history view is a fast way to spot individual queries with unexpectedly high byte counts — these are prime candidates for cache analysis.

A pattern we see repeatedly: a team running a daily dbt pipeline plus a BI tool with hourly dashboard refreshes. The dbt pipeline invalidates the cache on models each morning. Every dashboard refresh between 9am and 5pm — across six or eight analysts — re-scans the same data. That is potentially dozens of full table scans per day on data that has not changed. The fix — aligning refresh schedules, using views for stable intermediate layers, and eliminating non-deterministic functions from BI queries — consistently recovers 30–60% of the wasted compute in our experience.

If you are looking to implement a structured approach to BigQuery cost governance in your organisation, explore how Fintel Analytics approaches this — we work with growth-stage businesses globally to design and deliver exactly this kind of optimisation, from query audit through to production.

---

What to Fix First: A Prioritised Action Plan

Not all cache fixes are equal. Here is how we prioritise in a real client engagement:

Priority 1 — Eliminate non-deterministic functions from BI queries

This is the highest-leverage, lowest-effort change. Audit every dashboard query in your BI tool for CURRENT_DATE(), CURRENT_TIMESTAMP(), and NOW(). Where the date logic belongs in dbt, move it there. Create a stable, materialised model with the bounded date range baked in. This single change can turn a 0% cache hit rate into a 60–70% rate for time-bounded dashboard queries.

Priority 2 — Review materialisation strategy for stable models

If a staging or intermediate model does not change between runs — because its source data is append-only and you are running incremental loads — materialise it as a view, not a table. Views do not have a modification timestamp in the same way; the cache key is resolved to the underlying table at query time. If the underlying table hasn't changed, the cache is intact.

For models that genuinely need to be tables for performance — large aggregations, heavily joined marts — consider whether they need to run on every dbt execution or only when the source data has actually changed. Conditional execution (dbt run --select with freshness checks) is underused.

Priority 3 — Stabilise BI-layer query generation

If your BI tool generates ad-hoc SQL, the query string variation between users and sessions may be killing cache hits even for logically identical requests. Moving toward a governed semantic layer — whether through dbt's Semantic Layer, Holistics, or a similar tool — standardises the SQL generated against BigQuery and dramatically increases the proportion of queries that can be served from cache. The dbt Semantic Layer introduces declarative caching, allowing you to cache common queries to speed up performance and reduce query compute costs. This is exactly the kind of structural fix that compounds over time as your team scales.

Priority 4 — Align dbt run schedules with cache windows

Align your dashboard refresh intervals and automated pipeline triggers to sync with data update cycles. If your data only changes once a day, refreshing a dashboard every hour is an avoidable expense — unless you lean on the cache. The practical implication: if your dbt pipeline runs at 6am and produces stable outputs, your dashboards can safely cache query results for the rest of the business day. Do not run dbt again mid-day unless data has actually changed. Every unnecessary run invalidates the cache for every downstream consumer.

Priority 5 — Use materialized views for high-traffic aggregations

Materialized views can dramatically reduce compute by serving precomputed results instead of re-scanning raw data. For aggregations that are queried repeatedly throughout the day — daily transaction summaries, rolling 7-day metrics, live GMV totals — a BigQuery materialized view auto-refreshes when the underlying data changes and serves subsequent queries from the precomputed result. This is a different layer of caching from the query result cache and works even when the underlying data is changing frequently.

---

Real-World Outcome: What Fixing Cache Invalidation Actually Delivers

The clearest way to illustrate the value is with a real client pattern. We worked with a Series A fintech running a reporting stack on BigQuery: a nightly dbt pipeline producing roughly 40 models, plus a BI tool with six analysts and a set of executive dashboards refreshing every 30 minutes throughout the business day.

When we audited the INFORMATION_SCHEMA.JOBS data for their project, cache hits accounted for under 8% of all query jobs. The primary causes were: CURRENT_DATE() in BI-layer queries, tables being recreated nightly (invalidating cache for all downstream BI queries), and an intermediate mart model materialised as a table that was referenced by 12 downstream dashboard queries — re-scanned on every refresh.

After four changes — moving date boundaries into dbt models, converting three intermediate models from table to view materialisation, stabilising the BI tool's query templates, and aligning the dbt run schedule to avoid mid-day invalidation — cache hit rate rose from 8% to 61% within two weeks. The estimated monthly saving was significant relative to their prior bill, achieved with no infrastructure changes and no new tools.

This is consistent with a broader principle: organisations using BigQuery and dbt together are seeing real, measurable cost savings and performance gains — but only when the stack is configured with production realities in mind, not just development defaults.

---

Frequently Asked Questions

Q: Why does BigQuery query cache not work with dbt?

A: The most common reasons are that dbt materialises models as tables (which are "modified" on every run, invalidating the cache), BI-layer queries contain non-deterministic functions like CURRENT_DATE(), or query strings vary slightly between executions. Each of these prevents BigQuery from matching the query to a cached result. Fixing them requires a combination of materialisation strategy changes, query standardisation, and schedule alignment.

Q: Does BigQuery query cache cost anything?

A: No. BigQuery query result caching is completely free. When a cached result is returned, zero bytes are processed and zero compute cost is incurred. This makes it one of the highest-value optimisations available — the savings are pure, with no additional infrastructure or spend required.

Q: How long does BigQuery cache query results?

A: BigQuery caches query results for 24 hours by default. If the underlying table data changes within that window, the cached result is invalidated immediately. If the query contains non-deterministic functions, no caching occurs at all, regardless of the 24-hour window.

Q: How can I check my BigQuery cache hit rate?

A: Query the INFORMATION_SCHEMA.JOBS view in your project, grouping by cache_hit and summing total_bytes_processed. This gives you a direct breakdown of cached versus non-cached work and lets you calculate the estimated cost of cache misses. The BigQuery console's job history UI also shows cache status per query execution.

Q: Will using dbt incremental models improve BigQuery cache hit rates?

A: Potentially, yes — but only if the incremental model targets narrow partitions and does not modify stable partitions on every run. If your incremental model writes to a partition that downstream BI queries reference, and it writes every run regardless of new data, the cache is still invalidated. Tight partition filters in your incremental logic are essential to preserving cache eligibility for unchanged data ranges.

---

BigQuery's query result cache is not a magic feature — it is a precision instrument that only works when your dbt project and BI layer are designed with its constraints in mind. Most teams leave substantial money on the table because the defaults work against cache efficiency, and the feedback loop is invisible until you go looking for it. At Fintel Analytics, we have helped fintech, payments, and e-commerce businesses audit and restructure exactly this kind of problem — from INFORMATION_SCHEMA analysis through to materialisation strategy and BI layer standardisation — and the savings consistently justify the investment many times over. If your BigQuery bill is growing faster than your data volume, cache invalidation is almost certainly part of the answer.