dbt Models Scanning the Same BigQuery Table Twice: Find & Fix It

When a dbt model references the same BigQuery source table more than once, BigQuery can scan that table multiple times in a single query execution — silently inflating your bytes-processed bill with every pipeline run. The fix is a single shared CTE anchoring every reference to that table. This post shows you how to find the models causing the bleed, patch them, and stop the pattern from reappearing.

This is one of the most underdiagnosed cost patterns in early-stage data stacks. It does not throw an error. It does not show up in dbt test. It does not appear on any dbt documentation warning list. It just quietly processes extra terabytes every time your pipeline runs — and your BigQuery bill climbs for a reason nobody can immediately explain.

A pattern we see repeatedly when auditing data stacks at growth-stage companies: a mart or intermediate model built under time pressure that JOINs the same source table once for revenue figures, then again for a filtered cohort count, then again inside a CASE expression for a status flag. Three references, three scans. If that source table is 50 GB and the pipeline runs six times per day, you are processing 900 GB of redundant data every 24 hours — at on-demand pricing, that is a meaningful and entirely avoidable cost.

Why Does BigQuery Scan the Same Table Multiple Times?

BigQuery's query planner is highly sophisticated, but it does not automatically deduplicate table reads across independent references within a query. When your compiled dbt SQL references {{ source('payments', 'transactions') }} in three different places — even in three different CTEs that are all within the same model — BigQuery treats each reference as an independent read against storage.

This is different from the CTE re-evaluation problem (where a single CTE is materialised and then referenced multiple times downstream). The issue here is upstream: the same raw source table is being read from storage more than once within a single compiled SQL statement.

BigQuery bills on bytes processed in on-demand pricing mode, so well-optimised queries that scan dramatically less data execute faster and cost significantly less. When your model has three independent reads of a 40 GB transactions table, you are billed for 120 GB every single run — not 40 GB.

The query execution plan in BigQuery console will show you this directly: look for multiple Read stages pointing at the same table. Most engineers never look at the execution plan for a model they did not write.

---

📺 Watch: Database Indexing for Dumb Developers

---

How to Find dbt Models With Duplicate Table Scans in BigQuery

The fastest way to detect this in production is via INFORMATION_SCHEMA.JOBS. The query below pulls the 30-day job history, parses referenced tables from the job metadata, and surfaces jobs where the same table appears more than once in a single query — sorted by bytes billed descending so the most expensive violations surface first.

SELECT
  job_id,
  user_email,
  query,
  total_bytes_billed,
  total_bytes_processed,
  creation_time,
  ARRAY_LENGTH(
    REGEXP_EXTRACT_ALL(query, r'`[^`]+`')
  ) AS total_backtick_refs,
  REGEXP_EXTRACT_ALL(query, r'`[^`]+`') AS all_table_refs
FROM
  `region-us`.INFORMATION_SCHEMA.JOBS
WHERE
  creation_time >= TIMESTAMP_SUB(CURRENT_TIMESTAMP(), INTERVAL 30 DAY)
  AND job_type = 'QUERY'
  AND state = 'DONE'
  AND error_result IS NULL
  AND total_bytes_billed > 10 * 1024 * 1024 * 1024  -- > 10 GB billed
ORDER BY
  total_bytes_billed DESC
LIMIT 100;

Once you have your top offenders, pull the compiled SQL for any suspicious model and run the following query — substituting the actual job ID — to extract all table references and count their occurrences:

SELECT
  ref AS table_reference,
  COUNT(*) AS times_referenced
FROM (
  SELECT
    REGEXP_EXTRACT_ALL(
      query,
      r'`(\w+\.\w+\.\w+)`'
    ) AS refs
  FROM
    `region-us`.INFORMATION_SCHEMA.JOBS
  WHERE
    job_id = 'YOUR_JOB_ID_HERE'
),
UNNEST(refs) AS ref
GROUP BY 1
HAVING COUNT(*) > 1
ORDER BY 2 DESC;

Any row with times_referenced > 1 is a duplicate scan. If the table in question is large, this is your culprit.

For a faster local check without touching the warehouse at all, fintel-scan is a free open-source CLI (MIT licensed) that runs this and fourteen other checks across your entire dbt project locally: uvx fintel-scan.

You can also catch this in compiled SQL before it ever reaches the warehouse. After running dbt compile, grep your target directory:

