> ## Documentation Index
> Fetch the complete documentation index at: https://docs.paradime.io/llms.txt
> Use this file to discover all available pages before exploring further.

# Incremental Materialization

> Explains dbt's incremental modeling capabilities for updating only new/modified data in large/complex transformations, covering different strategies and best practices.

Incremental models allow you to update only new or modified data in your warehouse instead of rebuilding entire tables. This optimization is particularly valuable when working with:

* Large datasets (millions/billions of rows)
* Computationally expensive transformations
* Time-series data with frequent updates

### Basic Configuration

<Info>
  While these examples use Snowflake syntax, the core concepts apply to most data warehouses. Specific syntax and available features may vary by platform.
</Info>

```sql theme={"system"}
{{
  config(
    materialized='incremental',
    unique_key='id'
  )
}}

SELECT 
    id,
    status,
    amount,
    updated_at
FROM {{ ref('stg_source') }}

{% if is_incremental() %}
    -- This filter will only be applied on an incremental run
    WHERE updated_at > (SELECT MAX(updated_at) FROM {{ this }})
{% endif %}
```

**Key Components**

1. **Materialization Config**: Set `materialized='incremental'` in your config block
2. **Unique Key**: Define what makes each row unique (single column or multiple columns)
3. **Incremental Logic**: Use the `is_incremental()` macro to filter for new/changed records

***

### Incremental Strategies

dbt™ supports several strategies for incremental models, each with specific use cases:

| Strategy                                                                                                                                 | Description                                            | Best For                                  |
| ---------------------------------------------------------------------------------------------------------------------------------------- | ------------------------------------------------------ | ----------------------------------------- |
| [Merge](/guides/dbt-fundamentals/model-materializations/incremental-materialization/using-merge-for-incremental-models)                  | Updates existing records and inserts new ones          | Tables requiring both inserts and updates |
| [Delete+Insert](/guides/dbt-fundamentals/model-materializations/incremental-materialization/using-delete+insert-for-incremental-models)  | Deletes matching records and reinserts new versions    | Batch updates where most records change   |
| [Append-Only](/guides/dbt-fundamentals/model-materializations/incremental-materialization/using-append-for-incremental-models)           | Simply adds new records without updating existing ones | Event logs, immutable data                |
| [Microbatch (Beta)](/guides/dbt-fundamentals/model-materializations/incremental-materialization/using-microbatch-for-incremental-models) | Processes updates in smaller batches                   | Very large time-series datasets           |

For detailed examples and configuration options for each strategy, see their dedicated pages.

***

### Advanced Features

**1. Schema Change Management**

Handle column additions or removals with the `on_schema_change` parameter:

```sql theme={"system"}
{{
  config(
    materialized='incremental',
    on_schema_change='sync_all_columns'  -- Options: ignore, fail, append_new_columns, sync_all_columns
  )
}}
```

Options explained:

* `sync_all_columns`: Automatically adapts to column changes (recommended)
* `fail`: Halts execution when schema changes (useful during development)
* `ignore`: Maintains existing schema (use cautiously)
* `append_new_columns`: Adds new columns without removing old ones

**2. Incremental Predicates**

Optimize performance for large datasets:

```sql theme={"system"}
{{
  config(
    materialized='incremental',
    incremental_predicates=[
      "DBT_INTERNAL_DEST.session_start > dateadd(day, -7, current_date)"
    ],
    cluster_by=['session_start']
  )
}}
```

This configuration:

* Limits the scan of existing data
* Improves merge performance
* Works with clustering for better query optimization

**3. Strategy-Specific Configurations**

Control column updates in merge operations:

```sql theme={"system"}
{{
  config(
    materialized='incremental',
    merge_update_columns=['email', 'ip_address'],  -- Only update these columns
    merge_exclude_columns=['created_at']  -- Never update these columns
  )
}}
```

**4. Custom Strategies**

Create your own incremental strategy:

```sql theme={"system"}
-- macros/my_custom_strategies.sql
{% macro get_incremental_insert_only_sql(arg_dict) %}
  {% do return(some_custom_macro_with_sql(
    arg_dict["target_relation"],
    arg_dict["temp_relation"],
    arg_dict["unique_key"],
    arg_dict["dest_columns"],
    arg_dict["incremental_predicates"]
  )) %}
{% endmacro %}

-- models/my_model.sql
{{
  config(
    materialized='incremental',
    incremental_strategy='insert_only'
  )
}}
```

***

### Best Practices

**1. Handle Late-Arriving Data**

Data doesn't always arrive in perfect chronological order. Include a buffer period in your incremental logic:

```sql theme={"system"}
{% if is_incremental() %}
    -- Look back 3 days to catch late-arriving data
    WHERE updated_at > DATEADD(days, -3, (SELECT MAX(updated_at) FROM {{ this }}))
{% endif %}
```

**2. Optimize Performance**

Use appropriate configurations to improve query performance and efficiency:

```sql theme={"system"}
{{
  config(
    materialized='incremental',
    cluster_by=['date_field', 'customer_id'],  -- Improve query performance
    merge_update_columns=['status', 'amount']  -- Minimize update overhead
  )
}}
```

**3. Regular Maintenance**

To prevent potential data inconsistencies that might accumulate over time, periodically rebuild your entire table:

```bash theme={"system"}
dbt run --full-refresh --select model_name
```

**4. Multiple Column Keys**

When a single column isn't enough to identify unique records:

```sql theme={"system"}
{{
  config(
    materialized='incremental',
    unique_key=['customer_id', 'order_date']
  )
}}
```


## Related topics

- [Incremental Materialization](/guides/dbt-fundamentals/model-materializations/incremental-materialization/index.md)
- [Model Materializations](/guides/dbt-fundamentals/model-materializations/index.md)
- [dbt™ Model Query Cost Optimizer — BigQuery](/guides/programmable-agents/dbt-tm-model-query-cost-optimizer-bigquery.md)
- [dbt™ Model Query Cost Optimizer — Snowflake](/guides/programmable-agents/dbt-tm-model-query-cost-optimizer-snowflake.md)
- [Table Materialization](/guides/dbt-fundamentals/model-materializations/table-materialization.md)