BigQuery referenced_tables: Find dbt Models Scanning Ghost Tables

The Short Answer

If a dbt model references a BigQuery table with a hardcoded identifier instead of {{ source() }} or {{ ref() }}, that table is invisible to dbt's DAG — but BigQuery is still scanning it and billing you for every byte. You can find every undeclared table your dbt jobs have touched in the last 30 days using INFORMATION_SCHEMA.JOBS and its referenced_tables repeated field. Run the diagnostic below, compare the output to your declared sources, and you will find the gaps in under five minutes.

---

There is a class of BigQuery cost problem that does not show up in your dbt docs, does not trigger a source freshness warning, and does not appear anywhere in your lineage graph. It just silently adds bytes billed to your monthly invoice.

The cause is almost always the same: a model somewhere in your project contains a hardcoded table reference — a full project.dataset.table string written directly into the SQL — instead of using {{ source() }} or {{ ref() }}. dbt never registers the dependency. The table does not appear in the DAG. Freshness checks never run against it. Schema drift from that table will break your model with zero warning. And if that table has no partition filter and happens to be large, you are paying for a full scan on every single dbt run.

A pattern we see repeatedly in analytics engineering audits at fast-growing companies: teams inherit a dbt project from a contractor, a previous hire, or a rapid build sprint, and nobody has ever audited what BigQuery has actually been scanning versus what dbt thinks it is scanning. The gap between those two things is where cost and correctness problems hide.

Here is how to close that gap.

---

Why Do dbt Models Scan Tables Outside the DAG?

The most common cause is convenience during prototyping that never got cleaned up. An engineer writes a model quickly, hardcodes a project reference to get it working, ships it to production, and moves on. The {{ source() }} declaration never gets added. The model works — output looks right — and nobody questions it.

Other causes we have seen in the field:

