Join / merge upsert · All operations

Merge into a table

Merge into a target, upsert, slowly changing dimension.

Status

In arcform

Runs as a SQL step

arcform carries it as a sql: step today.

Shape of the result
one table from two or more
Level
several clauses of SQL, or a window

The DuckDB step

Merge a batch into a target

It reads the orders table the proofs share. Create it first:

SQL
CREATE TABLE orders AS SELECT * FROM (VALUES
  (1,  'Alice Ng',      'north', DATE '2026-01-03', 120.00, 'widget', 'a|b',   '{"channel":"web","items":[1,2]}'),
  (2,  'alice ng',      'north', DATE '2026-01-05',  80.50, 'gadget', 'b',     '{"channel":"shop","items":[3]}'),
  (3,  'Bob  Okafor',   'south', DATE '2026-01-05', 300.00, 'widget', 'a|c',   '{"channel":"web","items":[4,5,6]}'),
  (4,  'Carla Díaz',    'east',  DATE '2026-01-09',  NULL,  'gizmo',  NULL,    '{"channel":"web","items":[]}'),
  (5,  'Bob Okafor',    'south', DATE '2026-01-12',  45.25, 'gadget', 'c',     '{"channel":"phone","items":[7]}'),
  (6,  'Dev Patel',     'west',  DATE '2026-01-12', 510.00, 'widget', 'a',     '{"channel":"web","items":[8,9]}'),
  (7,  'Carla Diaz',    'east',  DATE '2026-01-20',  99.99, 'gizmo',  'b|c',   '{"channel":"shop","items":[10]}'),
  (8,  'Dev Patel',     'west',  DATE '2026-02-02', 510.00, 'widget', 'a',     '{"channel":"web","items":[8,9]}'),
  (9,  'Eve Larsen',    NULL,    DATE '2026-02-03',  15.00, 'gadget', NULL,    '{"channel":"web","items":[11]}'),
  (10, 'Alice Ng',      'north', DATE '2026-02-10', 220.00, 'gizmo',  'a|b|c', '{"channel":"phone","items":[12,13]}')
) t(order_id, customer, region, order_date, amount, product, tags, meta);

Then run the statement:

SQL
CREATE TABLE target AS SELECT order_id, amount FROM orders WHERE order_id <= 5;
UPDATE target SET amount = 0 WHERE order_id = 5;
MERGE INTO target t USING (SELECT order_id, amount FROM orders WHERE order_id BETWEEN 5 AND 7) s ON t.order_id = s.order_id
  WHEN MATCHED THEN UPDATE SET amount = s.amount
  WHEN NOT MATCHED THEN INSERT VALUES (s.order_id, s.amount);
CREATE TEMP TABLE result AS
SELECT * FROM target;
SELECT * FROM result ORDER BY order_id;
SELECT CASE WHEN ((SELECT count(*) = 7 AND max(amount) FILTER (WHERE order_id = 5) = 45.25 AND max(amount) FILTER (WHERE order_id = 7) = 99.99 FROM result)) THEN 'as expected' ELSE error('the merge did not update order 5 and insert orders 6 and 7') END AS verdict;
  • It needs nothing installed beyond DuckDB itself.
  • The statement checks its own result and raises an error when it is wrong: “the merge did not update order 5 and insert orders 6 and 7”.
  • It passed on DuckDB v2.0.0 (Cyanoptera) d54cbbe3df.

Variants

Process only new rows

Read and process only the rows that are new since the last run and add them to the existing result.

Update or insert by key

Update the target rows whose key is in the source and insert the source rows whose key is not.

What other tools call it

Meridian's classification; an independent blind rating agreed on 86.2% of 312 rows (exact class, version 2).

Apache Hop (6)
  • Combination lookup/updateread 2026-09-27

    Look up or insert a junk-dimension row for a combination of attributes, returning its key

  • Dimension lookup/updateread 2026-09-27

    Maintain or look up a Kimball slowly changing dimension, type 1 and type 2

  • Insert / Updateread 2026-09-27

    Upsert: update the database row matching the key, or insert when absent

  • Spark lake table mergeread 2026-09-27

    Run MERGE INTO against Delta Lake or Iceberg tables on native Spark

  • Synchronize after mergeread 2026-09-27

    Insert, update or delete database rows by the flag Merge rows (diff) produced

  • Updateread 2026-09-27

    Update the database rows that match lookup keys, without inserting

