Every load runs more than once — tomorrow, after a fix, by accident. What the second run does is decided by two settings on PostgreSQL Write: the Table Operation (what happens to the table) and the Data Operation (what happens to the rows). This level runs the same load twice in each mode and watches the row count.
Goal
Know, for each combination, what a second run does to orders — and which one to pick for a full
reload versus a set of changes.
You'll need
- Level 4.1 done (
ordershas 3,000 rows). - A way to count rows: a PostgreSQL Read with
SELECT COUNT(*) FROM ordersand a Console is enough. - About 20 minutes.
Steps
1. Insert into an existing table — twice
Create a pipeline w4-3-write-modes: a CSV Source for orders.csv and a PostgreSQL Write
orders_db on academy_pg, table orders, with:
| Table Operation (DDL) | Data Operation (DML) |
|---|---|
| None — use the existing table | Insert |

Execute, then count: 6,000. Every order is now in the table twice. Insert means add these
rows, and nothing stops it — the table 4.1 created has no primary key, so the database has no rule
that says an order_id may appear only once.
2. Truncate, then insert
Set Table Operation to Truncate and run again: 3,000. Truncate empties the table before the insert, keeping its columns and types. For a full reload — the whole dataset, every time — Truncate + Insert (or Drop & Create + Insert, as in 4.1) is the right choice, and it's fast: about a second for 3,000 rows.
3. Merge
Set Table Operation back to None, Data Operation to Merge, and run:
Merge requires key_columns to be specified
Merge has to know what makes two rows the same row. Add order_id in Key Columns (type it and
press Enter):

Run it. For each incoming row, Merge updates the table's row with the same order_id if
there is one, and inserts it if there isn't. Count: still 3,000. Run it again: still 3,000.
That's the property you want from a load that may repeat: idempotent.
It takes a second or two — a little slower than Insert, which streams rows with COPY, because
Merge has to look each key up. Odara sends the rows in statements of up to 1,000 at a time; if two
incoming rows share a key, the later one wins.
4. The rule
| You're loading… | Use |
|---|---|
| the full dataset, every time | Truncate (or Drop & Create) + Insert |
| a set of changes — new and updated rows | None + Merge, with key columns |
| rows that must never be duplicated, in a table you own | add a primary key in the database, too |
Leave the table at 3,000 rows (Truncate + Insert once more) — level 4.4 starts from there.
Expected result
| Run | Table Operation | Data Operation | Rows after |
|---|---|---|---|
| second load | None | Insert | 6,000 |
| reload | Truncate | Insert | 3,000 |
| merge, twice | None | Merge on order_id |
3,000 |
Common pitfalls
Rows doubled after a rerun. None + Insert appends. Use Truncate for full reloads, Merge for changes.
Merge requires key_columns to be specified.
Add the column(s) that identify a row — order_id here.
Using Merge for a full reload. It works, but every row is looked up. For the whole dataset every time, Truncate + Insert is simpler and faster; keep Merge for changes.
Merge on the wrong key.
Keying on customer_id would make every order of a customer "the same row": each incoming order would
be written over all of that customer's orders. The key must identify exactly one row.
What you built
A working mental model of the two knobs on every database target, and the rule for choosing: reload with Insert, apply changes with Merge. Next level applies a real set of changes.
Check yourself
One question, drawn from a larger bank. Get it right to pass.
You run None + Insert a second time on the 3,000-row table. Result?