How to create incremental model dbt

DbtHow-ToBeginner · 3 min read

How to Create Incremental Model in dbt: Simple Guide

To create an incremental model in dbt, define your model SQL file with the config(materialized='incremental') setting and use the is_incremental() function to control which rows to insert or update. This lets dbt add only new or changed data instead of rebuilding the whole table every time.

📐

Syntax

Use the config() function to set the model as incremental. Inside your SQL, use is_incremental() to filter new or updated rows.

config(materialized='incremental'): tells dbt to build the model incrementally.
is_incremental(): a function that returns true if dbt is running an incremental update.
WHERE clause: filters rows to insert only new or changed data during incremental runs.

sql

/* models/my_incremental_model.sql */
{{ config(materialized='incremental') }}

SELECT
  id,
  data,
  updated_at
FROM source_table
{% if is_incremental() %}
  WHERE updated_at > (SELECT MAX(updated_at) FROM {{ this }})
{% endif %}

💻

Example

This example shows a simple incremental model that adds only rows with a newer updated_at timestamp than the current table.

sql

/* models/incremental_orders.sql */
{{ config(materialized='incremental') }}

SELECT
  order_id,
  customer_id,
  order_date,
  updated_at
FROM raw.orders
{% if is_incremental() %}
  WHERE updated_at > (SELECT MAX(updated_at) FROM {{ this }})
{% endif %}

Output

When run, dbt creates or updates the table by inserting only orders with updated_at newer than the last run, saving time and resources.

⚠️

Common Pitfalls

Common mistakes when creating incremental models include:

Not using is_incremental() to filter new data, causing full table rebuilds.
Using a filter that does not correctly identify new or updated rows, leading to missing or duplicate data.
Not having a unique key or primary key to identify rows for updates.
Forgetting to set materialized='incremental' in the config.

sql

/* Wrong: No incremental filter, rebuilds full table every run */
{{ config(materialized='incremental') }}

SELECT * FROM raw.orders

/* Right: Use is_incremental() to filter new rows */
{{ config(materialized='incremental') }}

SELECT * FROM raw.orders
{% if is_incremental() %}
  WHERE updated_at > (SELECT MAX(updated_at) FROM {{ this }})
{% endif %}

📊

Quick Reference

Concept	Description
config(materialized='incremental')	Set model to incremental materialization
is_incremental()	Check if dbt is running an incremental update
Incremental filter	Filter rows to insert only new or updated data
Unique key	Ensure unique key exists for row identification

✅

Key Takeaways

Set your model with config(materialized='incremental') to enable incremental builds.

Use is_incremental() in your SQL to filter only new or updated rows during incremental runs.

Always have a reliable column (like updated_at) to identify new data for incremental loading.

Without proper filtering, dbt will rebuild the entire table, losing incremental benefits.

Test your incremental model to ensure it adds data correctly without duplicates.