AWS Glue (2)
  • Mergeread 2026-09-27

    Merge a frame with a staging frame on primary keys; duplicate records are not de-duplicated

  • mergeDynamicFrameread 2026-09-27

    Merge a frame with a staging frame on primary keys; staging records replace matching source records

Azure Data Factory (2)
  • Alter rowread 2026-09-27

    Mark rows for insert, update, delete or upsert at the sink by condition

  • Change data capture resourceread 2026-09-27

    Top-level resource that runs continuously, reading changed rows from sources and writing them to targets

Bruin (9)
  • datavault_hubread 2026-09-27

    Insert one row per hub hash key, earliest load first, only when the key is new

  • datavault_linkread 2026-09-27

    Insert one row per link hash key, earliest load first, only when the key is new

  • datavault_satelliteread 2026-09-27

    Insert a row per parent hash key only when its hashdiff differs from the latest stored row

  • delete+insertread 2026-09-27

    Delete target rows for the incremental_key values in the result, then insert the new rows

  • ingestr: CDC (cdc: true)read 2026-09-27

    Capture row-level changes from Postgres, MySQL family, MongoDB or SQL Server logs and merge them

  • mergeread 2026-09-27

    Update rows matching the primary key and insert the rest, per column update rules

  • scd2_by_columnread 2026-09-27

    Keep type-2 history, opening a new version when any non-key column value changes

  • scd2_by_timeread 2026-09-27

    Keep type-2 history, opening a new version when the time-based incremental_key advances

  • time_intervalread 2026-09-27

    Replace rows inside the run time window, keyed on a date or timestamp incremental_key

dbt-utils (8)
  • checkread 2026-09-27

    Detect changed rows by comparing a list of columns with their stored values

  • delete+insertread 2026-09-27

    Delete target rows matching the unique_key, then insert the new rows

  • incrementalread 2026-09-27

    Insert or update only new or changed rows into an existing table since the last run

  • insert_overwriteread 2026-09-27

    Replace whole partitions of a partitioned table with new data

  • mergeread 2026-09-27

    Update rows whose unique_key exists in the target and insert those that do not

  • microbatchread 2026-09-27

    Process a time-series model as separate time-based batches keyed on event_time

  • snapshotread 2026-09-27

    Record changes to a mutable table over time as type-2 slowly changing dimension rows

  • timestampread 2026-09-27

    Detect changed rows by comparing an updated_at column with the last snapshot

DuckDB (2)
  • INSERTread 2026-09-27

    Add rows to a table, by position or by name, replacing or skipping on conflict

  • MERGE INTOread 2026-09-27

    Insert, update or delete in a target according to a source, in one statement

Duckle (6)
  • DuckLake CDCread 2026-09-25

    change feed since the last consumed DuckLake snapshot via ducklake_table_changes

  • Merge / Upsertread 2026-09-25

    emit the upsert payload: new and changed rows against a previous input

  • PostgreSQL CDCread 2026-09-25

    log-based change capture from a PostgreSQL replication slot, pgoutput decoded in Rust

  • SCD Type 1read 2026-09-25

    SCD type 1: current rows plus previous rows whose key is absent

  • SCD Type 2read 2026-09-25

    SCD type 2: close changed versions, keep history, insert new versions

  • SCD Type 3read 2026-09-25

    SCD type 3: keep the previous value of tracked columns in sibling columns

KNIME Analytics Platform (2)
  • DB Mergerread 2026-09-27

    Update database rows that match on key columns and insert the ones that do not

  • Table Updaterread 2026-09-27

    Overwrite cells of a table with those of an update table matched by RowID and column

Pentaho Data Integration (5)
Polars (1)
  • DataFrame.updateread 2026-09-27

    Overwrite values with those of another frame, matched on key columns or row position

RapidMiner Studio (2)
  • Update Databaseread 2026-09-27

    Update rows in a database table where the ID columns match the input rows

  • Update MongoDBread 2026-09-27

    Update matching documents in a MongoDB collection

