Append vs Replace vs Upsert: Write Modes Explained

Append, replace, and upsert are the three write modes every data pipeline tool exposes, and they differ in what happens to rows already in the destination. Append adds every incoming row and touches nothing. Replace discards the table and loads the new dataset whole. Upsert updates the existing row that shares a key and inserts the rest. As of October 16, 2026, the choice turns on keys, deletes, and retries.

How does upsert work in SQL?

An upsert is one statement that inserts a row or, when a row with the same key already exists, updates it instead. The Postgres INSERT docs describe INSERT ... ON CONFLICT DO UPDATE as a clause that "guarantees an atomic INSERT or UPDATE outcome" and note the name is short for UPDATE or INSERT.

The key is not optional. Postgres infers an arbiter index from the conflict target and raises an error when no unique index matches. The MySQL 8.4 manual says REPLACE "makes sense only if a table has a PRIMARY KEY or UNIQUE index". Every engine has its own spelling, and the table below names each one.

INSERT INTO customers (id, email, plan)
VALUES (42, 'ada@example.com', 'team')
ON CONFLICT (id) DO UPDATE
SET email = EXCLUDED.email,

INSERT INTO customers (id, email, plan)
VALUES (42, 'ada@example.com', 'team')
ON CONFLICT (id) DO UPDATE
SET email = EXCLUDED.email,

INSERT INTO customers (id, email, plan)
VALUES (42, 'ada@example.com', 'team')
ON CONFLICT (id) DO UPDATE
SET email = EXCLUDED.email,

MERGE is the SQL standard form of the same job, joining a source to the target and running one WHEN clause per row so it can insert, update, and delete in a single pass. It arrived in PostgreSQL 15 per the release notes, and the Postgres MERGE page warns that MERGE and ON CONFLICT "are not interchangeable", because only ON CONFLICT turns a concurrent insert into an update.

How replace mode works and what it costs

Replace mode throws away the destination table and reloads it, which is the simplest way to get an exact copy and the only non-CDC mode that lands hard deletes. Airbyte's overwrite docs put it bluntly, new syncs "will destroy all data in the existing destination table and then pull the new data in."

The cost is the gap between the drop and the reload. In Postgres, TRUNCATE takes an ACCESS EXCLUSIVE lock and "is not MVCC-safe", so a reader holding an older snapshot sees an empty table, per the TRUNCATE docs. dlt defaults to truncate-and-insert and says tables "will have no data for a while until the load is completed."

Warehouses hide the gap with an atomic swap. Snowflake's CREATE OR REPLACE TABLE is atomic, so concurrent queries "use either the old or new table version", and BigQuery's WRITE_TRUNCATE disposition overwrites in one job but "removes the constraints" per the Job reference. dbt's insert_overwrite strategy is the middle path for partitioned tables, replacing only the touched partitions.

Why is append not idempotent?

Append has no memory of what it already wrote, so running the same load twice stores every row twice. dbt's incremental docs spell it out, append "doesn't check for duplicates or verify whether a record already exists in the destination."

That matters because pipelines retry. Airbyte's incremental append promises "at least once" delivery and says the warehouse "increases monotonically" in size, per its sync mode docs. The Debezium JDBC sink sees the other side, since in insert mode "redelivered records fail with constraint violations", so it recommends upsert for idempotent writes.

Append still wins for immutable events, because there is nothing to update. dlt frames the split as stateful versus stateless data, where a recorded page view "cannot change" and can simply be appended, per its incremental loading guide. The fix for duplicates is a key plus a dedup step, at write time through upsert or at read time through ReplacingMergeTree, which ClickHouse says "does not guarantee the absence of duplicates."

Which write mode handles deletes?

Only replace and CDC merge remove a destination row when the source row disappears, and upsert on its own never does. An upsert driven by an updated_at cursor cannot see a hard delete because the deleted row never arrives. That is why Fivetran says most application APIs "don't return deletes as changes" and it cannot capture them.

Tools paper over the gap with a flag instead of a delete. Fivetran writes _fivetran_deleted, PeerDB writes _peerdb_is_deleted when soft delete is on per its mirror docs, and Airbyte stamps _ab_cdc_deleted_at on CDC delete events per its CDC page. The Debezium JDBC sink drops delete events unless delete.enabled is true.

