The route

Insert, Truncate, Merge: writing twice

Download the exercise files

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 (orders has 3,000 rows).
  • A way to count rows: a PostgreSQL Read with SELECT COUNT(*) FROM orders and 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

Insert into the existing table, no table operation

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):

Merge, matching rows on order_id

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.

  1. You run None + Insert a second time on the 3,000-row table. Result?