BigQuery CTEs Re-evaluated Every Time: The dbt Cost You're Missing

BigQuery re-evaluates a CTE every time it is referenced within the same query — it does not cache or materialise the result between uses. In dbt models where a single CTE is fanned out into multiple downstream references, this behaviour silently multiplies the bytes scanned and inflates your bill in a way that is nearly invisible until you dig into INFORMATION_SCHEMA. If your BigQuery costs are climbing and your dbt models look clean on the surface, CTE re-evaluation is one of the first places to look.

This is one of those problems that trips up nearly every data engineering team at some point. The SQL reads well, the logic is correct, the tests pass — and yet a model that ought to scan 50 GB is scanning 200 GB on every run. The root cause is a misunderstanding of how BigQuery actually executes CTEs, and it is compounded by the way dbt encourages composing logic through layered WITH blocks.

How Does BigQuery Handle CTE Execution?

In most SQL dialects, engineers assume a CTE is computed once and then referenced cheaply — like a named view pinned in memory for the duration of the query. In BigQuery, that assumption is wrong.

BigQuery treats a CTE as a logical alias, not a materialised intermediate result. Every time your SQL references a CTE by name, BigQuery re-executes the full subquery behind it. There is no intra-query caching of CTE results. The bytes scanned are multiplied by the number of times the CTE appears downstream.

So if you have a staging CTE that filters and cleans a 10 GB source table, and you reference that CTE three times in the same model — for different join paths, aggregations, or UNION branches — BigQuery scans 30 GB, not 10 GB. At the current on-demand rate of $6.25 per TiB, that difference compounds quickly at scale.

This is not a bug in BigQuery. It is documented behaviour. But it is deeply counterintuitive if your mental model comes from SQL Server, Postgres, or Snowflake — all of which handle CTEs differently under certain conditions.

---

📺 Watch: SQL indexing best practices | How to make your database FASTER!

---

Why Does This Become Expensive Inside dbt Models?

dbt's design philosophy encourages composing transformations through chained CTEs within a single model file. The canonical dbt model pattern looks something like this:

with source as (
    select * from {{ ref('stg_payments') }}
),
cleaned as (
    select
        payment_id,
        amount,
        status,
        created_at
    from source
    where status is not null
),
enriched as (
    select c.*, r.region
    from cleaned c
    left join {{ ref('dim_regions') }} r on c.region_id = r.id
),
flagged as (
    select *, amount > 10000 as is_high_value
    from cleaned
)
select
    e.*,
    f.is_high_value
from enriched e
left join flagged f on e.payment_id = f.payment_id

Notice that cleaned is referenced twice — once in enriched, once in flagged. In BigQuery, the filter-and-select logic inside cleaned executes twice. If the underlying stg_payments table is large, that duplication is real and paid for.

Now scale this to a production dbt project with 80 or 100 models, some of which have been extended by multiple team members over time, each adding references to shared staging CTEs. The cost accumulates invisibly.

A pattern we see repeatedly at Fintel Analytics when auditing dbt projects is exactly this: a handful of "heavy" staging CTEs — typically ones that join or filter large raw event tables — referenced multiple times in the same model or across a chain of CTEs that themselves reference each other. One model might look innocent in isolation, but its execution plan tells a different story.

How Do You Find Multi-Referenced CTEs That Are Costing You Money?

The first step is confirming that CTE re-evaluation is actually happening in your environment. The most reliable way is to query INFORMATION_SCHEMA.JOBS and look for queries where bytes billed is disproportionately high relative to the apparent size of the tables involved.

Run this against your project:

SELECT
  job_id,
  query,
  total_bytes_billed,
  total_bytes_processed,
  ROUND(total_bytes_billed / POW(1024, 4) * 6.25, 4) AS estimated_cost_usd,
  creation_time
FROM `region-eu.INFORMATION_SCHEMA.JOBS`
WHERE creation_time >= TIMESTAMP_SUB(CURRENT_TIMESTAMP(), INTERVAL 7 DAY)
  AND job_type = 'QUERY'
  AND statement_type = 'SELECT'
ORDER BY total_bytes_billed DESC
LIMIT 50

For each expensive model in those results, open the query text and count how many times each CTE name appears after the closing parenthesis of its definition. Any CTE referenced more than once is a candidate for refactoring.

You can also use the BigQuery query plan explorer in the console — the execution plan will show repeated scan stages for the same table if a CTE is being re-evaluated. Look for duplicate "READ" nodes pointing at the same source.

In our work with early-stage companies, a single model with two or three multi-referenced CTEs scanning a large transactions table has been enough to double the per-run cost of an entire dbt job. When that job runs hourly on a scheduled basis, the monthly bill impact is significant.

If you are building or auditing a dbt project and want a second pair of eyes on your query patterns, explore how Fintel Analytics approaches data engineering for growth-stage companies — this kind of audit is something we do as part of every new client engagement.

How Do You Fix Multi-Referenced CTEs in BigQuery?

Once you have identified the offending CTEs, the fix depends on the context.

Option 1: Replace with a temporary table

For CTEs that are referenced two or more times within the same model or script, materialise them as a CREATE TEMP TABLE statement before the main query. Temp tables are written to disk once and read cheaply on subsequent references.

CREATE TEMP TABLE _SESSION.cleaned_payments AS
SELECT
    payment_id,
    amount,
    status,
    created_at
FROM {{ ref('stg_payments') }}
WHERE status IS NOT NULL;

