Non-Deterministic Functions in dbt BigQuery: Why Your Cache Never Hits

Non-Deterministic Functions in dbt BigQuery Models Are Silently Busting Your Cache

If your dbt models contain CURRENT_TIMESTAMP(), RAND(), GENERATE_UUID(), or SESSION_USER(), BigQuery will never serve those results from cache — not for that model, and not for any downstream model that inherits its output. Every run re-scans the full underlying tables, every time, regardless of whether the data changed. The fix is surgical and takes under an hour once you know which models are affected.

This is one of the least-discussed cost anti-patterns in dbt projects built on BigQuery, and it is surprisingly common. A project that looks well-optimised on the surface — partitioned tables, incremental models, selective column projection — can still haemorrhage unnecessary bytes scanned simply because one staging model drops CURRENT_TIMESTAMP() into an inserted_at audit column. Every model downstream of that staging layer pays the price on every single run, and on every dashboard refresh that queries those models directly.

How BigQuery's Query Cache Works — and What Breaks It

BigQuery maintains a query results cache keyed on the exact query text plus the state of the underlying tables. 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. When a cache hit occurs, BigQuery returns results instantly at zero bytes billed. This is genuinely powerful for BI tools like Looker or Holistics that repeatedly run the same queries as multiple users refresh dashboards during the business day.

The cache is invalidated whenever BigQuery cannot guarantee the result would be identical. Non-deterministic functions are a hard blocker: because CURRENT_TIMESTAMP() returns a different value every millisecond, BigQuery correctly concludes that the result of any query containing it cannot be safely reused. Inefficient result caching is one of the primary cost drivers in BigQuery workloads, alongside repeated full-table scans and poor partitioning that does not match filter patterns.

The functions that bust your cache in BigQuery are:

• CURRENT_TIMESTAMP() / CURRENT_DATE() / CURRENT_TIME()
• NOW()
• RAND()
• GENERATE_UUID()
• SESSION_USER()
• Any call to a remote function or external UDF

The insidious part is propagation. If stg_payments contains CURRENT_TIMESTAMP(), then int_payment_events, fct_transactions, and every mart built on top of it also gets no cache benefit — even if those downstream models contain zero non-deterministic logic themselves. The cache miss cascades silently through your entire DAG.

---

📺 Watch: AtScale + Snowflake Demo

---

How to Find Every Offending dbt Model in Under Five Minutes

You need two things: the SQL text of your compiled dbt models, and the job history from BigQuery's INFORMATION_SCHEMA. Run the following against your BigQuery region's INFORMATION_SCHEMA.JOBS view to identify queries that were cache-eligible but never hit — and that contain the tell-tale function signatures:

SELECT
  job_id,
  user_email,
  ROUND(total_bytes_billed / POW(1024, 3), 2)  AS gb_billed,
  ROUND((total_bytes_billed / POW(1024, 4)) * 6.25, 4) AS estimated_cost_usd,
  cache_hit,
  REGEXP_CONTAINS(
    query,
    r'(?i)(CURRENT_TIMESTAMP|CURRENT_DATE|CURRENT_TIME|\bNOW\b|\bRAND\b|GENERATE_UUID|SESSION_USER)'
  ) AS has_non_deterministic_fn,
  LEFT(query, 300) AS query_preview
FROM
  `region-eu.INFORMATION_SCHEMA.JOBS`
WHERE
  DATE(creation_time) >= DATE_SUB(CURRENT_DATE(), INTERVAL 30 DAY)
  AND job_type = 'QUERY'
  AND statement_type = 'SELECT'
  AND cache_hit IS FALSE
  AND error_result IS NULL
HAVING
  has_non_deterministic_fn IS TRUE
ORDER BY
  total_bytes_billed DESC
LIMIT 50;

Replace region-eu with your actual BigQuery region (e.g. region-us, region-europe-west2). This query surfaces the fifty most expensive cache-busting queries in the last 30 days. Cross-reference the query_preview column against your compiled dbt model SQL to identify which models are responsible.

