In this lesson, you will learn how to select the right materialization strategy to balance query performance speed against the costs of data storage in your dbt models.
When you create a model in dbt, you need to decide how that model exists in your data warehouse. Materialization tells dbt how to turn your SQL code into a physical object. Choosing the right one is the key to optimizing your pipeline’s cost and efficiency.
The Three Core Strategies
-
View (
view): The simplest strategy. dbt creates a virtual table by running the SQL every time you query it.- Pros: Zero storage cost; data is always "fresh."
- Cons: Can be slow for complex transformations because the calculation happens on every request.
-
Table (
table): dbt runs the SQL and saves the result as a physical table in your warehouse.- Pros: Very fast for end-users to query because the heavy lifting is done once during the dbt run.
- Cons: Consumes storage space and requires time to rebuild if your data changes.
-
Ephemeral (
ephemeral): This model is never actually created in your database. Instead, dbt injects the SQL of this model into any other model that references it as a Common Table Expression (CTE).- Pros: Keeps your database clean; great for simple, reusable logic that doesn't need its own storage footprint.
How to Configure Them
You define the strategy at the top of your SQL model file using a configuration block:
{{ config(materialized='table') }}
SELECT
user_id,
sum(order_amount) as total_spent
FROM {{ ref('stg_orders') }}
GROUP BY 1
If you don’t specify a strategy, dbt defaults to a view. As your project grows, you’ll find that using view for simple logic, table for heavy aggregations, and ephemeral for code modularity keeps your data platform performant and cost-effective.
Key Takeaway: Use views to avoid storage costs for simple logic, tables to accelerate complex queries by caching results, and ephemeral models to keep your database tidy while maintaining modular, reusable SQL code.