Track · 2:18 · Liner note
The Upsert
An upsert is an operation that inserts a row if its key does not exist and updates the existing row if it does, in a single step.
Insert if new, update if not: the upsert is the small idea that makes pipelines safe to retry. Run the same batch twice and the table ends in the same state, which is the working definition of idempotent.
The details matter. Decide what the key is, which columns an update may overwrite and what happens when the incoming row is older than the stored one. Different engines implement it differently, so read how yours resolves conflicts. A good upsert turns a nervous re-run into a non-event.