grep -rn "source_table_name" ./target/compiled/ | \
  awk -F: '{print $1}' | \
  sort | uniq -d

Replace source_table_name with the literal table name as it appears in compiled SQL. Any file that appears more than once in the grep output is referencing the same table multiple times within that model's compiled SQL.

For a more rigorous check, use Python to parse every compiled SQL file and flag multi-reference patterns:

import os, re
from collections import Counter

compiled_dir = "./target/compiled"
pattern = re.compile(r"`([\w-]+\.[\w-]+\.[\w-]+)`")

for root, _, files in os.walk(compiled_dir):
    for f in files:
        if not f.endswith(".sql"):
            continue
        path = os.path.join(root, f)
        sql = open(path).read()
        refs = pattern.findall(sql)
        counts = Counter(refs)
        dupes = {t: c for t, c in counts.items() if c > 1}
        if dupes:
            print(f"\n{path}")
            for table, count in dupes.items():
                print(f"  {table} referenced {count}x")

Run this after dbt compile and you will have a full list of every model with duplicate table references, scoped to your actual compiled output — not just the Jinja source.

The Fix: One Shared CTE at the Top of Every Model

Once you have identified the offending model, the fix is straightforward. The rule is simple: a source table should appear in a dbt model's compiled SQL exactly once. Every subsequent reference to that data should be against a CTE built from that single read.

Here is the before pattern — three separate references to the same table:

-- BEFORE: transactions table referenced three times
WITH

revenue AS (
  SELECT merchant_id, SUM(amount) AS total_revenue
  FROM {{ source('payments', 'transactions') }}
  WHERE status = 'settled'
  GROUP BY 1
),

refund_count AS (
  SELECT merchant_id, COUNT(*) AS total_refunds
  FROM {{ source('payments', 'transactions') }}
  WHERE transaction_type = 'refund'
  GROUP BY 1
),

active_merchants AS (
  SELECT DISTINCT merchant_id
  FROM {{ source('payments', 'transactions') }}
  WHERE created_at >= DATE_SUB(CURRENT_DATE(), INTERVAL 90 DAY)
)

SELECT ...

Here is the corrected pattern — one read, three derived CTEs:

-- AFTER: transactions table referenced exactly once
WITH

transactions AS (
  SELECT
    merchant_id,
    amount,
    status,
    transaction_type,
    created_at
  FROM {{ source('payments', 'transactions') }}
),

revenue AS (
  SELECT merchant_id, SUM(amount) AS total_revenue
  FROM transactions
  WHERE status = 'settled'
  GROUP BY 1
),

refund_count AS (
  SELECT merchant_id, COUNT(*) AS total_refunds
  FROM transactions
  WHERE transaction_type = 'refund'
  GROUP BY 1
),

active_merchants AS (
  SELECT DISTINCT merchant_id
  FROM transactions
  WHERE created_at >= DATE_SUB(CURRENT_DATE(), INTERVAL 90 DAY)
)

SELECT ...

Note: in BigQuery, a CTE referenced multiple times downstream can itself be re-evaluated — that is a separate (but related) problem. When the anchoring CTE is large and referenced many times, consider whether an intermediate dbt model materialised as a table is more appropriate. See our post on BigQuery CTEs re-evaluated every time for the companion pattern.

The cost impact of this fix can be dramatic. Implementing efficient models in dbt reduced one company's BigQuery costs by $20,000 per month — and in our experience, duplicate-scan patterns are often the largest single contributing factor once obvious issues like missing partition filters have been addressed.

For very large source tables where the full read is genuinely expensive, consider whether the anchoring CTE should itself be an incremental intermediate model — materialised as a table with a partition filter — so that downstream mart models never touch the raw source directly. This is a pattern we implement regularly for payments and transaction data at growth-stage fintech clients, where raw event tables can exceed hundreds of GB within the first year of operation.

How to Stop This Pattern From Coming Back

Detection after the fact is necessary but insufficient. The pattern returns because there is no automated gate stopping an engineer from writing {{ source(...) }} three times in the same model. Here are the three controls we implement for clients:

1. A dbt custom generic test for duplicate source references

Write a simple shell test that runs post-compile as part of your CI pipeline:

#!/bin/bash
# run after: dbt compile
python scripts/check_duplicate_source_refs.py
if [ $? -ne 0 ]; then
  echo "CI FAILED: Duplicate source references detected in compiled models."
  exit 1
