Snowflake-Specific Incremental Strategies
By default, when you set materialized='incremental' and provide a unique_key, dbt will use the Merge strategy. It executes a MERGE INTO SQL statement, updating existing records and inserting new ones.
However, different data warehouses handle incremental updates differently. Snowflake provides three specific "Strategies" you can use to optimize the performance of your incremental models.
Topics Covered
- The default
mergestrategy - The
delete+insertstrategy - The
appendstrategy - How to choose the right strategy
1. The merge Strategy (Default)
The merge strategy is the default for Snowflake. It uses the MERGE INTO command.
{{ config(
materialized='incremental',
unique_key='order_id',
incremental_strategy='merge'
) }}
How it works under the hood:
- dbt creates a temporary table with the new data.
- It runs:
MERGE INTO target_table USING temp_table ON target_table.order_id = temp_table.order_id - If there is a match, it updates the row. If not, it inserts the row.
When to use it:
Use merge when your source data contains a mix of inserts (new orders) and updates (e.g., an order changing from 'PENDING' to 'SHIPPED').
2. The delete+insert Strategy
The MERGE command can be computationally expensive in Snowflake, especially on massive tables. Sometimes, it is much faster to simply delete the old rows and insert the new ones.
{{ config(
materialized='incremental',
unique_key='order_id',
incremental_strategy='delete+insert'
) }}
How it works under the hood:
- dbt creates a temporary table with the new data.
- It runs a
DELETE FROM target_table WHERE order_id IN (SELECT order_id FROM temp_table). - It runs an
INSERT INTO target_table SELECT * FROM temp_table.
When to use it:
In Snowflake, delete+insert is often faster than merge if you are updating a large volume of data but the updates are heavily clustered. However, it requires a strict unique_key. If your table has duplicates, the DELETE will remove all copies, and the INSERT will only put one back, potentially altering your data.
3. The append Strategy
What if you have a massive table of immutable event logs? For example, an Indian telecom company logging call detail records (CDRs). Once a call is made, that record never changes. There are no updates, only inserts.
In this scenario, merge and delete+insert are a massive waste of compute power, because they spend time searching for rows that need to be updated.
{{ config(
materialized='incremental',
incremental_strategy='append'
) }}
(Note: We do not provide a unique_key for the append strategy!)
How it works under the hood:
- dbt simply runs
INSERT INTO target_table SELECT * FROM new_data. - It completely skips the step of checking for existing records.
When to use it:
Use append for immutable event streams (Logs, Clicks, Transactions) where data is never updated. It is the fastest possible incremental strategy.
Warning: If you accidentally run the same data twice with the append strategy, you will create duplicate rows!
4. Summary: Choosing the Right Strategy
| Data Behavior | Best Strategy | Performance in Snowflake | Risk |
|---|---|---|---|
| Updates & Inserts | merge | Medium (Standard) | Low (Safest option) |
| Bulk Updates & Inserts | delete+insert | High (Often faster than merge) | Medium (Must ensure unique_key is truly unique) |
| Insert Only (Immutable) | append | Extremely High (Blazing fast) | High (Prone to duplication if source data overlaps) |
To see exactly what SQL dbt is running behind the scenes, you can always check the target/run/ directory after running an incremental model, or check the QUERY_HISTORY in Snowflake!
-- Check exactly what dbt did!
SELECT QUERY_TEXT
FROM TABLE(INFORMATION_SCHEMA.QUERY_HISTORY_BY_USER())
WHERE QUERY_TEXT ILIKE '%MERGE INTO%' OR QUERY_TEXT ILIKE '%DELETE FROM%';