Upsert

As explained in Architecting an Apache Iceberg Lakehouse

An upsert operation inserts records that are absent and updates records that already exist according to a matching key or condition.

An upsert conditionally inserts new records and updates existing records according to matching logic. In Iceberg ingestion, it supports transactional pipelines where incoming data reconciles with stored data. section 6.3.1

Upserts create new Iceberg snapshots rather than modifying existing data files. Compute engines such as Spark and Flink execute MERGE operations and translate them into Iceberg-compatible commits, so engine support, delete handling, compaction, performance, cost, and correctness remain architectural considerations. section 6.3.1 section 6.3

Spark MERGE

A staging dataset is matched against the target on an identifier; matching rows are updated, while unmatched rows are inserted. This supports slowly changing dimensions, late-arriving data, and reconciliation workflows. section 6.4.1

Figure 6.10 Merge transactions allow you to compare records to a staging dataset and conditionally insert, update, and delete records.

Compare a staging dataset with a target and conditionally insert, update, or delete records. section 6.3.1

How Architecting an Apache Iceberg Lakehouse covers it

How deep it goes

Explains matching, snapshot commits, engine responsibilities, and operational tradeoffs without detailing the Iceberg specification.

Examples

Uses a Spark MERGE between a staging table and target, matching records by identifier before updating or inserting.

What it assumes

Assumes familiarity with tables and ingestion pipelines while establishing snapshots, transactional writes, and matching behavior.

What you can do after

Apply it via Apache Spark to reconcile staging data with Iceberg tables through conditional updates and inserts.

How it connects

Connects upserts with Apache Iceberg, Apache Spark, Apache Flink, change data capture, slowly changing dimensions, and compaction.

liveBookmanning.com

Read Architecting an Apache Iceberg Lakehouse on liveBook Get the book at manning.com

Also explained in

• 

  liveBookmanning.com
  ScyllaDB in ActionAn upsert is a write operation that combines insert and update behavior by creating missing data or modifying existing data.
• 

  liveBookmanning.com
  Akka in Action, Second EditionAn upsert is a database operation that inserts a record when no matching record exists and updates the matching record otherwise.

Compare how 3 books explain upsert →

Related concepts

• database updateimplemented by · A database update is an operation that modifies existing records, rows, documents, or other stored data according to specified criteria.
• ON CONFLICT clauseimplemented by · An ON CONFLICT clause specifies the action an SQL insert takes when it violates a uniqueness or exclusion constraint.
• Documentproduces · A discrete representation of information, such as content or a structured record, optionally accompanied by metadata.
• updateOne()configures · updateOne() is a MongoDB update method that modifies the first document matching a filter, or inserts a document when upsert is enabled and no match exists.

This explainer was generated by Manning's knowledge graph from the text of Architecting an Apache Iceberg Lakehouse. The section links point to the parts of the book it draws on.