Loading & Write Patterns

Loading is the step where you write finished rows into a table in the warehouse. A warehouse is a database built for analysis.

Recap: the raw layer keeps data as it arrived. The curated layer holds the clean tables that people query. A load writes into a curated table.

How you write matters, because a job often runs twice. A scheduler (a tool that starts jobs on time) runs a failed job again. This is called a retry.

A person may also run a job again to fix a bug.

One order tells the story in this chapter. On day 1, order 105 is pending and has no amount yet.

On a later day it is paid, for 22.00. We made up that amount for this example.

Here is the warehouse table orders after the first load.

Order 106 does not exist yet. It arrives on day 2. Each pattern below starts from this table.

New rows first go into a staging table. A staging table is a holding table for rows that are not yet in the final table. In the examples, it is called orders_staging.

The next sections show six ways to write from staging into orders.

• Append: add the new rows at the end.
• Full refresh: empty the table, then load everything.
• Replace one day: delete one day of rows, then load that day again.
• Upsert: update a row if the same order_id is already there; add it if not.
• Soft delete: mark a row as deleted, but keep it.
• Atomic swap: build a new table, then switch to it.

For each one, ask two questions. What does the table look like after the load? What happens if the same load runs again?

Append adds rows at the end of a table. It never touches the rows that are already there. In SQL, this is a plain INSERT.

Day 1: the table orders is empty, and staging holds five orders. We append them.

INSERT INTO orders (order_id, status, amount, order_date)
SELECT order_id, status, amount, order_date
FROM orders_staging;

Now the job fails at its last step, and the scheduler runs it again. The staging table still holds the same five orders.

-- the same statement runs a second time
INSERT INTO orders (order_id, status, amount, order_date)
SELECT order_id, status, amount, order_date
FROM orders_staging;

Append cannot tell that it already loaded these rows. Each run adds them again. The same happens when order 105 changes: you get one pending row and one paid row.

A full refresh empties the table and loads everything again. Another name is truncate-and-load. TRUNCATE removes all rows from a table very fast.

Day 2: the source now shows six orders, and order 105 is paid for 22.00. Staging holds all six.

We run both statements inside one transaction. A transaction makes all its steps succeed together, or none.

BEGIN;
TRUNCATE orders;
INSERT INTO orders (order_id, status, amount, order_date)
SELECT order_id, status, amount, order_date
FROM orders_staging;
COMMIT;

Run it again and you get the same six rows. A full refresh is safe to re-run. It is also simple, because the table always equals the staging table.

The cost is time. Every run rewrites every row, even rows that did not change.

For six rows this is nothing. For a table with billions of rows, it can take hours.

Recap from the data modeling chapter: to fix one day, you delete that day, then insert it again. In this chapter, a partition means all rows for one day.

Order 105 has order_date 2026-01-20. The new rows for that day hold one row: order 105, now paid for 22.00.

BEGIN;
DELETE FROM orders
WHERE order_date = '2026-01-20';
INSERT INTO orders (order_id, status, amount, order_date)
SELECT order_id, status, amount, order_date
FROM orders_staging
WHERE order_date = '2026-01-20';
COMMIT;

Run it again. DELETE removes the new 105 row, and INSERT puts the same row back. The table is the same after the second run.

This is cheaper than a full refresh, because it touches one day. It also keeps all the other days. Many pipelines run it once per day.

An upsert means update plus insert. If the key already exists, the row is updated. If not, the row is added.

A key is a column that is different for every row. Here the key is order_id.

For ON CONFLICT, order_id must have a PRIMARY KEY or UNIQUE constraint. Without one, PostgreSQL stops with an error.

Day 2: staging holds two rows. Order 105 changed, and order 106 is new.

In PostgreSQL, you write an upsert with INSERT ... ON CONFLICT.

-- orders has PRIMARY KEY (order_id)
INSERT INTO orders (order_id, status, amount, order_date)
SELECT order_id, status, amount, order_date
FROM orders_staging
ON CONFLICT (order_id)
DO UPDATE SET status = EXCLUDED.status,
              amount = EXCLUDED.amount;

