04 - dbt: Materializations

16 min read

dbt: Materializations

Quote

“There are only two ways to handle state in computing: recompute it or cache it. Everything else is a variation on that theme.”

Source: Pat Helland

The Five Materialisation Types

TypeWarehouse objectData is stored?Rebuilt each run?
viewView / virtual tableNo (query-time)Yes (DDL is re-applied)
tablePhysical tableYesYes (DROP + CREATE)
incrementalPhysical tableYesNo (append/merge only)
ephemeralCTE (no object)NoN/A (inlined)
snapshotPhysical tableYesAppended via SCD logic

dbt view Materialisation

The default materialisation. dbt issues a CREATE OR REPLACE VIEW on every run. The underlying query executes at query time, always reflecting current source data.

This view example keeps a staging model lightweight by pushing the final query evaluation to read time instead of persisting a physical table.

-- models/staging/market_data/stg_market_data__daily_prices.sql
{{ config(materialized='view') }}
 
select
    security_id,
    cast(price_date as date)          as price_date,
    cast(close_price as numeric)      as close_price,
    cast(volume as bigint)            as volume
from {{ source('market_data', 'daily_prices') }}

When to use: All staging models. Simple intermediate models. Any model where storage cost matters more than query latency.

When not to use: When downstream queries are complex and scan many rows — the view re-executes the full query every time it is referenced.

dbt table Materialisation

dbt drops and recreates the physical table on every run. Simple and predictable.

This table example shows the simplest persistent mart pattern: rebuild the whole relation each run when the dataset is still cheap enough to recompute fully.

-- models/marts/reference/dim_indices.sql
{{ config(
    materialized = 'table',
    tags         = ['reference']
) }}
 
select
    index_id,
    index_name,
    index_family,
    currency_code,
    rebalance_frequency
from {{ ref('stg_indices__master') }}

When to use: Reference dimensions (dim_*). Intermediate models that are expensive to recompute and referenced by many downstream models. Any model where full rebuild time is acceptable.

When not to use: Tables with hundreds of millions of rows where a full rebuild takes too long. Use incremental instead.

Full-refresh parity

Running dbt run --full-refresh against an incremental model should produce the same logical result as its non-incremental path, but only if that path selects the full historical dataset correctly. Prefer a permanent table materialization when the model is small enough that a full rebuild is the normal, low-risk operating mode.

dbt incremental Materialisation

dbt first checks whether the relation exists. If it does, it runs the model’s {% if is_incremental() %} branch to produce only new/changed rows, then merges or appends them. If the table does not exist (or --full-refresh is passed), it behaves like table.

Bad incremental predicates lose data quietly

The is_incremental() branch determines which rows are processed. If the filter references max(price_date) FROM {{ this }} but the table was loaded with a gap such as a missed backfill, that gap can remain unprocessed indefinitely. Always use a lookback window instead of an exact boundary so late-arriving and corrected rows are revisited intentionally.

Safe pattern: lookback window

Always subtract a lookback offset from max() in the incremental filter:

where price_date >= (
    select dateadd(day, -{{ var('lookback_days', 3) }}, max(price_date))
    from {{ this }}
)

Pair with unique_key and merge strategy so re-processed rows are updated, not duplicated.

Basic Pattern

This baseline incremental pattern uses a unique key, a bounded lookback window, and schema-change handling so reruns can update recent slices instead of duplicating them.

-- models/marts/performance/fct_index_performance.sql
{{ config(
    materialized     = 'incremental',
    unique_key       = ['index_id', 'price_date'],
    on_schema_change = 'append_new_columns'
) }}
 
with source as (
 
    select
        index_id,
        price_date,
        index_daily_return,
        constituent_count
    from {{ ref('int_daily_returns_indexed') }}
 
    {% if is_incremental() %}
    -- Late-arriving data lookback: reprocess last N days to catch corrections
    where price_date >= (
        select dateadd(day, -{{ var('lookback_days', 3) }}, max(price_date))
        from {{ this }}
    )
    {% endif %}
 
)
 
select * from source

Incremental Strategies

append

Inserts new rows only. Never updates existing rows. Fastest option.

This append strategy is appropriate only when existing rows are immutable and corrections are handled outside the model.

{{ config(
    materialized = 'incremental',
    incremental_strategy = 'append'
) }}

Use when: rows are immutable once written (e.g., audit logs, intraday tick snapshots).