SQLMesh (5)
  • INCREMENTAL_BY_PARTITIONread 2026-09-27

    Replace whole partitions, by partitioning key, with newly loaded rows

  • INCREMENTAL_BY_TIME_RANGEread 2026-09-27

    Compute only missing time intervals, filtered on a time column, replacing each interval

  • INCREMENTAL_BY_UNIQUE_KEYread 2026-09-27

    Insert rows with new keys and update rows whose key already exists

  • SCD_TYPE_2_BY_COLUMNread 2026-09-27

    Track row history with valid_from and valid_to, detecting change by comparing listed columns

  • SCD_TYPE_2_BY_TIMEread 2026-09-27

    Track row history with valid_from and valid_to, detecting change by an updated-at column

Tableau Prep (1)
  • Incremental refreshread 2026-09-27

    Read and process only the rows new since the last run, detected by a chosen field

Talend (6)
VisiData (4)
  • join-cols-mergeread 2026-09-23

    Join on selected columns: all rows from all sheets; matching keys take the latest truthy value

  • join-selected-mergeread 2026-09-23

    Join selected sheets: all rows from all sheets; matching keys take the latest truthy value

  • join-sheets-all-mergeread 2026-09-23

    Join all stacked sheets: all rows from all sheets; matching keys take the latest truthy value

  • join-sheets-top2-mergeread 2026-09-23

    Join top two sheets: all rows from all sheets; matching keys take the latest truthy value

Background

A SQL MERGE statement inserts new records into a target table, or updates or deletes existing ones, depending on whether a match condition holds.[1] MERGE was officially introduced in the SQL:2003 standard and expanded in the SQL:2008 standard.[1] Some databases use the term upsert, a portmanteau of update and insert, for a statement that inserts a record if it is absent and updates it if it exists.[1]

When to use it

In dbt, the merge strategy inserts records whose unique_key is not yet in the destination table and updates those whose key is, overwriting changes rather than tracking them.[2] In dbt, the merge strategy can be expensive for large tables, because it scans the entire destination table to determine what to update or insert.[2] In Bruin, merge never deletes, so rows removed from the source stay in the target.[3] Decide before the first run what a row that has gone from the source should mean for the target: kept as it is, deleted, or closed off as history. (Meridian's view)

In DuckDB, a MERGE INTO statement throws an error when a target row matches more than one source row and a WHEN MATCHED clause applies.[4] Reduce the source to one row per key before merging, with an ordering that says which row wins, so the result does not depend on the order rows happened to arrive in. (Meridian's view)

In Tableau Prep, an incremental refresh retrieves and processes only the new rows when a flow runs.[5] In Tableau Prep, a flow can refresh incrementally every day and run a full refresh weekly or monthly to refresh all of its data.[5]

The method

MERGE employs a right join over the target, the INTO table, and the source, the USING table, view or sub-query.[1] In a MERGE, a source row whose ON fields match a target row updates that row, and a source row whose ON fields match none is inserted.[1] In a MERGE, no action is performed for a target row whose ON fields do not exist in the source.[1] The SQL:2003 standard mandates an error when multiple source rows match a given target row, so a MERGE cannot update one target row several times.[1] In DuckDB, a MERGE INTO with WHEN NOT MATCHED BY SOURCE THEN DELETE removes target rows that are not present in the source.[4]

MySQL's INSERT ... ON DUPLICATE KEY UPDATE can join target and source only on PRIMARY KEY or UNIQUE constraints, which the ANSI/ISO MERGE standard does not require.[1] DuckDB's MERGE INTO statement does not need a primary key, because it allows a custom match condition, unlike INSERT INTO ... ON CONFLICT.[4]

Ralph Kimball's The Data Warehouse Toolkit popularised a numbered set of techniques for slowly changing dimensions, called Types 0 to 6.[6] Among the slowly changing dimension types, Type 1 overwrites the old value so no history is kept, and Type 2 adds a new row for each change.[6] In dbt, the merge strategy mirrors the logic of a Type 1 slowly changing dimension, where changes are overwritten rather than historically tracked.[2]

Sources

  1. [1]Merge (SQL) - Wikipediasecondary source
  2. [2]About incremental strategy | dbt Developer Hubdocumentation
  3. [3]Materialization | Bruin CLIdocumentation
  4. [4]MERGE INTO Statement – DuckDBdocumentation
  5. [5]Refresh Flow Data Using Incremental Refresh - Tableaudocumentation
  6. [6]Slowly changing dimension - Wikipediasecondary source