dbt Script Models & Temp Tables in BigQuery: The Hidden Cost

dbt script models that issue CREATE TEMP TABLE statements inside BigQuery are one of the most quietly expensive patterns in a modern data stack. They bypass dbt's materialisation system entirely, produce no lineage, and can scan the same source data multiple times inside a single job — all without a single warning in your dbt logs.

If your BigQuery bill is climbing and your dbt docs lineage graph has unexplained gaps, there is a reasonable chance a script model is responsible. This post shows you how to find it, fix it, and stop it happening again.

What Are dbt Script Models in BigQuery — and Why Do They Exist?

dbt supports a materialisation type called script (and before that, engineers would use run-operation or raw SQL with CREATE TEMP TABLE statements inside model files tagged as view or table). The temptation is understandable: sometimes you need to break a complex transformation into sequential steps, and temp tables feel like a natural solution.

Temporary tables persist for a session or connection and can store large datasets for reuse — unlike CTEs, which exist only during query execution. In a classical SQL workflow, that is useful. Inside dbt on BigQuery, it becomes a trap.

Here is the core problem: when an engineer writes a dbt model that contains one or more CREATE TEMP TABLE statements — either as a script materialisation or embedded inside a view-materialised model — dbt treats the whole thing as a single opaque job. It cannot parse the intermediate steps. It cannot build lineage from them. And BigQuery bills for every byte scanned across every statement in that job, in sequence.

Without the _SESSION prefix, temp tables persist for up to 24 hours and count against your storage quota — a detail that catches engineers who write CREATE TEMP TABLE without scoping it correctly, and end up paying for storage they assumed was free.

---

📺 Watch: Macros in dbt | Data Build Tool | Jinja and macros tutorial

---

How dbt Script Models Break Your Lineage Graph

This is the part that bites teams six months after the model was written, when someone tries to safely change an upstream source.

When dbt compiles a model, it parses ref() and source() calls to build the DAG. A CREATE TEMP TABLE statement inside a script model does not use ref(). It references a table by its raw string name. dbt's parser cannot follow that reference. The downstream temp table becomes invisible to the lineage graph.

The practical consequence: an upstream model gets refactored, a column is renamed or dropped, and the script model fails silently — or worse, produces wrong results without failing. Nobody knows which downstream reports have been corrupted until a finance team flags a number that doesn't look right.

A pattern we see repeatedly when auditing early-stage data stacks: a single script model sitting in the middle of a transformation layer, acting as a junction between five upstream models and three downstream marts. The engineer who wrote it left the company. Nobody dares touch the upstream models because the impact is unknowable. The lineage gap has effectively frozen part of the data stack.

For more on how lineage blind spots propagate through a project, see our post on dbt run-operation in BigQuery: The Lineage Blind Spot — the same root mechanism applies here.

How to Find dbt Script Models Using Temp Tables in BigQuery

There are two detection surfaces: your dbt project files (static analysis) and BigQuery's INFORMATION_SCHEMA (runtime cost evidence).

Step 1 — Find script models and raw CREATE TEMP TABLE usage in your project

Run this from your project root. It finds any .sql file in your models/ directory that contains a CREATE TEMP TABLE statement:

grep -rn --include="*.sql" -i "CREATE TEMP TABLE" models/

Also check for the script materialisation config:

grep -rn --include="*.sql" "materialized='script'" models/
grep -rn --include="*.yml" "materialized: script" models/

Any hit here is worth investigating. The presence of a CREATE TEMP TABLE in a model file is not automatically wrong — but it almost always is when it appears in a file that should be producing a lineage-tracked output.

Step 2 — Quantify the cost of those jobs in BigQuery

This query pulls the last 30 days of multi-statement jobs from INFORMATION_SCHEMA.JOBS and surfaces any that reference temp table creation, sorted by bytes billed:

SELECT
  job_id,
  user_email,
  creation_time,
  ROUND(total_bytes_billed / POW(1024, 4) * 6.25, 4)   AS estimated_cost_usd,
  total_bytes_billed,
  statement_type,
  query
FROM
  `region-eu.INFORMATION_SCHEMA.JOBS`