delete+insert

Deletes rows matching unique_key in the target, then inserts all rows from the incremental run. Simpler than merge; avoids merge lock contention on some warehouses.

This strategy rewrites the affected key slice on each incremental run, which is often easier to reason about than a warehouse-specific merge plan.

{{ config(
    materialized         = 'incremental',
    unique_key           = 'corporate_action_key',
    incremental_strategy = 'delete+insert'
) }}

Use when: rows can be corrected/restated and you want clean replacement without a full rebuild.

merge (default for most adapters)

Issues a SQL MERGE statement matching on unique_key. Rows that match are updated; rows that do not match are inserted.

This merge configuration is the common choice when corrected rows and late-arriving facts need to upsert into an existing published table.

{{ config(
    materialized         = 'incremental',
    unique_key           = ['index_id', 'price_date'],
    incremental_strategy = 'merge',
    merge_update_columns = ['index_daily_return', 'constituent_count', 'total_weight_coverage']
) }}

merge_update_columns restricts which columns are updated on a match, preventing overwrite of columns not included in the incremental query.

insert_overwrite (BigQuery / Spark)

Overwrites entire partitions rather than individual rows. Extremely efficient for partitioned tables.

This pattern replaces whole date partitions, which is efficient only when the warehouse and table design make partition-level replacement the right unit of change.

{{ config(
    materialized         = 'incremental',
    incremental_strategy = 'insert_overwrite',
    partition_by         = {
        'field': 'price_date',
        'data_type': 'date',
        'granularity': 'day'
    }
) }}
 
select * from {{ ref('int_daily_returns') }}
 
{% if is_incremental() %}
where price_date >= date_sub(current_date(), interval {{ var('lookback_days', 3) }} day)
{% endif %}

on_schema_change: ignore drops new columns

If you add a column to your incremental model but forget to set on_schema_change, dbt defaults to ignore. The new column appears in development where the table is created fresh, but it is silently omitted in production when the existing table lacks that column. Set on_schema_change: 'append_new_columns' on incremental models unless you have a stronger compatibility requirement.

Safe default

Set on_schema_change: 'append_new_columns' in incremental model configs that evolve over time. That adds new columns to the existing table in production without immediately forcing a full refresh or manual DDL.

dbt on_schema_change Behaviour

Controls what happens when the model’s column set changes compared to the existing table.

ValueBehaviour
ignoreNew columns silently dropped from incremental run
failRun fails if schema differs
append_new_columnsNew columns added to table; old columns preserved
sync_all_columnsAdds new, removes deleted columns (destructive)

This config snippet opts into additive schema evolution so published incremental tables can accept new columns without breaking the whole run path.

{{ config(
    materialized     = 'incremental',
    unique_key       = ['security_id', 'score_date'],
    on_schema_change = 'append_new_columns'   -- safe default for evolving models
) }}

sync_all_columns can remove consumer columns

sync_all_columns will drop columns that were removed from your model SQL. That can break downstream BI tools and APIs that still reference them. Prefer append_new_columns for ordinary evolution and handle destructive removals through an explicit rollout plan.

Safe removal workflow

Use append_new_columns in production. To retire a column, deprecate it in documentation first, notify consumers, and then schedule a controlled --full-refresh or replacement deployment in a maintenance window after consumers have migrated.

dbt Late-Arriving Data Lookback Pattern

A core challenge with incremental models processing financial data is that source systems frequently backfill or correct historical data. A price vendor might correct a corporate action adjustment 2 days after initial delivery.

The lookback pattern reprocesses a rolling window of recent data on every incremental run:

This example reprocesses a bounded recent slice so corrected vendor data can overwrite stale rows instead of being missed permanently.

{{ config(
    materialized = 'incremental',
    unique_key   = ['security_id', 'price_date']
) }}
 
with prices as (
 
    select *
    from {{ ref('stg_market_data__daily_prices') }}
 
    {% if is_incremental() %}
    -- Reprocess lookback window to catch late corrections.
    -- var('lookback_days') defaults to 3; extend for vendors with longer correction windows.
    where price_date >= (
        select dateadd(day, -{{ var('lookback_days', 3) }}, max(price_date))
        from {{ this }}
    )
    {% endif %}
 
)
 
select * from prices

