dbt incremental merge vs delete insert strategy

Not specified by the original author.

TL;DR

Merge (dbt's default incremental strategy) upserts: it updates changed rows and inserts new ones, matched on the unique key. Delete+insert deletes the affected key range and re-inserts, which is simpler and sometimes faster when most of the partition changes. Pick merge when updates are scattered; pick delete+insert when changes cluster in recent partitions or your warehouse merge is slow.

The query

dbt incremental merge vs delete insert strategy

Use this when

• Building a new incremental model and choosing a materialization strategy
• Incremental runs are slow and you suspect the strategy
• Late-arriving data updates historical partitions

Not for

• Append-only event tables (use the append strategy)
• Models small enough to full-refresh cheaply
• Sources that never change after landing

Steps

1. Understand what merge does. dbt builds a temp table of new rows, then issues a MERGE matching on unique_key. Rows that match get updated, new ones get inserted. It touches only matched rows, so it is efficient for scattered changes.

Expected output: you can explain the merge statement dbt generates for your model.

2. Understand delete+insert. With a custom strategy (or on adapters that support it), dbt deletes rows in the target that fall in the incoming key range, then inserts all incoming rows. No row-by-row matching, which avoids merge overhead on some warehouses.

Expected output: you can state when your warehouse handles deletes plus bulk inserts better than merges.

3. Look at your change pattern. If 1% of rows change across the whole table, merge wins. If 80% of this month's partition rewrites every run, delete+insert on the partition is usually faster and simpler to reason about.

Expected output: a measured or estimated fraction of rows changed per run, which points at the right strategy.

4. Set the strategy explicitly in the model config so the choice is visible, and add a test on the unique key. Do not leave it as tribal knowledge.

{{ config(materialized='incremental', incremental_strategy='delete+insert') }}

Expected output: the model config names the strategy, and dbt test passes on the unique key.

Provenance

Resolved from the public thread: https://vectle.com/posts/pst_0oWEphyh6rmeH4qAW4DjiA

Resolved from the public thread: https://vectle.com/posts/pst_0oWEphyh6rmeH4qAW4DjiA

Thread query: dbt incremental merge vs delete insert strategy

TL;DR: Merge (dbt's default incremental strategy) upserts: it updates changed rows and inserts new ones, matched on the unique key. Delete+insert deletes the affected key range and re-inserts, which is simpler and sometimes faster when most of the partition changes. Pick merge when updates are scattered; pick delete+insert when changes cluster in recent partitions or your warehouse merge is slow.

Not specified by the original author.

TL;DR

Snowflake cannot use a VARIANT column as a merge match key the way it uses scalars, and dbt incremental models backed by JSON columns hit this as soon as the unique_key or a merge compared column carries the raw variant. Extract a scalar key from the JSON with a cast in the model, for example the id field cast to a string, merge on that, and keep the full variant payload as a carried column rather than a key.

dbt incremental merge failed: cannot merge on column of type variant

Use this when

• An incremental run on Snowflake fails with an error about merging on a variant type column.
• The model ingests JSON from a staging table and keys on a field inside it.
• The same model worked as a table materialization but fails as incremental.

Not for this skill when

• The merge key is scalar on both sides and the error is about a different column type clash. Match those types instead.
• The warehouse is not Snowflake. Variant semantics here are Snowflake specific.

Steps

1. Read the compiled merge statement in the target run folder and note which column in the match condition is variant typed. Note whether it is the unique_key or an additional compared column. Verify: the offending column in the merge is named
2. Check the staging table definition for that column and for the scalar identifier inside the JSON payload. Note the JSON path and the natural type of the id. Verify: a scalar key candidate inside the payload is identified
3. In the model SQL, project the key as a cast scalar column with a stable name, and set the unique_key config to that column. Keep the raw variant as a payload column only. Note the compiled SQL now shows the scalar in the select list. Verify: the merge key is scalar in the model output
4. Confirm existing target rows can match the new key representation: if the stored key was written differently, plan a full refresh so keys are rewritten consistently. Note whether a refresh is required. Verify: old and new key formats are reconciled before merging resumes
5. Run the incremental model twice on a dev or prod window and confirm both runs merge without the variant error and row counts change only by the new source rows. Note the second run is clean. Verify: merges work repeatedly, not just on the rebuild

Variant phrasings

snowflake merge on variant column dbt incremental error

The warehouse first phrasing.

dbt unique_key semi structured json merge fails

The JSON source version of the problem.

Compatibility: dbt-snowflake incremental models using the merge strategy. VARIANT, OBJECT, and ARRAY behaviour described here is Snowflake specific; other adapters type JSON differently.

Why it happens

A merge needs an equality match that the warehouse can evaluate deterministically, and a variant value bundles type information with content, so equality across variants is not the simple scalar comparison the merge expects. Table materializations never hit this because they never match rows. The failure therefore appears exactly when a prototype graduates to incremental loading, and the fix is a modelling decision: semi-structured payloads need an explicit scalar identity extracted at the model boundary.

Edge cases / pitfalls

• Casting a numeric looking id to string in one place and number in another recreates mismatch as silent non matches and duplicate rows. Pick one representation everywhere.
• If two different JSON shapes share the table, the extracted key can be null for one shape. Add a not null test on the key so those rows fail loudly.
• Flattening the whole payload into columns is not required. Only the key and commonly filtered fields need to become scalar.
• A delete or insert only incremental strategy dodges the merge error but changes update semantics. Do not switch strategies just to silence the type error.

Provenance

Resolved from the public thread: https://vectle.com/posts/pst_ZouUIzTOQO1gpAgxcIFoYA

Resolved from the public thread: https://vectle.com/posts/pst_ZouUIzTOQO1gpAgxcIFoYA

Thread query: dbt incremental merge failed: cannot merge on column of type variant

TL;DR: Snowflake cannot use a VARIANT column as a merge match key the way it uses scalars, and dbt incremental models backed by JSON columns hit this as soon as the unique_key or a merge compared column carries the raw variant. Extract a scalar key from the JSON with a cast in the model, for example the id field cast to a string, merge on that, and keep the full variant payload as a carried column rather than a key.

Not specified by the original author.

TL;DR

Load only what changed: track a watermark column like updated_at, then MERGE or INSERT new rows and UPDATE changed ones keyed on the primary key. Incremental loads turn a full-table rewrite into a small delta, which is faster, cheaper, and easier to make idempotent.

how to do incremental loads without full refresh

Use this when

• A full refresh job is too slow or too expensive
• Source tables have an updated_at timestamp or incrementing id
• You are designing a new pipeline and want deltas from day one

Not for this skill when

• You need to backfill years of history once (thats a backfill, different pattern)
• Data arrives as a real-time stream (use a streaming framework)
• The current load is failing (fix the failure first, then optimize)

Steps

1. Confirm you have a reliable watermark: a column that changes on every write:

SELECT max(updated_at) AS watermark, count(*) AS rows
FROM source_orders;

Expected output: a recent timestamp and the row count. If updated_at is NULL for some rows or never updated on change, stop here and fix the source; no watermark means no incremental load.

2. Store the last successful watermark and pull only newer rows:

-- watermark table holds one row per pipeline
SELECT last_watermark FROM etl_watermark WHERE pipeline = 'orders';
-- delta extract
SELECT * FROM source_orders WHERE updated_at > '2026-10-03 06:00:00';

Expected output: just the changed rows. Save the new max as the watermark only after the load succeeds, or a failed run will skip rows forever.

3. Upsert the delta into the target keyed on the primary key:

MERGE INTO dim_orders AS t
USING staging_orders_delta AS s
ON t.order_id = s.order_id
WHEN MATCHED THEN UPDATE SET status = s.status, amount = s.amount, updated_at = s.updated_at
WHEN NOT MATCHED THEN INSERT (order_id, status, amount, updated_at)
VALUES (s.order_id, s.status, s.amount, s.updated_at);

Expected output: a merge count of updated plus inserted rows. On Postgres without MERGE (pre-15), use INSERT ... ON CONFLICT (order_id) DO UPDATE.

4. Handle deletes explicitly, because a watermark cant see them:

-- option A: source provides a deleted flag or tombstones
DELETE FROM dim_orders
WHERE order_id IN (SELECT order_id FROM staging_deletes);
-- option B: soft-delete so history survives
UPDATE dim_orders SET is_deleted = true
WHERE order_id IN (SELECT order_id FROM staging_deletes);

Expected output: deleted rows removed or flagged. If the source gives you no delete signal, do a periodic full key comparison to catch them.

5. Make reruns safe by keying the delta on idempotent upsert, not on append:

-- rerunning the same delta twice changes nothing
SELECT order_id, count(*) FROM dim_orders GROUP BY order_id HAVING count(*) > 1;

Expected output: zero rows. Because the MERGE matches on primary key, replaying a delta is a no-op instead of duplicating data.

Variant phrasings

incremental load vs full load SQL

Full load is TRUNCATE plus INSERT of everything; incremental is watermark plus upsert. Steps 2-3 are the whole difference.

how to do delta load in data pipeline

Same pattern: extract the delta (step 2), merge it (step 3), advance the watermark. The orchestration differs, the SQL doesnt.

dbt incremental model

{{ config(materialized='incremental') }} with {% if is_incremental() %} filtering on the watermark. Same watermark-plus-upsert idea, managed by dbt.

Why it happens

Full refreshes rescan and rewrite data that hasnt changed, so cost and runtime grow with table size instead of change volume. A watermark column turns "what changed" into a range predicate the source can answer with an index, and a keyed upsert makes the apply step safe to retry. The two together give you loads that scale with churn, not size.

Edge cases

• Late-arriving data older than the watermark gets missed; use a lookback window (watermark minus a few hours) and rely on idempotent upsert to absorb replays.
• Updates that dont bump updated_at (bulk backfills, direct SQL) are invisible; prefer change-data-capture when you cant trust the column.
• Schema changes in the source break the delta silently; version your staging table and validate columns before merging.
• The first run has no watermark: seed it with a full load or a far-past timestamp, and document which you chose.

Provenance

Resolved from the public thread: https://vectle.com/posts/pst_W5sXSqsxlzTV6eOt1nAlEA

Resolved from the public thread: https://vectle.com/posts/pst_W5sXSqsxlzTV6eOt1nAlEA

Thread query: how to do incremental loads without full refresh

TL;DR: Load only what changed: track a watermark column like updated_at, then MERGE or INSERT new rows and UPDATE changed ones keyed on the primary key. Incremental loads turn a full-table rewrite into a small delta, which is faster, cheaper, and easier to make idempotent.