Set Bad Rows Aside

When a check finds bad rows, the load still has to put them somewhere. Drop them and real orders vanish without a trace; let them in and the report goes wrong later. A data engineer quarantines them instead: rows that pass the rules go to the staging table stg_orders, and rows that break one go to rejected_orders with a reason column, so someone can fix the source and nothing is lost.

New SQL: INSERT INTO table SELECT … adds the rows a SELECT finds to a table that already exists. CASE picks a value row by row: CASE WHEN condition THEN value … ELSE value END gives the value of the first WHEN that is true, or the ELSE value when none is. NOT (…) is true exactly when the condition in the brackets is false. For example, on customers.csv:

SELECT customer_id,
    CASE WHEN age IS NULL THEN 'no age' ELSE 'has age' END AS note
FROM 'customers.csv'
WHERE NOT (city IS NULL)

The starting code loads the week's 78 rows into raw_orders, every column as text, and makes the two empty tables: SELECT * FROM raw_orders LIMIT 0 copies the columns but no rows, and rejected_orders gets one more column at the end, reason. Two rules decide where a row goes: it needs a customer_id, and TRY_CAST(quantity AS INT) must not be NULL (TRY_CAST gives NULL when the text isn't a whole number). The first INSERT takes the rows that break a rule; the second takes the opposite rows, so every row lands in exactly one table. After your code, the lesson's test prints:

raw_orders: 78 rows
stg_orders: 76 rows
rejected_orders: 2 rows
reason | order_id | customer_id | quantity
bad quantity | 5030 | 182 | two
no customer | 5058 | NULL | 1
(2 rows)

Your task: finish the first query so it inserts every row that breaks a rule, with its reason ('no customer' when customer_id is NULL, otherwise 'bad quantity'), and run it. Then give the second query the opposite WHERE, with NOT, and run it too.

Assignment
Follow the Coding Tutorial to fill rejected_orders and stg_orders with two INSERT … SELECT statements whose WHERE clauses are opposites.

Hint
A query in a Python string does nothing until con.execute(query) runs it. Put the exact reject rule inside NOT ( … ), so no row is lost or loaded twice.