Materialized Views
So far, we have covered view (a saved query) and table (a physical copy of data).
There is a third, hybrid materialization strategy supported by dbt: the Materialized View.
It combines the performance of a physical table with the automatic freshness of a view. However, because its behavior is entirely dependent on the underlying Data Warehouse (Snowflake, BigQuery, etc.), it must be used with caution.
Topics Covered
- What is a Materialized View?
- Configuring it in dbt
- Auto-Refresh Behavior
- Cost Implications
- When to use them
1. What is a Materialized View?
A Materialized View (MV) is a pre-computed dataset derived from a query and stored on disk.
- Like a Table, the data is physically stored, meaning queries against it are blazing fast (no complex joins need to be re-computed at read time).
- Like a View, the data is automatically kept fresh by the data warehouse.
In Snowflake, if an underlying base table changes, a background service automatically updates the Materialized View so that it is always perfectly in sync.
2. Configuring it in dbt
To build a Materialized View, simply change the materialized configuration in your .sql file.
{{ config(
materialized='materialized_view'
) }}
SELECT
store_city,
COUNT(order_id) as total_orders,
SUM(order_amount) as total_revenue
FROM {{ ref('fct_sales') }}
GROUP BY store_city
When you run dbt run, dbt will execute:
CREATE OR REPLACE MATERIALIZED VIEW ... AS SELECT ...
Important dbt Behavior
Because the Data Warehouse (e.g., Snowflake) automatically refreshes the MV in the background, dbt does not need to refresh it.
In fact, if you run dbt run on a Materialized View that already exists, dbt does nothing! It skips the execution because the warehouse is already handling the data freshness. dbt will only recreate the MV if you change the actual SQL code inside the model.
3. Auto-Refresh Behavior and Cost Implications
The biggest trap Data Engineers fall into is thinking Materialized Views are free performance boosters. They are not.
In Snowflake, the background service that constantly monitors the base table (fct_sales) and updates the MV (total_revenue_by_city) consumes Serverless Compute Credits.
If your Indian retail company processes 50,000 orders an hour, the base table is constantly changing. Snowflake will constantly wake up the background compute to update the Materialized View. At the end of the month, your bill will be massive.
4. When to Use Them vs Tables vs Incremental
Because of the high background compute cost, you should only use Materialized Views under specific conditions.
| Materialization | When to Use It | Performance | Cost |
|---|---|---|---|
| Table | Most models. Data is built once a day/hour via dbt scheduler. | Fast | Low (Compute used only during build) |
| Incremental | Massive tables where full rebuilds take too long. | Fast | Low (Compute used only during build) |
| Materialized View | When business users require sub-second dashboard load times AND real-time data freshness, and you are willing to pay for it. | Fastest | High (Continuous background compute) |
Best Practice: Do not use Materialized Views as intermediate models in your DAG. Only use them at the very end of your DAG (the presentation layer) for specific aggregate dashboards that require ultra-low latency.
Summary
- A Materialized View physically stores the results of a query on disk for maximum read performance.
- The Data Warehouse (not dbt) automatically refreshes the MV when the underlying base tables change.
- Configure them using
materialized='materialized_view'. - They incur continuous background compute costs in platforms like Snowflake.
- Use them sparingly, primarily for high-priority, real-time aggregate dashboards.