dbt Incremental Models and Snapshots

In modern data engineering, repeatedly transforming entire datasets is a costly drain on time and computational resources. dbt incremental models and snapshots are foundational techniques that enable you to build efficient transformation pipelines which process only new or changed data. Mastering these approaches is essential for scaling your data warehouse operations, controlling costs, and maintaining accurate historical records.

Foundations of Incremental Data Processing

At its core, incremental processing is the practice of updating a dataset by only applying transformations to new records since the last run, rather than rebuilding the entire table. This is the engine behind cost-effective and performant data pipelines. In dbt, this concept is materialized through incremental models, which are SQL models configured to update incrementally. The primary advantage is stark: when dealing with large datasets, you avoid redundant computation on unchanged rows, leading to faster build times and significantly reduced warehouse compute costs. For instance, if you have a billion-row fact table where only one million rows change daily, an incremental model processes just that one million, not the full billion.

The alternative, a full refresh, rebuilds the entire table from scratch each time. While sometimes necessary for correctness after schema changes, full refreshes are inefficient for frequent updates. Incremental models strike a balance by using metadata to identify what's new. This requires careful design around a reliable timestamp or id column that can monotonically identify new data. Without a clear incremental logic, you risk missing data or creating duplicates, which underscores why understanding the mechanics is the first step toward reliable pipelines.

Implementing dbt Incremental Models

Creating an incremental model in dbt starts with a configuration block. You specify materialized='incremental' in the model's config. The heart of the incremental logic is the is_incremental() macro. This Jinja function returns true when dbt is running in incremental mode, allowing you to conditionally filter your SQL to target only new data.

Consider a scenario where you transform raw web event logs into a cleaned events table. Your model might look like this:

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

SELECT
    event_id,
    user_id,
    event_type,
    event_timestamp
FROM {{ source('logging', 'raw_events') }}
{% if is_incremental() %}
    WHERE event_timestamp > (SELECT MAX(event_timestamp) FROM {{ this }})
{% endif %}

Here, {{ this }} refers to the current model's target table. The WHERE clause only activates during incremental runs, filtering for events newer than the latest one already processed. This simple pattern relies on a reliably increasing event_timestamp. For it to work, you must also define a unique_key when using certain strategies. The unique_key is a column (or combination of columns) that uniquely identifies each row, such as event_id. It is crucial for merge operations where dbt needs to determine if an incoming row is an update to an existing record or a brand-new insertion.

Selecting and Configuring Incremental Strategies

dbt supports multiple incremental strategies to handle how new data is integrated: append, merge (the default on many warehouses), and delete+insert. Your choice depends on your data's characteristics and your warehouse's capabilities.

The append strategy is the simplest. It merely inserts all new rows returned by the model's SQL into the target table. It assumes no updates to existing rows and is ideal for immutable, append-only event streams. You configure it with incremental_strategy='append' in your config.

The merge strategy uses SQL's MERGE statement (or equivalent) to insert new rows and update existing ones if the unique_key matches. This is essential for slowly changing dimensions where records can be corrected or amended. For example, if a user's email address is updated in the source, a merge operation ensures the target table reflects this change. Configuration involves specifying both incremental_strategy='merge' and unique_key='user_id'.

The delete+insert strategy is a two-phase operation: it first deletes all records from the target table that match the unique_key of the incoming data, then inserts the new set. This is useful on data warehouses that don't support MERGE or when you need to fully replace a subset of records. It can be more expensive but guarantees a complete refresh of the identified rows. You control this with incremental_strategy='delete+insert' and a defined unique_key.

Choosing the right strategy requires analyzing your data update patterns. Append-only data benefits from the simplicity of append. Data with updates necessitates merge or delete+insert. Always verify your data warehouse's support for these operations, as syntax and performance can vary.

Introduction to dbt Snapshots and SCD Type 2 Tracking

While incremental models handle new and updated data, dbt snapshots are designed specifically for tracking historical changes to rows over time, implementing Slowly Changing Dimension (SCD) Type 2 tracking. An SCD Type 2 table maintains a full history of changes by adding new rows for each modification, each with effective and expiration timestamps. This allows you to query the state of any record at any point in history.

