Where
pyspark/joins.ipynb, the "Ship it to Dev" section and "Solution 1 - loyalty attachment rate per store".
The problem
reconciled joins pos_transactions to the loyalty redemptions on store_id alone:
reconciled = pos_transactions.join(loyalty_normalized, on="store_id", how="left")
Both tables have multiple rows per store, so this is a many-to-many join and every transaction row is duplicated once per redemption at that store. With the notebook's own data, store 42 has 10 transactions and 8 redemptions, so reconciled contains 10 x 8 = 80 rows for store 42. The by_store aggregate that the story presents as the final, correct deliverable therefore reports:
total_revenue = the real revenue multiplied by 8 (each amount summed once per redemption);tx_count = 80 instead of 10 (F.count("tx_id") counts joined rows, not transactions).
The narrative then celebrates the result ("ok this should work... YOU ARE A LIFESAVER"), so a reader walks away having learned to hand finance numbers inflated by fan-out - which is exactly the class of bug the notebook is trying to teach people to avoid.
The same fan-out breaks Solution 1 (the loyalty attachment rate). After the same join, for any store with at least one redemption every joined row has a non-null redemption_id, so
F.count("redemption_id") / F.count("tx_id")is 80/80 = 1.00 for store 42 (the intended answer is 8/10 = 0.8). The metric degenerates to exactly 1.0-or-0.0 for every store, while the surrounding text says "the counts work out correctly".
Suggested fix
Join on the real relationship key - with this data, ["store_id", "customer_id"] - or keep the store-level join but aggregate the two tables separately before joining:
tx_by_store = pos_transactions.groupBy("store_id").agg(
F.sum("amount").alias("total_revenue"),
F.count("tx_id").alias("tx_count"),
)
red_by_store = loyalty_normalized.groupBy("store_id").agg(
F.count("redemption_id").alias("redemption_count"),
F.sum("points_redeemed").alias("total_points_redeemed"),
)
by_store = tx_by_store.join(red_by_store, "store_id", "left")(Aggregating before joining also fixes the attachment rate: redemption_count / tx_count becomes 8/10.)
This could even be turned into a teaching moment: run the naive store-only join first, show the inflated numbers, and let the reader catch the fan-out - it is a better interview lesson than the type-mismatch bug the notebook already covers.
Where
pyspark/joins.ipynb, the "Ship it to Dev" section and "Solution 1 - loyalty attachment rate per store".The problem
reconciledjoinspos_transactionsto the loyalty redemptions onstore_idalone:Both tables have multiple rows per store, so this is a many-to-many join and every transaction row is duplicated once per redemption at that store. With the notebook's own data, store 42 has 10 transactions and 8 redemptions, so
reconciledcontains 10 x 8 = 80 rows for store 42. Theby_storeaggregate that the story presents as the final, correct deliverable therefore reports:total_revenue= the real revenue multiplied by 8 (each amount summed once per redemption);tx_count= 80 instead of 10 (F.count("tx_id")counts joined rows, not transactions).The narrative then celebrates the result ("ok this should work... YOU ARE A LIFESAVER"), so a reader walks away having learned to hand finance numbers inflated by fan-out - which is exactly the class of bug the notebook is trying to teach people to avoid.
The same fan-out breaks Solution 1 (the loyalty attachment rate). After the same join, for any store with at least one redemption every joined row has a non-null
redemption_id, sois 80/80 = 1.00 for store 42 (the intended answer is 8/10 = 0.8). The metric degenerates to exactly 1.0-or-0.0 for every store, while the surrounding text says "the counts work out correctly".
Suggested fix
Join on the real relationship key - with this data,
["store_id", "customer_id"]- or keep the store-level join but aggregate the two tables separately before joining:(Aggregating before joining also fixes the attachment rate:
redemption_count / tx_countbecomes 8/10.)This could even be turned into a teaching moment: run the naive store-only join first, show the inflated numbers, and let the reader catch the fan-out - it is a better interview lesson than the type-mismatch bug the notebook already covers.