43. Capstone 3 — flatten a nested clickstream

The events clickstream is nested: a user struct, an items array, an optional payment struct. Analysts want it flat — one tidy row per item, ready to group and join. This is Module 6 end to end.

The brief

Turn events into one row per item with these flat columns: event_id, action, user_id (from user.id), country (from user.country), sku, and qty (from each exploded item).

The two moves

1. Explode the items array so each item gets its own row.
2. Dot into the user struct to lift id and country up to the top level, aliasing them cleanly.

Sketch:

events.select(
    "event_id",
    "action",
    F.col("user.id").alias("user_id"),
    F.explode("items").alias("item"),
).select("event_id", "action", "user_id", "item.sku", "item.qty").show()

Note events with an empty items array drop out (that's explode). On a real cluster explode_outer would keep them with null item fields — here, one item-less event disappears, leaving 10 item rows from 12 events.

Your turn

Produce the flat, one-row-per-item table with columns event_id, action, user_id, country, sku, qty — aliasing sku and qty cleanly. Assign to result.

result = events  # <- select event_id, action, user.id AS user_id, user.country AS country,
                 #    explode(items) AS item; then pull sku & qty out of item
result.show()

result = events.select(
    "event_id",
    "action",
    F.col("user.id").alias("user_id"),
    F.col("user.country").alias("country"),
    F.explode("items").alias("item"),
).select(
    "event_id",
    "action",
    "user_id",
    "country",
    F.col("item.sku").alias("sku"),
    F.col("item.qty").alias("qty"),
)