Materializations: view, table, incremental
Same SELECT. You choose what dbt builds from it.
Alice's models build fine, but she hasn't told dbt what to build them as. A materialization is that choice: a view, a full table, or a table that only gets the new rows added to it. Same SQL in the file, different object in Snowflake. Four cover nearly every model you'll write. (dbt has a fifth, materialized_view; on Snowflake, part 4 uses a dynamic table instead. Skip both for now.)
dbt run, then reads are instant, and stale until the next run.WITH ... AS block) into every model that refs it.The difference isn't in your SQL, it's in what ends up sitting in Snowflake afterwards:
VIEW
stored in Snowflake:the query text only
0 rows · runs on every readTABLE
stored in Snowflake:every row, written out
every row rewritten, every runINCREMENTAL
stored in Snowflake:the old rows stay put
only the new row is writtenEphemeral isn't in the picture because it leaves nothing at all in Snowflake. It gets its own section below.
The decision, in four questions
Ask them in order, stop at the first yes. Q1 is rare, so most models go straight to Q2:
Set it on one model
-- models/marts/ {{ config(fct_ orders. sql materialized= SELECT ... -- step 2's join, unchanged'table') }}
Put config() at the top of the file, by convention. It doesn't touch the SELECT: dbt reads it, then drops it from the compiled SQL.
Set it for a whole folder
Usually you set a default per folder in dbt_project.yml: staging and intermediate as views, marts as tables.
# dbt_project. models: my_project: # the project's name: line, not the profile staging: # a folder under models/ +materialized: view +schema: staging intermediate: +materialized: view +schema: intermediate marts: +materialized: table +schema: martsyml
Under models: comes the project's name, then folders. The + marks a setting, not a folder. dbt often copes without it, but always write it.
+schema gives each layer its own schema. dbt's default rule glues it onto the target's schema: dbt_alice_staging for you, analytics_staging in production (step 5). Step 14 changes the rule: production gets plain STAGING, and your runs go back to dbt_alice. Views you built before stay in DBT_ALICE: drop them.
The same setting can live in three places. The most specific wins: config() in the .sql file beats config: in _models.yml, which beats dbt_project.yml.
Incremental in one breath
An incremental model builds the full table the first time, then only processes new rows after that. You define "new" with is_incremental() and {{ this }} (the table as it already exists). is_incremental() is true only when the table exists and you didn't pass --full-refresh. Otherwise the {% if %} block (Jinja logic, step 2) drops the WHERE.
-- models/marts/ {{ config(fct_ orders. sql (simplified: SELECT * stands in for step 2's join) materialized= SELECT * FROM {{ ref('incremental', unique_ key= 'order_ id') }} 'stg_ {% if is_orders') }} incremental( -- re-read the last 3 days: late rows get caught, unique_key stops duplicates WHERE order_date >= (SELECT max(order_date) - INTERVAL '3 days' FROM {{ this }}) {% endif %}) %}
What that saves is the whole point. Same 10M-row table, three nightly runs:
the first run is identical · after that, each run reads a few days, not the whole history
unique_key set, dbt runs a Snowflake MERGE: a row whose key exists is updated, not added twice. And a strict > max(order_date) filter misses late rows. Say a Monday order arrives Tuesday. Tuesday night's filter keeps only dates after Monday, so it's skipped forever. The three-day window re-reads it (about 150k rows, tiny next to 10M).# normal run dbt run --select fct_orders # changed the logic or the columns? drop and rebuild from scratch dbt run --select fct_orders --full-refresh
incremental_strategy: how the new rows land
materialized='incremental' decides that only new data gets processed. incremental_strategy decides how those rows land in the table. On Snowflake, three answers cover most models, and they're not interchangeable:
merge (default)unique_key: updates it if it exists, inserts it if not.delete+insertappendCareful: for delete+insert, unique_key doesn't mean unique. It names the column that defines each slice to replace:
-- every order_date present in the new batch is deleted, then reinserted whole {{ config( materialized='incremental', incremental_strategy= unique_'delete+insert', key= ) }}'order_ date'
Microbatch (dbt 1.9+) is the newer option, built for big time-series tables. You write a plain SELECT, with no is_incremental() block, and dbt runs one small query per period:
{{ config( materialized='incremental', incremental_strategy= event_'microbatch', time= batch_'order_ date', size= begin='day', '2024-01-01' ) }}
event_time names the column that dates each row. Rebuilding a past month becomes thirty daily batches. If day 15 fails, dbt retry (rerun what failed last time) redoes day 15 alone.
What happens when the schema changes?
Incremental models have a second classic wound: schema drift. Someone adds or renames a column, and the model keeps appending to the old shape, or fails cryptically. Guard it in the config:
-- refuses to build if the columns drifted {{ config( materialized='incremental', unique_key= on_'order_ id', schema_ ) }}change= 'fail'
ignore (default)failappend_new_columnssync_all_columnsOld rows get null in any added column. To fill them in, rebuild with --full-refresh.
The quiet fourth: ephemeral
An ephemeral model leaves nothing in Snowflake. dbt pastes its SQL as a CTE into every model that ref()s it. Use it for thin, shared snippets. Two cautions: you can't query it in Snowflake, because it isn't there, and a chain of ephemerals compiles into one giant nested query that's painful to debug. The moment you want to look at one, change it to materialized='view'.

