Repeated Subqueries in dbt BigQuery: Stop Paying Twice (2026)

Repeated subqueries in dbt BigQuery models are one of the most common causes of runaway scan costs that teams cannot easily explain from their billing dashboard alone. When the same large table is referenced in multiple subqueries inside a single model — or across a chain of view-materialised models — BigQuery scans that table again for every reference. You pay for each scan independently. This post shows you how to find those models in under five minutes and fix them permanently.

Why Repeated Subqueries in BigQuery Cost More Than You Think

BigQuery's on-demand pricing bills you per byte scanned, not per query. At $6.25 per TiB processed in US multi-region, that sounds manageable — until you realise that repeated subqueries compound that cost silently inside a single dbt run.

Here is the mechanism. When you write a dbt model materialised as a view, every downstream query that references that view causes BigQuery to re-execute the full view definition at read time. If that view definition itself contains two or three subqueries referencing the same large staging table, BigQuery does not deduplicate those scans. It executes each subquery independently. A model with three subqueries against a 500 GB table scans 1.5 TB. Run that model every hour via a scheduled query, and a single poorly written query scanning a 10 TiB table costs $62.50, and if it runs every hour, it costs $1,500 per day. Scale that across a dbt project where this pattern appears in a dozen models, and you have a cost problem that will not show up as any single line item on your bill.

BigQuery caches query results for 24 hours by default at no additional cost — if an identical query runs again within that window and the underlying data hasn't changed, BigQuery serves the cached results instantly. The problem is that repeated subqueries almost always contain non-deterministic elements — CURRENT_DATE(), CURRENT_TIMESTAMP(), window functions with dynamic offsets — that invalidate the cache every run. The scan happens in full, every time.

A pattern we see repeatedly in early-stage fintech and e-commerce clients: a dbt mart model built under time pressure where the engineer pulled the same stg_transactions table in a main query, in a subquery to compute a rolling average, and in a second subquery to derive a prior-period comparison. Three full scans of the same table, baked into a single model, running on a schedule. The team saw the BigQuery bill climb but could not pinpoint the culprit because the cost was spread across jobs that looked like normal transformation work.

---

📺 Watch: Translate Any SQL Query into BigQuery Query | Translate queries with the interactive SQL Translator

---

How to Find Repeated Subquery Scans Using INFORMATION_SCHEMA

The fastest detection route is INFORMATION_SCHEMA.JOBS_BY_PROJECT. This view records every job executed in your project, including total_bytes_billed, query, and the service account or user that ran it. The query below surfaces the twenty most expensive jobs from the past seven days where the same referenced table appears more than once in the query text — a reliable proxy for a repeated subquery pattern.

Run this directly in the BigQuery console:

SELECT
  job_id,
  user_email,
  ROUND(total_bytes_billed / POW(1024, 4) * 6.25, 4)   AS estimated_cost_usd,
  ROUND(total_bytes_billed / POW(1024, 3), 2)           AS total_gb_billed,
  statement_type,
  REGEXP_COUNT(query, r'`[^`]+\.stg_[^`]+`')           AS stg_table_references,
  LEFT(query, 400)                                       AS query_preview,
  creation_time
FROM
  `region-us`.INFORMATION_SCHEMA.JOBS_BY_PROJECT
WHERE
  creation_time >= TIMESTAMP_SUB(CURRENT_TIMESTAMP(), INTERVAL 7 DAY)
  AND job_type = 'QUERY'
  AND statement_type = 'SELECT'
  AND total_bytes_billed > 10000000000   -- 10 GB minimum to filter noise
QUALIFY
  REGEXP_COUNT(query, r'`[^`]+\.stg_[^`]+`') > 1
ORDER BY
  total_bytes_billed DESC
LIMIT 20;

Replace stg_ with whatever prefix convention your staging layer uses. If you want to catch any repeated table reference — not just staging models — swap the REGEXP_COUNT pattern for the specific dataset and table you suspect:

REGEXP_COUNT(query, r'your_dataset\.your_large_table') > 1

What you are looking for: any job where stg_table_references is two or higher and total_gb_billed is large. If you see the same user_email (often a dbt service account) appearing across multiple rows with the same table referenced repeatedly, you have found your cost driver.

A second useful query surfaces jobs by the service account running dbt and ranks them by total bytes billed over the past 30 days:

