The Complete Production SOP: How to Deploy and Run dbt Core Incremental Models & Window Partitions in Autonomous AI Agents

---
name: "dbt-core-incremental-models-snowflake"
description: "Transform enterprise data warehouses efficiently using dbt Core incremental materializations on Snowflake."
metadata:
  author: "dbt Labs"
  version: "1.0.0"
  runtime_compatibility:
    - "codex"
    - "claude-code"
    - "cursor"
    - "windsurf"
triggers:
  - "write dbt incremental model"
  - "configure dbt unique_key merge"
  - "handle late arriving data dbt"
permissions:
  fs_read: true
  fs_write: "workspace"
  shell_exec: false
  net_access: false
---

# dbt Core Incremental Models & Window Partitions
Develops dbt models: configuring incremental materializations with merge strategies, handling late-arriving records with dynamic lookbacks, and optimizing warehouse compute costs.

{{ config(
    materialized = 'incremental',
    unique_key = 'order_id',
    incremental_strategy = 'merge',
    on_schema_change = 'fail',
    cluster_by = ['order_date']
) }}

WITH source_data AS (
    SELECT 
        order_id,
        customer_id,
        order_status,
        order_amount,
        updated_at,
        order_date
    FROM {{ ref('stg_ecommerce__orders') }}
),

filtered_data AS (
    SELECT *
    FROM source_data
    {% if is_incremental() %}
    WHERE updated_at >= (SELECT coalesce(max(updated_at), '1970-01-01') - INTERVAL '3 DAYS' FROM {{ this }})
    {% endif %}
)

SELECT * FROM filtered_data