Choosing the Right Materialization
In dbt, a "Materialization" defines exactly how a SQL model is built in the data warehouse.
The choice of materialization is the single biggest factor in the performance and cost of your dbt project. If you choose the wrong materialization, your project will either take 4 hours to compile, or your Tableau dashboards will take 5 minutes to load.
As a Data Engineer, you must balance Build Time (the time it takes dbt run to execute) against Query Time (the time it takes an analyst to query the final data).
Topics Covered
- The Four Core Materializations
- The Trade-off Matrix
- Best Practice Rules
1. The Four Core Materializations
1. Ephemeral (materialized='ephemeral')
- What it is: dbt does not build anything in the database. Instead, it injects the SQL code directly into downstream models as a Common Table Expression (CTE).
- Pros: Zero storage cost. Zero build time.
- Cons: If 5 downstream models rely on it, the code is executed 5 separate times, wasting massive compute. You cannot query an ephemeral model directly in Snowflake.
2. View (materialized='view')
- What it is: dbt runs a
CREATE VIEWstatement. A view does not store data; it stores the SQL logic. The data is calculated "on the fly" every time someone queries it. - Pros: Fast build time (dbt just saves the logic). Zero storage cost. Always shows the latest raw data.
- Cons: Very slow Query Time. Every time the CEO opens Tableau, Snowflake has to recalculate the massive joins from scratch.
3. Table (materialized='table')
- What it is: dbt runs a
CREATE TABLE AS SELECT. It calculates the data once during the dbt run and saves the physical result to the hard drive. - Pros: Extremely fast Query Time. Tableau dashboards load instantly.
- Cons: Slow Build Time. If the table has 5 billion rows, dbt must drop the table and rebuild all 5 billion rows every night. High storage cost.
4. Incremental (materialized='incremental')
- What it is: dbt looks at the existing table, finds only the new rows that arrived today, and inserts them into the table.
- Pros: The ultimate balance. Fast Build Time (only processing 1 day of data) AND fast Query Time (it's a physical table).
- Cons: Extremely complex to configure. You must write custom
is_incremental()logic and manage late-arriving data.
2. The Trade-off Matrix
| Materialization | Build Time (dbt run) | Query Time (Tableau) | Storage Cost | Complexity |
|---|---|---|---|---|
| Ephemeral | Instant | Very Slow | None | Low |
| View | Fast | Slow | None | Low |
| Table | Slow | Fast | High | Low |
| Incremental | Fast | Fast | High | High |
| Materialized View | Instant (auto) | Fastest | Medium | Low |
| Dynamic Table | Instant (auto) | Fast | Medium | Medium |
3. Best Practice Rules
If you are building the pipeline for an Indian retail company, follow these rules:
- Staging Models (
stg_): ALWAYS use Views.- Why? Staging models just rename columns and cast types. They do very little math. Re-calculating them on the fly is cheap.
- Intermediate Models (
int_): Start with Views. Upgrade to Ephemeral if the logic is incredibly simple and only used by ONE downstream model. Upgrade to Tables if the logic contains a massiveJOINorGROUP BYthat takes a long time to compute. - Marts Dimension Tables (
dim_): Use Tables.- Why? Dimension tables (like
dim_customers) are usually small (e.g., 500,000 rows). Rebuilding a 500k row table every night takes 3 seconds in Snowflake, so just build it as a Table for maximum query speed.
- Why? Dimension tables (like
- Marts Fact Tables (
fct_): Start with Tables. Upgrade to Incremental ONLY when thedbt runstarts taking too long.- Why? Fact tables (like
fct_transactions) grow infinitely. Rebuilding 10 million rows is fine. Rebuilding 10 billion rows is expensive. Wait until it hurts, then refactor to Incremental.
- Why? Fact tables (like
- Real-Time Dashboards: Use Dynamic Tables with a
target_lagof 5–10 minutes.- Why? No need to schedule frequent
dbt runcalls. Snowflake handles the refresh automatically. Supports full SQL including JOINs.
- Why? No need to schedule frequent
- Simple Aggregate Dashboards: Consider Materialized Views ONLY for very simple, single-table aggregations where real-time freshness is required.
Summary
- Ephemeral: Good for lightweight, single-use logic.
- View: Good for
stagingmodels. Fast to build, slow to query. - Table: Good for
martsmodels. Slow to build, lightning-fast to query. - Incremental: Necessary for massive
facttables to save compute costs. - Materialized View: Auto-refreshed by Snowflake, but limited SQL (no JOINs).
- Dynamic Table: Auto-refreshed by Snowflake with full SQL support. Modern choice for real-time pipelines.
- Custom: Write your own DDL/DML for Snowflake Streams, Tasks, External Tables, etc.