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_ATMODIFIED_ATLAST_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_ID | CUSTOMER_ID | ORDER_DATE | STATUS | AMOUNT |
|---|---|---|---|---|
| 1001 | 1 | 2025-12-01 | completed | 1999.99 |
| 1002 | 2 | 2025-12-02 | completed | 1299.00 |
| 1003 | 3 | 2025-12-03 | pending | 3499.50 |
| 1005 | 4 | 2025-12-05 | completed | 799.99 |
Step-by-step lab: Run and verify in Snowflake
In this lab, candidates should verify the result in two places:
- dbt terminal - to run the model
- Snowflake worksheet - to check the table and result set
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.
| Question | Use Incremental Model | Use Snapshot |
|---|---|---|
| Do you want faster model builds? | Yes | No |
| Do you want to avoid processing the full source every time? | Yes | Sometimes |
| Do you want to keep old versions of changed records? | No | Yes |
| Do you want SCD Type 2 history? | No | Yes |
Example:
If a customer's email changes in stg_customers:
- An incremental
silver_ordersmodel 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
| Mistake | Why it is a problem |
|---|---|
| No reliable date or timestamp column | dbt may miss new or changed rows |
Wrong unique_key | Updates may create duplicates |
Filtering only on ORDER_DATE when old orders can change | Updates to old orders may be missed |
Forgetting --full-refresh after logic changes | Old incorrect data may remain |
| Using incremental for small tables | Adds 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