Project: Model the Shop in Python

This project puts the whole chapter together. From customers.csv and orders.csv you build a star schema. In the middle is one fact table, fact_order_lines. Its grain is one row per order line, and it holds only keys and the numbers you add up. Around it are three dimensions, the things you group and filter by: dim_customer, dim_product and dim_date.

Each business question then becomes one short query: the fact joined to the dimension the question is about. That is why a data engineer builds a star. Every report starts from the same cleaned tables. When each report cleans the raw files its own way, two reports end up with different numbers for the same revenue.

The starting code already builds dim_customer and cleans each city once: " cluj " becomes Cluj, "BERLIN" becomes Berlin and a blank becomes Unknown. It also holds the other three tables and the three report queries, with eight lines missing. You add:

• the surrogate key of dim_product, a number the warehouse makes itself (ROW_NUMBER() OVER (ORDER BY …) numbers the rows 1, 2, 3… in the order you name)
• the days of dim_date
• the fact's join to dim_product
• the fact's measures: quantity, and amount (quantity times unit price)
• one join in each query
• the share of cancelled lines

Two tricks help. range(start, stop, step) makes one row per step in a column called range, and it leaves the stop out. For example, range(DATE '2026-05-01', DATE '2026-05-08', INTERVAL 1 DAY) gives the seven days from May 1 to May 7. It gives them as timestamps, which is why dim_date uses CAST(range AS DATE). The second trick: the average of ones and zeros is a share. This query uses the raw file to find, for each category, the share of lines that bought 2 or more of an item.

big_lines = (
    "SELECT category, "
        "ROUND(AVG(CASE WHEN quantity >= 2 THEN 1 ELSE 0 END), 2) AS big_share "
    "FROM 'orders.csv' "
    "GROUP BY category "
    "ORDER BY category "
)

Your task: fill in the eight missing lines. The program must build dim_product (8 products, keys 1 to 8), dim_date (the 365 days of 2026) and fact_order_lines (1,497 lines). The three queries must answer: paid revenue per month, the top city by paid revenue, and the share of cancelled lines per category. The check prints the sizes of the four tables, then the three answers:

customers | products | days | lines
300 | 8 | 365 | 1497
(1 row)
month | month_name | revenue
1 | January | 552
2 | February | 919
3 | March | 1580
4 | April | 2203
5 | May | 3020
6 | June | 4470
7 | July | 3754
8 | August | 5562
9 | September | 10137
10 | October | 11890
11 | November | 12891
(11 rows)
city | revenue
Rome | 9956
(1 row)
category | lines | share
gifts | 186 | 0.04
home | 388 | 0.07
kitchen | 375 | 0.07
office | 548 | 0.06
(4 rows)

Assignment
Follow the Coding Tutorial to finish the star (the product key, the days of 2026, the fact's join and its measures) and the three report queries (a join in each one, and the cancelled share).

Hint
The code runs from top to bottom, so a table must exist before another table can join it. That's why dim_product comes before the fact. The fact holds only keys, so the month, the city and the category each come from the dimension you join. Use the alias the SELECT already uses: d for dim_date, c for dim_customer and p for dim_product.