Run it again. Both keys now exist, so both rows are updated to the same values. The table stays the same six rows.

PostgreSQL 15 and newer also have MERGE. It gives the same result here.

MERGE does not need a unique key, and it can also delete rows. ON CONFLICT is safer when two loads write at the same time.

In older versions, use ON CONFLICT. Other warehouses, such as Snowflake and BigQuery, offer MERGE too.

MERGE INTO orders AS t
USING orders_staging AS s
  ON t.order_id = s.order_id
WHEN MATCHED THEN
  UPDATE SET status = s.status, amount = s.amount
WHEN NOT MATCHED THEN
  INSERT (order_id, status, amount, order_date)
  VALUES (s.order_id, s.status, s.amount, s.order_date);

Sometimes the source deletes an order. An upsert cannot see this, because the deleted row is simply missing from staging. The old row stays in the warehouse.

A hard delete removes the row for good. A soft delete keeps the row and marks it with a flag. A flag is a column that is true or false.

Say the shop deletes order 104. This is made up for the example.

The source must tell you which orders it deleted. Here it sends their order_ids in a table, deleted_orders.

We add the column with ALTER TABLE orders ADD COLUMN is_deleted boolean NOT NULL DEFAULT false. We start from the table after the upsert and hide order_date to keep it small.

UPDATE orders
SET is_deleted = true
WHERE order_id IN (SELECT order_id FROM deleted_orders);

Readers must now skip deleted rows. A query adds a filter.

SELECT order_id, amount
FROM orders
WHERE NOT is_deleted
  AND status = 'paid';

The row for 104 is still in the table.

You can still look at it later, for example to check what was deleted. Many teams make a view that hides deleted rows. A view is a saved query that you read like a table.

A reader can query the table while you load it. With the full refresh above, PostgreSQL locks the table, so readers wait until the whole load ends.

Without a transaction, a reader may see an empty or half-filled table.

An atomic swap avoids that. Atomic means it happens all at once.

You build the new table beside the old one. You check it. Then you switch the names in one transaction.

Staging holds the six orders of day 2.

-- 0. clean up after a failed run
DROP TABLE IF EXISTS orders_new;
DROP TABLE IF EXISTS orders_old;

-- 1. build the new table beside the old one
CREATE TABLE orders_new (LIKE orders INCLUDING ALL);
INSERT INTO orders_new
SELECT * FROM orders_staging;

-- 2. check it
SELECT count(*) FROM orders_new;  -- expect 6

-- 3. switch the names in one transaction
BEGIN;
ALTER TABLE orders RENAME TO orders_old;
ALTER TABLE orders_new RENAME TO orders;
COMMIT;

-- 4. remove the old table
DROP TABLE orders_old;

Follow what a reader sees at each step.

The reader never sees a table with 3 or 4 rows. If the build fails, you drop orders_new and the old table is untouched. The switch is quick, but it makes readers wait for a moment.

The cost is the same as a full refresh: you build every row. You also need space for two copies of the table for a short time.

A load is safe to re-run when two runs give the same table as one run. The next chapter gives this a name and goes deeper.

The table compares all six patterns. Each answer comes from the examples above.

Only append grows the table on every run. All the other patterns replace or update rows, so the second run gives the same result.

To keep history of changes, you need more than these patterns. The data modeling chapter on slowly changing dimensions shows how.

A real pipeline writes more than once. Each write can use a different pattern. Here is one way to place them for the shop.

Ask the re-run question at every write. One unsafe write makes the whole pipeline unsafe.

The next chapter shows how to make append safe.

Recap from the Python chapter: each trip to the database costs time. One INSERT per row means one trip per row. For a million rows, that is a million trips.

A batch sends many rows in one trip. In Python, executemany does this.

A bulk load goes further. PostgreSQL has a COPY command that loads a whole file at once.

Commit once per batch, not once per row. If one batch fails, roll it back and run that batch again. Use a pattern that is safe to re-run, so running the batch again does not double rows.

These five scenarios use the write patterns from this chapter. Build each one on the pipeline canvas.

Optional: some scenarios need ideas from the idempotency chapter and the backfills chapter. Try them after those chapters.