dbt Materialization Strategies: A Practical Guide With Examples

Views, tables, ephemeral models, and every incremental strategy dbt supports — what each one actually does under the hood, when to reach for it, and where teams get it wrong.

By Rakesh Madala — 10 years in the data field

If you've spent any time in a dbt project, you've typed this line more times than you can count:

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

And if you're honest, there's a decent chance you picked table — or copied whatever the last model used — without thinking too hard about it. That's fine while you're learning, but materialization strategy is one of the few dbt decisions with a direct, measurable effect on your warehouse bill, your pipeline runtime, and how fresh your data actually is. Get it wrong across a few hundred models and you'll notice: slow dashboards, ballooning compute costs, or a dbt run that used to take 12 minutes and now takes two hours.

This is a practical walkthrough of every materialization strategy dbt supports — what each one actually does under the hood, when to reach for it, and where people get it wrong. We'll cover the four core materializations, every incremental strategy (append, merge, delete+insert, insert_overwrite, microbatch), snapshots for slowly changing dimensions, native materialized views, and a decision framework you can apply to your own project.

What "Materialization" Actually Means in dbt

Every dbt model starts life as a SELECT statement — that's it. dbt doesn't move data around in some proprietary format; it compiles your Jinja-templated SQL into plain SQL and hands it to your warehouse. The materialization is simply the answer to one question: what does dbt do with that SELECT statement when it runs?

• Does it wrap the query in a CREATE VIEW, so it re-runs live every time someone queries it?
• Does it run the query once and persist the results as a physical CREATE TABLE?
• Does it only process the new rows since last run, and merge them into an existing table?
• Does it not create anything in the warehouse at all, and just inline the SQL into whatever references it?

Those four questions map almost exactly onto dbt's four built-in materializations: view, table, incremental, and ephemeral. Every dbt adapter — Snowflake, BigQuery, Databricks, Redshift, Postgres — implements these the same way conceptually, even though the underlying SQL each one generates is warehouse-specific.

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

SELECT
    id AS customer_id,
    first_name,
    last_name,
    email,
    created_at AS customer_created_at
FROM {{ source('raw', 'customers') }}

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

WITH customers AS (
    SELECT * FROM {{ ref('stg_customers') }}
),

orders AS (
    SELECT * FROM {{ ref('stg_orders') }}
),

customer_orders AS (
    SELECT
        customer_id,
        count(*) AS number_of_orders,
        sum(order_total) AS lifetime_value
    FROM orders
    GROUP BY 1
)

SELECT
    c.customer_id,
    c.email,
    coalesce(co.number_of_orders, 0) AS number_of_orders,
    coalesce(co.lifetime_value, 0) AS lifetime_value
FROM customers c
LEFT JOIN customer_orders co USING (customer_id)

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

SELECT
    *,
    row_number() OVER (
        PARTITION BY order_item_id
        ORDER BY _loaded_at DESC
    ) AS rn
FROM {{ source('raw', 'order_items') }}
QUALIFY rn = 1

{{
  config(
    materialized='incremental',
    unique_key='event_id'
  )
}}

SELECT
    event_id,
    user_id,
    event_type,
    event_timestamp
FROM {{ source('raw', 'events') }}

{% if is_incremental() %}
  WHERE event_timestamp > (SELECT max(event_timestamp) FROM {{ this }})
{% endif %}

{{
  config(
    materialized='incremental',
    unique_key=['order_id', 'line_item_id']
  )
}}

Incremental Strategies: Append vs. Merge vs. Delete+Insert vs. Insert Overwrite

is_incremental() and unique_key tell dbt which rows to process. The incremental_strategy config tells dbt how to apply those rows to the existing table — and this is where behavior differs meaningfully by warehouse, so it's worth understanding each one at the record level.

append

The simplest strategy: new rows are just inserted, nothing is checked or replaced. Fast, but only safe when you can guarantee incoming rows are genuinely new — e.g. an immutable, append-only event log with no unique_key. If duplicates are even remotely possible, don't use this.

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

merge

The default (and generally safest) strategy on warehouses with native MERGE support — Snowflake, BigQuery, Databricks, Postgres 15+. Using unique_key, dbt generates a MERGE statement: matching rows get updated, non-matching rows get inserted.

