How to Implement Incremental Models in dbt Using incremental_strategy

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, the configuration specifies four critical parameters that control how dbt manages the table:

{{
  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, 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:

{% 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 model combines the config block, base query joining dimension tables, and incremental filtering:

-- 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, 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.

{
  "mcpServers": {
    "instagit": {
      "command": "npx",
      "args": ["-y", "instagit@latest"]
    }
  }
}