dbt
Advanced 15 min read dbt

dbt Incremental Models

Rebuild small, append big

Incremental models append only new rows instead of rebuilding every time. They keep big fact tables fast, but add a contract: a reliable way to detect what is new.

dbtincrementalincremental_strategyappenddelete+insertlarge tables
Prerequisites: dbt Models dbt Test Basics
Before we start

What you will learn

  • Explain when incremental materialization wins and when it bites
  • Configure a filter and is_incremental() block
  • Choose an incremental strategy for your warehouse
Introduction

What is this?

An incremental model materializes as a table that only processes new rows each run. Inside the model, an is_incremental() block filters to rows newer than the table's last max timestamp. The base table persists, so repeated runs do not reprocess full history. Strategies vary: append (Snowflake/BigQuery), delete+insert, and merge.

Motivation

Why do you need it?

  • A 2-year fact table rebuilt nightly is slow, expensive, and wasteful.
  • Incremental keeps freshness near-real-time without full refreshes.
  • It is the standard answer for append-heavy event and order data.
Think about it

Mental model

Imagine

Re-copying the whole encyclopedia every night

Think of

Appending today's new pages to the set

Both finish complete, but one costs a fraction of the other and leaves the archive just as current.

Mechanics

How it works

  • Mark the model incremental via config(materialized='incremental').
  • Inside the SQL, wrap new-row filters in {% if is_incremental() %} blocks.
  • Define the unique_key for upsert or delete+insert strategies.
  • dbt queries the existing table to decide the incremental filter boundary at runtime.

The first run builds full history; later runs append only what is new.

First run
Filter
Merge
Grow
Real world

In production

  • A daily events fact table processes only yesterday's rows each night.
  • A stream of order updates uses delete+insert on a unique key to keep corrections landing.
  • A large subscription table merges changes daily instead of full rebuilding.
Guidelines

Best practices

  • Pick a filter bound (max created_at) and declare it, plus a unique_key when applicable.
  • Test the incremental model after a full refresh — the two must agree.
  • Keep the incremental block logic simple; unreadable filters are unverifiable.
Watch out

Common mistakes

  • Omitting the unique_key on an upsert strategy and silently duplicating rows.
  • Filtering on a timestamp that backfills change past the bound, missing corrections.
  • Using incremental on small tables where the complexity only costs maintenance.
When things break

Debugging tips

  • If history looks missing, the filter bound was too tight — widen and rebuild.
  • If duplicates appear, the unique_key does not match the true grain — inspect before blaming the strategy.
Interviews

Interview checkpoint

Practice

Hands-on lab

Build an Incremental Events Fact
45 min · advanced
In progress
Objectives
  • Mark a model incremental with an is_incremental() filter
  • Configure a unique_key and a strategy
  • Verify incremental and full-refresh builds agree

Build an Incremental Events Fact

45 min · advanced

0/3 steps
0%
sandbox · Linux sandbox
$
Try these commands
Test yourself

Quick quiz

0/2
1

The is_incremental() block is empty on

2

An upsert-style incremental model without a unique_key will

Go deeper

Official documentation

dbt incremental models
docs
incremental strategies
docs