What You'll Master Here
Loading is not "insert the rows". It is "make the destination match the intended state, even if this runs twice". The write pattern is how you achieve that.
Loading is the "L": writing the finished data into its destination. It feels like the trivial last step, but how you write is where some of the nastiest pipeline bugs live, the ones that double revenue on a retry, or wipe out history on a refresh. The write pattern you choose is as consequential as the transform that produced the data.
This chapter covers the three fundamental write patterns, append, upsert (merge), and overwrite, and when each is correct. It then builds the single most important property of a good loader: idempotency, the guarantee that running the load twice produces the same result as running it once.
By the end you will be able to choose the right write pattern for any dataset, write loads that survive retries without duplicating or losing data, and explain the trade-off between bulk and row-by-row writes. These are the skills that keep the numbers right when, inevitably, a job is re-run.
Loading is not "insert the rows". It is "make the destination match the intended state, even if this runs twice". The write pattern is how you achieve that.
Pipelines re-run constantly, on schedule, after failures, during backfills. A load that is not safe to repeat will sooner or later double-count or corrupt the destination. Choosing the right write pattern and making it idempotent is core to a pipeline you can trust.
- load
- Writing finished data into the destination (warehouse, lake, or downstream system).
- write pattern
- How rows are written: append, upsert/merge, or overwrite.
- idempotent load
- A load that, run twice, leaves the destination in the same state as running it once.
- merge key
- The key used to decide whether an incoming row updates an existing row or inserts a new one.
Treating loading as a trivial INSERT and ignoring re-runs. The first retry or backfill duplicates rows, silently inflating every metric built on the table.
Choose the write pattern from the data’s nature, not by habit.
Design every load to be safe to run twice.
Define the merge key before writing an upsert.
A good load is idempotent: running it again changes nothing. Because pipelines re-run constantly, idempotency is not a nicety, it is what keeps the destination correct.
Learn the three write patterns, then upsert in depth (it is the workhorse), then idempotency. Notice how upsert is what makes idempotent loading easy.
Loading is choosing a write pattern that makes the destination correct even on a re-run; idempotency, usually via upsert, is the property that keeps repeated loads safe.
- Define idempotency in the context of a data load.
- Name the three write patterns and one dataset each suits.