{{
  config(
    materialized='incremental',
    unique_key='order_id',
    incremental_strategy='merge'
  )
}}

SELECT
    order_id,
    order_status,
    order_total,
    updated_at
FROM {{ source('raw', 'orders') }}

{% if is_incremental() %}
  WHERE updated_at > (SELECT max(updated_at) FROM {{ this }})
{% endif %}

This is the right choice for anything mutable — orders that change status, subscriptions that get upgraded, any row that can be legitimately updated after it was first created.

delete+insert

Used mainly on warehouses without native MERGE, or when you specifically want to delete-then-reinsert entire matching keys rather than update columns individually. dbt runs a DELETE against rows matching the unique_key in the incoming batch, then inserts the new batch — functionally similar to merge for most purposes, but as two separate statements instead of one atomic operation.

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

insert_overwrite

Specific to warehouses with native partition support (BigQuery, Databricks/Spark). Instead of matching on a row-level unique_key, this strategy replaces entire partitions at once — the standard approach when data is naturally partitioned by date and a given partition might get reprocessed wholesale.

{{
  config(
    materialized='incremental',
    incremental_strategy='insert_overwrite',
    partition_by={
      'field': 'activity_date',
      'data_type': 'date'
    }
  )
}}

SELECT
    activity_date,
    user_id,
    count(*) AS events_count
FROM {{ ref('stg_events') }}

{% if is_incremental() %}
  WHERE activity_date >= date_sub(current_date(), INTERVAL 3 DAY)
{% endif %}

GROUP BY 1, 2

Notice the lookback window — 3 days, not strictly "since last run." This is a deliberate pattern: late-arriving data means the most recent partitions are never really "final." Reprocessing the last 3 days on every run, and overwriting those partitions wholesale, keeps things correct without reprocessing your entire history.

microbatch

Introduced in dbt 1.9, and worth calling out separately because it changes the mental model rather than just being another config option. Instead of one query with a manually-written is_incremental() filter, you tell dbt the time column and batch size, and dbt automatically breaks processing into discrete time-based batches, processing — and retrying — each one independently.

{{
  config(
    materialized='incremental',
    incremental_strategy='microbatch',
    event_time='event_timestamp',
    begin='2024-01-01',
    batch_size='day'
  )
}}

SELECT
    event_id,
    user_id,
    event_type,
    event_timestamp
FROM {{ source('raw', 'events') }}

No is_incremental() block needed — dbt handles the filtering itself, running one query per day of data. The advantage shows up at scale and on backfills: reprocessing two years of daily data runs as 730 independent batch queries instead of one enormous query, and if batch #400 fails, you retry just that one batch instead of the whole run — a meaningfully different failure mode than a traditional incremental model, where one bad run can mean re-running everything.

Full Refreshes

Because incremental models only add new logic on top of what's already there, they can drift from what a from-scratch build would produce — a bug in your is_incremental() filter, a schema change upstream, or a historical backfill are all situations where you need to rebuild the whole thing:

dbt run --select fct_events --full-refresh

This drops (or truncates, depending on adapter) the existing table and rebuilds it completely, as if it were the first run. It's worth running full refreshes periodically, or automating one after any change to an incremental model's logic — a model that "runs successfully" incrementally can still be silently wrong if the underlying logic changed but historical rows were never reprocessed.

{% snapshot customers_snapshot %}

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

SELECT * FROM {{ source('raw', 'customers') }}

{% endsnapshot %}

{{
    config(
      target_schema='snapshots',
      unique_key='customer_id',
      strategy='check',
      check_cols=['email', 'address', 'plan_tier'],
    )
}}

{{
  config(
    materialized='materialized_view',
  )
}}

SELECT
    order_date,
    sum(order_total) AS daily_revenue
FROM {{ ref('stg_orders') }}
GROUP BY 1

{{
  config(
    materialized='materialized_view',
    snowflake_warehouse='transform_wh',
    target_lag='5 minutes',
  )
}}

Warehouse-Specific Behavior Worth Knowing

Materializations are a dbt abstraction, but what actually happens underneath varies by adapter:

