dbt Intermediate Models in BigQuery: The Materialisation Mistake Costing You

The Wrong Materialisation on Your dbt Intermediate Models Is Costing You More Than You Think

Materialising dbt intermediate models as tables in BigQuery instead of views — or vice versa, without a deliberate strategy — is one of the most common and costly mistakes on growth-stage data stacks. When an intermediate model is persisted as a full table and then scanned by every downstream model that references it, you are paying BigQuery's compute price on data that has already been computed and could have been served far more cheaply. In a well-structured dbt project, intermediate layer materialisation deserves as much attention as your mart layer — and in practice, it almost never gets it.

Most teams build their staging and mart layers with care. Staging models are set to views, marts are set to tables — and somewhere in between sits a collection of intermediate models that were configured once during initial build and never revisited. As the project grows, those intermediates get referenced in more places, run more frequently, and quietly balloon your BigQuery bill. By the time finance flags it, the cost has been accumulating for months.

This post explains exactly what goes wrong, how to detect it across your dbt project, and how to make the right materialisation call for each intermediate model — with a decision framework grounded in real delivery experience.

What Is the dbt Intermediate Layer and Why Does Materialisation Matter?

In a canonical dbt project structure, models are organised into three logical layers: staging, intermediate, and marts. The staging layer cleans and renames raw source data — one model per source table, materialised as a view because it is never the final consumer-facing product and does not need to be persisted. The marts layer holds your business-ready fact and dimension tables — materialised as tables because downstream BI tools and analysts query them directly and repeatedly.

The intermediate layer sits in between. Its job is to join, aggregate, or reshape data from the staging layer into logical building blocks that multiple mart models can share. This is where payment events get joined to customers, where session activity gets attributed to channels, where ledger entries get normalised before appearing in financial summaries.

The materialisation decision here is not obvious, and that is the problem. Most teams default to one of two positions:

• They copy the staging default and materialise everything as a view — which means every time a downstream mart runs, it re-executes all the upstream intermediate logic, re-scanning the source tables from scratch.
• They copy the mart default and materialise everything as a table — which means every dbt run writes the full intermediate result set into BigQuery storage, even when it is only used once or twice per day.

Neither of these is wrong in isolation. Both become expensive at scale, depending on the shape of your data and how your pipeline runs.

<br>

The cost arithmetic is simple. <br> <br>

Under on-demand pricing, BigQuery charges $6.25 per TiB of data scanned, with the first 1 TiB free each month. A query scanning 100 GB costs approximately $0.61. That sounds manageable — until you have twelve mart models all referencing the same intermediate view, each triggering a full re-scan of a 200 GB join result, running three times a day. That is $13.25 per day from a single intermediate model. Multiply across a project with ten or fifteen poorly-materialised intermediates and you have a meaningful monthly cost with no business value attached.

The inverse problem — over-materialising as tables — is equally damaging but shows up differently. When an intermediate model is materialised as a table but is only referenced by a single downstream model, you are paying for BigQuery active storage at $0.02 per GB per month, plus the compute cost of writing the table on every dbt run, for no scan-reduction benefit. You have added latency and storage cost without eliminating a single unnecessary scan.

---

📺 Watch: What are "intermediate" models in dbt?

---

How to Detect Materialisation Waste in Your dbt Intermediate Layer

The first place to look is your dbt project's dbt_project.yml. If your intermediate folder has a blanket materialisation setting — either view or table applied to all models — that is almost certainly wrong for at least some of them. Intermediate models have different characteristics: some are referenced by ten downstream models, some by one. Some join large transaction tables; some join small reference data. A blanket setting cannot account for that variation.

From the dbt side, run this query against your project's manifest to identify how many downstream models reference each intermediate:

-- In your dbt manifest.json (or via metadata tooling)
-- Count direct downstream dependents per intermediate model
SELECT
  node_id,
  model_name,
  materialisation,
  COUNT(downstream_node) AS downstream_references
FROM dbt_manifest_parsed
WHERE layer = 'intermediate'
GROUP BY 1, 2, 3
ORDER BY downstream_references DESC

