Incremental Strategy in dbt: What It Is and How It Works

The incremental strategy in dbt allows you to build models that update only new or changed data instead of rebuilding the entire dataset every time. This makes data processing faster and more efficient by appending or updating rows incrementally.

How It Works

The incremental strategy in dbt works like updating a growing notebook instead of rewriting the whole book every time you add new notes. Instead of recreating the entire table from scratch, dbt adds only the new or changed data since the last run.

It uses a unique key or condition to identify which rows are new or need updating. This way, it saves time and computing resources, especially when working with large datasets that change over time.

Example

This example shows a simple dbt model using the incremental strategy to add new sales records based on a date column.

sql

{{ config(
    materialized='incremental',
    unique_key='order_id'
) }}

select
    order_id,
    customer_id,
    order_date,
    total_amount
from source.sales
{% if is_incremental() %}
    where order_date > (select max(order_date) from {{ this }})
{% endif %}

Output

A table named after the model is created or updated with only new sales orders where order_date is greater than the last loaded date.

When to Use

Use the incremental strategy when your data grows over time and you want to avoid rebuilding large tables from scratch. It is ideal for daily or hourly data loads where only new or updated records need to be processed.

For example, if you have a sales database that adds new orders every day, incremental models let you update your analytics tables quickly without reprocessing all past orders.

Key Points

• Incremental models update only new or changed data, saving time and resources.
• They require a unique key or condition to identify new rows.
• Use is_incremental() to write logic that runs only during incremental runs.
• Best for large datasets that grow over time with new records.

Key Takeaways

The incremental strategy updates only new or changed data instead of rebuilding entire tables.

It requires a unique key or condition to identify which rows to add or update.

Use incremental models to speed up processing of growing datasets.

The is_incremental() function helps control logic for incremental runs.

Ideal for daily or frequent data loads with new records.