Casts and Bad Values in Python

CAST is all or nothing. Order 5030 in 05-03's file has the quantity 'two', so CAST(quantity AS INTEGER) over raw_orders stops the whole statement at that one value: Conversion Error: Could not convert string 'two' to INT32. One typo in one file, and not a single row loads. That's why the last lesson left quantity out.

TRY_CAST(x AS type) works like CAST, but a value it can't convert becomes NULL, the database's mark for a missing value, and the statement goes on. Blank values get there without any cast: order 5058 in 05-06's file came without a customer, read_csv read the empty field as NULL, and casting NULL gives NULL, with no error.

SELECT TRY_CAST('2' AS INTEGER) AS a, TRY_CAST('two' AS INTEGER) AS b
-- a | b
-- 2 | NULL

SELECT CAST('two' AS INTEGER) AS c
-- stops with a Conversion Error

Why a data engineer needs both: a load that stops on every typo never finishes, but a NULL is quiet. A NULL quantity simply drops out of every SUM, and nobody notices. So TRY_CAST always comes with a check: list the rows where a value is missing, with their filename, so someone can fix the file. Missing values are found with IS NULL: = NULL is never true, so it finds nothing. Two conditions where either one is enough are joined with OR.

The check first tries CAST on raw_orders, so you see the error TRY_CAST avoids, then prints the type of quantity in stg_orders, your list of rows to look at, and the count of good rows:

CAST on raw_orders: Conversion Error: Could not convert string 'two' to INT32 when casting from source column quantity
column_name | column_type
quantity | INTEGER
(1 row)
order_id | customer_id | quantity | filename
5030 | 182 | NULL | orders/2026-05-03.csv
5058 | NULL | 1 | orders/2026-05-06.csv
(2 rows)
good_rows
76
(1 row)

Your task: the starting code builds raw_orders and has the stg_orders query from the last lesson. Add quantity to stg_orders as an INTEGER with TRY_CAST. Then write the second query: order_id, customer_id, quantity and filename from stg_orders, for the rows whose quantity or customer is NULL, sorted by order_id.

Assignment
Follow the Coding Tutorial to load quantity with TRY_CAST and list the rows that need a look.

Hint
The new column goes below product, shaped like its neighbours: the TRY_CAST, then AS quantity and a comma. The second query is one line each for SELECT, FROM, WHERE … IS NULL, OR … IS NULL and ORDER BY.