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

The append strategy in incremental dbt means adding only new rows to a target table without updating or deleting existing data. It is used when your data source only grows by new records, making incremental loads faster and simpler by just appending new data.

How It Works

The append strategy in dbt incremental models works by adding new rows from your source data to the existing target table without changing or removing any old rows. Imagine you have a notebook where you write down new daily sales records. Instead of rewriting the whole notebook every day, you just add the new sales at the end. This is exactly how the append strategy works—it only adds new data.

This approach is efficient when your data source only grows and does not change past records. dbt checks for new rows based on a unique key or timestamp and inserts them into the target table. It does not update or delete any existing rows, which makes the process faster and simpler.

Example

This example shows a simple dbt model using the append strategy to add new rows incrementally.

sql

{{ config(
    materialized='incremental',
    incremental_strategy='append'
) }}

select * from source_table

{% if is_incremental() %}
  where created_at > (select max(created_at) from {{ this }})
{% endif %}

Output

This code will add only rows from source_table where created_at is newer than the latest created_at in the target table, appending new data without changing existing rows.

When to Use

Use the append strategy when your source data only adds new records and never updates or deletes old ones. For example, log files, event tracking data, or daily snapshots where each row is unique and never changes.

This strategy is ideal when you want fast incremental loads without the complexity of handling updates or deletes. It reduces processing time and resource use because dbt only inserts new rows.

Key Points

• The append strategy only adds new rows, no updates or deletes.
• It is best for data that only grows, like logs or event data.
• It makes incremental loads faster and simpler.
• Requires a way to identify new rows, often a timestamp or unique ID.

Key Takeaways

The append strategy adds only new rows without modifying existing data.

It is best for datasets that only grow and never update old records.

Using append makes incremental dbt runs faster and simpler.

You need a reliable way to detect new rows, like a timestamp.

Avoid append if your data requires updates or deletions.