dbt Models Materialised as Table in BigQuery: Fix the Cost Leak

Over-materialised dbt models are a silent, compounding cost leak in BigQuery. Every model configured as materialization='table' that runs on a schedule re-creates its full result set from scratch — scanning every upstream byte each time. If that model is queried infrequently but the table is large, you are paying for an expensive rebuild that nobody needed. Here is how to find every offending model in your project in under five minutes and fix it for good.

Why Over-Materialised dbt Models Quietly Wreck Your BigQuery Bill

In dbt, the default materialization for most models is table. That default made sense when your project was small and the tables were thin. The problem is that nobody revisits that decision as the project grows. A staging model that scanned 50 MB on day one might be scanning 40 GB eighteen months later — still rebuilding the entire thing on every dbt run, still configured as materialization='table', still sitting there in the dbt project untouched because it works and nobody has looked at the cost.

This is a pattern we see repeatedly in early-stage analytics engineering teams. The dbt project was scaffolded quickly, sensible defaults were accepted, and then the focus moved to shipping new models rather than reviewing old ones. Meanwhile, the BigQuery bill crept upward quarter by quarter.

The core issue is a mismatch between how often a model is queried and how often it is rebuilt. A dbt model configured as table that is rebuilt on every nightly run but only queried once a week by a single analyst is paying for five unnecessary rebuilds per week. Multiply that across a project with forty or fifty intermediate models and you have a substantial, entirely avoidable cost.

A related failure mode is the opposite: models that legitimately should be table or incremental but were left as view, causing every downstream query to re-execute the full transformation chain. Both errors are diagnosable. This post focuses on the former — over-materialisation — because it is the more common cost driver on BigQuery's on-demand billing model.

As noted in dbt Labs' own cost guidance (2025), most minor query optimisations are insignificant compared to choosing the correct materialisation strategy — every downstream query on a view recomputes the full logic, which is "extremely dangerous over complex joins or massive datasets." The same logic applies in reverse: rebuilding a table unnecessarily is equally destructive.

📺 Watch: DBT Materialisations | Data Build Tool | Table Vs View vs Ephemeral

How to Find Over-Materialised Models in BigQuery

You need two things: the list of tables your dbt project owns, and the query history that tells you how often those tables are actually read. BigQuery's INFORMATION_SCHEMA gives you both.

Step 1 — Identify every dbt-managed table in your project

Run this query against your BigQuery project. It pulls every table in your analytics dataset along with its size, the last time it was modified, and the last time it was queried.

SELECT
  t.table_schema AS dataset,
  t.table_name,
  t.table_type,
  ROUND(SUM(ps.total_logical_bytes) / POW(1024, 3), 3) AS size_gb,
  MAX(t.creation_time) AS created_at,
  MAX(t.last_modified_time) AS last_modified
FROM
  `region-eu`.INFORMATION_SCHEMA.TABLES t
LEFT JOIN
  `region-eu`.INFORMATION_SCHEMA.TABLE_STORAGE ps
  ON t.table_schema = ps.table_schema
  AND t.table_name = ps.table_name
WHERE
  t.table_type = 'BASE TABLE'
  AND t.table_schema IN ('dbt_prod', 'analytics') -- replace with your dataset names
GROUP BY 1, 2, 3
ORDER BY size_gb DESC;

Replace region-eu with your BigQuery region (e.g. region-us) and update the dataset names to match your production dbt environment. This gives you a ranked list of all materialised tables, largest first — your cost surface at a glance.

Step 2 — Cross-reference against actual read frequency

Now find out which of those tables are actually being read, and how often:

SELECT
  referenced_table.dataset_id AS dataset,
  referenced_table.table_id AS table_name,
  COUNT(*) AS times_referenced_last_30d,
  SUM(total_bytes_processed) / POW(1024, 3) AS total_gb_billed
FROM
  `region-eu`.INFORMATION_SCHEMA.JOBS_BY_PROJECT,
  UNNEST(referenced_tables) AS referenced_table
WHERE
  creation_time >= TIMESTAMP_SUB(CURRENT_TIMESTAMP(), INTERVAL 30 DAY)
  AND job_type = 'QUERY'
  AND state = 'DONE'
  AND referenced_table.dataset_id IN ('dbt_prod', 'analytics') -- replace as above