With unique_key and merge strategy, dbt will update existing rows that fall in the lookback window with corrected values, then insert genuinely new rows. This merge-based approach is a key ingredient of idempotent-pipeline-design — re-running the same date range produces identical results without duplicating data.

dbt ephemeral Materialisation

Ephemeral models are not materialised in the warehouse at all. dbt inlines their SQL as a CTE in every model that references them via ref().

This ephemeral helper keeps trivial row-level logic out of the warehouse object list, but it still becomes part of every downstream compiled statement that references it.

-- models/intermediate/market_data/int_price_flags.sql
{{ config(materialized='ephemeral') }}
 
select
    security_id,
    price_date,
    case
        when close_price <= 0       then 'ZERO_OR_NEGATIVE'
        when high_price < low_price then 'INVERTED_HLOC'
        else                             'VALID'
    end as price_flag
 
from {{ ref('stg_market_data__daily_prices') }}

When int_daily_returns references int_price_flags, dbt compiles the ephemeral model’s SQL directly into int_daily_returns as a CTE. No warehouse object is created.

Limitations:

  • Cannot be queried directly.
  • Not accessible via --defer (no artifact).
  • Reused in many models = the CTE is duplicated in each compiled output, potentially confusing query planners.

dbt snapshot Materialisation

Snapshots implement SCD Type 2 (slowly changing dimensions) — they record the full history of how a row changed over time.

This snapshot captures constituent membership history so downstream consumers can query either the current state or a historical point in time without rebuilding old rows.

-- snapshots/snap_index_constituents.sql
{% snapshot snap_index_constituents %}
 
{{ config(
    target_schema           = 'snapshots',
    unique_key              = 'constituent_key',
    strategy                = 'timestamp',
    updated_at              = '_ingested_at',
    invalidate_hard_deletes = true
) }}
 
select
    {{ dbt_utils.generate_surrogate_key(['index_id', 'security_id']) }} as constituent_key,
    index_id,
    security_id,
    weight,
    effective_date,
    _ingested_at
 
from {{ ref('stg_market_data__index_constituents') }}
 
{% endsnapshot %}

dbt adds four metadata columns to the snapshot table:

ColumnMeaning
dbt_scd_idUnique identifier for each snapshot record
dbt_updated_atTimestamp of the source row’s last change
dbt_valid_fromWhen this version of the row became active
dbt_valid_toWhen this version was superseded (NULL = current)

Query the current state:

This query filters snapshot metadata to the rows that are still active today.

select * from snap_index_constituents
where dbt_valid_to is null

Query the state on a specific date:

This query reconstructs the state that was valid on a chosen historical date by using the snapshot validity window.

select * from snap_index_constituents
where '2023-06-30' between dbt_valid_from and coalesce(dbt_valid_to, '9999-12-31')

Strategies:

  • timestamp: uses an updated_at column to detect changes. Most reliable.
  • check: compares a list of columns (check_cols) and marks a new version when any column changes. Use when no reliable updated_at exists.

Materialisation Decision Matrix

ScenarioRecommended materialisation
Staging model (1:1 with source)view
Lightweight intermediate CTEephemeral
Reference dimension, infrequently changingtable
Expensive intermediate, many downstream refstable
Daily incremental fact table, large historyincremental (merge or insert_overwrite)
Audit log, append-only event streamincremental (append)
SCD Type 2 history trackingsnapshot
Model too slow as view, too large for table rebuildincremental

dbt Full-Refresh Mechanics

Running dbt run --full-refresh against an incremental model causes dbt to:

  1. Drop the existing table.
  2. Execute the model SQL without the {% if is_incremental() %} filter.
  3. Create a new table with all rows from the full query.

This is equivalent to dropping and recreating a table materialisation. It is the escape hatch when incremental state becomes corrupted or when a schema change requires a complete rebuild.

These commands trigger targeted or broad rebuilds of incremental models when you intentionally want to bypass incremental state and recompute from scratch.

# Full-refresh a single incremental model
dbt run --select fct_index_performance --full-refresh
 
# Full-refresh all models tagged 'incremental'
dbt run --select tag:incremental --full-refresh

Treat full refresh as an exception path

Do not schedule routine production --full-refresh runs by default. Use them when schema drift, incremental corruption, or major logic changes justify the extra cost, and verify that the warehouse window, downstream SLAs, and backfill volume can absorb the rebuild safely.

Graph View

Backlinks