dbt delete+insert Strategy: A Complete Guide to the Rebuild Approach

delete+insert is an incremental strategy that first deletes the rows matching the target keys and then inserts the new rows to keep the table consistent. It works reliably in environments where MERGE is not optimal or has limited support.

This article follows the assumptions in the official documentation (dbt docs: Incremental models and each adapter's strategies) and focuses on basic design and operational patterns that are not easily affected by version differences, providing perspectives that help both daily work and the Analytics Engineer exam.

Key Points of the delete+insert Strategy and the Rebuild Mindset

delete+insert identifies target rows by their unique key, deletes the old rows, and inserts the new ones, producing an incremental upsert that preserves eventual consistency. It is useful when a single-statement upsert like MERGE is unavailable or operationally undesirable.

As a rebuild approach, it lets you partially recreate data by explicitly stating the target key range, and it makes rollback easier — when problems occur you can just delete the affected keys and re-insert. Transaction isolation and locking behavior are adapter-dependent, so consider them together with job parallelism and physical design (clustering, partitioning).

• Prerequisite: materialized=incremental with a unique_key specified
• Control delta extraction with is_incremental()
• Recovery on failure: re-delete then re-insert the same keys — highly reversible
• Where it fits: historical Redshift implementations, environments with MERGE restrictions, pipelines that emphasize partitioned rebuilds

delete+insert Processing Flow (Rebuild-Oriented)

Minimal dbt Incremental Model (delete+insert)

with source as (
  select * from {{ ref('stg_orders') }}
  {% if is_incremental() %}
    where updated_at >= (select coalesce(max(updated_at), '1900-01-01') from {{ this }})
  {% endif %}
),

new_rows as (
  select
    order_id as id,
    customer_id,
    status,
    updated_at
  from source
)

select * from new_rows

-- model config (at top of file, or via config block)
-- {{ config(
--   materialized='incremental',
--   incremental_strategy='delete+insert',
--   unique_key='id'
-- ) }}

Requirements and Prerequisites: unique_key and Delta Control

delete+insert cannot work without a unique_key. Duplicate keys create ambiguity about which rows to delete and become a breeding ground for mistaken deletes and duplicate inserts. The key should be logically unique (composite is fine), and in practice it should be materialized on the target side as an index or cluster key.

Delta extraction is controlled with is_incremental(). Narrow the range by updated_at or a load boundary column and produce the key set that matches the delete range. Account for late-arriving timestamps by widening the window slightly or by clarifying your rerun policy.

• unique_key can be single or composite; NULL-free design is strongly preferred
• Practical delta conditions include update timestamps, incremental flags, or hash-change detection
• Combine unique and not_null dbt tests for validation
• Design clustering / sort / indexes on the target to make key lookups fast

Mapping Delta Extraction to the Key Set

Composite Key with a Look-Back Window Example

{{ config(
  materialized='incremental',
  incremental_strategy='delete+insert',
  unique_key=['id','effective_date']
) }}

with src as (
  select * from {{ ref('stg_line_items') }}
  {% if is_incremental() %}
    where updated_at >= dateadd(day, -1, (select coalesce(max(updated_at),'1900-01-01') from {{ this }}))
  {% endif %}
)
select id, effective_date, qty, price, updated_at from src

Implementation Patterns: Splitting Model Definition and Configuration

Manage configuration with a config block at the top of the model file, or in the models section of dbt_project.yml. When you need to switch strategies between environments (prod vs. dev), branching on vars or target.name makes maintenance easier.

On some adapters MERGE becomes the default, but explicitly specifying delete+insert forces that strategy to be used. Check support status and default behavior in the adapter's official documentation.

• Specify the strategy and keys inside the model and enforce consistency with tests
• Switching incremental_strategy per environment makes validation easier
• Use dbt run-operation as a supplementary tool for maintenance scripts

Strategy Switching by Environment

Default Strategy and Overrides in dbt_project.yml

models:
  project_name:
    +materialized: incremental
    +incremental_strategy: delete+insert
    +unique_key: id
    marts:
      orders:
        +incremental_strategy: delete+insert  # explicit
        +unique_key: [id, effective_date]

Performance and Operational Pitfalls

delete+insert tends to increase I/O. To speed up locating delete targets, combine platform-specific optimizations on the target table: clustering (Snowflake), cluster/sort keys (Redshift), clustering/partitioning (BigQuery), and Z-ORDER or clustering (Databricks Delta).

Be mindful of the locking impact of bulk DELETE and the storage growth from time travel / retention. Operational tricks such as staggering schedules to avoid concurrent runs over the same key range, or sharding the key range per job, go a long way.

• Use key-based clustering and partitioning to shrink the DELETE scan range
• Make inserts efficient with batched COPY/INSERT (adapter-dependent)
• When parallelizing jobs, avoid contention on the same keys (mind pessimistic vs. optimistic locking)
• Estimate the cost impact of retention and auto-clustering ahead of time

Minimize Scan with Key-Aligned Clustering

Clustering / Partitioning Design Examples (Conceptual)

-- Snowflake example (conceptual)
-- create or replace table T cluster by (id);
-- BigQuery example (conceptual)
-- partition by date(updated_at) cluster by id
-- Databricks Delta example (conceptual)
-- optimize T zorder by (id)

Validation and Rerun: A Reversible Rebuild Workflow

A key strength of delete+insert is that failures are easy to recover from by re-deleting and re-inserting the affected keys. Manage the timing of downstream references through model dependencies (ref) and run tests (unique, not_null, functional dedup) at the end of the pipeline to catch inconsistencies early.

A full refresh (--full-refresh) is the last resort for a complete rebuild. First try to identify the affected keys and recover with a partial rerun (--select model_name), and if needed, temporarily widen the rerun window to restore consistency.

• Reduce failure granularity to per-key and recover with partial reruns
• Layer validation tests across unique, not_null, and functional duplicate checks
• In production, watch for read consistency and control cutover timing
• Estimate cost and lock time for a full refresh up front

Reversible Flow on Failure

Choosing Between Partial Rerun and Full Refresh

# Rerun only the affected model
# dbt run --select my_incremental_model

# Full refresh (last resort)
# dbt run --full-refresh --select my_incremental_model

Exam Prep: Points Targeted on the Analytics Engineer Exam

The exam tests your understanding of when each incremental strategy applies, the fact that unique_key is required for delete+insert, how to use is_incremental(), the meaning of --full-refresh, and conservative judgment about adapter differences.

It is also important to organize the differences between MERGE and delete+insert, along with the prerequisites of insert_overwrite (partition replacement), as a structured comparison.

• delete+insert requires unique_key; harden it with delta extraction and tests
• MERGE is a single-statement upsert, but its availability is environment-dependent — verify before standardizing on it
• insert_overwrite is efficient for partition-level rebuilds
• Know when to use a rerun vs. a full refresh on failure

Learning Map Across Strategies

Frequently Tested Configuration Snippet

{{ config(
  materialized='incremental',
  incremental_strategy='delete+insert',
  unique_key='id'
) }}
{% if is_incremental() %}
  -- don't forget the delta condition
{% endif %}

Check Your Understanding

Frequently Asked Questions