SELECT
  user_email,
  COUNT(*)                                                AS job_count,
  ROUND(SUM(total_bytes_billed) / POW(1024, 4) * 6.25, 2) AS total_cost_usd,
  ROUND(SUM(total_bytes_billed) / POW(1024, 3), 2)         AS total_gb_billed
FROM
  `region-us`.INFORMATION_SCHEMA.JOBS_BY_PROJECT
WHERE
  creation_time >= TIMESTAMP_SUB(CURRENT_TIMESTAMP(), INTERVAL 30 DAY)
  AND job_type = 'QUERY'
GROUP BY
  user_email
ORDER BY
  total_cost_usd DESC
LIMIT 10;

Once you have your dbt service account isolated, drill into its specific job records and pull the full query text for the top five most expensive jobs. That is where you will find the repeated subqueries.

Running that query for one suspicious model is straightforward. Checking every model across a whole dbt project — including catching duplicated table references buried inside view chains — is not. fintel-scan is a free, open-source MIT CLI that automates this check and fourteen others locally, without needing a warehouse connection: uvx fintel-scan.

How to Fix Repeated Subqueries in dbt Models

Once you have identified the offending model, the fix is almost always one of three patterns.

Pattern 1 — Refactor subqueries into CTEs at the top of the model

This is the most common and easiest fix. Instead of referencing a staging table in the main SELECT and again inside subqueries, pull all required data from that table once in a named CTE at the top, then reference the CTE name everywhere downstream.

Before (scans stg_transactions three times):

SELECT
  user_id,
  SUM(amount)                                          AS total_revenue,
  (SELECT AVG(amount) FROM `prod.stg_transactions`
   WHERE created_at >= DATE_SUB(CURRENT_DATE(), INTERVAL 30 DAY)) AS avg_30d,
  (SELECT SUM(amount) FROM `prod.stg_transactions`
   WHERE created_at < DATE_SUB(CURRENT_DATE(), INTERVAL 30 DAY)
     AND created_at >= DATE_SUB(CURRENT_DATE(), INTERVAL 60 DAY)) AS revenue_prev_period
FROM `prod.stg_transactions`
WHERE created_at >= DATE_SUB(CURRENT_DATE(), INTERVAL 30 DAY)
GROUP BY user_id

After (scans stg_transactions once):

WITH base AS (
  SELECT
    user_id,
    amount,
    created_at
  FROM {{ ref('stg_transactions') }}
  WHERE created_at >= DATE_SUB(CURRENT_DATE(), INTERVAL 60 DAY)
),

current_period AS (
  SELECT user_id, SUM(amount) AS total_revenue, AVG(amount) AS avg_30d
  FROM base
  WHERE created_at >= DATE_SUB(CURRENT_DATE(), INTERVAL 30 DAY)
  GROUP BY user_id
),

prior_period AS (
  SELECT user_id, SUM(amount) AS revenue_prev_period
  FROM base
  WHERE created_at < DATE_SUB(CURRENT_DATE(), INTERVAL 30 DAY)
  GROUP BY user_id
)

SELECT
  c.user_id,
  c.total_revenue,
  c.avg_30d,
  COALESCE(p.revenue_prev_period, 0) AS revenue_prev_period
FROM current_period c
LEFT JOIN prior_period p USING (user_id)

Note that BigQuery CTEs in the same query are not guaranteed to be materialised — they can be re-evaluated inline by the query planner, which is a separate cost trap covered in detail in our post on BigQuery CTEs Re-evaluated Every Time. For a CTE that is referenced more than once in the same query, verify the execution plan in the BigQuery query explanation panel to confirm it is not re-scanned.

Pattern 2 — Change the materialisation from view to table or incremental

Repeated subqueries are disproportionately expensive when the model is materialised as a view, because the subquery logic executes fresh on every downstream read. Materialising results with dbt saves on reprocessing — you pay once for the query, then reuse the results cheaply. Changing a view to a table materialisation means the subquery runs once per dbt job, not once per downstream consumer query. For mart-layer models in production this is almost always the right default.

Pattern 3 — Split complex models into intermediate models

If a model is so complex that it needs to reference the same table three times because it is doing too much work at once, splitting it into two or three intermediate models — each materialised as a table — is often the cleaner long-term fix. Pre-aggregating for reuse by materialising repeated metrics in summary tables or dbt models avoids recalculating expensive aggregations on raw data every time. The intermediate model handles the pre-aggregation once; downstream models join against the pre-aggregated result rather than re-scanning the source.