WHERE
  creation_time >= TIMESTAMP_SUB(CURRENT_TIMESTAMP(), INTERVAL 30 DAY)
  AND state = 'DONE'
  AND UPPER(query) LIKE '%CREATE TEMP TABLE%'
ORDER BY
  total_bytes_billed DESC
LIMIT 50;

Replace region-eu with your actual BigQuery region (e.g. region-us, region-us-central1). The estimated_cost_usd column uses the standard on-demand rate of $6.25 per TiB scanned — adjust if you are on a reservation.

If you see jobs scanning hundreds of gigabytes that trace back to a dbt model file, you have found your problem. The bytes billed column will show you the full cost of the script job, which includes every temp table creation and every subsequent query against it — even if a well-structured set of dbt models would have computed the same result from a single filtered scan.

Step 3 — Check whether the same source is scanned multiple times inside one script job

Script models often scan a large source table once to create a temp table, then query that temp table several times for different transformations. On paper this looks efficient. In BigQuery, however, a CTE referenced twice is computed twice — and a temp table created without partition alignment and then queried with filters that cannot prune effectively can end up scanning more data across the combined statements than an equivalent set of partitioned dbt models would.

To confirm this, pull the individual statements within a multi-statement job:

SELECT
  job_id,
  script_statistics.evaluation_kind,
  statement_type,
  ROUND(total_bytes_billed / POW(1024, 3), 2) AS gb_billed
FROM
  `region-eu.INFORMATION_SCHEMA.JOBS`
WHERE
  job_id = 'YOUR_JOB_ID_HERE'
  AND creation_time >= TIMESTAMP_SUB(CURRENT_TIMESTAMP(), INTERVAL 30 DAY);

If the individual statement rows show repeated large scans against the same source table across different steps of the script, the fix is to decompose the script into discrete dbt models with appropriate materialisation.

For a broader look at the cost of scanning the same table multiple times across different models, see dbt Models Scanning the Same BigQuery Table Twice: Find & Fix It.

---

Running those checks manually across one suspect model is manageable. Checking every model in a large project is not. fintel-scan is a free, open-source, MIT-licensed CLI that runs this check and fourteen others locally against your dbt project — no warehouse connection needed: uvx fintel-scan.

---

How to Fix a dbt Script Model That Uses Temp Tables

The fix is straightforward in principle, though it requires understanding what the script was actually trying to do.

Option 1 — Decompose the script into a staging + intermediate + mart layer

This is almost always the right answer. Each CREATE TEMP TABLE in the script corresponds to a logical transformation step. Extract each one into its own dbt model:

• Steps that filter and rename source columns → staging/ models, materialised as view
• Steps that join or aggregate intermediate results → intermediate/ models, materialised as ephemeral or table depending on reuse
• Final output step → mart/ model, materialised as table or incremental

The result is a full DAG that dbt can track, test, and document. Every transformation becomes auditable. Every column reference becomes traceable. And BigQuery now has the opportunity to prune partitions and apply clustering at each step, rather than scanning raw source data in an unoptimised script context.

Using incremental models to limit data scanned — processing only new or changed records — can cut compute and speed up workflows; for large datasets, this alone can save thousands in query costs.

Option 2 — If the multi-step logic is genuinely inseparable, use dbt's script materialisation correctly

If you have a legitimate use case for a script model (for example, a procedure that must run as an atomic transaction), use dbt's official script materialisation with the _SESSION prefix on all temp tables to scope them correctly and avoid storage charges:

{{config(materialized='script')}}

CREATE TEMP TABLE _SESSION.my_intermediate AS
SELECT ...
FROM {{ ref('stg_my_source') }};  -- use ref() even inside scripts

SELECT * FROM _SESSION.my_intermediate WHERE ...;

Critically: even inside a script model, always use ref() and source() to reference upstream models. This is the only way dbt can build even partial lineage from a script. Engineers who write raw table paths inside scripts — my_project.my_dataset.my_table — eliminate all lineage tracking entirely.

Option 3 — Replace temp table fan-out with a single well-materialised intermediate model

