Hardcoded Table Names in dbt BigQuery: Find the Lineage Gaps

Hardcoded table names in dbt BigQuery models are one of the most common and quietly damaging patterns in a production dbt project. Any model that references a BigQuery table using a raw string — instead of {{ ref() }} or {{ source() }} — is invisible to dbt's DAG, meaning broken upstream tables will not trigger downstream failures, impact analysis is impossible, and dbt run --select +model_name will silently miss dependencies. The fix is straightforward once you find them. The problem is most teams do not know how many they have.

This is one of those issues that creeps in gradually. A new analyst needs to join a table from another dataset. They copy a BigQuery path from the console, paste it into a model, and it works. The query returns data. dbt run goes green. Nobody flags it because there is no error — just a quietly disconnected node sitting outside the DAG, waiting for something upstream to change.

In a project with thirty models that problem is annoying. In a project with three hundred it is a genuine operational risk. Refactoring becomes guesswork, impact analysis becomes impossible, and the first time an upstream table is renamed or moved you find out about every hardcoded reference at once — in production.

Why Hardcoded Table Names Break dbt Lineage Silently

dbt's entire dependency model is built on {{ ref() }} and {{ source() }}. Every time you write {{ ref('stg_orders') }}, dbt registers a dependency edge in the DAG and serialises it into manifest.json. That edge is what powers dbt run --select +fct_revenue+, impact analysis in dbt Docs, and automated CI selection. As one data governance reference puts it, hardcoded names "are invisible to the DAG and create lineage gaps" — the model runs, but dbt has no knowledge that a relationship exists.

The result is a class of failure that is particularly hard to debug. Upstream tables can be dropped, renamed, or migrated to a new dataset, and dbt will not warn you. The hardcoded model will fail at query execution time — not during dbt compile or dbt ls — with a BigQuery NOT_FOUND error that gives no indication the problem is a lineage gap rather than a missing table.

We see this pattern repeatedly in projects we inherit from teams that have grown fast. The early models are clean — ref() everywhere, sources declared in YAML. Then six months of pressure hits, someone needs a quick fix, and the first hardcoded reference appears. Three months later there are twenty. By the time the team notices, unpicking them requires reading every compiled SQL file by hand.

The critical insight: dbt's DAG is only as complete as the ref() and source() calls in your code. Every hardcoded string is a dependency that dbt cannot see, cannot test, and cannot act on.

---

📺 Watch: DE Zoomcamp 4.3.2 - dbt Sources

---

How to Find Every Hardcoded Table Reference in Your dbt Project

The detection approach has two parts: a static code scan across your .sql model files, and a cross-check against BigQuery's INFORMATION_SCHEMA.JOBS to find queries that are hitting raw tables directly rather than through dbt-managed views or tables.

Part 1 — Static grep scan (runs in under 60 seconds)

From the root of your dbt project, run:

grep -rn "`[a-zA-Z0-9_-]\+\.[a-zA-Z0-9_-]\+\.[a-zA-Z0-9_-]\+`" models/

This catches the three-part BigQuery fully-qualified table references (project.dataset.table) hardcoded as backtick strings inside your model SQL. Any match that is not inside a {{ source() }} or {{ ref() }} call is a lineage gap.

For a more targeted search that excludes legitimate source declarations:

grep -rn "`[a-zA-Z0-9_-]\+\.[a-zA-Z0-9_-]\+\.[a-zA-Z0-9_-]\+`" models/ \
  | grep -v "source(" \
  | grep -v "ref("

Each line in the output is a file path, line number, and the raw string — enough to locate and fix each one.

Part 2 — BigQuery INFORMATION_SCHEMA cross-check

The grep scan catches references in your model source. But sometimes hardcoded strings appear in macros, Jinja run_query() blocks, or in compiled SQL that differs from the source. This query against BigQuery's job history surfaces all tables that dbt service account queries are reading directly, segmented by whether those tables exist as dbt-managed models:

SELECT
  referenced_table.project_id,
  referenced_table.dataset_id,
  referenced_table.table_id,
  COUNT(*) AS query_count,
  MAX(creation_time) AS last_queried_at
FROM
  `region-eu`.INFORMATION_SCHEMA.JOBS,
  UNNEST(referenced_tables) AS referenced_table
WHERE
  creation_time >= TIMESTAMP_SUB(CURRENT_TIMESTAMP(), INTERVAL 14 DAY)
  AND user_email LIKE '%dbt%'
  AND statement_type = 'SELECT'
  AND referenced_table.dataset_id NOT IN (
    'dbt_prod', 'dbt_staging'   -- replace with your dbt target datasets
  )
GROUP BY 1, 2, 3
ORDER BY query_count DESC;

Swap region-eu for your BigQuery region and replace dbt_prod / dbt_staging with the dataset names your dbt models materialise into. Any table returned that is not in your dbt target datasets but is being queried by the dbt service account is a candidate hardcoded reference.

This query scans INFORMATION_SCHEMA.JOBS across 14 days. Filter the date range tightly — querying a long window against this view can be expensive. If you want to reduce bytes processed, add a tighter creation_time filter.

Running both checks together takes under five minutes and gives you a complete inventory of lineage gaps in your project.

---

Running that grep for one model is fine. Auditing every .sql file in a large project, including macros and Jinja blocks, is not. fintel-scan is a free, open-source, MIT-licensed CLI that runs this check and fourteen others locally with no warehouse connection: uvx fintel-scan.

---

What the Fix Actually Looks Like

