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

# Using Merge for Incremental Models

> Details the merge strategy for incremental models, which updates existing records and inserts new ones using a database MERGE statement.

The merge method uses your database's MERGE statement (or equivalent) to update existing records and insert new ones. This method provides the most control over how your incremental model is updated but requires more computational resources.

***

### When to Use Merge Method

* Tables requiring both inserts and updates
* When data consistency is critical
* When specific columns need updating
* For maintaining referential integrity

***

### Advantages and Trade-offs

| Advantages                          | Trade-offs                            |
| ----------------------------------- | ------------------------------------- |
| Most flexible method                | Slower than other methods             |
| Precise control over column updates | More resource-intensive               |
| Maintains data consistency          | Can be costly for very large datasets |
| Handles complex update patterns     | Requires a unique key                 |

***

### Example Implementation

```sql theme={"system"}
{{
  config(
    materialized='incremental',
    unique_key='event_id',
    incremental_strategy='merge'  // Default, can be omitted
  )
}}

SELECT 
    event_id,
    event_type,
    event_time,
    user_id,
    properties
FROM {{ ref('stg_events') }}

{% if is_incremental() %}
    WHERE event_time > (SELECT MAX(event_time) FROM {{ this }})
{% endif %}
```

In this example:

* `unique_key` identifies which records should be updated
* The `WHERE` clause in the `is_incremental()` block filters for new records
* Existing records with matching `event_id` values will be updated
* New records will be inserted

***

### Fine-Tuning Merge Operations

You can precisely control which columns are updated during merge operations:

```sql theme={"system"}
{{
  config(
    materialized='incremental',
    unique_key='user_id',
    incremental_strategy='merge',
    merge_update_columns=['email', 'last_login_at', 'profile_updated_at'],  -- Only update these columns
    merge_exclude_columns=['created_at', 'signup_source']  -- Never update these columns
  )
}}
```

This configuration gives you fine-grained control over the merge process.

***

### Step By Step Guide: Merge Method Implementation

Let's see how the merge method works with a practical example.

**Step 1: Initial Setup and Testing**

First, create the staging model:

```sql theme={"system"}
-- models/staging/stg_test_source.sql
WITH source_data AS (
    SELECT 1 as id,
           'active' as status,
           100 as amount,
           CURRENT_TIMESTAMP() as updated_at
    UNION ALL
    SELECT 2, 'pending', 200, CURRENT_TIMESTAMP()
    UNION ALL
    SELECT 3, 'active', 300, CURRENT_TIMESTAMP()
)
SELECT * FROM source_data
```

Run staging model:

```bash theme={"system"}
dbt run --select stg_test_source
```

Verify staging data in Snowflake:

```sql theme={"system"}
SELECT 
    * 
FROM 
    your_database.your_schema.stg_test_source 
ORDER BY 
    id
```

Expected Result:

```
ID | STATUS  | AMOUNT | UPDATED_AT
1  | active  | 100    | [timestamp]
2  | pending | 200    | [timestamp]
3  | active  | 300    | [timestamp]
```

Create the incremental model:

```sql theme={"system"}
-- models/incremental_test.sql
{{
  config(
    materialized='incremental',
    unique_key='id'
  )
}}

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

{% raw %}
{% if is_incremental() %}
    WHERE updated_at > (SELECT MAX(updated_at) FROM {{ this }})
{% endif %}
{% endraw %}
```

Run incremental model:

```bash theme={"system"}
dbt run --select incremental_test
```

The result should match the staging model (3 rows).

**Step 2: Testing Merge Method**

Update staging model with changes:

```sql theme={"system"}
-- models/staging/stg_test_source.sql
WITH source_data AS (
    SELECT 1 as id,
           'inactive' as status,  -- Changed status
           150 as amount,         -- Changed amount
           CURRENT_TIMESTAMP() as updated_at
    UNION ALL
    SELECT 4, 'new', 400, CURRENT_TIMESTAMP()  -- New record
)
SELECT * FROM source_data
```

Run staging model:

```bash theme={"system"}
dbt run --select stg_test_source
```

Verify staging data:

```sql theme={"system"}
SELECT 
    * 
FROM 
    your_database.your_schema.stg_test_source 
ORDER BY 
    id;
```

Expected Result:

```
ID | STATUS   | AMOUNT | UPDATED_AT
1  | inactive | 150    | [new_timestamp]
4  | new      | 400    | [new_timestamp]
```

Run incremental model:

```bash theme={"system"}
dbt run --select incremental_test
```

Verify incremental model:

```sql theme={"system"}
SELECT 
    * 
FROM 
    your_database.your_schema.incremental_test 
ORDER BY 
    id
```

Expected Result:

```
ID | STATUS   | AMOUNT | UPDATED_AT
1  | inactive | 150    | [new_timestamp]  -- Updated
2  | pending  | 200    | [old_timestamp]  -- Unchanged
3  | active   | 300    | [old_timestamp]  -- Unchanged
4  | new      | 400    | [new_timestamp]  -- New
```

Notice that record with ID 1 was updated with the new status and amount, while new record with ID 4 was inserted. Records with IDs 2 and 3 remained unchanged as they weren't in the staging data.

***

### Common Issues and Solutions

| Issue                               | Solution                                                                  |
| ----------------------------------- | ------------------------------------------------------------------------- |
| Merge performance with large tables | Use `incremental_predicates` to limit the scope of the merge operation    |
| Unexpected column updates           | Use `merge_update_columns` or `merge_exclude_columns` for precise control |
| Duplicate key errors                | Ensure your `unique_key` truly identifies records uniquely                |

The merge method offers the most flexibility for incremental models, but comes with higher computational costs. Consider it your default choice unless you have specific performance requirements or data characteristics that favor another method.


## Related topics

- [Using Append for Incremental Models](/guides/dbt-fundamentals/model-materializations/incremental-materialization/using-append-for-incremental-models.md)
- [Using Delete+Insert for Incremental Models](/guides/dbt-fundamentals/model-materializations/incremental-materialization/using-delete+insert-for-incremental-models.md)
- [Using Microbatch for Incremental Models](/guides/dbt-fundamentals/model-materializations/incremental-materialization/using-microbatch-for-incremental-models.md)
- [Incremental Materialization](/guides/dbt-fundamentals/model-materializations/incremental-materialization/index.md)
- [Perplexity Tool](/products/dino-ai/tools-and-features/perplexity-tool.md)