dbt Incremental Model Pattern

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

WITH source_orders AS (
    SELECT
        order_id,
        customer_id,
        order_date,
        amount,
        status,
        updated_at
    FROM {{ ref('stg_orders') }}

    {% if is_incremental() %}
    -- Only process rows newer than the last run
    WHERE updated_at > (
        SELECT COALESCE(MAX(updated_at), '1900-01-01')
        FROM {{ this }}
    )
    {% endif %}
),

enriched AS (
    SELECT
        o.*,
        c.segment AS customer_segment,
        c.region,
        o.amount * COALESCE(fx.rate, 1) AS amount_usd
    FROM source_orders o
    LEFT JOIN {{ ref('dim_customers') }} c
        ON o.customer_id = c.customer_id
    LEFT JOIN {{ ref('stg_fx_rates') }} fx
        ON DATE(o.order_date) = fx.rate_date
)

SELECT
    *,
    CURRENT_TIMESTAMP AS _loaded_at
FROM enriched