Once you have your list, the remediation follows a consistent pattern. For each hardcoded reference:

Step 1 — Determine whether the table is a dbt model or a raw source.

If the table being hardcoded is itself a dbt model (i.e. it appears in your models/ directory), replace the hardcoded string with {{ ref('model_name') }}. dbt will resolve the correct fully-qualified path at compile time for every environment — dev, staging, and production — without you needing to hardcode anything.

Step 2 — If it is a raw source table (not managed by dbt), declare it in a _sources.yml file.

A source declaration looks like this:

sources:
  - name: payments_raw
    database: my-gcp-project
    schema: raw_payments
    tables:
      - name: transactions

Then reference it in your model as {{ source('payments_raw', 'transactions') }}. dbt now knows about the dependency, can run freshness checks against it, and will surface it correctly in the lineage graph.

Step 3 — Replace the hardcoded string in the model.

Before:

SELECT *
FROM `my-gcp-project.raw_payments.transactions`
WHERE created_at >= '2024-01-01'

After:

SELECT *
FROM {{ source('payments_raw', 'transactions') }}
WHERE created_at >= '2024-01-01'

The compiled SQL output is identical. The difference is that dbt now tracks the dependency, respects environment targeting, and will warn you if the source goes stale.

In a project we audited for a Series A fintech, this process surfaced nineteen hardcoded references across forty-three models. Eight of them were pointing to tables in a deprecated dataset that the data engineering team had assumed were no longer in use — they were still running, silently querying stale data, and feeding numbers into a finance dashboard that nobody had flagged as suspicious because the numbers were plausible.

How to Stop Hardcoded Table Names Recurring

Detection and one-time remediation are not enough. The same pressure that produced the original hardcoded references — a deadline, a dataset in a different project, a quick fix that was never revisited — will produce new ones unless you add a structural barrier.

Add a dbt-sqlfluff or custom pre-commit hook. SQLFluff has a rule (L059 in legacy, configurable in newer versions) that can be configured to warn on raw fully-qualified table references inside model SQL. Wiring this into a pre-commit hook means hardcoded strings are caught before they reach a pull request.

Make it part of your PR review checklist. If your team does code review on dbt model changes — and it should — add a line: "Does this model introduce any hardcoded table references?" to the checklist. The grep scan from Part 1 above takes three seconds to run.

Run dbt ls --select source:+ regularly. If a source is being queried but is not declared in a _sources.yml file, it will not appear in this output. Gaps in the source list are a signal that something is being hardcoded.

Enforce via CI. Add the grep pattern as a CI step. If the pattern matches any file in models/ that does not also contain source( or ref( on the same line, fail the build. This is a blunt instrument but it eliminates the class of problem permanently.

For context: after implementing the pre-commit hook and CI check for the fintech project mentioned above, the team went from discovering hardcoded references in production post-incident to catching every one at the PR stage. The lineage graph went from partial to complete within two weeks of remediation, which unlocked accurate impact analysis for the first time — a capability the team had assumed they had but had never actually had.

If you want to see how complete your dbt DAG really is, the dbt Lineage Blind Spots post covers the broader failure modes, and dbt Models With No Tests covers the overlapping problem of untested nodes that compound the risk of silent lineage gaps.

Frequently Asked Questions

Q: Why does dbt not warn me when I hardcode a table name?

A: dbt only parses {{ ref() }} and {{ source() }} calls to build its DAG. A hardcoded string is treated as raw SQL — dbt passes it through to BigQuery without registering any dependency. There is no built-in warning because dbt has no way to distinguish a hardcoded table name from a SQL function or any other string literal.

Q: Will dbt run fail if I hardcode a table name that does not exist?

A: Not at compile time. dbt will compile the model successfully and BigQuery will return a NOT_FOUND error at query execution time. This means CI pipelines that rely on dbt compile will not catch missing hardcoded tables — only a full dbt run or dbt build will expose the failure.

Q: Can I use hardcoded table names for tables in a different GCP project?

A: You can, but you should not. For cross-project tables, declare them as sources in _sources.yml using the database and schema properties to specify the project and dataset. dbt will resolve the correct fully-qualified path across all environments and the dependency will appear in your lineage graph.

Q: Does dbt docs generate show hardcoded table references in the lineage graph?

A: No. The lineage graph in dbt Docs is built entirely from the DAG, which only contains nodes registered through ref() and source(). Hardcoded tables are invisible — they will not appear as upstream nodes, meaning the lineage graph gives a false picture of model dependencies.

Q: How do I find hardcoded table names inside dbt macros, not just models?

A: Extend the grep scan to the macros/ directory: grep -rn "[a-zA-Z0-9_-]+.[a-zA-Z0-9_-]+.[a-zA-Z0-9_-]+" models/ macros/. Macros that use run_query() or statement() with hardcoded table paths are especially common and especially dangerous because they run outside the normal dbt DAG execution order.

Hardcoded table names in dbt BigQuery projects are a maintainability debt that compounds silently — every new model built on top of an unregistered dependency inherits the lineage gap, and by the time a production failure exposes the problem, the blast radius is far wider than anyone expected. At Fintel Analytics, we have audited and remediated this class of issue for fintech, payments, and e-commerce teams whose dbt projects had grown faster than their engineering standards — surfacing broken dependencies, completing lineage graphs, and putting the structural barriers in place that prevent recurrence. If your team is not confident that your dbt DAG reflects what is actually running in BigQuery, that is worth finding out before your next production incident does it for you.