# How to Implement Incremental Models in dbt Using incremental_strategy

> Implement dbt incremental models efficiently using incremental_strategy merge. Process only new or changed rows with the is_incremental macro for optimized data pipelines.

- Repository: [DataTalksClub/data-engineering-zoomcamp](https://github.com/DataTalksClub/data-engineering-zoomcamp)
- Tags: how-to-guide
- Published: 2026-05-30

---

**Configure your dbt model with `materialized='incremental'` and `incremental_strategy='merge'` to process only new or changed rows, using the `is_incremental()` macro to filter source data against the existing target table.**

Incremental models in dbt allow data teams to update large tables efficiently by processing only new records rather than rebuilding entire datasets. In the **DataTalksClub/data-engineering-zoomcamp** project, the `fct_trips` fact table demonstrates production-grade incremental processing using the `merge` incremental_strategy to handle updates idempotently.


## Configuring the Incremental Strategy

The foundation of an incremental model rests in the **config block** at the top of your SQL file. In [`models/marts/fct_trips.sql`](https://github.com/DataTalksClub/data-engineering-zoomcamp/blob/main/models/marts/fct_trips.sql), the configuration specifies four critical parameters that control how dbt manages the table:

```sql
{{
  config(
    materialized='incremental',
    unique_key='trip_id',
    incremental_strategy='merge',
    on_schema_change='append_new_columns'
  )
}}

```

- **`materialized='incremental'`** tells dbt to treat this as an incremental model.
- **`unique_key='trip_id'`** defines the column used to identify records for upserts.
- **`incremental_strategy='merge'`** selects the upsert pattern (insert new rows, update existing ones).
- **`on_schema_change='append_new_columns'`** ensures new columns added to the source query automatically appear in the target table without full rebuilds.

According to the course notes in [`05-data-platforms/notes/03-nyc-taxi-pipeline.md`](https://github.com/DataTalksClub/data-engineering-zoomcamp/blob/main/05-data-platforms/notes/03-nyc-taxi-pipeline.md), alternative strategies include `append` (insert-only), `delete+insert`, and `time_interval`, though `merge` remains the default for BigQuery and most modern warehouses.


## Implementing Incremental Logic with is_incremental()

To process only new data, you must filter the source query to exclude records already present in the target. The **`is_incremental()`** macro detects whether dbt is currently running an incremental build (as opposed to a full refresh), allowing you to append a conditional `WHERE` clause:

```sql
{% if is_incremental() %}
  WHERE trips.pickup_datetime > (SELECT MAX(pickup_datetime) FROM {{ this }})
{% endif %}

```

This pattern compares `pickup_datetime` from the source `int_trips` model against the maximum value already stored in the target `fct_trips` table. The `{{ this }}` variable references the current model's output table. During the first run (full refresh), this block is skipped, loading all history; during subsequent runs, only newer trips enter the pipeline.


## Complete Model Implementation

The full [`fct_trips.sql`](https://github.com/DataTalksClub/data-engineering-zoomcamp/blob/main/fct_trips.sql) model combines the config block, base query joining dimension tables, and incremental filtering:

```sql
-- models/marts/fct_trips.sql
{{ 
  config(
    materialized='incremental',
    unique_key='trip_id',
    incremental_strategy='merge',
    on_schema_change='append_new_columns'
  ) 
}}

SELECT
    trips.trip_id,
    trips.vendor_id,
    trips.service_type,
    trips.rate_code_id,
    trips.pickup_location_id,
    pz.borough AS pickup_borough,
    pz.zone   AS pickup_zone,
    trips.dropoff_location_id,
    dz.borough AS dropoff_borough,
    dz.zone    AS dropoff_zone,
    trips.pickup_datetime,
    trips.dropoff_datetime,
    trips.store_and_fwd_flag,
    trips.passenger_count,
    trips.trip_distance,
    trips.trip_type,
    {{ get_trip_duration_minutes('trips.pickup_datetime','trips.dropoff_datetime') }} AS trip_duration_minutes,
    trips.fare_amount,
    trips.extra,
    trips.mta_tax,
    trips.tip_amount,
    trips.tolls_amount,
    trips.ehail_fee,
    trips.improvement_surcharge,
    trips.total_amount,
    trips.payment_type,
    trips.payment_type_description
FROM {{ ref('int_trips') }} AS trips
LEFT JOIN {{ ref('dim_zones') }} AS pz ON trips.pickup_location_id = pz.location_id
LEFT JOIN {{ ref('dim_zones') }} AS dz ON trips.dropoff_location_id = dz.location_id

{% if is_incremental() %}
  WHERE trips.pickup_datetime > (SELECT MAX(pickup_datetime) FROM {{ this }})
{% endif %}

```

This implementation references the custom macro `get_trip_duration_minutes` defined in [`macros/get_trip_duration_minutes.sql`](https://github.com/DataTalksClub/data-engineering-zoomcamp/blob/main/macros/get_trip_duration_minutes.sql), demonstrating how incremental models integrate with broader dbt project architecture.


## Running Incremental Models

The execution command determines whether dbt performs a full table rebuild or an incremental update:

- **Full refresh** (re-creates the table from scratch): `dbt run --full-refresh -m fct_trips`
- **Incremental run** (adds only new records based on the logic above): `dbt run -m fct_trips`

After the initial full build, standard runs execute the `merge` strategy, generating warehouse-specific SQL (such as BigQuery `MERGE` statements) that atomically inserts new `trip_id` values and updates existing ones if the source data changes.


## Summary

- Incremental models reduce processing time and compute costs by filtering for new records only after the initial build.
- The `incremental_strategy='merge'` option performs idempotent upserts based on the configured `unique_key`, ensuring data consistency without duplication.
- Use the `is_incremental()` macro to dynamically filter source queries, comparing timestamps or IDs against the current target table state.
- Set `on_schema_change='append_new_columns'` to safely evolve schemas without requiring `--full-refresh`.
- Execute `dbt run --full-refresh` to rebuild the table completely; omit the flag for incremental processing.


## Frequently Asked Questions

### What is the difference between merge and append incremental strategies?

**`merge`** performs an upsert: it inserts new records and updates existing ones whose `unique_key` matches, making it ideal for slowly changing dimensions ordeduplicated facts. **`append`** inserts only new rows without checking for updates, suitable for immutable event streams like logs where records never change after insertion.

### When should I use the is_incremental() macro?

Use `is_incremental()` whenever your incremental model selects from a source larger than the target table. Wrap filtering logic—typically a `WHERE` clause comparing a timestamp or incrementing ID—to ensure the model processes only data newer than the current target maximum. Skip this macro only when the source itself is already filtered externally or when using the `delete+insert` strategy.

### How does on_schema_change work with incremental models?

The **`on_schema_change`** parameter controls behavior when the source query adds or removes columns. Setting it to `append_new_columns` automatically adds new columns to the existing target table during the incremental run. Other options include `fail` (raise an error), `ignore` (silently ignore schema changes), and `sync_all_columns` (add new columns and remove missing ones, with caution).

### How do I perform a full refresh of an incremental model?

Run `dbt run --full-refresh -m <model_name>` to drop and recreate the entire table. Use this when your business logic changes significantly, when fixing data quality issues that require recomputing historical records, or when the `unique_key` definition changes. Without the `--full-refresh` flag, dbt runs the incremental logic, processing only new rows.