Comparison
Bulk loadvsUpsert
Bulk load
you stopped inserting a row at a time and pointed the warehouse at a folder of files, and the load went from hours to a minute.
Loading data through a warehouse's file-oriented path rather than as individual statements. Analytical stores are built to ingest large sorted chunks and are catastrophically slow at single-row writes, so this is less an optimisation than the intended door. It is the first thing to check when someone reports that the warehouse 'cannot keep up with writes' — usually it is being used as if it were a transactional database.
Full entry →Upsert
you re-ran the load after a failure and the rows updated in place instead of appearing twice.
A write that inserts a row if its key is new and updates it if the key is already there. It is the single most load-bearing operation in a pipeline, because it is what makes a re-run safe: the same batch applied twice leaves the same table. Its cost is that it needs a real key and a way to decide which of two versions is newer, and getting either wrong turns a harmless retry into silent data loss.
Full entry →