Do Source and Target Agree?

The interviewer asks: "You loaded a day of orders from the source into the warehouse. How do you prove the two agree?" That check is called reconciliation. At work, a load that quietly drops an order or keeps an old status makes every report built on it wrong, and nobody notices until the numbers don't add up. Here the source is May 2's file, loaded as raw_orders. The target is stg_orders, loaded from stg_orders.parquet, which a hidden setup writes before your code runs: the result of loading that day, with the file's columns.

The trap: most people compare row counts. Both sides have 12 rows, yet three orders differ. A count can't see which rows are there or what they hold.

EXCEPT compares whole rows: A EXCEPT B keeps the rows of A that B doesn't have with every column equal. Run it both ways, then JOIN the two results on the key: a key in both lists is in both tables with different values, and a key in one list only is missing from the other side. A small example, a school's register and its gradebook:

register:   (1, Ana, 10A)   (2, Ben, 10B)   (3, Cara, 10A)
gradebook:  (1, Ana, 10A)   (2, Ben, 10A)   (4, Dan, 10B)
register EXCEPT gradebook  →  (2, Ben, 10B)  (3, Cara, 10A)
gradebook EXCEPT register  →  (2, Ben, 10A)  (4, Dan, 10B)
join on id  →  2: class 10B vs 10A;  3 and 4 are each on one side only

DuckDB also lets a query start with FROM: FROM some_table alone returns every column, so you can watch a query run before you write its SELECT. Keep the counts as well: EXCEPT drops duplicate rows, so a row loaded twice shows up only in the counts. Your code and the check print:

source_rows | target_rows
12 | 12
(1 row)
order_id | product | status
5015 | Gift card | paid
5021 | Stapler | cancelled
(2 rows)
order_id | product | status
5021 | Stapler | paid
5036 | Pen | paid
(2 rows)
order_id | source_status | target_status
5021 | cancelled | paid
(1 row)

Order 5015 never reached the target, 5036 is in the target but not in May 2's file, and 5021 has a different status on each side.

Your task: finish only_in_source and only_in_target with EXCEPT, one each way, then join them on order_id and select each changed order's status on both sides as source_status and target_status.

Assignment
Follow the Coding Tutorial to find the rows that differ, both ways, and to put the two versions of a changed order side by side.

Hint
A EXCEPT B and B EXCEPT A give different rows. Name the lists s and t, so the SELECT can say which status comes from which side.