Capstone: Clean It

The raw layer holds what arrived. The staging layer holds what you can trust: real types, one row per order, and nothing broken. This is where a pipeline stops bad data. A report that reads raw tables breaks on a word in a number column, and it counts an order twice when its file is sent again.

TRY_CAST: CAST(x AS INTEGER) stops the whole query at the first value it can't convert, like the quantity two. TRY_CAST(x AS INTEGER) gives NULL there instead, so the query goes on. TRY_CAST('12' AS INTEGER) is 12, and TRY_CAST('two' AS INTEGER) is NULL. The starting code already casts the other columns. You cast quantity, and in WHERE you keep only the rows that have a customer_id and a quantity TRY_CAST can read.

One row per order: orders 5040 and 5062 came again in a later file, and 5027 came twice in the same file. The newest file wins, because it holds the order's latest state: 5062 was paid, then cancelled. ROW_NUMBER() OVER (PARTITION BY … ORDER BY …) numbers the rows 1, 2, … inside each group. QUALIFY filters on that number the way WHERE filters on a column. WHERE can't do it, because it runs before window functions. For example, this keeps each customer's latest order:

query = (
    "SELECT customer_id, order_id, ordered_at "
    "FROM stg_orders "
    "QUALIFY ROW_NUMBER() OVER ( "
        "PARTITION BY customer_id ORDER BY ordered_at DESC "
    ") = 1 "
)

Rejects and cities: the rows you leave out go to rejected_orders. It keeps every column as it arrived, plus a reason from a CASE, so nothing disappears quietly and someone can fix it at the source. Then stg_customers tidies city. TRIM removes the spaces around a text, and UPPER and LOWER change its case. LEFT(text, 1) is its first letter, SUBSTR(text, 2) is the rest, and || joins two texts. ' cluj ' must become Cluj, and BERLIN must become Berlin. The check prints:

stg_orders | rejected_orders | stg_customers
73 | 2 | 300
(1 row)
order_id | quantity | amount | status | filename
5027 | 2 | 4 | paid | orders/2026-05-03.csv
5040 | 2 | 4 | paid | orders/2026-05-05.csv
5062 | 1 | 50 | cancelled | orders/2026-05-07.csv
(3 rows)
order_id | customer_id | quantity | reason
5030 | 182 | two | bad quantity
5058 | NULL | 1 | no customer
(2 rows)
city | customers
Berlin | 44
Cluj | 52
Lyon | 48
Madrid | 51
Porto | 46
Rome | 50
NULL | 9
(7 rows)

Your task: in build_staging, finish three tables. In stg_orders, cast quantity with TRY_CAST, add the two WHERE tests, and add a QUALIFY that keeps each order's copy from the newest file. In rejected_orders, keep every column plus a reason, for the bad rows only. In stg_customers, add the tidy city.

Assignment
Follow the Coding Tutorial to build stg_orders, rejected_orders and stg_customers.

Hint
In WHERE, quantity is still the raw text, so test it with its own TRY_CAST(…) IS NOT NULL. The file names hold the dates, so ORDER BY filename DESC puts each order's newest copy first. rejected_orders uses the opposite tests, joined with OR.