03. Materializations Explained
️ Controlling How Data is Saved
In DBT, Materializations are essentially the strategy DBT uses to physically build your model in the data warehouse. Since you only write SELECT statements, DBT needs to know whether it should wrap your query in a CREATE VIEW, CREATE TABLE, or something else.
1. Materializations in dbt + Snowflake
Since we are using Snowflake, dbt supports seven materialization strategies. Every model in your project will use one of these.
🟢 Core Materializations (Available on all databases)
| Materialization | What it does in the Warehouse | Pros | Cons | Best Used For |
|---|---|---|---|---|
| View (Default) | CREATE VIEW AS... | Very fast to build. Computes logic instantly at query time. | Slow to query if the underlying logic is incredibly complex. | Staging models, light transformations. |
| Table | CREATE TABLE AS... | Extremely fast to query for end-users (like Tableau). | Rebuilds the entire table from scratch every time it runs. | Final Mart models (dim_, fct_). |
| Incremental | INSERT INTO... | Only processes new records. Extremely cost-efficient. | Requires complex SQL configuration to define the "new" records. | Massive event streams (e.g., website clicks). |
| Ephemeral | Nothing (Acts as a CTE) | Does not clutter your database with intermediate tables. | Hard to debug since it doesn't physically exist in the DB. | Small, reusable logic snippets. |
❄️ Snowflake-Specific Materializations
| Materialization | What it does in the Warehouse | Pros | Cons | Best Used For |
|---|---|---|---|---|
| Materialized View | CREATE MATERIALIZED VIEW AS... | Physically stored + auto-refreshed by Snowflake. Sub-second reads. | Cannot use JOINs or most complex SQL. Continuous background compute cost. | Simple aggregate dashboards needing real-time freshness. |
| Dynamic Table | CREATE DYNAMIC TABLE ... TARGET_LAG='...' AS... | Supports full SQL (JOINs, CTEs, etc.) + auto-refreshed by Snowflake. Modern replacement for Materialized Views. | Requires target_lag configuration. Serverless compute cost. | Real-time or near-real-time pipelines without scheduling dbt run. |
🛠️ Advanced
| Materialization | What it does | Best Used For |
|---|---|---|
| Custom Materialization | You define the exact DDL/DML that dbt should execute using a Jinja macro. | Unique use cases like creating Snowflake Streams, Tasks, or external tables via dbt. |
2. Mini Demo: Same Data, Different Materializations
Before moving to the full hands-on pipeline, let us use one tiny raw table and see how each materialization changes what dbt creates in the warehouse.
Demo Raw Data
Assume Snowflake has this raw table:
CREATE OR REPLACE TABLE DEMO_DB.RAW.ORDERS (
ORDER_ID INT,
CUSTOMER_ID INT,
ORDER_DATE DATE,
STATUS STRING,
AMOUNT NUMBER(10,2)
);
INSERT INTO DEMO_DB.RAW.ORDERS VALUES
(1001, 1, '2025-12-01', 'completed', 1999.99),
(1002, 2, '2025-12-02', 'completed', 1299.00),
(1003, 3, '2025-12-03', 'pending', 3499.50);
Now create different dbt models from this same data.
Quick Note: What Are {{ }} Symbols?
In dbt, your model file is not only plain SQL. It can also contain Jinja, which dbt reads before sending the final SQL to Snowflake.
For now, remember these three beginner patterns:
| Jinja pattern | Meaning in dbt |
|---|---|
{{ config(materialized='view') }} | Tells dbt how to build this model |
{{ ref('stg_orders') }} | Points to another dbt model safely |
{{ this }} | Points to the current model being built |
So when you see:
{{ config(materialized='table') }}
It means:
dbt, please build this model as a table.
You will learn Jinja in detail later in Module 07: Jinja, Macros & Automation. In this lesson, we only use the Jinja needed for materializations.
Configuring a Model with config()
The config() function sets model-level behavior directly inside a dbt model. Use it here to choose and tune a materialization:
{{ config(
materialized='incremental',
unique_key='order_id',
tags=['sales', 'incremental']
) }}
SELECT *
FROM {{ ref('stg_orders') }}
Common options include materialized, unique_key, schema, alias, tags, transient, and target_lag. Use dbt_project.yml for defaults that apply to a folder or project, and use config() when one model needs a specific override.
Variables are introduced later in Module 07. They can be used inside config() when a setting needs to vary by environment:
{{ config(schema=var('target_schema', 'analytics')) }}
Example A: View Materialization
Use a view when the model is simple and should always show the latest source data.
models/bronze/stg_orders.sql
{{ config(materialized='view') }}
SELECT
ORDER_ID,
CUSTOMER_ID,
ORDER_DATE,
STATUS,
AMOUNT
FROM DEMO_DB.RAW.ORDERS
After dbt run, dbt creates a database view.
STG_ORDERS = saved query, not stored data
If raw orders change, the view reads the latest raw data when queried.
Example B: Table Materialization
Use a table when the model should be physically stored for faster reporting.
models/silver/silver_completed_orders.sql
{{ config(materialized='table') }}
SELECT
ORDER_ID,
CUSTOMER_ID,
ORDER_DATE,
AMOUNT
FROM {{ ref('stg_orders') }}
WHERE STATUS = 'completed'
After dbt run, dbt creates a physical table.
SILVER_COMPLETED_ORDERS = stored result set
Every time this model runs, dbt rebuilds the full table.
Example C: Incremental Materialization
Use incremental when the table is large and only new or changed rows should be processed after the first run.
models/silver/silver_completed_orders_incremental.sql
{{ config(
materialized='incremental',
unique_key='ORDER_ID'
) }}
SELECT
ORDER_ID,
CUSTOMER_ID,
ORDER_DATE,
AMOUNT
FROM {{ ref('stg_orders') }}
WHERE STATUS = 'completed'
{% if is_incremental() %}
AND ORDER_DATE > (
SELECT MAX(ORDER_DATE)
FROM {{ this }}
)
{% endif %}
On the first run, dbt loads all completed orders.
On later runs, dbt loads only completed orders newer than the latest ORDER_DATE already present in the target table.
This is important enough to get its own next lesson: 04. Incremental Materialization with Examples.
Example D: Ephemeral Materialization
Use ephemeral when the logic is small and reusable, but you do not want a separate object in the warehouse.
models/intermediate/int_completed_orders.sql
{{ config(materialized='ephemeral') }}
SELECT
ORDER_ID,
CUSTOMER_ID,
ORDER_DATE,
AMOUNT
FROM {{ ref('stg_orders') }}
WHERE STATUS = 'completed'
Then another model can use it:
models/gold/gold_order_summary.sql
{{ config(materialized='table') }}
SELECT
CUSTOMER_ID,
COUNT(*) AS TOTAL_COMPLETED_ORDERS,
SUM(AMOUNT) AS TOTAL_AMOUNT
FROM {{ ref('int_completed_orders') }}
GROUP BY CUSTOMER_ID
dbt does not create a physical int_completed_orders table or view. Instead, it compiles that SQL into the downstream model as a CTE.
Demo Result Summary
| Model | Materialization | What dbt creates |
|---|---|---|
stg_orders | view | A saved query |
silver_completed_orders | table | A physical table rebuilt every run |
silver_completed_orders_incremental | incremental | A physical table updated with only allowed new rows |
int_completed_orders | ephemeral | No database object; compiled as a CTE |
3. Incremental Materialization Needs a Separate Lesson
The incremental materialization is more advanced than view or table because you must tell dbt which rows are new or changed.
For beginner trainees, learn the basic idea here first:
view= saved querytable= rebuilt physical tableincremental= physical table updated with only new or changed rowsephemeral= temporary CTE inside another model
Then study the next topic, 04. Incremental Materialization with Examples, to see is_incremental(), unique_key, and --full-refresh in action.
️ 4. How to Configure Materializations
You can configure a model's materialization in two places.
Method A: Inside the Model File (Overrides)
Place this Jinja config block at the very top of your .sql file:
{{ config(materialized='table') }}
SELECT * FROM {{ ref('stg_users') }}
Method B: Inside dbt_project.yml (Global Scale)
This is the Best Practice. You can configure entire folders at once from the root of your project:
models:
my_project:
staging:
+materialized: view # Every file in /models/staging/ becomes a view
marts:
+materialized: table # Every file in /models/marts/ becomes a physical table
Summary
| Materialization | One-line Summary |
|---|---|
| View | Saved query — fast to build, always fresh, slow to query. |
| Table | Physical copy — slow to build, lightning-fast to query. |
| Incremental | Append-only — processes only new rows. Best for massive fact tables. |
| Ephemeral | CTE injection — no database object created. |
| Materialized View | Auto-refreshed physical copy — limited SQL, continuous compute cost. |
| Dynamic Table | Auto-refreshed physical copy — full SQL support, modern Snowflake feature. |
| Custom | You write the DDL/DML macro yourself for unique use cases. |
Most modern data teams use Views for their base staging layer (since they just clean data), Tables for their final Mart layer (so dashboards load instantly), and Dynamic Tables for real-time pipelines in Snowflake.