One Wide Table or a Star? in Python

A star schema keeps each name in one place: the fact fact_order_lines holds keys and numbers, and the dimensions dim_customer and dim_product hold the cities, products and categories. Every report joins the fact to the dimensions it needs. Many teams also build a wide table (sometimes called one big table): the fact with the dimensions' columns copied onto each row, so a report needs no join at all.

CREATE TABLE … AS SELECT runs a query once and stores its rows as a new table: a copy. In a SELECT, f.* means every column of the table named f, and you can list more columns after it. For example, on another table this gives every column of dim_product plus a currency column:

SELECT p.*, 'EUR' AS currency
FROM dim_product p

What each shape costs: the wide table repeats the same few cities and categories on every line, and because it is a copy, it goes stale when a dimension changes, until someone rebuilds it. The star stores each name once and a change touches one row, but every report pays for its joins. A data engineer needs to weigh both: wide tables make dashboards simple, but a forgotten rebuild shows people old names.

The starting code builds the star: dim_customer (customer_id, city), dim_product (product_key, product, category) and fact_order_lines (order_id, status, customer_id, product_key, date, quantity, amount: every line, cancelled ones too). The check prints the first rows of orders_wide, revenue per category of the paid lines from both shapes, then moves customer 160 to Rome with an UPDATE of dim_customer and asks both for that customer's city. Your code must print:

order_id | status | customer_id | product_key | date | quantity | amount | city | product | category
1001 | paid | 1 | 2 | 2026-10-01 | 5 | 145 | Rome | Lamp | home
1001 | paid | 1 | 3 | 2026-10-01 | 2 | 24 | Rome | Mug | kitchen
1001 | paid | 1 | 5 | 2026-10-01 | 3 | 6 | Rome | Pen | office
(3 rows)
From the star:
category | revenue
gifts | 20750
home | 16809
kitchen | 12682
office | 6737
(4 rows)
From orders_wide:
category | revenue
gifts | 20750
home | 16809
kitchen | 12682
office | 6737
(4 rows)
After customer 160 moves to Rome:
star_city | wide_city
Rome | Cluj
(1 row)

Your task: finish CREATE TABLE orders_wide AS: every column of the fact plus c.city, p.product and p.category, from fact_order_lines f joined to dim_customer c on customer_id and to dim_product p on product_key.

Assignment
Follow the Coding Tutorial to build orders_wide from the fact and its two dimensions, then compare what the two shapes print.

Hint
Type the dimensions' columns last: c.city and p.category only exist once dim_customer c and dim_product p are joined. Joining on the keys adds columns, not rows, so the wide table keeps one row per order line.