-- Now reference _SESSION.cleaned_payments as many times as needed
-- BigQuery reads the materialised result, not the source, each time

Note the _SESSION. prefix — without it, BigQuery may count the temp table against your storage quota for up to 24 hours after the session ends.

Option 2: Refactor into a dbt staging model

If the logic inside the CTE is genuinely reusable across multiple downstream models, it belongs as its own dbt staging or intermediate model. dbt's materialisation as table or incremental means the result is written once and referenced cheaply by all downstream models. This is the cleaner, more maintainable solution.

The trade-off: adding a dbt model increases your storage footprint slightly. At BigQuery prices, this is almost always worth it — storage is cheap relative to repeated scan costs. You can read more about when to materialise intermediate results in "dbt Models Materialised as Table in BigQuery: Fix the Cost Leak".

Option 3: Restructure the model logic

Sometimes multi-referenced CTEs are a symptom of a model trying to do too much in one place. If you have a final model that branches a cleaned CTE into three different enrichment paths and then re-joins them, ask whether the branching logic can be reorganised linearly so each CTE feeds into the next without backtracking.

This will not always be possible — particularly in reconciliation or flagging logic where you genuinely need to evaluate the same base dataset under different conditions. In those cases, the temp table approach is your best tool.

What Else Interacts With This Problem?

CTE re-evaluation rarely exists in isolation. In the dbt projects we audit, it typically co-occurs with a cluster of related patterns:

No partition pruning on the source table. If the CTE scans a raw event table without a partition filter, BigQuery reads the entire table on every re-evaluation. A CTE referenced three times against a 100 GB unpartitioned table scans 300 GB per run. Combine this with an hourly job and the daily cost becomes material very quickly.

CTEs used as fan-out bases for large joins. A CTE that aggregates a wide table and is then joined in multiple branches multiplies the aggregation work. We covered the related fan-out join pattern in detail in "dbt Fan-Out Joins in BigQuery: Detect & Fix Inflated Metrics" — the two problems often appear together.

Volatile functions defeating the query cache. BigQuery caches identical query results for 24 hours at zero cost. But if your CTE includes CURRENT_TIMESTAMP(), CURRENT_DATE(), RAND(), or similar non-deterministic functions, the entire query becomes cache-ineligible — every run is a full scan regardless of whether the underlying data has changed. If your model refreshes hourly and the source data only changes daily, you may be paying for 24 full scans when one would do.

High-frequency scheduled queries amplifying the cost. A model that costs $0.10 per run looks trivial. Run it every 15 minutes and it costs $144 per month. Multiply that across 20 models with similar patterns and the monthly bill becomes a conversation-stopper at board level. The fix for the re-evaluation problem also needs to be paired with a review of scheduling frequency against actual data freshness requirements.

A capital reconciliation project we delivered for a global fintech uncovered a $25M discrepancy that had gone undetected — and part of what made it detectable was rebuilding the underlying queries as properly materialised SQL models that could be verified step by step. Without that structural fix, the logic was too tangled to audit reliably.

Frequently Asked Questions

Q: Does BigQuery always re-evaluate a CTE on every reference?

A: Yes. BigQuery treats CTEs as logical aliases rather than materialised intermediate results. Every reference to a CTE name causes BigQuery to re-execute the full subquery behind it. There is no automatic intra-query caching of CTE results in BigQuery, regardless of how many times the CTE is referenced within the same query.

Q: How is BigQuery CTE behaviour different from other databases?

A: In some databases (like Postgres with the MATERIALIZED keyword, or SQL Server in certain configurations), CTEs can be materialised on first execution and reused cheaply. BigQuery does not do this — it always re-executes. This makes BigQuery more sensitive to multi-referenced CTEs than most other SQL engines, and means patterns that are cost-neutral elsewhere become expensive in BigQuery.

Q: When should I use a temp table instead of a CTE in a dbt BigQuery model?

A: Use a temp table when a CTE is referenced more than once in the same query or script and the underlying data scan is large. Use a dedicated dbt staging model when the logic is genuinely reusable across multiple downstream models. CTEs remain the right choice for single-reference logic within a model — they are readable, composable, and free of overhead when used correctly.

Q: Will refactoring CTEs to temp tables affect my dbt model tests?

A: Temp table patterns require using pre-hook or post-hook blocks, or splitting logic across a script-style model. This can complicate standard dbt test coverage. For most cases, refactoring into a dedicated dbt intermediate or staging model is cleaner — it preserves testability, lineage, and documentation while eliminating the re-evaluation cost.

Q: How much does BigQuery CTE re-evaluation actually cost at startup scale?

A: It depends entirely on table size and run frequency. A 50 GB staging CTE referenced twice per model run, running hourly, costs roughly $0.03 per extra scan — which adds up to over $20 per month for that single pattern. Multiply across a dbt project with ten such models and you are looking at hundreds of dollars monthly in avoidable scan cost, before accounting for growth in data volume.

---

If your BigQuery bill is climbing faster than your data volume and your dbt models look clean on the surface, CTE re-evaluation is almost certainly part of the answer — and it is exactly the kind of structural problem that goes unfound until someone with delivery experience looks at the execution plans, not just the SQL. At Fintel Analytics, we audit and rebuild dbt projects for growth-stage companies precisely to surface these patterns before they compound into a bill that demands a budget conversation — and the fixes we implement tend to pay for the engagement within the first billing cycle.