A Skewed Join in Python

The interview question: “One customer has 40% of all order lines. Your join with dim_customer runs for an hour: one worker is busy while the others sit idle. Why, and what do you try first?” This is data skew: one join key holds far more rows than the rest. A distributed engine, like Spark or a cloud warehouse, runs a big join on many workers. It sends each row to a worker chosen by its join key, so that matching rows meet in one place. That means every row of the big key lands on the same worker, and the whole job waits for it. In a real job, that worker can also run out of memory and fail the job.

The hidden setup adds a reseller, customer 301 in Berlin, with 934 paid order lines. order_lines holds the shop's paid lines plus the reseller's: 2,335 in all. DuckDB runs on one machine, so the check imitates 4 workers with customer_id % 4. The trap: “add more workers”. One key always goes to one worker, however many there are. With 4 workers, worker 1 gets 1,284 rows and the others get 327 to 370. With more workers, one of them still holds all 934 lines of customer 301.

The usual first fix is to aggregate before the join: GROUP BY the join key first, so the big key arrives as one row. This works when the report only adds things up, as revenue per city does. Here is a small example on another key, with days joined to a calendar:

WITH per_day AS (
    SELECT date, COUNT(*) AS lines FROM order_lines GROUP BY date
)
SELECT d.weekday, SUM(p.lines) AS lines
FROM per_day p JOIN dim_date d ON p.date = d.date
GROUP BY d.weekday

Two other fixes come up in interviews. A broadcast join copies a small table such as dim_customer to every worker, so the big table never has to move by key. Salting splits the hot key into several keys by adding a random number to it. Your code must print:

customer_id | lines | share
301 | 934 | 40
1 | 13 | 0.6
72 | 13 | 0.6
(3 rows)
worker | rows_without_fix | rows_with_fix
0 | 327 | 72
1 | 1284 | 73
2 | 370 | 73
3 | 354 | 75
(4 rows)
city | revenue
Berlin | 64460
Rome | 9956
Cluj | 9800
Madrid | 9354
Lyon | 9035
Porto | 8808
Unknown | 1565
(7 rows)

Your task: build customer_totals, with one row per customer holding its lines and revenue. Then make the city report read it and join dim_customer.

Assignment
Follow the Coding Tutorial to aggregate the order lines per customer, then join that small table to dim_customer.

Hint
GROUP BY customer_id makes one row per customer, and COUNT(*) and SUM(...) work inside each group. The report already sums t.revenue per c.city. It still needs the tables that t and c stand for.