Merge: Upsert into a Table

Daily exports often send a row again with new values. Order 5062 is paid in 05-06's file, and 05-07's file sends it again, now cancelled. Appending would leave two rows for one order, so order counts and revenue go wrong. A merge fixes this: it compares each new row with the table on a key column. When the key matches, it updates the table row; when it doesn't, it inserts the new row. That's why a merge is also called an upsert, and it's how daily loads keep a table free of duplicates.

Merge in deltalake: dt.merge(source, predicate=..., source_alias="s", target_alias="t") starts a merge of the DataFrame source into the table dt. The target is the table and the source is the new rows; the aliases give them short names, and the predicate uses those names to say when two rows are the same. .when_matched_update_all() copies every column of a matching new row onto its table row, and .when_not_matched_insert_all() adds the new rows that match nothing. These calls only describe the merge: .execute() runs it, writes one new version and returns a dict of counts, such as num_target_rows_updated and num_target_rows_inserted.

Here a customers table takes new cities from a DataFrame moves. It only updates: customers the table doesn't have are left out. The parentheses around the chain let you write one method per line:

dt = DeltaTable("customers_delta")
result = (
    dt.merge(moves, predicate="t.customer_id = s.customer_id", source_alias="s", target_alias="t")
    .when_matched_update_all()
    .execute()
)
print("Moved:", result["num_target_rows_updated"])

Your code prints the two counts. The check then prints the table's version, how many rows and different order ids it has, and order 5062:

Updated: 1
Inserted: 11
Version: 1
rows | order_ids
21 | 21
(1 row)
order_id | product | status
5062 | Gift card | cancelled
(1 row)

Your task: the starting code reads 05-06's file into day6 (10 orders) and 05-07's into day7 (12 rows: order 5062 again, now cancelled, and 11 new orders). It writes day6 as the table orders_delta (version 0) and opens it as dt. Inside result = ( … ), merge day7 into dt on order_id, update the matches, insert the rest and execute; then print how many rows were updated and inserted.

Assignment
Follow the Coding Tutorial to merge 05-07's orders into the table, one method per line, and print the update and insert counts.

Hint
Name the table t and the new rows s, and match them on order_id. Until you add .execute(), nothing is written: the check keeps showing version 0 with 10 rows.