#061: How to Build Incremental Models w/ dbt

An incremental model in dbt processes only the new or changed rows from its source on each run instead of rebuilding the whole table. You set materialized='incremental' in the config block, wrap a filter in the is_incremental() macro so it only applies once the table exists, and compare a column like invoiced_at against the maximum already loaded. By default dbt appends whatever passes that filter, and adding a unique_key makes it update matching rows instead of inserting duplicates. The on_schema_change config decides what happens when columns are added or dropped, and the --full-refresh flag rebuilds the table from scratch when something drifts. The payoff is shorter runs and lower compute on large tables, at the cost of a few more decisions up front.

What incremental models are

The default materialization in dbt is a table. Every run drops the table and recreates it from the full source, which is fine until the source is large or the transformation is expensive.

An incremental materialization changes the behavior after the first run: dbt filters the source to rows that are new or updated since the last run and only transforms those.

Less data in means a shorter runtime, better performance and, on warehouses that bill by compute, a lower bill. That's the whole appeal.

Where people stall

Setting one up takes a few steps, and I find this is where people stall. There are four pieces to get right:

• A config block that sets the materialization.
• A Jinja macro that switches the filter on and off.
• A filter that has to be written carefully.
• A set of decisions about keys and schema changes.

The example below uses a tiny invoices table on purpose. You don't need big data to learn this. The mechanics are identical whether the table has ten rows or ten million.

How to use is_incremental()

The dbt documentation example uses a staging events model with an event_time column. Mine uses a raw invoices table and an invoiced_at column. The shape is the same.

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

with source as (

    select * from {{ source('raw', 'invoices') }}

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

),

final as (

    select
        invoice_id,
        customer_id,
        amount,
        invoiced_at,
        '{{ invocation_id }}' as batch_id
    from source

)

select * from final

The if block

When is_incremental() evaluates to true, dbt drops the where clause into the compiled SQL and the source CTE gets filtered. When it evaluates to false, the where clause disappears and the model runs as a normal full build.

Because every downstream CTE selects from source, trimming that first CTE trims the final result.

The filter

The where clause looks at whatever column identifies the latest record, a timestamp in most cases, sometimes an incrementing ID. It keeps rows where that value is greater than the max already in the table.

{{ this }} is dbt's reference to the current model in the warehouse, so max(invoiced_at) from {{ this }} is the high-water mark of what has already been loaded.

The batch_id column

I also add a batch_id column from invocation_id, a unique identifier dbt generates for each run.

It isn't required, but it makes every example below easy to read because you can see which rows landed in which run. It's a useful logging column in production too.

When the macro returns true

is_incremental() only returns true when all three conditions are met:

• The target table already exists in the warehouse.
• dbt is not running in full refresh mode.
• The model is configured with the incremental materialization.

The first run is always a full build. On the first dbt run we pass two of the three, but the table doesn't exist yet. The compiled SQL in the target folder is a plain create or replace, and every row comes through with the same batch_id.

Inserting new data

Now insert a second batch into the raw table: three invoices with invoiced_at values later than anything already loaded. Run dbt run --select invoices again.

This time all three conditions hold, and the compiled SQL is an insert statement rather than a create or replace. Only the three new rows pass the filter, and they land with a new batch_id.

Insert two more rows and run again. Before the run, the model's table still shows the previous state. After it, the two new rows appear under a third batch_id. Batch one, batch two, batch three, each run adding only what's new.

Append only by default

Note that with nothing else configured, this is append only. dbt isn't considering primary keys at this point. If a row with an existing invoice_id shows up with a later timestamp, it passes the filter and gets inserted as a second row.

Updating existing rows

Often you want updates, not duplicates. Invoice 1 and invoice 4 already exist, a corrected version of each arrives, and the model should replace the old row rather than add to it. That is what the unique_key config is for.

{{
  config(
    materialized='incremental',
    unique_key='invoice_id'
  )
}}

Three ways to define the key

The unique key can be defined three ways:

• A single column, such as invoice_id.
• A list of columns that are unique in combination, such as unique_key=['invoice_id', 'line_number'].
• A surrogate key generated with the generate_surrogate_key macro from dbt_utils.

Either way the goal is one value dbt can match on. When an incoming row matches an existing key, dbt updates it. When it doesn't, dbt inserts it.

The adapter picks the strategy

Which strategy dbt uses under the hood, merge, delete and insert, or something else, depends on your adapter. You can set it explicitly with incremental_strategy, but for most teams defining the unique key and taking the adapter default is enough to start.

After adding the key and rerunning, invoices 1 and 4 show the new invoiced_at values and a new batch_id. The row count hasn't changed.

Handling schema changes

Sources change. A column gets added to the raw table, or one gets dropped, and the incremental model has to decide what to do with that. The on_schema_change config has four values.

ignore

The default. New columns are ignored, missing columns are ignored, and the model carries on as if nothing happened.

fail

The run fails as soon as the source and target schemas are out of sync. You then go fix it by hand.

append_new_columns

New columns are added to the target table and populated going forward. Nothing is removed, and existing rows are not backfilled.

sync_all_columns

New columns are added and columns that no longer exist in the source are removed, keeping the target in sync with the source. Still no backfill.

None of these options retroactively update old rows. If you need history filled in for a new column, that's a full refresh.

Seeing it fail, then append

To see it work, add a payment_terms column to the raw table and set the config to fail first. The run stops at the incremental model with a message that the schemas are out of sync and a pointer to the config.

Switch to append_new_columns and run again. The column gets added with nulls in every existing row. Insert a fourth batch and the new rows carry values for payment_terms while the old ones stay null.

{{
  config(
    materialized='incremental',
    unique_key='invoice_id',
    on_schema_change='append_new_columns'
  )
}}

The row that never landed

This is the mistake I see most often. That fourth batch included invoice 11, and it never showed up in the model. Its invoiced_at was earlier than the max already in the table, because the update rows merged in the previous step carried later dates and moved the high-water mark forward.

The filter threw it out, and nothing warned me. Pushing its timestamp past the current max and re-inserting fixed it in the demo, but in a real pipeline that row is simply gone.

Late-arriving data needs a lookback window or a different filter column, and it's worth deciding that before the model goes to production.

Using the --full-refresh flag

There are a few reasons to rebuild an incremental model from scratch:

• The table has gotten out of sync with the source.
• You added a column and want history backfilled.
• You just want to start over.

The command is the same run with one flag.

dbt run --select invoices --full-refresh

This treats the model as a regular table and rebuilds it from scratch. Every row comes back with the same batch_id.

You don't want to do this on a schedule, since it throws away the savings the incremental model exists for, but it is the right tool when the table is wrong.

What a full refresh does not do

A full refresh does not dedupe. After the refresh, invoices 1, 4 and 11 appear twice. The raw table still holds both the original row and the corrected row for each, and a full refresh is a plain create or replace from source.

The unique key merge only runs during an incremental load. If raw keeps multiple versions of a record, deduplicate in the staging model so a full refresh and an incremental run produce the same table.

Related reading

• Start Using Jinja in dbt Macros (3 Examples)
• The "Key" To Building A Reliable Data Model
• How to Track History in a Data Warehouse
• How to Create a 3 Layer Data Model Pipeline

Final takeaway

Incremental models are usually the first optimization I reach for when a client's dbt runs start taking too long. The two mistakes above, the silent late-arriving row and the full refresh that brings duplicates back, are the ones I've had to debug most often on real projects.