fi

Use the Python script from the detection section above. Wire it into your GitHub Actions or equivalent CI step so that any PR introducing a duplicate reference fails the build before it reaches production.

2. A dbt source-only staging convention

Establish and enforce a project convention: {{ source(...) }} references are only permitted in staging models. Intermediate and mart models may only reference {{ ref(...) }}. This structurally prevents the pattern — an intermediate model cannot accidentally reference the same raw table twice because it is not allowed to reference raw tables at all.

This is a governance decision, not just a technical one. Document it in your dbt_project.yml comments and your team's engineering handbook. A surprising number of duplicate-scan bugs we find in the wild originate from engineers who were never told this convention existed.

3. Automated cost attribution per model

The best teams use dbt as the foundation for proactive cost management and FinOps practices, infusing cost intelligence throughout development and operations. Wire INFORMATION_SCHEMA.JOBS into a daily cost-per-model dashboard that surfaces bytes-billed trends. When a model's cost spikes without a data volume increase, that is your first signal that a duplicate scan may have been introduced. We cover the full setup for this in our post on BigQuery INFORMATION_SCHEMA cost attribution.

A Real Example: What This Costs in Production

In our work with an early-stage fintech processing several million transactions per month, a routine audit of their dbt project surfaced a mart model referencing the raw transactions table four times — once for volume metrics, once for value metrics, once to calculate a rolling 7-day window, and once to derive a merchant status flag. The raw table was approximately 35 GB at the time.

Every run of that single model was billing 140 GB instead of 35 GB. The model was scheduled to run every two hours, meaning the project was processing 840 GB of unnecessary data every day from this one model alone. BigQuery bills on bytes processed in on-demand pricing mode — at standard on-demand rates, that unnecessary 840 GB per day compounds into thousands of dollars per month from a bug that takes under an hour to fix.

The fix: restructure the model to anchor all four derivations on a single CTE read of the transactions table. Total compile time: 45 minutes. Monthly cost saving: material. The model ran correctly before and after — the output did not change by a single row.

This is exactly the category of problem that does not surface in sprint reviews, does not appear in dbt docs, and does not trigger any built-in warning. It just silently processes extra data, every run, until someone decides to look.

Frequently Asked Questions

Q: Does BigQuery automatically deduplicate multiple reads of the same table within one query?

A: No. BigQuery's query planner does not automatically consolidate independent references to the same table into a single storage read. Each distinct reference in your compiled SQL is treated as a separate scan. You must structure your SQL to read the table once and derive all downstream logic from that single CTE.

Q: Will this show up in dbt test or dbt build output?

A: No. dbt has no built-in test for duplicate source references within a model. The model compiles and runs correctly — the output is right, only the cost is wrong. Detection requires either a custom CI script against compiled SQL or post-hoc analysis via INFORMATION_SCHEMA.JOBS.

Q: Is the same problem present in Snowflake or Redshift?

A: Yes, though the cost mechanics differ. Snowflake bills on virtual warehouse credits (compute time), so duplicate reads increase slot consumption and slow overall run time. Redshift scans storage on a per-query basis similarly to BigQuery on-demand. The fix — single anchoring CTE per source — applies equally across all three warehouses.

Q: Does materialising the anchoring CTE as an intermediate dbt model help?

A: Yes, in cases where the raw source table is very large and the anchoring CTE is itself expensive to compute. By materialising it as an incremental table-type model with a partition filter, downstream models read a pre-built, partitioned table rather than the raw source — eliminating the full-scan cost entirely. This is the right pattern for very high-volume event tables.

Q: How do I find which dbt model generated a specific BigQuery job ID?

A: dbt injects job labels into BigQuery queries when using the BigQuery adapter. Look for the dbt_metadata label in INFORMATION_SCHEMA.JOBS — specifically the node_id label, which maps directly to the dbt model that generated the query. You can then cross-reference this with your dbt manifest to get the model file path.

Duplicate table scans in dbt BigQuery models are a silent and entirely preventable cost leak — one that compounds with every pipeline run and grows as your data volumes scale. At Fintel Analytics, we have found and fixed this exact pattern across payments, fintech, and e-commerce stacks at growth-stage companies, and the cost recovery is almost always immediate. If your BigQuery bill is climbing faster than your data volume and you cannot explain why, this is one of the first places we look.