Incremental Models in Detail

This lesson on Incremental Models in Detail is hands-on and example-driven. You will build and optimize dbt incremental models that only transform new or modified source records instead of rebuilding entire tables on every run. You will implement watermark filters using the is_incremental macro, manage record updates via unique keys, and handle evolving table schemas safely.

What You'll Be Able To Do

• Configure a model with incremental materialization using Jinja config blocks.
• Write conditional SQL filter clauses using the is_incremental() macro and {{ this }} target reference.
• Prevent duplicate records by configuring unique_key to switch dbt from append-only to merge operations.
• Manage schema drift using on_schema_change configurations like append_new_columns and fail.

Detailed Concept Walkthrough

1. Incremental Materialization and Conditional Filtering

Incremental models significantly reduce compute time and warehouse costs by transforming only records added or modified since the last dbt execution. You write a standard transformation query wrapped with a conditional Jinja block that restricts records during subsequent runs.

• Mechanism: You set materialization to 'incremental' in the dbt config block and wrap a WHERE clause inside the Jinja conditional block {% if is_incremental() %}.
• Under the Hood: During execution, dbt queries the existing target table using {{ this }} to find the highest existing watermark value (such as max(updated_at) or max(id)) and passes only newer rows into the model's transformation logic.
• Execution Flow: On the initial run, or when the destination table does not exist in the warehouse, is_incremental() evaluates to false, causing dbt to execute a full CREATE OR REPLACE TABLE statement containing all source data.

{{ config(
    materialized = 'incremental'
) }}

select
    id,
    user_id,
    event_name,
    event_timestamp
from {{ source('raw_data', 'events') }}
{% if is_incremental() %}
    -- Only process records newer than the max timestamp in the existing relation
    where event_timestamp > (select max(event_timestamp) from {{ this }})
{% endif %}

Key Takeaway: The is_incremental() macro only evaluates to true when the target table exists and the execution is not a full refresh.

2. Resolving Updates with Unique Keys

By default, incremental runs execute an append-only INSERT operation, which causes duplicate rows if existing records are updated in the source data. Defining a unique_key instructs dbt to execute an upsert/MERGE operation instead.

• Mechanism: You supply the unique_key parameter in the model's config() block with one or more primary key column names.
• Under the Hood: When unique_key is present during an incremental run, dbt stages incoming records and executes a SQL MERGE (or an equivalent delete-and-insert operation depending on the warehouse adapter) matching on the specified key.
• Best Practice: Use an append-only strategy without a unique_key solely for immutable event streams; always configure a unique_key whenever source records can be modified over time.

{{ config(
    materialized = 'incremental',
    unique_key = 'order_id' -- Enables MERGE strategy to update matching records
) }}

select
    order_id,
    customer_id,
    status,
    updated_at
from {{ source('raw_data', 'orders') }}
{% if is_incremental() %}
    where updated_at > (select max(updated_at) from {{ this }})
{% endif %}

Key Takeaway: Without a unique_key configuration, dbt defaults to append-only INSERTs that produce duplicate rows whenever existing records change.

3. Handling Schema Drift with on_schema_change

As upstream source schemas evolve, incremental models need explicit instructions on how to handle added, removed, or modified columns between runs. The on_schema_change configuration controls how dbt reconciles column mismatches between the query output and target table.

• Mechanism: Set on_schema_change in the configuration block to one of four supported values: ignore (default), fail, append_new_columns, or sync_all_columns.
• Under the Hood: When set to append_new_columns, dbt alters the target table to add newly detected columns during the incremental run and populates them for newly processed batches.
• Nuance: Adding new columns incrementally does not retroactively backfill values for historical rows already present in the target table; backfilling historical data requires a manual full refresh.

{{ config(
    materialized = 'incremental',
    unique_key = 'id',
    on_schema_change = 'append_new_columns' -- Automatically ALTER table to add new columns
) }}

select
    id,
    user_name,
    new_feature_flag,
    updated_at
from {{ source('raw_data', 'users') }}
{% if is_incremental() %}
    where updated_at > (select max(updated_at) from {{ this }})
{% endif %}

Key Takeaway: Schema evolution configs manage new column additions during runs, but historical rows require a full refresh to backfill values.

Topics Covered in Incremental Models in Detail

1. Incremental Concepts and Syntax (0:00 - 1:23) — Covers cost and runtime benefits alongside the incremental materialization config and is_incremental macro.
2. Implementing Incremental Filters (1:23 - 2:32) — Demonstrates writing the WHERE clause using the max value from the target relation reference.
3. Executing the Initial Run (2:32 - 4:00) — Explains why the first execution defaults to a full table create statement.
4. Testing Subsequent Runs (4:19 - 5:41) — Shows how inserting new source records triggers an append-only insert on existing tables.
5. Handling Updates with Unique Keys (5:41 - 7:09) — Illustrates how defining unique_key switches dbt from duplicate inserts to table merge operations.
6. Schema Evolution Configuration (7:09 - 8:52) — Walks through configuring on_schema_change settings to handle new columns without failing runs.

dbt Cheat Sheet

• materialized='incremental' — Configures model to build incrementally instead of rebuilding

  {{ config(materialized='incremental') }}
  

• is_incremental() — Evaluates true only when table exists and not refreshing

  {% if is_incremental() %}
  

• {{ this }} — References the current target relation in the database

  select max(updated_at) from {{ this }}
  

• unique_key='column_name' — Enables MERGE/upsert behavior matching on the specified key

  {{ config(unique_key='order_id') }}
  

• on_schema_change='fail' — Raises an error when source and target schemas differ

  {{ config(on_schema_change='fail') }}
  

• on_schema_change='append_new_columns' — Adds newly detected columns to the destination table

  {{ config(on_schema_change='append_new_columns') }}
  

• dbt run --full-refresh — Forces a complete table recreation ignoring incremental logic

  dbt run --select my_incremental_model --full-refresh
  

Comparison Table

Common Pitfalls

• Mistake: Expecting is_incremental to evaluate to true on the first run. Avoid: Allowing the first run to build the full table so subsequent runs can query the target.
• Mistake: Omitting unique_key on mutable source datasets. Avoid: Defining unique_key in the config block so dbt executes a merge rather than appending duplicate records.
• Mistake: Assuming append_new_columns backfills historical records automatically. Avoid: Executing a full refresh run with --full-refresh to compute new column values for existing data.

FAQs

• Why did my incremental model rebuild the entire table instead of inserting new rows? The target table did not exist yet, or the run was executed with the --full-refresh flag. In both cases, is_incremental() evaluates to false.
• What happens if source rows are updated but no unique_key is configured? dbt will perform an append-only INSERT, resulting in duplicate records containing both the old and updated versions of the same entity.
• Does on_schema_change='append_new_columns' populate values for older rows? No, it alters the table schema, leaving previous rows with NULL values until you perform a full refresh.