01. Optimizing Incremental Models
Optimizing Incremental Models in dbt
Incremental models allow dbt to process only new or updated rows rather than rebuilding the entire table.
Optimizing them improves performance, reduces compute costs, and ensures faster data refreshes.
Key Concepts
Incremental models process only new or changed data
Requires a unique key to identify updates
Can leverage merge strategies (insert_overwrite, merge) depending on the warehouse
Works best with timestamps or monotonically increasing IDs
1. Basic Incremental Model Example
{{ config(
materialized='incremental',
unique_key='order_id'
) }}
select *
from {{ source('app', 'orders') }}
{% if is_incremental() %}
where updated_at > (select max(updated_at) from {{ this }})
{% endif %}
Explanation:
materialized='incremental'→ Build the table incrementallyunique_key→ Ensures correct merging of updated rowsis_incremental()→ Limits the query to only new or updated records
2. Merge Strategy for Incremental Models
{{ config(
materialized='incremental',
unique_key='customer_id',
incremental_strategy='merge'
) }}
select *
from {{ source('app', 'customers') }}
incremental_strategy='merge'→ Updates existing rows efficiently- Recommended for warehouses that support MERGE (Snowflake, BigQuery, Redshift)
3. Partitioning and Clustering
- Partition tables for faster incremental loads on large datasets
- Cluster tables to improve query performance on frequently filtered columns
{{ config(
materialized='incremental',
unique_key='order_id',
cluster_by=['customer_id'],
partition_by={'field': 'order_date', 'data_type': 'date'}
) }}
Best Practices
Always define a unique key
Use is_incremental() to limit incremental queries
Prefer merge strategy for updates rather than truncating tables
Partition and cluster large tables for performance optimization
Schedule incremental models in your orchestration tool for efficiency