The takeaway isn't "memorize warehouse quirks" — it's that incremental_strategy isn't purely stylistic. Some strategies aren't even available on some adapters, and the ones that are can perform very differently. Check current adapter documentation for your specific warehouse rather than assuming a pattern that worked on Snowflake ports directly to Redshift.

A Decision Framework

A practical way to work through the choice for any given model:

Most projects land on: views for staging, tables for most marts, and incremental reserved specifically for the handful of genuinely large fact tables where it earns its complexity. Resist the urge to make everything incremental "for performance" — incremental models are harder to reason about, harder to debug, and easy to get subtly wrong. Reach for incremental when the numbers actually justify it, not by default.

Common Mistakes

A few patterns show up often enough in real dbt projects to call out specifically.

Testing Materialized Models

Materialization strategy and data testing are closely linked, because the failure modes above are exactly what tests catch. A few worth having on any incremental model specifically:

models:
  - name: fct_orders
    columns:
      - name: order_id
        tests:
          - unique
          - not_null
      - name: order_total
        tests:
          - not_null

A unique test on the unique_key column of an incremental model is close to mandatory — it's the test that catches the "forgot unique_key" mistake before it reaches a dashboard. Cheap to run, and it directly validates the thing most likely to silently break.

It's also worth adding a freshness check on the source feeding an incremental model, so a stalled pipeline shows up as a failed test rather than a stakeholder asking why yesterday's numbers look flat:

sources:
  - name: raw
    tables:
      - name: events
        loaded_at_field: _loaded_at
        freshness:
          warn_after: {count: 6, period: hour}
          error_after: {count: 24, period: hour}

Putting It Together: A Realistic Project Layout

Here's how these strategies typically play out across a real dbt project's layers:

models/
├── staging/
│   ├── stg_customers.sql        -- materialized='view'
│   ├── stg_orders.sql           -- materialized='view'
│   └── stg_order_items.sql      -- materialized='view'
├── intermediate/
│   └── int_order_items_deduped.sql   -- materialized='ephemeral'
└── marts/
    ├── dim_customers.sql        -- materialized='table'
    ├── dim_products.sql         -- materialized='table'
    ├── fct_orders.sql           -- materialized='table' (rebuilt daily)
    └── fct_events.sql           -- materialized='incremental', strategy='merge'

snapshots/
└── customers_snapshot.sql       -- SCD Type 2 history of customer records

The logic: staging is cheap and always fresh, so it's views. A single small deduplication step feeds only one downstream model, so it's ephemeral rather than cluttering the schema. The dimension tables are small enough that a daily full rebuild costs almost nothing, so they're plain tables — simple, easy to debug. fct_orders is medium-sized and still cheap enough to fully rebuild daily. fct_events, the actual high-volume event log, is the one model that justifies incremental complexity, with a unique_key and merge strategy to stay correct as source data occasionally gets reprocessed. The customer snapshot runs alongside all of this, independently, preserving history none of the current-state models would otherwise keep.

This is a reasonable default shape for a mid-sized project. The specific numbers — what counts as "small enough to just be a table," what counts as "large enough to justify incremental" — depend entirely on your own data volumes and warehouse cost structure, but the underlying decision process is the same regardless of scale.

Wrapping Up

Materialization strategy in dbt isn't a settings toggle you pick once and forget — it's an ongoing trade-off between compute cost, storage cost, freshness, and complexity, and the right answer differs for almost every model in a real project. Views are close to free but recompute on every query; tables are fast to query but expensive to rebuild in full every time; incremental models are efficient at scale but carry real complexity that needs active management; snapshots and materialized views solve specific problems — historical tracking and near-real-time freshness — that the four core materializations don't address at all.

The practical takeaway: default to view for staging, default to table for most marts, and reserve incremental for the specific handful of models where data volume genuinely justifies the added complexity. Add snapshots only where you actually need historical tracking, and treat materialized views as a warehouse-specific tool for freshness requirements nothing else can hit. Get those defaults right, and the rest of your dbt project's performance and cost profile tends to take care of itself.

— This article is part of an ongoing tooling series on techedge.in. Questions about which materialization fits your model? Drop a comment, I read every one.