In practice, most teams do not have manifest-parsing tooling set up, so the equivalent exercise is to grep your dbt project's ref() calls:

grep -r "ref('int_" models/ | awk -F: '{print $2}' | sort | uniq -c | sort -rn

Any intermediate model with more than three or four downstream references is a strong candidate for table materialisation — especially if it involves a large join. Anything referenced by just one mart model almost certainly does not need to be a table.

From the BigQuery side, cross-reference your INFORMATION_SCHEMA.JOBS_BY_PROJECT data to see how frequently each intermediate table or view is being scanned and at what volume. If you have already set up cost attribution tooling, this is straightforward — if not, see our guide on BigQuery INFORMATION_SCHEMA cost attribution for how to build that visibility.

A pattern we see repeatedly in our work with early-stage companies: intermediate models that were originally materialised as views get left that way as the downstream mart layer grows. Nobody revisits them because they are "just intermediate" — they are the invisible middle of the DAG. By the time we audit the project, a single intermediate view is being re-executed fifteen times per day, scanning 500 GB on each run, because six mart models and several BI tool queries all touch it independently.

The Decision Framework: View, Table, or Incremental?

This is the question that actually matters, and it does not have a single right answer. Here is the framework we apply when auditing or building an intermediate layer:

Materialise as a VIEW when:

• The model is referenced by only one or two downstream models
• The underlying source tables are small (under 5–10 GB after filtering)
• The model runs infrequently (once a day or less)
• The transformation logic is lightweight — renaming, casting, simple filtering
• The source tables are themselves already materialised as tables (not views), so the view does not re-trigger a chain of upstream scans

Materialise as a TABLE when:

• The model is referenced by three or more downstream models
• The model involves an expensive join (large fact table to large dimension table)
• The model's upstream sources are themselves large views that would be re-scanned
• The model runs multiple times per day and downstream queries are time-sensitive
• Eliminating redundant scans is worth the storage and write cost

Materialise as INCREMENTAL when:

• The model processes append-only or time-windowed data (events, transactions, logs)
• The full refresh cost is prohibitive but you need a persisted table
• You have late-arriving data patterns that require a merge window (though be careful here — see our post on late-arriving data in dbt BigQuery for the edge cases that break incremental logic)

The incremental option for intermediates is underused. Most teams reserve incremental materialisation for their mart layer, but a high-fan-out intermediate processing millions of transaction events per day is exactly where an incremental model pays for itself. The compute savings compound across every downstream mart that references it.

If you're working through a materialisation audit and want a second opinion on the right strategy for your project's shape, explore how Fintel Analytics approaches this — we work with growth-stage teams globally to audit and restructure dbt projects for both cost and maintainability.

The Fan-Out Scan Problem: When Views Inside Views Destroy Your Budget

There is a specific failure mode in intermediate-layer design that is worth naming explicitly: nested view chains. This happens when an intermediate model materialised as a view references a staging model also materialised as a view — which in turn scans a raw source table. Every time a mart model references that intermediate, BigQuery must resolve the full chain: mart → intermediate view → staging view → raw source table scan.

If your staging models are views (which is idiomatic and correct), and your intermediate models are also views, then a mart model referencing three intermediate views is effectively re-scanning your raw source tables three times — once per intermediate. Multiply that by the number of marts referencing those intermediates, and the scan volume grows non-linearly.

We encountered this exact pattern at a Series A fintech running event-level payment data. Their raw events table was 800 GB. Three intermediate views each referenced it. Four mart models each referenced multiple intermediate views. On every dbt run, that single raw table was being scanned nine times — over 7 TB of data processed per run, running four times a day. Restructuring the intermediate layer so that a single, well-partitioned table absorbed all the computation from the raw events table, with downstream views reading from that intermediate table instead, reduced their daily bytes scanned by over 80% on that slice of the pipeline alone.

This is the argument for materialising intermediate models as tables that sit at the boundary between raw/staging data and mart logic. They act as a computed "landing zone" — expensive to build once, cheap to read many times.

What a dbt Intermediate Layer Audit Actually Looks Like

