Capstone: The Star

Staging gave you clean orders. Now you shape them for reports as a star schema, the layout most warehouses use. A fact table sits in the middle with one row per event (here, one per order). Around it, dimension tables describe who, what and when: dim_customer, dim_product and dim_date. A fact row holds keys that point to the dimensions, plus measures, the numbers reports add up: quantity and amount. Why bother? In one wide table, every order repeats the product's name and the customer's city. A rename means updating every order, and one missed row splits a product into two in the report.

Surrogate keys: a surrogate key is a number the warehouse gives each dimension row itself, instead of trusting a value from the files. You make one with ROW_NUMBER() OVER (ORDER BY …) over grouped rows. For example, on the order statuses this gives 1 | cancelled and 2 | paid:

query = (
    "SELECT ROW_NUMBER() OVER (ORDER BY status) AS status_key, status "
    "FROM stg_orders "
    "GROUP BY status "
)

Three new tools: range(start, stop, step) makes one row per step, in a column named range, and leaves the stop out. range(DATE '2026-06-01', DATE '2026-06-04', INTERVAL 1 DAY) gives June 1, 2 and 3, as timestamps, and the given SELECT casts them to DATE. dim_date is built this way rather than from the orders, so a day with no sales still has its row. COALESCE(a, b) gives a, or b when a is NULL, so the 9 customers with no city show as Unknown instead of a blank. CAST(x AS DATE) keeps only the day of a timestamp. The fact's date is when the order was placed, so late order 5036, which came in 05-04's file, counts on 2026-05-02.

The check prints the tables' sizes, dim_product, two fact rows with their day names, and the customers with an Unknown city:

table_name | rows
dim_customer | 300
dim_date | 7
dim_product | 8
fact_orders | 73
(4 rows)
product_key | product
1 | Gift card
2 | Lamp
3 | Mug
4 | Notebook
5 | Pen
6 | Plant
7 | Stapler
8 | Teapot
(8 rows)
order_id | customer_id | product_key | date | status | quantity | amount | day_name
5036 | 238 | 5 | 2026-05-02 | paid | 1 | 2 | Saturday
5062 | 189 | 1 | 2026-05-06 | cancelled | 1 | 50 | Wednesday
(2 rows)
city | customers
Unknown | 9
(1 row)

Your task: in build_star, finish the three dimensions: product_key in dim_product, the week from range in dim_date, and the city or 'Unknown' in dim_customer. Then finish fact_orders, with one row per order. It needs the join to dim_product, the keys customer_id, product_key and date, and the measures quantity and amount.

Assignment
Follow the Coding Tutorial to build the three dimension tables and the fact table of the shop's star.

Hint
dim_product's query already has GROUP BY product, so each name is one row: number them in name order. The week starts at DATE '2026-05-01', and its stop is the day after the last day. In fact_orders, o is stg_orders and p is dim_product. amount is quantity times unit price.