To scan your dbt project's source SQL directly — without touching the warehouse at all — fintel-scan is a free, open-source MIT-licensed CLI that runs this check and fourteen others locally: uvx fintel-scan. It is the fastest way to audit an entire project before a production deploy.

For a deeper look at which models are scanning the same tables repeatedly and compounding the cost problem, see our post on dbt Models Scanning the Same BigQuery Table Twice: Find & Fix It.

The Three Patterns We See in Real Projects

After auditing dbt projects across fintech, payments, and e-commerce businesses, the non-deterministic cache-bust problem shows up in three distinct patterns:

Pattern 1: The audit column in a staging model. The most common offender. A developer adds CURRENT_TIMESTAMP() AS dbt_loaded_at to a staging model to track when the row was processed. Sensible intent, expensive consequence. Every downstream model now forfeits its cache.

Pattern 2: The freshness flag in a mart. A mart model adds a column like CURRENT_DATE() AS report_date or IF(event_ts >= DATE_SUB(CURRENT_DATE(), INTERVAL 7 DAY), TRUE, FALSE) AS is_recent. This is almost always unnecessary — the filter logic belongs in the BI layer or in a dbt variable, not baked into the model SQL.

Pattern 3: The UUID surrogate key in a model that should use a natural key. GENERATE_UUID() produces a different value on every run, which also means the model cannot be used as an incremental target reliably. In addition to busting the cache, this pattern causes fan-out and duplicate rows the moment someone runs dbt run --full-refresh without realising the UUIDs will change.

In a Series A payments company we worked with, a single CURRENT_TIMESTAMP() in a high-fan-out staging model was contributing to avoidable scans across eleven downstream models. The staging model itself was only 8 GB — but because it fed directly into three mart-layer tables averaging 200 GB each, the compounded cost of cache misses across all BI tool queries was significant, and invisible until we ran the diagnostic above.

How to Fix It Without Breaking Downstream Models

The fix follows a simple decision tree:

If the column tracks pipeline load time: Move it to a dbt post-hook or capture it as a snapshot column rather than embedding it in the model SQL. Alternatively, use {{ run_started_at }} — a dbt Jinja variable that resolves to the pipeline run's start timestamp at compile time, producing a deterministic string constant rather than a function call. This preserves the audit intent without touching the cache.

-- BEFORE: cache-busting
SELECT
  payment_id,
  amount,
  CURRENT_TIMESTAMP() AS loaded_at
FROM {{ source('payments', 'raw_transactions') }}

-- AFTER: cache-friendly
SELECT
  payment_id,
  amount,
  TIMESTAMP('{{ run_started_at }}') AS loaded_at
FROM {{ source('payments', 'raw_transactions') }}

If the column is a "days ago" flag or freshness indicator: Remove it from the model entirely and push the filter into the BI tool query or a Holistics/Looker calculated field. The model should return stable facts; the recency interpretation belongs to the presentation layer.

If RAND() is being used for sampling during development: Gate it behind a dbt variable so it only executes in development targets:

{% if target.name == 'dev' %}
  WHERE RAND() < 0.01
{% endif %}

If GENERATE_UUID() is being used as a surrogate key: Stop. Derive a deterministic surrogate from the natural key using TO_HEX(MD5(CONCAT(col1, '-', col2))). Same stable output every run, no cache bust, no duplicate key problem on full-refresh.

Most minor SQL optimisations are insignificant compared to choosing the correct materialisation strategy — and every downstream query recomputes the full logic when non-deterministic functions are present, which is extremely dangerous over complex joins or massive datasets. Fixing this at the staging layer is always higher leverage than optimising individual mart queries.

How to Stop It Recurring: Enforcement in CI

Detection is a one-time fix. Prevention is a CI gate. Add the following as a dbt singular test (save it as tests/assert_no_non_deterministic_functions.sql). It will fail the CI pipeline if any model's compiled SQL contains the banned functions:

