A Star Schema for Orders in Python

A star schema is the shape most warehouses give their tables. In the middle sits one fact table: one row per thing that happened (here, one per order line) with the numbers reports add up. Around it sit the dimension tables with the words you group by, such as a customer's city or a product's category. The fact holds no words, only keys: a value like product_key that points to exactly one row of a dimension.

Why bother: when every report reads the raw files, each one redoes the cleaning (is it " cluj " or Cluj?) and its own joins, and two reports on the same data end up with different numbers. In a star, a city is cleaned once, in dim_customer, and every report reads it from there.

A fact gets its keys with a join: each raw row meets its one row in the dimension and keeps that row's key instead of the words. Here is the same idea for a library (an example only; this file isn't in your folder):

con.execute(
    "CREATE TABLE fact_loans AS "
    "SELECT l.loan_id, b.book_key, l.days "
    "FROM 'loans.csv' l "
    "JOIN dim_book b ON l.title = b.title "
)

CREATE TABLE … AS stores the rows of the SELECT as a new table. Each title matches exactly one row of dim_book, so every loan is kept once, now with a book_key in place of its title.

The starting code builds dim_customer (customer_id and a cleaned city: trimmed, first letter capital, Unknown when blank) and dim_product (8 rows: a product_key from ROW_NUMBER() OVER (ORDER BY product), the product and its category). The check prints the first 3 rows of your fact, its number of lines and total amount, then runs your query:

order_id | status | customer_id | product_key | date | quantity | amount
1001 | paid | 1 | 2 | 2026-10-01 | 5 | 145
1001 | paid | 1 | 3 | 2026-10-01 | 2 | 24
1001 | paid | 1 | 5 | 2026-10-01 | 3 | 6
(3 rows)
lines | total
1497 | 60522
(1 row)
city | category | revenue
Lyon | gifts | 3800
Rome | gifts | 3550
Porto | gifts | 3300
Cluj | gifts | 3250
Madrid | gifts | 3200
Cluj | home | 3111
Berlin | gifts | 3100
Madrid | home | 2861
Porto | home | 2846
Rome | home | 2800
(10 rows)

Your task: build fact_order_lines from orders.csv (named o), joined to dim_product (named p) by product name. Give it the line's own order_id and status, the keys customer_id, product_key and date, and the numbers quantity and amount (quantity * unit_price). Then finish query by joining the fact to dim_customer and dim_product on their keys. Its WHERE f.status = 'paid' is already written: that is why the fact keeps status.

Assignment
Follow the Coding Tutorial to build the fact table fact_order_lines one clause at a time, then join it to its two dimensions in query.

Hint
Every column of the fact comes from o except p.product_key, which the join to dim_product hands over. DuckDB accepts a comma right before FROM, so the table builds after each step. In the report, join each dimension on the key both tables hold: customer_id, then product_key.