Surrogate Keys in Python

A fact table points to its dimensions with keys. The product's name could be the key, but names change. A surrogate key is a number the warehouse gives each dimension row itself: it means nothing outside the warehouse, so nothing outside can change it.

ROW_NUMBER() OVER (ORDER BY …) numbers the rows of a result 1, 2, 3… in the order you give. In a query with GROUP BY it runs after the grouping, so it numbers the groups. For example, a key for each category:

con.execute(
    "CREATE TABLE dim_category AS "
    "SELECT category, "
        "ROW_NUMBER() OVER (ORDER BY category) AS category_key "
    "FROM 'orders.csv' "
    "GROUP BY category "
)

There gifts gets 1, home 2, kitchen 3 and office 4. The fact table is built once by joining the order lines to the dimension on the name, and it keeps only the key; from then on, reports join on the key. A data engineer needs this because a join on a name loses rows without any error: when the shop renames Mug to Mug 300ml, joining orders.csv to dim_product by name finds only 1,311 of the 1,497 lines, and Mug's sales vanish from the report.

The rename is an UPDATE dim_product SET … WHERE …, which is what a reload does after the shop changes a name in its source. The hidden check runs your query, then counts the lines that a join on the key and a join on the name find:

product | amount
Gift card | 21500
Lamp | 11542
Mug 300ml | 4740
Notebook | 2961
Pen | 726
Plant | 6675
Stapler | 3600
Teapot | 8778
(8 rows)
lines_found_by_key | lines_found_by_name
1497 | 1311
(1 row)

Your task: give dim_product a product_key with ROW_NUMBER() OVER (ORDER BY product), keep p.product_key in fact_order_lines, join the report in query to dim_product p on product_key, and rename 'Mug' to 'Mug 300ml' in dim_product once the fact table is built.

Assignment
Follow the Coding Tutorial to give each product a surrogate key, store it in the fact table and show the report surviving a rename.

Hint
The report's join compares f.product_key with p.product_key, never names. The UPDATE goes in con.execute("…") with the statement in double quotes, so 'Mug' keeps its single quotes.