02. Example: Convert silver_orders to Incremental
In the previous hands-on demo, silver_orders was created like this:
{{ config(materialized='table') }}
To make it incremental, change the config to:
{{ config(
materialized='incremental',
unique_key='ORDER_ID'
) }}
models/silver/silver_orders.sql
{{ config(
materialized='incremental',
unique_key='ORDER_ID'
) }}
SELECT
o.ORDER_ID,
o.CUSTOMER_ID,
o.ORDER_DATE,
o.AMOUNT,
o.STATUS,
c.NAME AS CUSTOMER_NAME,
c.COUNTRY,
c.EMAIL AS CUSTOMER_EMAIL
FROM {{ ref('stg_orders') }} o
JOIN {{ ref('stg_customers') }} c
ON o.CUSTOMER_ID = c.CUSTOMER_ID
WHERE o.STATUS = 'completed'
{% if is_incremental() %}
AND o.ORDER_DATE > (
SELECT MAX(ORDER_DATE)
FROM {{ this }}
)
{% endif %}
Why unique_key and is_incremental() are different
unique_key='ORDER_ID' and is_incremental() do different jobs in an incremental model:
-
unique_key='ORDER_ID'- Defines how dbt uniquely identifies rows in the target table.
- Used during incremental merge logic to decide whether to insert a new row or update an existing one.
- Example: if the source contains
ORDER_ID = 100again, dbt knows it is the same target row.
-
is_incremental()filter- Controls which source rows are read for this incremental run.
- Reduces the amount of data processed by only selecting new or changed rows.
- Example:
AND o.ORDER_DATE > (SELECT MAX(ORDER_DATE) FROM {{ this }})means only newer rows are processed.
Example flow
- The first dbt run creates the full
silver_orderstable. - On subsequent runs, the
is_incremental()block limits the source data to new rows. - dbt uses
unique_key='ORDER_ID'to merge those rows into the target.
So in this model:
unique_key='ORDER_ID'answers: “What makes a row unique in the target?”- The
is_incremental()filter answers: “Which source rows should I read this time?”
Why this works
materialized='incremental'tells dbt to build the model incrementally.unique_key='ORDER_ID'tells dbt how to identify each order row.- The
is_incremental()block only runs after the target table already exists. {{ this }}refers to the target table being built:silver_orders.
Yes — is_incremental() can use other columns besides order date
is_incremental() does not require an order_date column. Use any column that helps identify new or changed records for this run.
1. Timestamp column (most common)
{% if is_incremental() %}
WHERE last_updated_ts > (
SELECT MAX(last_updated_ts)
FROM {{ this }}
)
{% endif %}
Common examples:
created_atupdated_atmodified_dateload_timestamp
2. Surrogate key / sequence ID
If IDs are strictly increasing:
{% if is_incremental() %}
WHERE order_id > (
SELECT MAX(order_id)
FROM {{ this }}
)
{% endif %}
Common examples:
order_idtransaction_idsequence_number
3. CDC column
If the source system exposes change data capture metadata:
{% if is_incremental() %}
WHERE cdc_timestamp > (
SELECT MAX(cdc_timestamp)
FROM {{ this }}
)
{% endif %}
Common examples:
change_timestampcommit_timestampoperation_timestamp
4. Snowflake metadata column
For staged files in Snowflake:
{% if is_incremental() %}
WHERE METADATA$FILE_LAST_MODIFIED > (
SELECT MAX(file_modified_ts)
FROM {{ this }}
)
{% endif %}
5. Composite logic
A timestamp alone may not be enough for updates. Use a timestamp filter together with unique_key:
{% if is_incremental() %}
WHERE updated_at >= (
SELECT MAX(updated_at)
FROM {{ this }}
)
{% endif %}
Then keep:
{{ config(
materialized='incremental',
unique_key='order_id'
) }}
This lets dbt insert new rows and update existing rows.
Best practice
Prefer these incremental filters in order:
- CDC timestamp Best
- Last modified timestamp Very good
- Created timestamp Good for insert-only tables
- Sequence / ID column ️ Only if strictly increasing
- Business date (order_date) ️ Least preferred for late-arriving data
For Snowflake + dbt, updated_at, modified_ts, or CDC timestamps are usually more reliable than order_date.
Check in Snowflake worksheet
After saving models/silver/silver_orders.sql, make sure the target schema exists in Snowflake:
DEMO_DB.DBT_TRAINING
Run this in a Snowflake worksheet:
CREATE DATABASE IF NOT EXISTS DEMO_DB;
CREATE SCHEMA IF NOT EXISTS DEMO_DB.RAW;
CREATE SCHEMA IF NOT EXISTS DEMO_DB.DBT_TRAINING;
SHOW SCHEMAS IN DATABASE DEMO_DB;
Expected schemas:
| schema_name |
|---|
| RAW |
| DBT_TRAINING |
The SILVER_ORDERS table will not exist immediately after saving the file.
dbt creates the table only after you run:
dbt run --select +silver_orders