The route

Apply a delta with Merge

Download the exercise files

Source systems rarely send you everything again. They send what changed: new orders, and orders whose status moved on. This file is called a delta, and applying it is exactly what Merge is for. You'll apply one, then check the result in the database — making sure the check runs only after the write.

Goal

A pipeline that merges orders_delta.csv into orders and then prints the order count per status, read from the database after the merge.

You'll need

  • orders at 3,000 rows (end of level 4.3).
  • The exercise files for this level: orders_delta.csv — 200 rows.
  • About 20 minutes.

The delta holds two kinds of rows:

  • 150 new orders (order_id 73001–73150), placed in January 2026;
  • 50 existing orders that were pending and are now paid (41) or refunded (9).

Steps

1. Check where you start

Run a PostgreSQL Read with this query and note the numbers:

SELECT status, COUNT(*) AS orders
FROM orders
GROUP BY status
ORDER BY status

paid 2682, pending 189, refunded 129.

2. Merge the delta

Create a pipeline w4-4-delta: a CSV Source orders_delta → a PostgreSQL Write merge_into_orders on academy_pg, table orders, Table Operation None, Data Operation Merge, Key Columns order_id.

3. Check after — and only after

Add a Run After (after_merge) behind the write, then the status query from step 1 as a PostgreSQL Read (status_counts), and a Console.

Merge, then a barrier, then the check

Without the Run After, the PostgreSQL Read has no input and nothing tells it to wait — it could run before the merge finishes and show you the old numbers. World 5's barrier, applied to a database.

4. Execute

Execute (the merge takes a few seconds: 200 rows, one statement each) and expand the output.

The counts, read back after the merge

Expected result

status before after
paid 2682 2854
pending 189 158
refunded 129 138
total 3000 3150

Check the arithmetic: 150 new rows (131 paid, 19 pending) plus 50 status changes (41 to paid, 9 to refunded) — 2682 + 131 + 41 = 2854, 189 + 19 − 50 = 158, 129 + 9 = 138.

Run the pipeline again: nothing changes. Applying the same delta twice is harmless — that's the point of Merge.

Common pitfalls

The check shows the old numbers. It ran before the merge. Put it behind a Run After connected to the write.

3,200 rows instead of 3,150. The write was on Insert, so all 200 delta rows were added — the 50 changed orders now exist twice, once with the old status and once with the new. Use Merge, with order_id as the key.

A delta that deletes. Merge adds and updates; it never removes a row that's missing from the delta. Deletions need their own handling — usually a deleted flag in the delta, or a Delete data operation fed with the keys to remove.

What you built

An incremental load: only what changed crosses the network, and the table ends up exactly as if you had reloaded everything. Every scheduled pipeline that feeds a database ends up looking like this.

Check yourself

One question, drawn from a larger bank. Get it right to pass.

  1. An order disappears from the source system. Does Merge remove it?