GROUP BY 1, 2
ORDER BY times_referenced_last_30d ASC;

The rows at the top of this result — lowest times_referenced_last_30d — are your candidates for dematerialisation. A large table referenced zero or one times in thirty days and rebuilt nightly is a textbook case for converting to a view.

Step 3 — Find the rebuild cost for each model

Cross-reference the tables flagged in step 2 against your dbt job history to see how much each rebuild actually costs:

SELECT
  destination_table.dataset_id AS dataset,
  destination_table.table_id AS table_name,
  COUNT(*) AS times_rebuilt_last_30d,
  ROUND(SUM(total_bytes_processed) / POW(1024, 3), 2) AS total_gb_scanned_rebuilds,
  ROUND(SUM(total_bytes_processed) / POW(1024, 3) * 6.25 / 1024, 4) AS est_cost_usd
FROM
  `region-eu`.INFORMATION_SCHEMA.JOBS_BY_PROJECT
WHERE
  creation_time >= TIMESTAMP_SUB(CURRENT_TIMESTAMP(), INTERVAL 30 DAY)
  AND job_type = 'QUERY'
  AND state = 'DONE'
  AND statement_type = 'CREATE_TABLE_AS_SELECT'
  AND destination_table.dataset_id IN ('dbt_prod', 'analytics') -- replace as above
GROUP BY 1, 2
ORDER BY est_cost_usd DESC;

Note: the $6.25 per TB rate in the formula above is BigQuery's standard on-demand price as of 2026 — adjust if you are on a flat-rate or edition-based commitment. This query isolates CREATE_TABLE_AS_SELECT jobs, which is the BigQuery statement type produced by every dbt table model run. The output ranks your models by what each rebuild is costing you in real dollars.

Combine the output from steps 2 and 3 and you have a prioritised list: models with high rebuild cost and low read frequency. Those are the ones to fix first.

Running that query pair manually once is fine. Checking every model in a project on a recurring basis is not — fintel-scan is a free, open-source CLI (MIT licence) that runs this check and fourteen others locally with no warehouse connection: uvx fintel-scan.

The Decision Framework: Table, View, or Incremental?

Once you have your candidates, the fix decision is mechanical. Walk each model through this framework:

Convert to view when:

• The model's upstream tables are already materialised (so re-execution is cheap)
• The model is queried fewer than five times per day
• The result set does not benefit from being pre-computed (no heavy aggregation, no complex window functions)
• The model exists purely to filter or rename columns from a staging layer

Convert to incremental when:

• The model processes a large, append-only dataset (events, transactions, logs)
• Only a fraction of the data changes on each run
• The full rebuild takes more than a few seconds and runs more than once per day
• You have a reliable updated_at or event timestamp column to use as the incremental predicate

Leave as table when:

• The model involves complex aggregations or joins that are expensive to re-run at query time
• It is queried frequently by a BI tool or dashboard with no caching layer
• Downstream models depend on it being materialised for performance

For anything in that third category, make sure the table is properly partitioned and clustered — if it is not, the rebuild itself is more expensive than it needs to be. See our post on BigQuery Partitioning & Clustering Mistakes Killing Your Bill for how to check that.

In practice, the majority of staging and intermediate models in a well-structured dbt project should be view. The table materialisation should be reserved for the mart layer where BI tools land. Most projects we see in the field have this inverted.

What the Fix Looks Like in dbt

Changing materialisation is a one-line config change:

-- models/intermediate/int_payments_enriched.sql
{{ config(
    materialized='view'
) }}

SELECT ...

For a bulk change across an entire folder, use dbt_project.yml:

models:
  your_project_name:
    staging:
      +materialized: view
    intermediate:
      +materialized: view
    marts:
      +materialized: table

After applying the change, run dbt run --select staging+ --full-refresh to ensure the old materialised tables are cleaned up. BigQuery will not automatically drop a table when a dbt model is converted to a view — the old table will continue to consume storage until it is explicitly removed. Check for and drop those ghost tables using the queries in our post on Orphaned dbt Models in BigQuery: Find & Drop Ghost Tables.

For models you are converting to incremental, the change requires slightly more care. You need to define an is_incremental() filter and choose your unique_key. See our detailed writeup on dbt Incremental Models Strategy: When and How to Use Them for the full implementation pattern.