Snapshots are distinct from models; they are configured as snapshot blocks in .sql files and use a snapshot strategy to detect changes. The two primary strategies are timestamp and check. A common use case is tracking dimension table changes, such as a products table where prices or categories may be updated. Without snapshots, you would only see the current state. With a snapshot, you can analyze historical pricing trends or audit changes.

Creating a snapshot involves defining the source data, a unique key, and a strategy. For example, to snapshot a products table:

{% snapshot products_snapshot %}

{{
    config(
      target_schema='snapshots',
      unique_key='product_id',
      strategy='timestamp',
      updated_at='last_modified_date',
    )
}}

SELECT * FROM {{ source('erp', 'products') }}

{% endsnapshot %}

When dbt runs this snapshot, it compares the current source data against the previous snapshot table. For any row where the updated_at timestamp has changed, it invalidates the old record (by updating its dbt_valid_to date) and inserts a new current record. This creates a timeline for each product_id.

Snapshot Strategies and Optimization for Scale

The timestamp strategy relies on a column that indicates when a row was last updated, like updated_at or last_modified_date. It's efficient and straightforward but requires that your source system reliably updates this timestamp on every change.

The check strategy is used when no reliable timestamp exists. Instead, you specify a list of columns to check for changes. If any value in these columns differs from the snapshot table, dbt records a new historical row. You configure it with strategy='check' and check_cols=['price', 'category']. This strategy can be more computationally expensive as it requires comparing values across multiple columns.

Optimizing incremental models for large datasets is critical for production performance. Beyond selecting the right strategy, consider these techniques: use clustering keys on your incremental model's target table to align with your WHERE filter (e.g., cluster on event_timestamp), which drastically improves query speed during incremental runs. Implement indexes where supported. Also, ensure your unique_key is properly indexed for merge operations. For snapshots, performance can degrade as history grows; periodically archiving old snapshots or using partitioning can help. Always test the incremental logic on a subset of data to confirm it correctly identifies new and changed rows without missing data or creating duplicates.

Common Pitfalls

1. Omitting or Misconfiguring the unique_key for Merge Strategies: If you use the merge strategy without a unique_key, or with a non-unique key, dbt may fail or produce incorrect results by updating multiple rows. Correction: Always verify that your unique_key uniquely identifies each row. Use composite keys if necessary, like ['customer_id', 'start_date'].

1. Relying on Unreliable Incremental Logic: Using a timestamp column that is not monotonically increasing or can have updates to old records can cause data loss. For example, if backfills occur with older timestamps, your WHERE event_timestamp > MAX(...) filter will miss them. Correction: Use a combination of a high-watermark timestamp and an id column, or consider a merge strategy that can handle updates to any row based on unique_key.

1. Ignoring Deletions in Source Data: The standard incremental append and merge strategies do not automatically handle row deletions from the source system. If a record is deleted upstream, it remains in your incremental table, leading to data inconsistency. Correction: For soft deletes, include a filter in your model logic (e.g., WHERE is_deleted = FALSE). For hard deletes, you may need a periodic full refresh or a more complex CDC (Change Data Capture) pipeline.

1. Over-snapshotting with the Check Strategy: Using the check strategy on many columns or on very wide tables can make snapshot runs slow and expensive. Correction: Only include columns that truly signify a meaningful change. If possible, advocate for an updated_at column in the source to switch to the more efficient timestamp strategy.

Summary

• dbt incremental models are configured with materialized='incremental' and use the is_incremental() macro to filter data, processing only new or changed rows to save compute costs and time.
• The unique_key parameter is essential for merge and delete+insert strategies, enabling dbt to identify and update existing records correctly.
• Incremental strategy selection—append, merge, or delete+insert—depends on your data update patterns and warehouse capabilities, with merge being the go-to for handling updates.
• dbt snapshots implement SCD Type 2 tracking, creating historical records of row changes using snapshot strategies like timestamp (based on an updated column) or check (based on column value comparisons).
• Optimization for large datasets involves using clustering, indexing, and careful incremental logic design to maintain performance as data volume grows.
• Avoid common pitfalls by ensuring reliable incremental logic, correctly configuring keys, and planning for source data deletions.