Skip to main content

04. More about Incremental Materialization

The is_incremental() block helps you avoid reprocessing old data.

In the silver_orders example, this block only runs when dbt is adding new rows after the first successful table creation.

{% if is_incremental() %}
AND o.ORDER_DATE > (
SELECT MAX(ORDER_DATE)
FROM {{ this }}
)
{% endif %}
  • First run → loads all completed orders.
  • Later runs → loads only new orders.
  • Full refresh → loads all orders again.

When to use a stronger incremental key​

This example uses ORDER_DATE because that is the column available in our training data.

For a production-ready incremental model, use a column like:

  • UPDATED_AT
  • MODIFIED_AT
  • LAST_UPDATED_AT

This is better when rows can change after they are first inserted.

Running the incremental model​

Run only the Silver orders model:

dbt run --select silver_orders

Run the model with its upstream dependencies:

dbt run --select +silver_orders

Rebuild with full refresh​

Use full refresh when the model logic changes, historical rows need correction, or you want to reload all records.

dbt run --select silver_orders --full-refresh

This tells dbt to drop and rebuild the full silver_orders table.


Beginner practice example using existing demo data​

From the earlier demo, stg_orders has rows like this:

ORDER_IDCUSTOMER_IDORDER_DATESTATUSAMOUNT
100112025-12-01completed1999.99
100222025-12-02completed1299.00
100332025-12-03pending3499.50
100542025-12-05completed799.99

Step-by-step lab: Run and verify in Snowflake​

In this lab, candidates should verify the result in two places:

  1. dbt terminal - to run the model
  2. Snowflake worksheet - to check the table and result set
Important

dbt does not usually create models inside the RAW schema.

Raw source tables are in:

DEMO_DB.RAW

dbt models are created in your dbt target schema, which comes from profiles.yml or dbt Cloud environment settings.

For this lab, use:

DEMO_DB.DBT_TRAINING

Your profiles.yml or dbt Cloud connection should use DBT_TRAINING as the dbt schema for this exercise.

For dbt Core, your Snowflake target should include:

database: DEMO_DB
schema: DBT_TRAINING

Incremental model vs snapshot​

This is very important.

QuestionUse Incremental ModelUse Snapshot
Do you want faster model builds?YesNo
Do you want to avoid processing the full source every time?YesSometimes
Do you want to keep old versions of changed records?NoYes
Do you want SCD Type 2 history?NoYes

Example:

If a customer's email changes in stg_customers:

  • An incremental silver_orders model usually keeps the latest joined customer value for reporting.
  • A snapshot keeps the old customer email and the new customer email with validity dates.

Use incremental materialization for performance.

Use snapshots for history.


Common mistakes​

MistakeWhy it is a problem
No reliable date or timestamp columndbt may miss new or changed rows
Wrong unique_keyUpdates may create duplicates
Filtering only on ORDER_DATE when old orders can changeUpdates to old orders may be missed
Forgetting --full-refresh after logic changesOld incorrect data may remain
Using incremental for small tablesAdds complexity without much benefit

Key takeaways​

Incremental materialization is used to improve performance and cost efficiency
In our demo, silver_orders is the best beginner example
The first run loads all completed orders
Later runs process only rows allowed by the is_incremental() filter
unique_key='ORDER_ID' helps dbt identify the same order across runs
Use --full-refresh when you need to rebuild the entire table
Incremental models are not a replacement for snapshots


When should we use incremental models?​

Use incremental materialization when:

  • The source table is very large
  • The model takes too long to rebuild fully
  • New data arrives regularly
  • You can identify new rows using a column like ORDER_DATE
  • You can identify changed rows using a column like UPDATED_AT

In our demo, silver_orders is built from stg_orders. Orders usually grow over time, so this is a natural beginner example.


When should we avoid incremental models?​

Avoid incremental materialization when:

  • The table is small
  • The transformation logic changes often
  • You cannot reliably identify new or changed rows
  • You need to recalculate the full dataset every time
  • Beginners are still learning basic dbt model flow