• Seeds or external datasets pulled in from another GCP project without a source definition. The model runs fine, the lineage is broken.
• Shared reference tables maintained by another team — a currency conversion lookup, a postcode mapping, a product taxonomy — referenced directly because "it's just a small table" (it often isn't).
• Legacy staging models that pre-date the dbt project and were ported to SQL files without being reclassified as sources.
• Cross-team models where one squad hardcodes a reference to another squad's dataset rather than declaring a cross-project source.

The consequence is layered. You lose lineage visibility — you cannot tell what breaks if that table changes. You lose freshness monitoring — if the upstream table stops updating, your downstream metrics degrade silently. And depending on the table's size and whether it has a partition filter applied, you may be scanning far more data than you realise on every dbt run.

In our work with Series A fintech and payments companies, we have seen undeclared sources that account for 20–40% of a project's total bytes billed — consistently overlooked because they never appear in dbt docs or cost-by-model reports.

---

How to Find Every Table Your dbt Jobs Have Actually Scanned

BigQuery records the complete set of tables read by every query job in INFORMATION_SCHEMA.JOBS. The column referenced_tables is a repeated record — one entry per table touched — and it captures the actual execution, not the declared intent.

This is the diagnostic. Run it in BigQuery for the region where your dbt project executes (swap region-us for region-eu or your specific region as needed):

-- Step 1: Get every unique table scanned by dbt jobs in the last 30 days
SELECT
  ref.project_id   AS scanned_project,
  ref.dataset_id   AS scanned_dataset,
  ref.table_id     AS scanned_table,
  COUNT(*)         AS job_count,
  ROUND(
    SUM(j.total_bytes_billed) / POW(1024, 4) * 6.25,
    2
  )                AS estimated_cost_usd
FROM
  `region-us`.INFORMATION_SCHEMA.JOBS AS j,
  UNNEST(j.referenced_tables) AS ref
WHERE
  j.creation_time >= TIMESTAMP_SUB(CURRENT_TIMESTAMP(), INTERVAL 30 DAY)
  AND j.job_type = 'QUERY'
  AND j.state = 'DONE'
  AND j.error_result IS NULL
  -- Filter to dbt-labelled jobs only
  AND EXISTS (
    SELECT 1
    FROM UNNEST(j.labels) AS lbl
    WHERE lbl.key = 'dbt_invocation_id'
  )
GROUP BY 1, 2, 3
ORDER BY estimated_cost_usd DESC;

The dbt_invocation_id label filter isolates jobs triggered by dbt runs specifically — so you are not mixing in ad-hoc analyst queries. (If your team has not configured dbt query labels yet, that is a separate problem worth fixing: see dbt Query Labels in BigQuery: Fix Your Cost Attribution.)

You now have a complete list of tables BigQuery actually scanned during dbt execution over the last 30 days. The next step is to compare that list against what dbt has declared.

-- Step 2: Pull your declared dbt sources from the metadata tables
-- (Run this in your dbt project's target dataset after `dbt docs generate`)
SELECT
  database   AS declared_project,
  schema     AS declared_dataset,
  name       AS declared_table
FROM
  your_project.your_dbt_dataset.sources
WHERE
  resource_type = 'source';

If you are not querying the dbt metadata tables directly, you can extract declared sources from your sources.yml files and build a static comparison, or use the dbt ls --select source:* command to enumerate them:

dbt ls --select source:* --output json

Now cross-reference. Any table in the Step 1 output that does not appear in Step 2 is an undeclared dependency — a ghost table in your BigQuery lineage.

Running that query for one model is fine. Auditing every model across a large project is not a five-minute job — fintel-scan is a free, open-source MIT-licensed CLI that does this check and fourteen others locally, with no warehouse connection: uvx fintel-scan.

---

What to Do When You Find Ghost Tables

Once you have a list of undeclared tables, triage them by estimated cost first — the output from Step 1 is already ordered by estimated_cost_usd, so the highest-value gaps surface immediately.

For each ghost table, apply one of three fixes:

Fix 1 — Declare it as a source. This is the right answer for any stable upstream table your model genuinely depends on. Add it to your sources.yml, replace the hardcoded reference with {{ source('schema_name', 'table_name') }}, and add a freshness block:

sources:
  - name: payments_raw
    database: external-project-id
    schema: raw_payments
    tables:
      - name: transactions
        freshness:
          warn_after: {count: 6, period: hour}
          error_after: {count: 24, period: hour}
        loaded_at_field: created_at

This immediately restores lineage, enables freshness monitoring, and makes the dependency visible in dbt docs.

Fix 2 — Replace it with a {{ ref() }}. If the table is actually the output of another dbt model in your project (common when a model was ported from a legacy script), replace the hardcoded reference with {{ ref('model_name') }}. dbt will rebuild the correct DAG dependency automatically.

Fix 3 — Remove it. If the table is no longer needed and the scan is just dead code that survived a refactor, delete the reference. This is more common than you would expect — especially in projects that have gone through multiple sprint cycles without a cleanup pass.

For the cost dimension: once the table is declared as a source, you can audit whether it has appropriate partition filters applied. If the referenced table is partitioned and your dbt model is not filtering on the partition column, you are scanning the full table on every run. That is a separate fix, but declaring the source first is the prerequisite — you cannot instrument what you cannot see.

A reconciliation pipeline we rebuilt for a global payments company had four undeclared source tables embedded in hardcoded SQL across three models. None of them had partition filters. Declaring them properly, adding filters, and adding freshness checks reduced the bytes billed for those models by over 60% — and gave the team their first accurate lineage graph for that part of the pipeline.

---

How to Stop Ghost Tables Recurring

Detection is a one-time fix. Prevention is structural.

1. Add a dbt test that catches hardcoded references at CI. You can write a simple linting check — either with dbt-osmosis, SQLFluff, or a custom Python script — that greps your model SQL for patterns matching project.dataset.table string literals outside of Jinja blocks. Block the PR if any are found.

2. Run the referenced_tables audit as a scheduled query. Schedule the Step 1 query above to run weekly and write results to a monitoring table. Alert when new tables appear that are not in your declared sources list.

3. Enforce source declarations in code review. Add an explicit item to your PR template: "Does this model reference any tables not declared via {{ source() }} or {{ ref() }}?" Simple, zero-cost, and effective when it becomes habit.

4. Use dbt's --fail-fast and test coverage. If you have schema tests on all your source declarations (and you should — see our related post on dbt Source Fan-Out in BigQuery: The DAG Anti-Pattern Costing You), any undeclared table will also be untested — making it a double signal during pipeline health reviews.

The goal is a project where everything BigQuery scans is also something dbt knows about. That state is achievable, and the diagnostic above gets you most of the way there in a single query.

---

Frequently Asked Questions

Q: What is the referenced_tables field in BigQuery INFORMATION_SCHEMA.JOBS?

A: referenced_tables is a repeated record field in INFORMATION_SCHEMA.JOBS that lists every BigQuery table actually read during a query job's execution. Each entry contains project_id, dataset_id, and table_id. It reflects actual runtime behaviour — not declared intent — making it the definitive source for auditing what your dbt models are really scanning.

Q: How do I filter INFORMATION_SCHEMA.JOBS to only dbt-generated queries?

A: dbt automatically attaches labels to every query it runs, including dbt_invocation_id and dbt_model. You can filter INFORMATION_SCHEMA.JOBS using EXISTS (SELECT 1 FROM UNNEST(labels) WHERE key = 'dbt_invocation_id') to isolate dbt jobs from ad-hoc analyst queries. This requires that query labels are configured in your dbt BigQuery profile — if they are not, every job will lack labels and the filter will return nothing.

Q: Can undeclared source tables affect my BigQuery bill significantly?

A: Yes, materially. If a ghost table is large, unpartitioned, or lacks clustering, every dbt run that touches it performs a full table scan. On on-demand pricing at $6.25 per TiB (as of 2026), a single 500 GB unpartitioned table scanned twice daily adds roughly $56/month from that one reference alone — and projects with multiple ghost tables compound this quickly.

Q: Why doesn't dbt warn me when a model references a table outside the DAG?

A: dbt parses Jinja to build its DAG — it only registers dependencies that flow through {{ ref() }} or {{ source() }} calls. A hardcoded SQL string like `project.dataset.table` is opaque to dbt's compiler. The SQL is valid, BigQuery executes it, but dbt has no knowledge the dependency exists. This is a known limitation of static DAG construction from Jinja templates.

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

A: To query INFORMATION_SCHEMA.JOBS_BY_PROJECT, you need the bigquery.jobs.list permission at the project level, typically granted via the roles/bigquery.resourceViewer or roles/bigquery.admin IAM role. Note that INFORMATION_SCHEMA.JOBS (without the _BY_PROJECT suffix) shows only the current user's jobs, which is insufficient for a project-wide audit.

---

If your dbt project has been running in production for more than a few months without a referenced_tables audit, there is a strong chance BigQuery is scanning tables your lineage graph has never seen — and your bill is reflecting it. At Fintel Analytics, we run this audit as a standard step in every data stack review we deliver for fintech and payments clients, and we have yet to find a project where zero ghost tables exist. If you want a clear picture of what your dbt project is actually scanning — and a remediation plan that closes the gaps — our team is ready to help.