A common pattern: a script creates one large temp table and then fans out into multiple INSERT INTO statements for different target tables. This is almost always a sign that a single intermediate model — materialised as a table with appropriate partitioning and clustering — would serve all the downstream consumers without the script overhead. Most minor optimisations are insignificant compared to choosing the correct materialisation strategy.

In our work with early-stage fintech and payments companies, we have seen this decomposition reduce a single script job from scanning 800 GB per run to a set of incremental models scanning under 2 GB — without changing any of the business logic. The query cost drops by two orders of magnitude. The lineage becomes fully visible. And the models become testable.

How to Stop This Pattern Recurring

Detection after the fact is necessary but not sufficient. Three controls prevent script models with unscoped temp tables from re-entering your project:

1. Add a pre-commit hook that blocks CREATE TEMP TABLE without _SESSION in model files

# .pre-commit-config.yaml
- repo: local
  hooks:
    - id: no-bare-temp-tables
      name: Block CREATE TEMP TABLE without _SESSION scope
      language: pygrep
      entry: 'CREATE\s+TEMP\s+TABLE\s+(?!_SESSION)'
      files: models/.*\.sql
      args: [--multiline, --ignore-case]

This blocks the commit if a developer writes CREATE TEMP TABLE my_table without the _SESSION qualifier — the pattern that bleeds storage cost.

2. Add a dbt test that asserts no model references raw table paths

A custom dbt singular test can scan your compiled SQL for hardcoded table references that bypass ref(). This is a static check that runs at test time, before any query hits BigQuery.

3. Enforce a review gate for any script materialisation

Add a CODEOWNERS rule or PR checklist item: any model with materialized='script' requires a senior review before merging. Script models are not inherently wrong, but they should be a conscious, justified choice — not a default reached for because it was easier than decomposing the logic.

Frequently Asked Questions

Q: Are dbt script models always bad in BigQuery?

A: No — they have legitimate uses for procedural logic that genuinely cannot be decomposed into discrete transformations. The problem is when they are used as a shortcut to avoid proper model layering. Most cases that reach for a script model should actually be a staging → intermediate → mart chain, with each step independently testable and lineage-tracked.

Q: Does BigQuery charge for temp tables created inside a script model?

A: Yes, in two ways. First, every statement in a multi-statement job that scans data is billed on bytes processed. Second, temp tables created without the _SESSION prefix persist for up to 24 hours and accrue active storage charges. Using _SESSION.table_name scopes the table to the current session and avoids the storage charge.

Q: Why don't CREATE TEMP TABLE statements inside dbt models appear in dbt lineage?

A: dbt builds its DAG by parsing ref() and source() macro calls in model files. A raw CREATE TEMP TABLE statement referencing a hardcoded table name is invisible to this parser — dbt cannot follow the reference and therefore cannot add it to the lineage graph. Always use ref() even inside script models.

Q: How do I find all dbt script models in a large project quickly?

A: Run grep -rn --include="*.sql" -i "CREATE TEMP TABLE" models/ from your project root to find any model SQL file containing the pattern. Then cross-reference with INFORMATION_SCHEMA.JOBS filtered on the same query text to quantify the actual cost. Alternatively, uvx fintel-scan checks this and related issues automatically.

Q: What is the performance difference between a temp table and a properly materialised dbt model in BigQuery?

A: A well-partitioned and clustered dbt table or incremental model allows BigQuery to prune irrelevant partitions on every downstream query. A temp table created inside a script carries no partition metadata by default, meaning every downstream reference within the script scans the full temp table. For large intermediate datasets, the cost difference can be significant — often an order of magnitude or more.

If your dbt project has grown faster than your governance has kept pace with — script models appearing where they shouldn't, lineage gaps nobody can explain, and a BigQuery bill that seems disconnected from the volume of work you're actually doing — that is a diagnosable and fixable problem. At Fintel Analytics, we have audited and remediated exactly this class of issue for fintech, payments, and e-commerce companies scaling through Series A and beyond, replacing opaque script-heavy pipelines with clean, tested, lineage-tracked model layers that cut cost and restore confidence in the data. If your stack has the symptoms described here, the fix is closer than it probably feels.