-- This test scans compiled SQL in the dbt manifest.
-- Run it as: dbt test --select assert_no_non_deterministic_functions
-- Returns rows only when a violation is found (test fails on any row returned).

WITH compiled_models AS (
  SELECT
    node_id,
    compiled_code
  FROM {{ graph.nodes.values() | selectattr('resource_type', 'equalto', 'model') | list }}
)
SELECT
  node_id AS offending_model
FROM compiled_models
WHERE
  REGEXP_CONTAINS(
    compiled_code,
    r'(?i)(CURRENT_TIMESTAMP|CURRENT_DATE|CURRENT_TIME|\bNOW\b|\bRAND\b|GENERATE_UUID|SESSION_USER)'
  )

Note: Because dbt singular tests operate on compiled SQL via the manifest rather than live warehouse data, you can also parse target/manifest.json in a pre-commit hook using a lightweight Python script — which avoids any warehouse connection cost for the check entirely.

For broader BigQuery cost governance across your dbt project, the INFORMATION_SCHEMA.JOBS view is your most powerful diagnostic tool. Our post on BigQuery INFORMATION_SCHEMA Cost Attribution: Stop the Guessing walks through the full attribution query set.

Frequently Asked Questions

Q: Does BigQuery's query cache work for dbt models materialised as tables?

A: Yes, but the cache applies to the queries that read from those tables, not the dbt run that builds them. If a model materialised as a table is queried by your BI tool and the query contains non-deterministic functions, the BI tool's query will still miss the cache. The bigger risk is non-deterministic functions in models materialised as views — because views execute their SQL on every read, so the cache miss hits every downstream consumer.

Q: Does using {{ run_started_at }} instead of CURRENT_TIMESTAMP() actually fix the cache?

A: Yes. {{ run_started_at }} is resolved by dbt at compile time into a static string literal (e.g. '2026-09-25T08:00:00+00:00'). BigQuery sees a constant, not a function call, so the query is treated as deterministic and cache-eligible. The audit timestamp will still reflect when the pipeline ran — it simply won't change between identical reruns within the same dbt invocation.

Q: Can I whitelist certain non-deterministic functions and only block the expensive ones?

A: Yes. The REGEXP pattern in both the diagnostic SQL and the CI test is fully configurable. In practice, CURRENT_TIMESTAMP() and CURRENT_DATE() are the highest-volume offenders because they appear in audit columns across multiple models. RAND() and GENERATE_UUID() are less frequent but higher severity because they also break incremental model idempotency.

Q: Does this problem affect BigQuery slot reservations (flat-rate pricing) as well as on-demand?

A: On capacity pricing you are not billed per byte, but you are still consuming slots, which means slower queries and more contention across your team. Cache misses under slot reservations mean your team exhausts its slot quota faster, increasing query queue times and degrading dashboard load performance — even if the dollar cost per byte does not appear on your bill line-by-line.

Q: How do I find which dbt models are downstream of an offending model so I know the blast radius?

A: Run dbt ls --select +<offending_model_name> to list all models that depend on it. For a visual view, dbt docs generate && dbt docs serve renders the full DAG. In a large project, the blast radius of a single cache-busting staging model is often 10–20 downstream models — every one of which pays the re-scan cost on every pipeline run and every BI dashboard load.

If your team is watching its BigQuery bill grow month-on-month without a clear explanation, non-deterministic functions sitting quietly in staging models are one of the first places we look — because they are cheap to introduce, invisible in normal monitoring, and expensive to leave in place. At Fintel Analytics, we have found and fixed this pattern in dbt projects across payments, e-commerce, and financial services businesses, typically recovering meaningful scan cost within the same sprint the fix is deployed. If your pipeline costs are climbing and your cache hit rate is lower than you expect, that is a solvable problem — and the diagnostic takes five minutes to run.