How to Stop This Recurring

The diagnostic above is useful for fixing the current state. The real goal is to build a process that catches materialisation drift before it compounds.

In your dbt project, enforce defaults at the folder level. As shown above, set +materialized: view for staging and intermediate layers in dbt_project.yml. Any engineer who wants to materialise a model as a table has to do it explicitly in the model config — which is the right forcing function. Convenience defaults should favour the cheaper option.

Add a dbt test that alerts on unexpectedly large model sizes. This is not a native dbt test, but you can write a custom singular test that queries INFORMATION_SCHEMA.TABLE_STORAGE and fails if any model in a specified folder exceeds a size threshold you define. The alert surfaces in your CI pipeline before the model ships to production.

Review the INFORMATION_SCHEMA.JOBS output weekly. Pull the step 3 query into a scheduled Looker or Holistics dashboard. Sort by est_cost_usd descending. Any model that appears in the top ten consistently is a candidate for architectural review, not just a config change.

Tag dbt models with expected materialisation intent. Use dbt's meta config to record why a model is materialised as a table. If a model has no documented reason, that is a flag:

{{ config(
    materialized='table',
    meta={'materialisation_rationale': 'High-frequency BI queries, mart layer'}
) }}

This sounds like overhead but takes thirty seconds per model and pays dividends when a new engineer inherits the project and tries to understand what is safe to change.

By implementing efficient incremental models in dbt, one organisation reduced its BigQuery costs by $20,000 per month (2025). That is not a theoretical saving — it is the direct result of auditing materialisation choices and converting the right models to incremental. The same discipline applied to unnecessary table materialisations across a staging and intermediate layer typically cuts a meaningful fraction from the monthly compute bill within a single sprint.

Frequently Asked Questions

Q: How do I know if a dbt model is currently materialised as a table in BigQuery?

A: Query INFORMATION_SCHEMA.TABLES in your analytics dataset and filter on table_type = 'BASE TABLE'. Every dbt model configured as materialization='table' or materialization='incremental' will appear as a BASE TABLE. Views appear as VIEW. Cross-reference with your dbt_project.yml and model-level configs to confirm which are intentional table materialisations.

Q: Is it always cheaper to use a view instead of a table in BigQuery?

A: Not always. A view re-executes its full SQL on every query, so if a model is hit dozens of times per hour by dashboards, a view can cost more than a table. The key variable is query frequency versus rebuild frequency. If a model is queried rarely but rebuilt constantly, dematerialise it. If it is queried constantly by downstream consumers, keep it as a table and focus on partitioning and clustering instead.

Q: What is the risk of converting a dbt table model to a view?

A: The main risk is query latency: dashboards and downstream models that previously hit a pre-computed table will now re-execute the full SQL at query time. Test the view's query time before switching production traffic. For complex transformations with multi-second runtimes, a view may degrade user experience even if it saves cost. Always benchmark before deploying to production.

Q: How do I clean up the old BigQuery table after converting a dbt model to a view?

A: dbt does not automatically drop the old table. After converting the model config and running dbt run, the old table will still exist in BigQuery consuming storage. You need to drop it manually using DROP TABLE project.dataset.model_name in the BigQuery console, or automate the cleanup using the post-hook pattern in dbt. See our post on orphaned dbt models for a detection query that will find all such ghost tables.

Q: Can I set different materialisation defaults for different dbt folders?

A: Yes. In dbt_project.yml, you can set +materialized at the folder level under the models: key. A common pattern is to set view for staging and intermediate layers and table for marts. Individual models can override this with a {{ config(materialized='...') }} block. Folder-level defaults are the most effective way to prevent accidental over-materialisation as the project grows.

Over-materialised dbt models are one of the most fixable sources of BigQuery cost waste — diagnosable in under five minutes with the queries above, and correctable with a one-line config change. At Fintel Analytics, we have audited dbt projects for growth-stage fintech and e-commerce companies where unnecessary table materialisations across the staging and intermediate layers were responsible for a third or more of the monthly compute bill — and where a single targeted sprint brought that cost permanently under control. If your BigQuery bill has been climbing and your dbt project has never had a materialisation review, that is exactly the kind of problem our team fixes every week.