dbt Incremental Models and Snapshots on Snowflake (Part 7)

So far every dbt run rebuilds every table from scratch. That is simple and correct, and for small tables it is the right choice. But order lines only ever grow, and rebuilding years of history every morning to add one day of data wastes warehouse time. This part makes fct_order_items incremental, so each run processes only new rows, and adds a snapshot that records how customers change over time.

Part 7 of 10 in the series Build a modern data pipeline with Snowflake, dbt and Airflow. Previous: Part 6, Testing and documenting with dbt. Next: Part 8, Orchestrating dbt with Airflow.

Code for this part: fct_order_items.sql, snap_customers.sql, snowflake/03_next_batch.sql. Full project: tpch_analytics on GitHub.

Incremental models

An incremental model is built in full the first time. On later runs, dbt adds a filter you write inside is_incremental() and merges the result into the existing table. Here is the final version of fct_order_items:

models/marts/core/fct_order_items.sql{{
    config(
        materialized='incremental',
        unique_key='order_item_key',
        incremental_strategy=('merge' if target.type == 'snowflake' else 'delete+insert'),
        on_schema_change='append_new_columns'
    )
}}

select * from {{ ref('int_order_items__enriched') }}

{% if is_incremental() %}
-- only process rows loaded since the last run
where _loaded_at > (select max(_loaded_at) from {{ this }})
{% endif %}

The parts that matter:

• is_incremental() is true only when the table already exists and you did not ask for a full refresh. The first run therefore builds everything.
• {{ this }} is the table being built, so the filter reads "rows loaded after the newest row we already have". This is why part 3 added the _LOADED_AT audit column: it tracks when rows arrived, which is what an incremental load needs, not when the order was placed.
• unique_key tells dbt how to match rows. If a line arrives again (a correction, a re-sent file), it updates the existing row instead of duplicating it.
• incremental_strategy='merge' makes dbt issue a Snowflake MERGE. The project also runs on DuckDB for local testing, which uses delete+insert instead; the Jinja expression picks the right one per platform.
• on_schema_change controls what happens when you add a column to the model: here new columns are added to the table rather than failing the run.

Compile it once the table exists and you can see the filter dbt adds:

bashdbt compile --select fct_order_items

select * from "analytics"."dev_intermediate"."int_order_items__enriched"

-- only process rows loaded since the last run
where _loaded_at > (select max(_loaded_at) from "analytics"."dev_marts"."fct_order_items")

This and the following outputs come from the local DuckDB build used to test the series (hence the quoted names and smaller row counts). On Snowflake, dbt builds the same query and wraps it in a MERGE.

Try it: deliver tomorrow's data

In part 3 we held back every order from July 1998 onwards. snowflake/03_next_batch.sql plays the source system once more: it writes those orders to the stage as a new batch and changes one customer, for the snapshot below.

snowflake/03_next_batch.sqlUSE ROLE LOADER;
USE WAREHOUSE LOADING_WH;
USE SCHEMA RAW.TPCH;

COPY INTO @LANDING/orders/batch_1998_08/
  FROM (SELECT * FROM SNOWFLAKE_SAMPLE_DATA.TPCH_SF1.ORDERS WHERE O_ORDERDATE >= '1998-07-01')
  HEADER = TRUE;

COPY INTO @LANDING/lineitem/batch_1998_08/
  FROM (SELECT * FROM SNOWFLAKE_SAMPLE_DATA.TPCH_SF1.LINEITEM
        WHERE L_ORDERKEY IN (SELECT O_ORDERKEY FROM SNOWFLAKE_SAMPLE_DATA.TPCH_SF1.ORDERS
                             WHERE O_ORDERDATE >= '1998-07-01'))
  HEADER = TRUE;

-- a customer changes segment: picked up by the snapshot in part 7
UPDATE CUSTOMER SET C_MKTSEGMENT = 'AUTOMOBILE' WHERE C_CUSTKEY = 1;

Then run the load from part 3 again (it picks up only the new files, thanks to load metadata) and build:

bashdbt build

In the test run, fct_order_items grew from 295,635 to 299,814 rows: the incremental run processed only the 4,179 newly arrived lines instead of rebuilding all 299,814. The unique-key test on the table still passed, confirming nothing was duplicated.

When to rebuild from scratch

Change the logic of an incremental model (a new calculation, a fixed bug) and existing rows keep the old logic. Rebuild just that model and everything downstream with:

bashdbt build --select fct_order_items+ --full-refresh

Should this model be incremental?

Make it incremental when…	Keep it a table when…
It is large and grows by appending (events, order lines, logs)	It is small (dimensions, lookups) or rebuilds in seconds
Old rows rarely change, or changes arrive with a newer load timestamp	Old rows change in ways you cannot detect
Full rebuilds are measurably slow or costly	You are still changing the logic often

On large Snowflake tables, also consider a clustering key on the column most queries filter by, for example cluster_by=['order_date'] in the model config, so queries and merges scan fewer micro-partitions. Measure first; clustering has its own maintenance cost.

Snapshots: keep history of changing records

Source systems usually overwrite records. When customer 1 moves from the BUILDING segment to AUTOMOBILE, the CUSTOMER table simply shows AUTOMOBILE, and every past report that grouped by segment silently changes. A snapshot keeps history instead: a slowly changing dimension type 2, where each version of a record gets its own row with validity dates.

snapshots/snap_customers.sql{% snapshot snap_customers %}

{{
    config(
        schema='snapshots',
        unique_key='customer_key',
        strategy='check',
        check_cols=['market_segment', 'account_balance', 'customer_address']
    )
}}

select * from {{ ref('stg_tpch__customers') }}

{% endsnapshot %}

The check strategy compares the listed columns on every run; if any changed, the old row gets a dbt_valid_to date and a new row is inserted. (The other strategy, timestamp, relies on a reliable updated_at column in the source and is cheaper when one exists.) Using schema= rather than the older target_schema= setting matters: it goes through the naming macro from part 4, so CI runs in part 9 write to their own snapshot schema instead of production's.

After the batch above, the snapshot holds both versions of customer 1:

SQLselect customer_key, market_segment, dbt_valid_to
from snapshots.snap_customers
where customer_key = 1
order by dbt_valid_from;

customer_key  market_segment  dbt_valid_to
           1  BUILDING        2026-10-02 21:58:18
           1  AUTOMOBILE      (null: current version)

To report on segment as it was at the time of each order, join facts to the snapshot on customer_key where the order falls between dbt_valid_from and dbt_valid_to. To report on the current segment, use dim_customers.

Snapshot rules of thumb

• Snapshot raw or staging data, not marts: a snapshot can never be rebuilt, so it should capture what the source said, not your logic.
• Run snapshots on a schedule (part 8 does it daily). Changes between runs are invisible: if a value changes twice in a day, you see only the last.
• Never drop a snapshot table; it is the only copy of that history. Back it up like source data.

Next

We now load, transform, test, increment and snapshot, but only when someone types dbt build. In part 8 Airflow takes over: a daily DAG that loads new files with COPY INTO and runs every dbt model and test as its own task.

The series

1. The stack and what we will build
2. Setting up Snowflake for dbt
3. Loading raw data into Snowflake
4. Your first dbt project on Snowflake
5. Modeling with dbt: staging to marts
6. Testing and documenting with dbt
7. Incremental models and snapshots (this post)
8. Orchestrating dbt with Airflow
9. CI/CD for dbt with GitHub Actions
10. Running the pipeline in production

All parts: Data Pipeline Series. Every file shown in this series is in the companion project tpch_analytics on GitHub; clone it to follow along, or run it locally on DuckDB without a Snowflake account.