Apply a Change Log

The interview question: “The app's database sends us a change log for the customers table: inserts, updates and deletes, each with a time. Apply it.” A change log, or CDC feed (change data capture), is how a warehouse keeps up with an app's database without copying the whole table every night. If you apply it wrong, deleted customers stay in your reports, or a customer keeps an old address.

Two rules. First, only the latest change per id counts. The log is not in time order, so only the timestamp tells you which change is the latest: if product 4's price log reads “12 at 09:00” and then “10 at 08:00”, the price now is 12, not the 10 on the last line. Second, a delete is a change like any other: a customer deleted at 09:20 who signs up again at 14:00 stays.

So you apply the log in three moves. Keep each customer's newest change in a table latest, with ROW_NUMBER() and QUALIFY. DELETE the customers whose latest change is a delete. Then upsert the others with INSERT … ON CONFLICT (customer_id) DO UPDATE: a new id is inserted and an existing one gets its new city, so inserts and updates don't need separate code. Warehouses such as Snowflake or BigQuery do the whole pass in one MERGE INTO statement. In DuckDB, the DELETE plus the upsert does the same job.

The trap is to upsert the whole log as it is. The starting code does just that and ends with 302 customers: customer 3 is still in Porto, its older change, and customers 5 and 9 are back with no city. Here is the log, customer_changes.csv, written for this lesson:

customer_id,op,city,changed_at
3,update,Porto,2026-05-04 09:00:00
301,insert,Cluj,2026-05-04 09:05:00
5,delete,,2026-05-04 09:10:00
9,delete,,2026-05-04 09:20:00
3,update,Berlin,2026-05-04 11:30:00
301,update,Rome,2026-05-04 12:00:00
9,insert,Madrid,2026-05-04 14:00:00
302,insert,Porto,2026-05-04 15:00:00
7,update,Cluj,2026-05-04 16:00:00
7,update,Madrid,2026-05-04 10:00:00

The check prints latest, then the log's customers as they now are in dim_customer, then how many customers there are (300 before the log):

customer_id | op | city | changed_at
3 | update | Berlin | 2026-05-04 11:30:00
5 | delete | NULL | 2026-05-04 09:10:00
7 | update | Cluj | 2026-05-04 16:00:00
9 | insert | Madrid | 2026-05-04 14:00:00
301 | update | Rome | 2026-05-04 12:00:00
302 | insert | Porto | 2026-05-04 15:00:00
(6 rows)
customer_id | city
3 | Berlin
7 | Cluj
9 | Madrid
301 | Rome
302 | Porto
(5 rows)
customers
301
(1 row)

Your task: in the first query, keep each customer's newest change. Then make the DELETE remove only the customers whose latest op is 'delete', and keep those customers out of the upsert.

Assignment
Follow the Coding Tutorial to apply the change log to dim_customer: first the newest change per customer, then the delete, then the upsert.

Hint
Number each customer's changes from the newest changed_at down and keep number 1. Customer 9's latest change is an insert, so 9 stays. Customer 5's latest change is a delete, so 5 is deleted and must not come back through the upsert.