Raw, Then Clean: Staging

raw_orders holds the week's files exactly as they arrived: every value is text, and some text has stray spaces. That's right for a raw table and wrong for analysis, which needs numbers it can add and times it can compare. So a warehouse adds a second layer: a staging table, stg_orders, built from raw_orders by one CREATE TABLE … AS SELECT that picks the columns, gives them types and cleans the text. raw_orders itself is never changed.

Three tools do the cleaning. CAST(x AS INTEGER) turns text into a whole number, and CAST(x AS TIMESTAMP) turns '2026-05-08 09:00:00' into a TIMESTAMP, a date and time the database can compare and take the day from. Follow each with AS and a name, so the column keeps its name. TRIM(x) removes the spaces at the start and the end of a text: ' paid ' becomes 'paid', so WHERE status = 'paid' finds it.

query = (
    "CREATE OR REPLACE TABLE stg_customers AS "
    "SELECT "
        "CAST(customer_id AS INTEGER) AS customer_id, "
        "TRIM(city) AS city, "
        "CAST(joined AS DATE) AS joined "
    "FROM raw_customers "
)
con.execute(query)

CREATE OR REPLACE TABLE lets the same statement run again: if the table exists, it is thrown away and built again. A plain CREATE TABLE would stop with an error that the table already exists. Why a data engineer wants two layers: cleaning rules change. When you add a rule next month, you rebuild stg_orders from raw_orders in a second, without asking the shop to send the files again.

The check prints the columns of stg_orders and the row counts of both tables. Then it adds a raw row with spaces, runs your query again, and prints the product and status between brackets, so you can see no spaces are left:

column_name | column_type
order_id | INTEGER
customer_id | INTEGER
product | VARCHAR
unit_price | INTEGER
status | VARCHAR
ordered_at | TIMESTAMP
filename | VARCHAR
(7 rows)
raw_rows | stg_rows
78 | 78
(1 row)
A new raw row with spaces, and stg_orders built again from raw_orders:
order_id | product | status
5076 | [Mug] | [paid]
(1 row)

Your task: the starting code loads raw_orders and has a query that already selects customer_id and unit_price as whole numbers, and filename. Make it build stg_orders with CREATE OR REPLACE TABLE, and add order_id as an INTEGER, product and status trimmed, and ordered_at as a TIMESTAMP. Leave quantity out for now: one of its values is the word 'two', which the next lesson deals with.

Assignment
Follow the Coding Tutorial to build the clean table stg_orders from raw_orders.

Hint
Each column is one line: the CAST or TRIM, then AS and the column's name, then a comma and a space. Only filename, the last column, has no comma before FROM.