Start learning free

LESSON 13 OF 107 · DATA ENGINEERING: THE WAREHOUSE

Casts and Bad Values in Python

Practice

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. Put the second query, as a string, in the empty query at the end of the starting code. Do not run it or print anything. The check prints every line of the expected output itself, using its own helper show(con, sql), so do not write a function named show.


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.


STEP 1 OF 6
    Ctrl + Enter to check

    De Duck Casts in Python

    Keep going with Python1,600+ step-by-step lessons, from your first line of code to coding interviews. Free to start.
    Start learning free