Why Materialization is the Most Important Decision of a dbt Model

Every dbt model must answer a question before even writing the first line of SQL: where and how will the result be physically stored? The answer determines performance, cost, data freshness and operational complexity.

dbt provides four main materializations: view, table, incremental e snapshot. Each one is useful a specific purpose, and choosing the wrong one can mean queries that take minutes instead of seconds, or computing costs increased tenfold for no reason.

This module explores each materialization in depth: internal mechanism, configuration, advanced strategies, and the criteria for choosing the right one in the real context.

View: Maximum Freshness, Zero Storage

La view is the default materialization in dbt. When you run dbt run on a model configured as a view, dbt creates (or replaces) a view SQL in the warehouse. No data is copied - the query is re-executed every time someone query the view.

-- Configurazione in dbt_project.yml (default globale)
models:
  my_project:
    staging:
      +materialized: view   # tutti i modelli in staging/ sono view
    marts:
      +materialized: table  # tutti i modelli in marts/ sono table

-- Oppure config() inline nel file .sql
{{ config(materialized='view') }}

SELECT
    order_id,
    customer_id,
    created_at,
    total_amount,
    status
FROM {{ source('raw', 'orders') }}
WHERE status != 'test_order'

Table: Maximum Performance, Complete Rebuild

With materialization table, dbt runs a CREATE TABLE AS SELECT to every dbt run. The table is dropped and recreated from scratch. All data comes physically written to the warehouse, so downstream queries find pre-aggregated and non-aggregated data they have to recalculate anything.

-- models/marts/finance/fct_orders_daily.sql
{{ config(
    materialized='table',
    schema='marts_finance',
    tags=['daily', 'finance']
) }}

SELECT
    DATE_TRUNC('day', o.created_at)          AS order_date,
    c.country                                 AS customer_country,
    p.category                                AS product_category,
    COUNT(o.order_id)                         AS order_count,
    SUM(o.total_amount)                       AS gross_revenue,
    SUM(o.total_amount * (1 - o.discount_pct)) AS net_revenue,
    AVG(o.total_amount)                       AS avg_order_value,
    COUNT(DISTINCT o.customer_id)             AS unique_customers
FROM {{ ref('stg_orders') }}        o
JOIN {{ ref('stg_customers') }}     c ON o.customer_id = c.customer_id
JOIN {{ ref('stg_order_items') }}   i ON o.order_id    = i.order_id
JOIN {{ ref('stg_products') }}      p ON i.product_id  = p.product_id
WHERE o.status = 'completed'
GROUP BY 1, 2, 3

Warehouse Specific Optimizations

dbt supports specific configurations for each warehouse that significantly improve the performance of materialized tables:

-- Snowflake: cluster keys per evitare full table scan
{{ config(
    materialized='table',
    cluster_by=['order_date', 'customer_country']
) }}

-- BigQuery: partition e cluster
{{ config(
    materialized='table',
    partition_by={
        'field': 'order_date',
        'data_type': 'date',
        'granularity': 'day'
    },
    cluster_by=['customer_country', 'product_category']
) }}

-- Redshift: distribution key e sort key
{{ config(
    materialized='table',
    dist='customer_id',
    sort=['order_date', 'customer_country']
) }}

Incremental: Process Only New Data

The models incremental they are dbt's answer to everyone's real problem data pipeline: how to process billions of historical rows without recalculating everything from scratch at every execution. The idea is simple - update or insert only changed records or new since the last run.

Basic Structure of an Incremental Model

-- models/marts/events/fct_page_views.sql
{{ config(
    materialized='incremental',
    unique_key='page_view_id',
    on_schema_change='sync_all_columns'
) }}

SELECT
    page_view_id,
    session_id,
    user_id,
    page_url,
    referrer_url,
    DATE_TRUNC('hour', viewed_at) AS viewed_hour,
    viewed_at,
    duration_seconds,
    device_type
FROM {{ ref('stg_page_views') }}

{% if is_incremental() %}
-- Questo blocco viene eseguito SOLO nelle run incrementali (non in full refresh)
WHERE viewed_at > (
    SELECT MAX(viewed_at)
    FROM {{ this }}
)
{% endif %}

On the first run (table not existing), dbt executes the complete query and creates the table. In subsequent runs, is_incremental() becomes true and the filter WHERE is included, processing only the new data.

The Four Incremental Strategies

The strategy defines the merge mechanism between new data and existing table. The choice depends on the warehouse and the type of updates expected:

