Row-Level Write Operations: MERGE INTO, DELETE, UPDATE

Summary

Row-level write operations (MERGE INTO, DELETE FROM, UPDATE) are implemented for Iceberg tables via Copy-on-Write using the iceberg-rust fork vendored at vendor/iceberg-rust/, which provides the rewrite_files() transaction API. The fork tracks risingwavelabs/iceberg-rust and is rebased onto DataFusion 53.1 + Arrow 58 (see docs/blog/2026-04-14-datafusion-53-and-the-iceberg-fork.md).

Motivation

SQE needed row-level mutations to be a viable Trino replacement:

• MERGE INTO: the most common pattern for incremental data pipelines (upserts). Required by dbt incremental models.
• DELETE FROM: GDPR right-to-erasure, data corrections, partition cleanup.
• UPDATE: in-place corrections without full table rewrites.

Current State (as of 2026-04-01)

What SQE Has

Iceberg Dependency

Uses the iceberg-rust fork vendored at vendor/iceberg-rust/ (a risingwavelabs/iceberg-rust DF 53 + Arrow 58 rebase, ported in-tree to DataFusion 54) for rewrite_files() transaction support. When upstream iceberg-rust ships OverwriteAction (tracked in Epic #2186), the dependency can be migrated back to the official crate.

Future: Merge-on-Read

MoR with position deletes is not yet implemented. Upstream PRs to watch:

MoR would reduce write amplification for write-heavy workloads but requires compaction to maintain read performance.

Design

Iceberg Delete Strategies

Iceberg v2 supports two strategies for row-level deletes:

Copy-on-Write (CoW):

• Read affected data files entirely
• Rewrite without the deleted/modified rows
• Produce new data files + remove old ones via OverwriteAction
• Pro: simple reads (no delete file reconciliation)
• Con: write amplification (rewrites entire files even for single-row changes)

Merge-on-Read (MoR):

• Write small position/equality delete files marking deleted rows
• Readers reconcile deletes at scan time
• Pro: fast writes (small delete files)
• Con: read overhead (must merge deletes during scans)

SQE strategy: CoW implemented, MoR planned. CoW is simpler and is fully operational via the RisingWave fork’s rewrite_files(). MoR will be added when upstream ships the required primitives.

Architecture

SQL: MERGE INTO target USING source ON condition
     WHEN MATCHED THEN UPDATE SET ...
     WHEN NOT MATCHED THEN INSERT ...

                    ┌──────────────────┐
                    │   SQL Parser     │
                    │   (sqlparser)    │
                    └───────┬──────────┘
                            │ StatementKind::Merge
                            ▼
                    ┌──────────────────┐
                    │  QueryHandler    │
                    │  execute()       │
                    └───────┬──────────┘
                            │
                            ▼
              ┌─────────────────────────────┐
              │  MergeHandler               │
              │                             │
              │  1. Plan source + target     │
              │     via DataFusion           │
              │                             │
              │  2. Execute the join to      │
              │     produce matched/         │
              │     unmatched rows           │
              │                             │
              │  3. Classify each row:       │
              │     - matched → UPDATE/DEL   │
              │     - not matched → INSERT   │
              │                             │
              │  4. For CoW: rewrite         │
              │     affected data files      │
              │     without deleted rows,    │
              │     add new rows             │
              │                             │
              │  5. Commit via               │
              │     OverwriteAction          │
              └─────────────────────────────┘

DELETE FROM Implementation

DELETE FROM target WHERE condition

1. Scan target table metadata to find data files
2. For each data file that may contain matching rows:
   a. Read the file
   b. Apply the WHERE filter
   c. If all rows match → mark file for removal
   d. If partial match → rewrite file without matching rows (CoW)
3. Commit via OverwriteAction:
   - Remove old data files
   - Add rewritten data files (partial matches)

UPDATE Implementation

UPDATE target SET col = expr WHERE condition

1. Same as DELETE, but:
   a. Read affected files
   b. Apply WHERE filter
   c. For matching rows: apply SET expressions
   d. Rewrite file with modified rows (CoW)
2. Commit via OverwriteAction

MERGE INTO Implementation

MERGE INTO target USING source ON condition
  WHEN MATCHED AND <cond> THEN UPDATE SET ...
  WHEN MATCHED AND <cond> THEN DELETE
  WHEN NOT MATCHED THEN INSERT (cols) VALUES (vals)

1. Execute the join: DataFusion LEFT OUTER JOIN of source and target
2. For each result row, classify:
   - Matched + update condition → apply SET, write to new file
   - Matched + delete condition → omit from rewrite (delete)
   - Not matched → write as new INSERT row
3. For CoW: identify affected target data files, rewrite them
4. Commit via OverwriteAction:
   - Remove old data files (affected ones)
   - Add new data files (rewritten + inserted)

SQE Implementation

Testing Strategy

Unit Tests (no external deps)

• Parse and classify MERGE/DELETE/UPDATE statements
• Row classification logic (matched/unmatched/insert/update/delete)
• CoW rewrite logic with in-memory RecordBatches

Integration Tests (require Polaris + MinIO)

• DELETE FROM with WHERE clause, then verify rows removed
• DELETE FROM without WHERE, then verify table emptied
• UPDATE SET, then verify values changed
• MERGE with WHEN MATCHED THEN UPDATE, then verify upsert
• MERGE with WHEN MATCHED THEN DELETE, then verify conditional delete
• MERGE with WHEN NOT MATCHED THEN INSERT, then verify new rows added
• Concurrent MERGE operations, then verify conflict detection
• MERGE with schema mismatch, then verify error handling

dbt Compatibility Tests

• dbt run with incremental materialization strategy maps to MERGE INTO
• dbt run with delete+insert strategy maps to DELETE + INSERT
• Verify dbt test passes after incremental runs

Acceptance Criteria

• DELETE FROM table WHERE condition removes matching rows
• DELETE FROM table removes all rows (empty table, metadata preserved)
• UPDATE table SET col = expr WHERE condition modifies matching rows
• MERGE INTO target USING source ON cond WHEN MATCHED THEN UPDATE ... works
• MERGE INTO target USING source ON cond WHEN NOT MATCHED THEN INSERT ... works
• MERGE INTO with multiple WHEN clauses works
• All operations are atomic (commit or rollback, no partial state)
• Bearer token passthrough works for all operations (no privilege escalation)
• Audit log captures DELETE/UPDATE/MERGE operations
• Metrics track row-level write operations
• Works in single-node mode (distributed deferred)

Rollback Strategy

Each operation is a single Iceberg snapshot commit. Rollback = revert to previous snapshot via Polaris REST API. No partial state is possible. Iceberg’s snapshot isolation guarantees atomicity.

If the feature is unstable, the StatementKind::Merge/Delete arms in query_handler.rs can be reverted to NotImplemented in a single commit.

Timeline

All three operations (DELETE, UPDATE, MERGE INTO) were implemented using the RisingWave iceberg-rust fork rather than waiting for upstream. Implementation completed 2026-03-28.

Remaining Action Items

• Watch upstream iceberg-rust Epic #2186 for OverwriteAction: migrate from RisingWave fork to official crate when available
• Implement DELETE FROM (done)
• Implement UPDATE (done)
• Implement MERGE INTO (done)
• Integration tests (done)
• TPC-C write benchmarks (17/17 pass)