In LanceDB, what does table.merge_insert() do that table.add() cannot?

## The gap merge_insert fills LanceDB tables have an Arrow schema but no primary key and no uniqueness constraint. Nothing in the storage layer objects if you insert the same `id` twice. `table.add(data)` reflects that honestly: it appends rows, commits a version, and is done. Every real pipeline eventually hits the consequence — a re-embedding job re-runs, a Kafka consumer replays, a nightly load overlaps yesterday's — and the table now has two rows per document, both returned by search, both counted, neither obviously wrong. `merge_insert` is the operation that closes the gap: a keyed merge between the incoming batch (the *source*) and the table (the *target*), expressed as a small builder. ## The builder `table.merge_insert("id")` names the column (or columns) to match on and returns a builder. You then declare what happens in each of the three cases: - `when_matched_update_all()` — rows present in both: overwrite the target row with the source row. An optional `where` condition narrows which matches actually update, for example only when a version column is newer. - `when_not_matched_insert_all()` — rows only in the source: insert them. - `when_not_matched_by_source_delete()` — rows only in the target: delete them, optionally restricted by a `where` condition. `.execute(new_data)` runs it. Whichever clauses you omit simply do nothing for that case, so the same builder expresses several distinct operations. ## The three useful shapes **Upsert** — `when_matched_update_all()` + `when_not_matched_insert_all()`. The everyday shape: new documents land, changed documents are replaced, nothing duplicates. Re-running the same batch is a no-op in effect, which is exactly what makes a pipeline retry-safe. **Insert-if-absent** — `when_not_matched_insert_all()` alone. Backfills that must never clobber a newer value. **Replace a slice** — all three clauses, with the delete clause restricted by a `where` on, say, a tenant or source column. This makes the table's rows for that slice exactly equal to the incoming batch, which is how you re-embed one customer's corpus without touching anyone else's. ## Why not delete-then-add The hand-rolled alternative is `table.delete("id IN (...)")` followed by `table.add(new_rows)`. It works, but it is two commits with a window between them where the rows are missing — a concurrent reader can see the deletion but not the reinsertion. It also requires you to build the id list, which for large batches means an ugly filter string. `merge_insert` commits once, so readers move from the old state straight to the new one. ## What it costs A merge is not free. Matching requires scanning or looking up the key column across the table, and updating rows means rewriting the fragments that contain them, not patching bytes in place — so a merge that touches rows scattered across the whole dataset rewrites a lot of fragments. Two habits follow: put a scalar index on the key column so matching does not become a full scan on a large table, and batch your merges rather than calling `merge_insert` per row, which would commit a version and rewrite fragments each time. Also remember that, like every write, a merge commits a new version, and the pre-merge data survives in the previous version until pruning reclaims it. That is a feature when a bad merge needs rolling back, and a storage cost otherwise. ## Choosing a key The merge key must be stable across runs — a document id, a URI, a content hash. A key derived from row order or from the embedding itself is not stable, and the merge silently degenerates into inserts. If a natural key does not exist, minting one upstream in the pipeline is far cheaper than deduplicating a table afterwards. ## The interview answer Say: `add` appends, unconditionally; `merge_insert` matches on a key and applies update/insert/delete rules in one atomic commit; and the reason it matters is that LanceDB enforces no uniqueness, so idempotent ingestion is the writer's job and this is the tool for it.