When we run a materialisation audit on a client's dbt project, we work through five questions for each intermediate model:

1. How many models reference it? Pull from the manifest or grep ref() calls. More than three downstream references almost always justifies a table.
2. How large is the data it processes? Check INFORMATION_SCHEMA.JOBS_BY_PROJECT for average bytes scanned when the model runs. Over 50 GB per run strongly favours table materialisation.
3. How many times per day does it run? A view that runs once a day is cheap. A view that runs ten times a day because multiple BI queries trigger it is not.
4. What does its upstream chain look like? If it sits on top of views, the scan chain multiplies. If it sits on top of tables, a view is more defensible.
5. Does the data grow monotonically? If yes, incremental is on the table.

This five-question framework takes about twenty minutes per model once you have the INFORMATION_SCHEMA query results in front of you. A typical growth-stage project has ten to thirty intermediate models. A half-day audit, done once, produces a materialisation map that can cut monthly BigQuery compute spend significantly.

One outcome from a recent engagement: a global payments company had fourteen intermediate models all set to views as a project-wide default. After a materialisation audit, we converted six to tables and two to incremental models. The remaining six stayed as views — legitimately. Monthly BigQuery compute costs on those models dropped by approximately 65% within the first billing cycle after deployment. Equally important: downstream mart build times dropped, which improved dashboard freshness without changing the schedule.

Frequently Asked Questions

Q: Should dbt intermediate models be views or tables in BigQuery?

A: It depends on how many downstream models reference them and how large the data they process is. As a rule of thumb: materialise as a view if only one or two models reference the intermediate and the source data is small; materialise as a table if three or more downstream models reference it or if it involves an expensive join on large data. A blanket setting applied to the entire intermediate layer is almost always wrong for at least some models.

Q: Why are my BigQuery costs high even though I'm using views in dbt?

A: Views in BigQuery do not cache results — they re-execute their SQL every time they are queried. If multiple downstream models reference the same view, each one triggers a full re-scan of the underlying tables. This is particularly expensive when intermediate views sit on top of staging views, creating a chain of scans back to the raw source tables. Converting high-fan-out intermediate views to tables eliminates this repeated scan cost.

Q: When should I use incremental models at the dbt intermediate layer?

A: Use incremental materialisation at the intermediate layer when the model processes a large, append-only dataset (event logs, transaction records) and the cost of a full refresh on every run is prohibitive. If your intermediate model is processing millions of rows of event data and is referenced by several mart models, an incremental strategy that only processes new records on each run can deliver significant compute savings while still providing a persisted table for downstream consumption.

Q: How do I find which dbt intermediate models are causing the most BigQuery cost?

A: Query INFORMATION_SCHEMA.JOBS_BY_PROJECT in BigQuery, filter for jobs where the statement type is SELECT or CREATE_TABLE_AS_SELECT, and group by the query label or job ID to correlate with dbt model runs. dbt applies job labels in the format dbt_<model_name> which makes attribution tractable. Sort by total bytes processed descending to identify your most expensive models.

Q: Does materialising more dbt models as tables always reduce BigQuery costs?

A: No — over-materialisation is also a trap. Materialising intermediate models as tables adds active storage cost ($0.02 per GB per month in 2026) and incurs a write cost on every dbt run. If a model is only referenced once and processes a small dataset, keeping it as a view is cheaper. The goal is to materialise at the right point in the DAG — where the data is large enough and referenced often enough that paying once to store it is cheaper than paying repeatedly to scan it.

---

The dbt intermediate layer is where most BigQuery cost waste quietly compounds — not in a single dramatic query, but in dozens of redundant scans that accumulate across every pipeline run, every dashboard refresh, and every ad hoc query that touches an unoptimised view chain. At Fintel Analytics, we have run materialisation audits on data stacks across fintech, e-commerce, and financial services — and the intermediate layer is consistently where the largest cost-to-fix ratio sits. If your BigQuery bill is growing faster than your data volume justifies, and your team has never deliberately audited intermediate model materialisation, that gap is almost certainly part of the answer. Get in touch with our team and we will tell you exactly what we find.