How to Stop Repeated Subqueries Recurring

Detecting and fixing the immediate problem is one thing. Preventing the pattern from re-appearing across a team is another.

Add a dbt test or custom macro that flags repeated source references

You can write a simple dbt macro that checks model SQL at parse time for duplicate ref() or source() calls. This is not a native dbt feature but is implementable as a custom test using dbt-utils and a Jinja macro that counts source references per model. Wire it into your CI pipeline so any model with the same source referenced more than twice fails a pre-merge check.

Use dbt-project.yml to enforce sensible default materialisations per layer

For staging models that underpin expensive mart logic, enforce materialized: table or materialized: incremental at the folder level via dbt_project.yml. This prevents a developer from accidentally leaving a heavy staging model as a view that then gets repeatedly scanned by downstream consumers.

Set up INFORMATION_SCHEMA cost alerting

Schedule the detection query above to run daily via a BigQuery scheduled query, outputting results to a monitoring dataset. Add a Data Studio or Holistics alert that fires when any single job exceeds a bytes-billed threshold you set based on your project norms. You will catch new instances of this pattern before they compound across a billing cycle. This pairs well with the broader query governance approach covered in our post on dbt Sources With No Freshness Config — both are symptoms of the same underlying gap in project hygiene.

In our work with a Series A payments company, a single mart model containing three correlated subqueries against the same 800 GB transactions table was running on a 30-minute schedule. Refactoring it into a CTE-based model materialised as an incremental table reduced that model's daily bytes billed by approximately 65% and removed the cost entirely from the on-demand bill for the two hours of the day when cache hits were reliable. The fix took one sprint. The bill reduction was visible in the first weekly billing report afterwards.

Frequently Asked Questions

Q: Does BigQuery automatically optimise repeated subqueries so I don't get charged twice?

A: No — BigQuery does not deduplicate table scans that appear in separate subqueries within the same query. Each reference to a table in a correlated or uncorrelated subquery is evaluated independently, and bytes billed accumulate per scan. The query planner may occasionally optimise simple cases, but you cannot rely on this for cost planning. Only CTEs that the planner materialises internally (which you cannot guarantee) or explicit table materialisations give you cost-stable repeated access.

Q: How is a repeated subquery different from a CTE re-evaluation in BigQuery?

A: Both can cause the same table to be scanned more than once per query execution, but the mechanism differs. A repeated subquery is an explicit SQL structure where the same table appears in multiple subquery clauses. A CTE re-evaluation occurs when BigQuery's query planner inlines a CTE that is referenced multiple times, treating it as repeated logic rather than a materialised intermediate result. Both problems result in higher bytes billed — the detection and fix approach is similar but not identical.

Q: Will switching all my dbt models from views to tables fix repeated subquery costs?

A: Partially. Changing view materialisations to table materialisations means the subquery logic runs once per dbt job rather than once per downstream read. But if a single model's SQL contains repeated subqueries against the same source table, you still pay for each scan during that one dbt run. You need both: fix the SQL pattern inside the model, and choose the right materialisation strategy for each layer.

Q: How far back can I query INFORMATION_SCHEMA.JOBS_BY_PROJECT in BigQuery?

A: By default, INFORMATION_SCHEMA.JOBS_BY_PROJECT retains job history for 180 days. You can query any window within that range by adjusting the creation_time filter. For cost investigation purposes, a 7–30 day window is typically sufficient to identify recurring expensive patterns.

Q: What BigQuery IAM permissions do I need to query INFORMATION_SCHEMA.JOBS_BY_PROJECT?

A: You need the bigquery.jobs.list permission at the project level, which is included in the roles/bigquery.resourceViewer role. For organisation-level views (JOBS_BY_ORGANIZATION), you need the roles/bigquery.resourceViewer role at the organisation level. Most data engineers working in their own project already have sufficient permissions to query the project-level view.

Repeated subqueries in dbt BigQuery models are one of those cost problems that compounds quietly — each model looks innocent in isolation, but across a project running on a schedule, you are paying for scans you have already paid for before. At Fintel Analytics, we have diagnosed and fixed exactly this pattern for fintech, payments, and e-commerce teams whose BigQuery bills were climbing faster than their data volumes justified, and in every case the fix was faster than the team expected once the right model was identified. If your BigQuery costs are rising without a clear explanation, that is a problem worth investigating before another billing cycle closes.