Where write modes go wrong

Three failures account for most write mode incidents, and each has a one line fix.

Duplicate rows after a retry

Appends that retry without a checkpoint write the batch twice. ClickHouse dedups retried inserts by block hash only inside its deduplication window, and its retry guide warns the same data "can be inserted multiple times" once that window passes. Give the table a key and switch to upsert, or dedup on read.

MERGE command cannot affect row a second time

Postgres raises this cardinality violation when two source rows match one target row, with the hint "Ensure that not more than one source row matches any one target row" in the executor source. Snowflake errors the same way by default under ERROR_ON_NONDETERMINISTIC_MERGE, and BigQuery says MERGE "must match at most one source row for each target row" in its DML reference. Deduplicate the staging set by key before merging.

FINAL on every query

ReplacingMergeTree defers dedup to background merges, so queries that need exact answers add FINAL, which ClickHouse says "requires additional compute and memory resources" in its FROM clause docs. Use argMax or a grouped query on large tables instead.

How the main tools name the write modes

Every tool exposes the same three ideas under its own names, and the names decide whether a key is mandatory. The table quotes the documentation of 9 tools, linked in the first column.

Galaxy builds Filament. Mode names are quoted from each tool's own documentation.

Which write mode should you use?

Upsert is the default for any keyed table, append is for immutable events, and replace is for anything small enough to reload whole or anything where deletes must land tonight.

Related guides

• Full load vs incremental vs CDC covers the read side of the same decision.

• What is change data capture explains the stream that feeds CDC merge.

• Data integrity verification in replication pipelines shows how to prove the destination matches afterward.

• Checkpointing in data pipelines covers the checkpoint that stops a retried append landing twice.

• The database replication guide is the pillar this page hangs off.

Frequently asked questions

What does upsert mean in SQL?

Upsert is a contraction of update and insert. One statement checks whether a row with the same key already exists, updates it when it does, and inserts a new row when it does not. Postgres spells it INSERT ON CONFLICT DO UPDATE, MySQL spells it INSERT ON DUPLICATE KEY UPDATE, and most warehouses use MERGE.

What is the difference between upsert and append?

Append writes every incoming row as a new row and never looks at what is already there, so the same load run twice stores everything twice. Upsert matches incoming rows to existing ones by key and overwrites them, so repeating the load changes nothing. Append needs no key and is cheaper per row, while upsert needs a unique key.

Is upsert the same as MERGE?

Not quite. MERGE is the SQL standard statement that can insert, update, and delete in one pass by joining a source to a target, and a plain upsert is its two clause case. Postgres documents that MERGE and INSERT ON CONFLICT are not interchangeable, because only ON CONFLICT turns a concurrent insert into an update.

When should you use append instead of upsert?

Use append when rows never change after they are written, such as page views, log lines, or ledger entries, and when the table has no natural key. There is nothing to update, so the extra lookup an upsert performs buys nothing. Pair it with a checkpoint so a retried batch does not land twice.

Does upsert require a primary key?

Yes. Every upsert form needs a unique index or primary key to decide whether an incoming row matches an existing one. Postgres raises an error when it cannot infer an arbiter index, and MySQL turns REPLACE into a plain INSERT without one. Pipeline tools such as dbt fall back to append when no unique key is configured.

Can upsert delete rows that were removed from the source?

No. An upsert only sees rows the source sends, and a deleted row is never sent, so it stays in the destination forever. To land hard deletes you need a full replace, a CDC stream that carries delete events, or a soft delete column such as a deleted flag that the reader filters out.

What is the difference between replace and truncate and load?

They are the same idea under two names. Replace, overwrite, and full refresh all mean the destination table is emptied and reloaded from scratch. Truncate and load names the mechanism in SQL databases, where TRUNCATE clears the table and a bulk INSERT or COPY refills it, with a short window where readers can see no rows.

Why does my pipeline create duplicate rows on every run?

The load is running in append mode, or an upsert is running against a table without a unique key. Either way nothing matches incoming rows to existing ones, so each run inserts the full set again. Add a primary key and switch to upsert or merge, or deduplicate on read with a window function over the key.