Materialisations

What a materialisation is

Your model is a SELECT; the materialisation decides what dbt turns it into in Snowflake. You rarely need to choose — the project sets sensible defaults by layer — but knowing the options explains why builds behave the way they do.

The logic of the defaults: staging is cheap renaming, so views keep it always fresh for free. Modelling and reporting do real computation, so tables pay the cost once per night instead of on every query.

dbt's own guidance compresses the whole decision into one escalation ladder, worth memorising: start with a view; when the view gets too slow to query, make it a table; when the table gets too slow to build, make it incremental. Each promotion is a response to a pain you have actually felt, never a precaution.

Overriding per model

A config() block at the top of the model wins over the project default:

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

select ...

Incremental models

A full rebuild of a multi-billion-row activity table every night is wasteful when yesterday is the only new data. An incremental model builds the full table once, then on later runs only processes rows matching the is_incremental() filter and merges them in:

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

select
    event_id,
    sk_patient_id,
    event_date,
    ...
from {{ ref('stg_big_event_feed') }}

{% if is_incremental() %}
  -- only rows newer than what's already in this table
  where event_date > (select max(event_date) from {{ this }})
{% endif %}

• {{ this }} refers to the already-built table itself.
• unique_key lets dbt update changed rows rather than duplicate them.
• dbt build --full-refresh -s my_model drops and rebuilds from scratch — required after logic changes, so existing rows pick up the new logic.

Dynamic tables move refresh responsibility to Snowflake

Analysts increasingly create Snowflake dynamic tables for transformations that need to refresh more continuously than a batch workflow. The author still supplies a SELECT, but Snowflake monitors the upstream data and refreshes the result towards a declared target_lag. dbt's Snowflake adapter can manage the definition with the dynamic_table materialisation:

{{
    config(
        materialized='dynamic_table',
        snowflake_warehouse='WH_NCL_ENGINEERING_XS',
        target_lag='30 minutes',
        refresh_mode='INCREMENTAL'
    )
}}

select ...

Target lag is a freshness objective, not a promise to refresh at an exact interval. A target of 30 minutes means Snowflake should try to keep the result no more than 30 minutes behind its base tables. Actual lag can be greater when refresh work, warehouse capacity or pipeline depth prevents Snowflake meeting the target. The consumer requirement should therefore determine the lag, and monitoring must compare actual freshness with it.

Refresh mode is a separate decision. INCREMENTAL processes changes where the query is compatible; FULL recomputes the result; and AUTO allows Snowflake to choose. Production work should make that behaviour deliberate rather than treating “dynamic” as an automatic guarantee of efficient incremental processing. Snowflake documents the supported modes and query limitations in its dynamic-table refresh guidance.

Where dynamic tables fit around this dbt project

The current dbt project does not define models with the dynamic_table materialisation. Its normal model freshness is controlled by the deployment and scheduled workflows described in From merge to production. Dynamic tables nevertheless exist in the wider Snowflake working environment and are legitimate analytical assets for analysts to create when their use requires that refresh model.

The important point is to make the orchestration boundary explicit. A dynamic table created directly in Snowflake is not automatically present in dbt lineage, CI, contracts or the project's Elementary run history. If a dbt model consumes it, declare and document the source, its owner and its freshness expectation. If the organisation decides to manage dynamic tables through dbt, their definitions can gain Git review and DAG lineage, while their background refresh health still needs Snowflake-specific monitoring.

An analyst can own the SELECT and propose the freshness needed by a consumer. The warehouse used for refresh, access controls, cost guardrails and integration with production monitoring may require engineering support. This is the same “hats, not badges” boundary described in Analysts and dbt: responsibility follows the decision and its risk, not the presence of a CREATE statement.

Where incremental models go wrong

The pattern above looks simple; the failure modes are where the care goes:

• Late-arriving data. If Tuesday's rows arrive on Thursday, a strict “newer than my max date” filter never picks them up. The common fix is a reprocessing window — recompute the last N days every run and let unique_key merge the overlap.
• Logic changes don't propagate. Edit the SQL and only new rows get the new logic; history silently keeps the old behaviour until a --full-refresh. Easy to forget, hard to spot afterwards.
• Schema changes need a decision. Adding a column to the SELECT does not backfill it for existing rows — they hold null until a full refresh. The on_schema_change config decides whether dbt adds the column or fails loudly.
• Dev tables drift. Your dev copy was built from whatever existed when you last full-refreshed it. When dev results look stale or impossible, full-refresh your dev table before debugging anything else.

A reasonable decision rule: stay with table until the scheduled rebuild of a specific model is measurably slow or expensive, then make that model incremental and write its is_incremental() filter with the failure modes above in mind.