Skip to main content

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 = 100 again, 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​

  1. The first dbt run creates the full silver_orders table.
  2. On subsequent runs, the is_incremental() block limits the source data to new rows.
  3. 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_at
  • updated_at
  • modified_date
  • load_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_id
  • transaction_id
  • sequence_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_timestamp
  • commit_timestamp
  • operation_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:

  1. CDC timestamp Best
  2. Last modified timestamp Very good
  3. Created timestamp Good for insert-only tables
  4. Sequence / ID column ️ Only if strictly increasing
  5. 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
Important

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