{{ config(
    materialized='incremental',
    incremental_strategy='append'
) }}
-- Inserisce solo nuove righe, non aggiorna mai.
-- Perfetto per dati immutabili (log di eventi,
-- audit trail, time-series append-only).
-- Attenzione: i duplicati non vengono rimossi.

{{ config(
    materialized='incremental',
    unique_key='order_id',
    incremental_strategy='merge',
    merge_update_columns=['status', 'updated_at']
) }}
-- Upsert basato su unique_key.
-- merge_update_columns limita le colonne
-- aggiornate (ottimizzazione performance).

{{ config(
    materialized='incremental',
    unique_key=['order_id', 'order_date'],
    incremental_strategy='delete+insert',
    partition_by={
      'field': 'order_date',
      'data_type': 'date'
    }
) }}
-- Elimina le partizioni interessate e reinserisce.
-- BigQuery: efficiente perché opera per
-- partizioni intere, non riga per riga.

{{ config(
    materialized='incremental',
    incremental_strategy='insert_overwrite',
    partition_by={
      'field': 'event_date',
      'data_type': 'date'
    }
) }}
-- Spark/BigQuery: sovrascrive intere partizioni.
-- Atomico e idempotente per partizione.
-- Ideale per re-processing di finestre temporali.

Full Refresh: Forced Reconstruction

-- Forza la ricostruzione completa ignorando is_incremental()
-- dbt run --full-refresh --select fct_page_views

-- Utile quando:
-- 1. La logica del modello è cambiata (nuove colonne, logica diversa)
-- 2. I dati sorgente sono stati corretti retroattivamente
-- 3. Vuoi verificare che il modello sia idempotente
-- 4. Migrazione schema (on_schema_change non è sufficiente)

-- on_schema_change controlla il comportamento dbt in caso di
-- mismatch tra schema attuale e schema del modello:
{{ config(
    materialized='incremental',
    on_schema_change='sync_all_columns'
    -- opzioni: 'ignore' | 'fail' | 'append_new_columns' | 'sync_all_columns'
) }}

Advanced Pattern: Lookback Window

The filter MAX(viewed_at) does not manage i late-arriving data: events with timestamp passed that arrive late in the pipeline. The lookback window solves this:

-- models/marts/events/fct_events_hourly.sql
{{ config(
    materialized='incremental',
    unique_key=['event_id'],
    incremental_strategy='merge'
) }}

SELECT
    event_id,
    user_id,
    event_type,
    DATE_TRUNC('hour', event_timestamp) AS event_hour,
    event_timestamp,
    properties
FROM {{ ref('stg_events') }}

{% if is_incremental() %}
WHERE event_timestamp >= (
    -- Riprocessa le ultime 3 ore per catturare i late-arriving data
    SELECT DATEADD('hour', -3, MAX(event_timestamp))
    FROM {{ this }}
)
{% endif %}

Snapshot: Change Data Capture and SCD Type 2

Snapshots are the dbt mechanism for tracking changes to records over time. They implement automatically Slowly Changing Dimensions type 2 (SCD-2): whenever a record changes, the snapshot creates a new row with the effective dates and maintains the previous line as history.

Structure of a Snapshot File

-- snapshots/snap_customers.sql
-- I file snapshot vanno nella directory snapshots/ (non in models/)

{% snapshot snap_customers %}

{{ config(
    target_schema='snapshots',
    unique_key='customer_id',
    strategy='timestamp',
    updated_at='updated_at',
    invalidate_hard_deletes=True
) }}

SELECT
    customer_id,
    email,
    first_name,
    last_name,
    customer_tier,
    billing_country,
    annual_revenue,
    updated_at
FROM {{ source('raw', 'customers') }}

{% endsnapshot %}

dbt automatically adds four columns to the snapshot table:

-- Colonne aggiunte automaticamente dbt allo snapshot:
-- dbt_scd_id      : identificatore univoco della riga di storia (hash)
-- dbt_updated_at  : quando dbt ha elaborato questa riga
-- dbt_valid_from  : inizio del periodo di validità
-- dbt_valid_to    : fine del periodo di validità (NULL = record corrente)

-- Query per il valore corrente (più recente):
SELECT *
FROM {{ ref('snap_customers') }}
WHERE dbt_valid_to IS NULL

-- Query per il valore storico in un dato momento:
SELECT *
FROM {{ ref('snap_customers') }}
WHERE '2024-06-15'::date BETWEEN dbt_valid_from AND COALESCE(dbt_valid_to, '9999-12-31')

