Join / merge upsert · All operations

Replace a slice of the table

A variant of Merge into a table

Delete the target rows in the partitions, time range or keys the new data covers, then insert the new rows.

Status

In arcform

Runs as a SQL step

arcform carries it as a sql: step today.

The DuckDB step

Replace the slice of the target that the new data covers

SQL

CREATE TEMP TABLE sales AS SELECT * FROM (VALUES
  (1, 'north', DATE '2026-01-03', 10), (2, 'north', DATE '2026-01-20', 20), (3, 'south', DATE '2026-01-05', 30),
  (4, NULL, DATE '2026-01-06', 40), (5, 'south', DATE '2026-02-02', 50), (6, 'east', NULL, 60), (9, 'south', DATE '2026-01-10', 90)) t(order_id, region, order_date, amount);
CREATE TEMP TABLE fresh AS SELECT * FROM (VALUES
  (2, 'north', DATE '2026-01-20', 21), (7, 'north', DATE '2026-01-25', 70), (8, NULL, DATE '2026-01-07', 80)) t(order_id, region, order_date, amount);
CREATE TEMP TABLE by_partition AS SELECT * FROM sales;
CREATE TEMP TABLE by_range AS SELECT * FROM sales;
CREATE TEMP TABLE by_key AS SELECT * FROM sales;
-- partitions: the regions fresh covers, NULL included (IN would leave the NULL region's old rows in place)
BEGIN;
DELETE FROM by_partition WHERE EXISTS (SELECT 1 FROM fresh WHERE fresh.region IS NOT DISTINCT FROM by_partition.region);
INSERT INTO by_partition SELECT * FROM fresh;
COMMIT;
-- time range: from the earliest to the latest date fresh holds; a row with no date is outside every range
BEGIN;
DELETE FROM by_range WHERE order_date BETWEEN (SELECT min(order_date) FROM fresh) AND (SELECT max(order_date) FROM fresh);
INSERT INTO by_range SELECT * FROM fresh;
COMMIT;
-- keys: the order ids fresh holds
BEGIN;
DELETE FROM by_key WHERE EXISTS (SELECT 1 FROM fresh WHERE fresh.order_id = by_key.order_id);
INSERT INTO by_key SELECT * FROM fresh;
COMMIT;
SELECT * FROM by_partition ORDER BY order_id;
SELECT CASE WHEN ((SELECT list(order_id ORDER BY order_id) = [2, 3, 5, 6, 7, 8, 9] AND max(amount) FILTER (WHERE order_id = 2) = 21 FROM by_partition)
  AND (SELECT list(order_id ORDER BY order_id) = [1, 2, 3, 4, 5, 6, 7, 8] AND max(amount) FILTER (WHERE order_id = 2) = 21 FROM by_range)
  AND (SELECT list(order_id ORDER BY order_id) = [1, 2, 3, 4, 5, 6, 7, 8, 9] AND max(amount) FILTER (WHERE order_id = 2) = 21 FROM by_key)) THEN 'as expected' ELSE error('replacing by partition should drop the old north and NULL-region rows (1, 2 and 4), by time range the rows dated 2026-01-07 to 2026-01-25 (2 and 9), and by key only order 2, each followed by the 3 new rows') END AS verdict;

The statement builds its own data.
It needs nothing installed beyond DuckDB itself.
The statement checks its own result and raises an error when it is wrong: “replacing by partition should drop the old north and NULL-region rows (1, 2 and 4), by time range the rows dated 2026-01-07 to 2026-01-25 (2 and 9), and by key only order 2, each followed by the 3 new rows”.
It passed on DuckDB v2.0.0 (Cyanoptera) d54cbbe3df.

What other tools call it

Meridian's classification; an independent blind rating agreed on 95.7% of 324 rows (variant, version 3).

Bruin (2)

delete+insertread 2026-09-27
Delete target rows for the incremental_key values in the result, then insert the new rows
time_intervalread 2026-09-27
Replace rows inside the run time window, keyed on a date or timestamp incremental_key

dbt-utils (3)

delete+insertread 2026-09-27
Delete target rows matching the unique_key, then insert the new rows
insert_overwriteread 2026-09-27
Replace whole partitions of a partitioned table with new data
microbatchread 2026-09-27
Process a time-series model as separate time-based batches keyed on event_time

SQLMesh (2)

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

Background

In dbt, the insert_overwrite strategy updates a partitioned table by replacing entire partitions with new data, rather than merging or updating individual rows.^[1] In Bruin, the delete+insert strategy replaces every row that shares an incremental_key value with the new query result.^[2]

When to use it

In Bruin, delete+insert suits data that arrives in batches identified by a key, such as a date, a load ID or a tenant, where a new batch should fully replace the old one.^[2] In Bruin, delete+insert removes rows that disappeared from the source within a batch, because the batch is deleted and reinserted, which merge cannot do.^[2] In Bruin, delete+insert touches only the keys present in the new result, so if the result holds no rows for a date, the existing rows for that date stay.^[2] In dbt, insert_overwrite is ideal for tables partitioned by date or another key, for refreshing recent or corrected data without full table rebuilds.^[1]

SQLMesh appends a time range filter to an INCREMENTAL_BY_TIME_RANGE model's query at runtime, which prevents unintentionally overwriting unrelated records when handling late-arriving data.^[3] Derive the slice to delete from the new data itself, and decide on purpose whether a NULL in the slicing column belongs to a slice. (Meridian's view) Run the delete and the insert in one transaction, so no reader ever sees the slice gone and not yet replaced. (Meridian's view)

The method

In dbt, insert_overwrite overwrites only the affected partitions, not the whole table.^[1] In SQLMesh, INCREMENTAL_BY_TIME_RANGE models are materialised with INSERT OVERWRITE by time column partition on Spark and Databricks, and with DELETE by time range, then INSERT, on DuckDB and Postgres.^[3]

In SQLMesh, an INCREMENTAL_BY_PARTITION model replaces all the existing rows of a partitioning key with the newly loaded rows for that key, and inserts the rows of a key the table does not yet hold.^[3]

Compared with the alternatives

In dbt, delete+insert deletes the data for each unique_key from the target and then inserts the new data, which ensures updated records are fully replaced and avoids partial updates.^[1] SQLMesh suggests INCREMENTAL_BY_TIME_RANGE in most scenarios, and INCREMENTAL_BY_PARTITION only when the data must be loaded by partition, usually for performance reasons.^[3]

Sources

[1]About incremental strategy | dbt Developer Hubdocumentation
[2]Materialization | Bruin CLIdocumentation
[3]sqlmesh/docs/concepts/models/model_kinds.md at 23b237cea7218cc8f338b89c14f0957394a0003b · SQLMesh/sqlmesh · GitHubdocumentation