dbt incremental merge vs append 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

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

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.