-- Query per vedere tutta la storia di un cliente:
SELECT
    customer_id,
    customer_tier,
    dbt_valid_from,
    dbt_valid_to,
    CASE WHEN dbt_valid_to IS NULL THEN 'current' ELSE 'historical' END AS record_status
FROM {{ ref('snap_customers') }}
WHERE customer_id = 42
ORDER BY dbt_valid_from

Check vs timestamp strategy

-- Strategia timestamp (raccomandata): usa una colonna updated_at
-- La sorgente DEVE avere una colonna di timestamp affidabile
{% snapshot snap_orders %}
{{ config(
    target_schema='snapshots',
    unique_key='order_id',
    strategy='timestamp',
    updated_at='updated_at'
) }}
SELECT order_id, customer_id, status, total_amount, updated_at
FROM {{ source('raw', 'orders') }}
{% endsnapshot %}

-- Strategia check: confronta i valori di colonne specifiche
-- Utile quando la sorgente non ha un campo updated_at
{% snapshot snap_product_prices %}
{{ config(
    target_schema='snapshots',
    unique_key='product_id',
    strategy='check',
    check_cols=['price', 'discount_pct', 'category']
    -- oppure check_cols='all' per confrontare tutte le colonne
) }}
SELECT product_id, product_name, price, discount_pct, category
FROM {{ source('raw', 'products') }}
{% endsnapshot %}

How to Use Snapshots in Downstream Models

-- models/marts/dim_customers_scd2.sql
-- Dimensione con storia completa per l'analisi storica

{{ config(materialized='table') }}

WITH customer_history AS (
    SELECT
        customer_id,
        email,
        first_name || ' ' || last_name   AS full_name,
        customer_tier,
        billing_country,
        dbt_valid_from                    AS valid_from,
        COALESCE(dbt_valid_to, '9999-12-31'::date) AS valid_to,
        dbt_valid_to IS NULL              AS is_current,
        dbt_scd_id                        AS surrogate_key
    FROM {{ ref('snap_customers') }}
)
SELECT * FROM customer_history

-- Ora puoi fare join storici precisi:
-- JOIN dim_customers_scd2 c
--   ON f.customer_id = c.customer_id
--   AND f.order_date BETWEEN c.valid_from AND c.valid_to

Ephemeral: Inline CTE without Persistence

A lesser-known but useful fifth materialization: ephemeral. A model ephemeral does not create any objects in the warehouse - it is injected as a CTE into the model which references it via ref().

-- models/staging/util/base_date_spine.sql
{{ config(materialized='ephemeral') }}

-- Questo modello diventa una CTE inline ovunque sia referenziato
-- Nessuna tabella o view viene creata nel warehouse
SELECT
    DATEADD('day', seq4(), '2020-01-01'::date) AS date_day
FROM TABLE(GENERATOR(ROWCOUNT => 3650))  -- 10 anni di date (Snowflake)

Guide to Choosing the Right Materialization

Here is a practical decision framework based on query frequency, data volume and type of updates:

Combine Materializations in the Real DAG

A mature dbt design uses different materializations in different layers. Here's an example of typical architecture with the Medallion pattern (Bronze/Silver/Gold):

# dbt_project.yml - configurazione materializzazioni per layer
models:
  my_project:
    staging:
      +materialized: view         # staging: view, sempre fresh
      +schema: staging

    intermediate:
      +materialized: ephemeral    # join intermedi: CTE, nessun overhead

    marts:
      finance:
        +materialized: table      # report finance: aggregazioni pre-calcolate
        +schema: marts_finance
      events:
        +materialized: incremental # eventi: troppi per ricostruire ogni volta
        +schema: marts_events
      dimensions:
        +materialized: table      # dimensioni: piccole, rebuilt giornalmente
        +schema: marts_dim

snapshots:
  my_project:
    +target_schema: snapshots     # snapshot: schema dedicato

Conclusions and Next Steps

Materializations are the mechanism by which DBT balances freshness, performance and cost. There is no universal answer: each model must be evaluated based on the volume of data, to the update rate and downstream access pattern.

The rule of thumb: start with view in staging, move on to table in marts when queries are slow, and use incremental only when the volume makes complete reconstruction economically impractical.

The next module addresses the data quality: like dbt test ensures that the models produce correct data, from the built-in generic tests (unique, not_null) to custom tests with dbt-expectations.