Comparison
Partition overwritevsUpsert
Partition overwrite
the run deletes its own day's partition and writes it again, so re-running never duplicates a row.
The standard implementation of pipeline idempotency: a run owns a partition and replaces it wholesale. It is simple, it needs no keys and no merge logic, and it bounds the damage of a bad run to one partition. Its limits are that late-arriving data belonging to an old partition needs that partition re-run, and that the partition has to actually be the unit a run produces, which is a modelling decision made much earlier.
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 →