Materialized Views vs Incremental Models: The dbt Decision That Actually Matters

The single biggest dbt performance and cost lever is the materialization choice. Get it right and your warehouse costs are bounded, your downstream consumers get fresh data, and your incremental builds finish in minutes. Get it wrong and you’re either rebuilding terabytes of data on every run or you’re serving stale views that confuse the business.

This post walks through the decision tree we apply for every important model in a client engagement.

The five materializations#

dbt supports five materializations natively: view, table, incremental, ephemeral, and materialized view (depending on the warehouse). Each has specific characteristics.

View is the lightweight default. The model definition is registered as a SQL view; nothing is materialized. Every query against the view re-executes the SQL. This is the right choice for inexpensive transformations and for prototyping. It’s the wrong choice for anything compute-intensive.

Table materializes the model on each dbt run. The whole table is rebuilt from scratch. This is the right choice for small-to-medium datasets and for models whose logic doesn’t lend itself to incremental processing. It’s the wrong choice for large tables where the rebuild cost is substantial.

Incremental rebuilds only new or changed records. The model has a filter that identifies the new records and merges them into the existing table. This is the right choice for large fact tables, event-stream-derived models, and anything where the daily delta is much smaller than the total table.

Ephemeral doesn’t materialize anything. The model is inlined as a CTE in downstream queries. Useful for very small transformations that are only used by one downstream model. Rarely the right answer; most teams over-use ephemeral when table or view would be cleaner.

Materialized view (Snowflake, BigQuery, Redshift in different forms) is the warehouse-managed incremental materialization. The warehouse handles the freshness; you write the query. The trade-off is less control over how the refresh works.

The decision tree#

For each model, ask in order:

Is the transformation cheap? If yes, use a view. Don’t materialize what doesn’t need it.

Is the table small (under 10M rows or a few GB)? If yes, use table. The rebuild cost is acceptable; the operational simplicity is worth it.

Is the table large but the daily delta is small? If yes, use incremental. This is the bread-and-butter case for big fact tables.

Is the table large but the daily delta is also large? Reconsider the model. You might be aggregating at the wrong grain, or you might need a fundamentally different architecture.

Is the warehouse-managed materialized view available for this use case? Sometimes yes — for specific access patterns on Snowflake, BigQuery, or Redshift, the materialized view feature is more efficient than a manual incremental.

The incremental model patterns#

Once you’ve decided to use incremental, the design choices matter.

Unique key. Define what makes a row unique. Most incremental models merge on this key. Get it wrong and you’ll either miss records or duplicate them.

Incremental predicate. The filter that selects the new records. Typically based on a timestamp column. The predicate should be conservative enough that you don’t miss records due to late-arriving data.

Strategy. dbt supports several incremental strategies: merge, delete+insert, append, insert_overwrite. The right strategy depends on the warehouse and the access pattern. Snowflake and BigQuery work well with merge; Redshift sometimes benefits from delete+insert.

Full refresh capability. Maintain the ability to do a full refresh when needed. The incremental logic should be a layer on top of a query that, with --full-refresh, can rebuild the whole table from source.

The cost math#

For warehouses with consumption-based pricing (Snowflake, BigQuery, Redshift Serverless), the materialization choice has direct cost implications.

A 100GB fact table rebuilt daily as a table materialization costs the compute to process 100GB × 365 days = 36.5 TB of compute per year. The same table as incremental with a 1GB daily delta costs 1GB × 365 = 365 GB of compute. The cost difference is ~100x.

For typical mid-sized data platforms, getting the top 5-10 expensive models from table to incremental cuts the warehouse bill by 30-60%. The work is usually a few days of focused engineering and produces ongoing savings.

What we typically see at clients#

The most common patterns at client engagements:

Everything is a view. The team prototyped with views and never revisited. Queries against the views are slow; the warehouse bill is high because every query re-executes the transformations.

Everything is a table. The team learned about incremental but never implemented it. Large tables rebuild daily; the warehouse bill is dominated by the rebuilds.

Incremental without proper handling of late-arriving data. The incremental predicate is too aggressive; records that arrive late get missed. The downstream data is silently wrong.

Over-use of ephemeral. The CTEs nest deeply; query plans become unreadable; debugging is painful.

Where pdpspectra fits#

Our data engineering practice routinely audits dbt projects for materialization choices. The materialization work is usually high-leverage — a few days of focused engineering produces ongoing cost and performance benefits.

Related reading: the dbt advanced patterns post, the SQLMesh vs dbt post, and the modern data stack post.

---

dbt materialization is the highest-leverage optimization. Talk to our team about your data platform.