# Incremental Materialization

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

{% hint style="info" %}
While these examples use Snowflake syntax, the core concepts apply to most data warehouses. Specific syntax and available features may vary by platform.
{% endhint %}

```sql
{{
  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](/app-help/concepts/dbt-fundamentals/model-materializations/incremental-materialization/using-merge-for-incremental-models.md)                  | Updates existing records and inserts new ones          | Tables requiring both inserts and updates |
| [Delete+Insert](/app-help/concepts/dbt-fundamentals/model-materializations/incremental-materialization/using-delete+insert-for-incremental-models.md)  | Deletes matching records and reinserts new versions    | Batch updates where most records change   |
| [Append-Only](/app-help/concepts/dbt-fundamentals/model-materializations/incremental-materialization/using-append-for-incremental-models.md)           | Simply adds new records without updating existing ones | Event logs, immutable data                |
| [Microbatch (Beta)](/app-help/concepts/dbt-fundamentals/model-materializations/incremental-materialization/using-microbatch-for-incremental-models.md) | 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
{{
  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
{{
  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
{{
  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
-- 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
{% 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
{{
  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
dbt run --full-refresh --select model_name
```

**4. Multiple Column Keys**

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

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


---

# Agent Instructions: Querying This Documentation

If you need additional information that is not directly available in this page, you can query the documentation dynamically by asking a question.

Perform an HTTP GET request on the current page URL with the `ask` query parameter:

```
GET https://docs.paradime.io/app-help/concepts/dbt-fundamentals/model-materializations/incremental-materialization.md?ask=<question>
```

The question should be specific, self-contained, and written in natural language.
The response will contain a direct answer to the question and relevant excerpts and sources from the documentation.

Use this mechanism when the answer is not explicitly present in the current page, you need clarification or additional context, or you want to retrieve related documentation sections.