Skip to main content

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)​

MaterializationWhat it does in the WarehouseProsConsBest 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.
TableCREATE 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_).
IncrementalINSERT INTO...Only processes new records. Extremely cost-efficient.Requires complex SQL configuration to define the "new" records.Massive event streams (e.g., website clicks).
EphemeralNothing (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​

MaterializationWhat it does in the WarehouseProsConsBest Used For
Materialized ViewCREATE 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 TableCREATE 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​

MaterializationWhat it doesBest Used For
Custom MaterializationYou 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 patternMeaning 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​

ModelMaterializationWhat dbt creates
stg_ordersviewA saved query
silver_completed_orderstableA physical table rebuilt every run
silver_completed_orders_incrementalincrementalA physical table updated with only allowed new rows
int_completed_ordersephemeralNo 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 query
  • table = rebuilt physical table
  • incremental = physical table updated with only new or changed rows
  • ephemeral = 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​

MaterializationOne-line Summary
ViewSaved query — fast to build, always fresh, slow to query.
TablePhysical copy — slow to build, lightning-fast to query.
IncrementalAppend-only — processes only new rows. Best for massive fact tables.
EphemeralCTE injection — no database object created.
Materialized ViewAuto-refreshed physical copy — limited SQL, continuous compute cost.
Dynamic TableAuto-refreshed physical copy — full SQL support, modern Snowflake feature.
CustomYou 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.