Principle:Lance format Lance Row Level Updates
| Knowledge Sources | |
|---|---|
| Domains | Data_Engineering, Columnar_Storage |
| Last Updated | 2026-02-08 19:00 GMT |
Overview
Row-level updates allow modifying existing rows in a Lance dataset by applying computed expressions to matched rows or by merging new data with existing data based on key columns.
Description
Lance provides two complementary APIs for updating data in place:
UpdateBuilder implements SQL UPDATE-style semantics. It allows setting column values using expressions for rows that match an optional filter predicate. For example, you can set status = 'archived' for all rows where age > 65. If no filter is specified, all rows are updated. The builder produces an UpdateResult containing the new dataset and the count of updated rows.
MergeInsertBuilder implements UPSERT / MERGE semantics (similar to SQL MERGE). It joins incoming source data with existing target data on specified key columns and allows defining behavior for three cases:
- When matched: What to do when a source row matches a target row (update all columns, update specific columns, or do nothing).
- When not matched: What to do when a source row has no match in the target (insert or skip).
- When not matched by source: What to do when a target row has no match in the source (delete or keep).
This enables patterns such as find-or-create, full synchronization (delete missing, update existing, insert new), and selective upserts.
Both operations are versioned: they produce a new dataset version, leaving the previous version intact. Both also support configurable conflict retry logic for concurrent write scenarios.
Usage
Use row-level updates when:
- Correcting or enriching specific column values based on a predicate (e.g., backfilling null fields, applying data transformations).
- Synchronizing a Lance dataset with an upstream source of truth using MERGE/UPSERT logic.
- Implementing change data capture (CDC) pipelines where inserts, updates, and deletes arrive as a unified stream.
- Performing idempotent "find or create" operations using key-based matching.
Theoretical Basis
Update Mechanics (UpdateBuilder)
The update operation follows a copy-on-write strategy:
- Scan phase: The dataset is scanned with the filter predicate to identify matching rows and their fragment locations.
- Rewrite phase: For each affected fragment, a new fragment is written containing the original rows with the specified columns replaced by the evaluated expressions. Unaffected fragments are not rewritten.
- Commit phase: A transaction is committed that replaces the old fragments with the new ones. The transaction includes conflict retry logic (default 10 retries, 30-second timeout).
Merge Insert Mechanics (MergeInsertBuilder)
The merge insert operation uses a hash join strategy:
- Join phase: The source data and target dataset are joined on the specified key columns. This produces three partitions: matched rows (present in both), source-only rows (new data), and target-only rows (existing data not in source).
- Action phase: Each partition is processed according to the configured behavior (update, insert, delete, or keep).
- Write phase: New and updated rows are written as new fragments. Deleted rows are tracked in deletion vectors on existing fragments.
- Commit phase: The transaction is committed with retry logic. The
MergeStatsstruct reports the counts of inserted, updated, and deleted rows.
Both operations maintain ACID properties at the version level through Lance's transactional commit mechanism.