dbt incremental model merge vs delete+insert

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

Dynamic tables are Snowflake-native: you write a SELECT and Snowflake keeps the result fresh on a lag you choose, no scheduler needed. dbt incremental models are code-managed: dbt rebuilds or merges on your schedule with full tests, docs, and version control. Pick dynamic tables for simple always-fresh transforms inside Snowflake; pick dbt incremental when you need tests, complex logic, or a tool that works across warehouses.

The query

snowflake dynamic tables vs dbt incremental

Use this when

• You need a transformed table in Snowflake that stays fresh with minimal plumbing
• A dbt incremental model feels like overkill for a simple SELECT-over-raw pattern
• You are deciding where freshness logic lives: warehouse-native or pipeline code

Not for

• Warehouses other than Snowflake, dynamic tables are a Snowflake-only feature
• One-off or historical backfills, where a plain CTAS or dbt full refresh is simpler
• Models that need dbt tests, documentation, or cross-warehouse portability

Steps

1. Write the dynamic table version. It is just a SELECT plus a target lag:

CREATE OR REPLACE DYNAMIC TABLE marts.orders_daily
  TARGET_LAG = '5 minutes'
  WAREHOUSE = transform_wh
AS
SELECT DATE_TRUNC('day', created_at) AS order_day,
       COUNT(*) AS orders,
       SUM(amount) AS revenue
FROM raw.orders
GROUP BY 1;

Expected output: the table is created and starts refreshing automatically. Snowflake tracks which source rows changed and incrementally updates the result; you never schedule anything.

2. Write the equivalent dbt incremental model for comparison:

{{ config(materialized='incremental', unique_key [your value] }}
SELECT DATE_TRUNC('day', created_at) AS order_day,
       COUNT(*) AS orders,
       SUM(amount) AS revenue
FROM {{ source('raw', 'orders') }}
{% if is_incremental() %}
WHERE created_at > (SELECT MAX(order_day) FROM {{ this }})
{% endif %}
GROUP BY 1

Expected output: same result set, but freshness depends on your dbt run schedule, and the merge logic is yours to maintain. The win is tests, docs, and the rest of the dbt toolchain.

3. Compare freshness honestly. Dynamic tables guarantee data no older than the target lag plus refresh time; dbt incremental is fresh as of the last successful run. If your dbt runs hourly and the business wants 5-minute freshness, dynamic tables win without a fight.

Expected output: a number per approach (lag vs schedule interval) you can put next to the requirement.

4. Compare cost. Dynamic tables bill for the background refresh warehouse; dbt incremental bills for your scheduled runs. For a simple aggregation, dynamic tables are usually cheaper because Snowflake only processes changed rows. For complex models with big merges, measure both for a week.

Expected output: warehouse credit usage per approach from ACCOUNT_USAGE. Dont guess; dynamic table refreshes show up as their own queries.

5. Check the logic limits. Dynamic tables support most SELECTs but not everything: no non-deterministic functions, no external tables in some cases, no DML inside. If your transform needs procedural steps, staging tables, or multi-step merges, dbt incremental handles it and dynamic tables dont.

Expected output: a yes/no per requirement. Complexity is the main reason teams keep dbt even when dynamic tables look simpler.

6. Decide with this split: dynamic tables for declarative always-fresh tables with simple SQL and tight lag requirements; dbt incremental for anything needing tests, docs, macros, cross-warehouse code, or orchestration alongside other tools. Many teams run both: dynamic tables for the hot path, dbt for the curated marts.

Expected output: a layered design instead of a religious war, with each table using the mechanism that fits its actual requirements.

Variant phrasings

snowflake dynamic tables limitations

Non-deterministic functions, certain joins with external tables, and procedural logic are restricted. The docs list the current set; it shrinks over time, so recheck before ruling them out.

replace dbt incremental with dynamic tables

Works when the model is a plain SELECT with a unique key and no dbt-specific machinery. Keep dbt around for the tests and docs even if the compute moves.

dynamic table target lag explained

TARGET_LAG is the maximum staleness Snowflake aims for: '5 minutes' means the table is never more than ~5 minutes behind, and Snowflake picks refresh timing to meet it. Shorter lag costs more.

Why it matters

This is the most common Snowflake architecture question right now because the two tools overlap heavily and the wrong pick costs real money or real freshness. The deciding factors are almost never performance; they are tests, portability, and who owns the schedule. Name those explicitly and the choice is usually obvious.

Provenance

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

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

Thread query: snowflake dynamic tables vs dbt incremental

TL;DR: Dynamic tables are Snowflake-native: you write a SELECT and Snowflake keeps the result fresh on a lag you choose, no scheduler needed. dbt incremental models are code-managed: dbt rebuilds or merges on your schedule with full tests, docs, and version control. Pick dynamic tables for simple always-fresh transforms inside Snowflake; pick dbt incremental when you need tests, complex logic, or a tool that works across warehouses.

Not specified by the original author.

TL;DR

The merge is not matching existing rows, so every run inserts duplicates. Usually uniquekey is missing, misspelled, or its values do not match between source and target (type, case, or nulls). Restore a correct uniquekey, make its type consistent on both sides, full refresh once to clean the duplicates, then verify two consecutive runs keep counts stable.

dbt incremental model merge not deduplicating: duplicate rows keep growing

Use this when

• an incremental model with incremental_strategy merge grows duplicate rows for the same key on every run
• select on the unique_key shows the same key many times with identical or near identical data
• the model config recently changed strategy, unique_key, or the key expression

Not for this skill when

• the strategy is append or insert_overwrite by design, duplicates follow different rules there
• the source itself contains true duplicates for the key, dedupe the source query with qualify or distinct logic
• the table is a snapshot, snapshot dedup uses its own strategy and keys

Steps

1. Read the model config block: confirm incrementalstrategy and uniquekey values exactly, and confirm the model is actually materialized incremental (not falling back to table on this run). Verify: config shows merge strategy with the intended unique_key, and the run used the incremental path.

2. Compare key values between the incoming batch and the target: check types, case, whitespace, and null rates for the key column on both sides with warehouse queries. Verify: you know whether keys mismatch in type or content, or match and something else is wrong.

3. Fix the cause: correct the unique_key name, cast the key to one type in the model select, or filter null keys (merge cannot match null keys). Keep the fix in the model so every run benefits. Verify: model SQL produces clean, non-null, consistently typed keys.

4. Run dbt run --select with --full-refresh once to rebuild without duplicates, then run twice more without full refresh and compare counts and duplicate-key checks after each. Verify: counts stay stable across runs and a duplicate-key query returns zero.

Variant phrasings

duplicates only for late arriving updates

If updates arrive with an older watermark than the incremental filter allows, they reprocess or miss. Widen the lookback window in the incremental predicate deliberately.

merge works on one adapter, duplicates on another

Adapters implement merge differently and some fall back to delete plus insert keyed on unique_key. A missing key degrades silently on some adapters; verify strategy support per adapter.

Compatibility: dbt-core incremental models with merge strategy on Snowflake, BigQuery, Spark, and Postgres (1.3 and later semantics). unique_key may be a single column or a list on supported versions.

Why it happens

Merge deduplication is a match on unique_key between the incoming batch and the target. If the key is absent, misspelled, null, or represented differently on each side, the match finds nothing, so the merge degenerates into pure inserts. Row growth per run is the arithmetic signature of a match that never hits.

Edge cases / pitfalls

• Composite keys need the list form of unique_key; concatenating keys into one string invites delimiter collisions.
• A full refresh hides the bug temporarily; always verify with repeat incremental runs, not one green run.
• Backfills that overlap the regular window double count unless the key match is sound; fix the key before backfilling.

Provenance

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

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

Thread query: dbt incremental model merge not deduplicating: duplicate rows keep growing

TL;DR: The merge is not matching existing rows, so every run inserts duplicates. Usually uniquekey is missing, misspelled, or its values do not match between source and target (type, case, or nulls). Restore a correct uniquekey, make its type consistent on both sides, full refresh once to clean the duplicates, then verify two consecutive runs keep counts stable.