The Before and After

Concepts covered: Broadcast Join

Two measurements of one job

A before-and-after compares the same job on comparable input, read from the same place each time. A before from a run over 60 days of data against an after over 90 says nothing, and an interviewer who asks 'were those the same input?' will catch it. Priya took 7 nightly runs on each side, every one rebuilding 90 days, and quoted the median.

The spread matters as much as the median. Before runs ranged from 88 to 103 minutes and after runs from 19 to 24, so the slowest run after the fix still beat the fastest run before it by 64 minutes. When the 2 ranges do not overlap, you can say 'every run after the change beat every run before it', and nightly variation cannot explain the result. Quoting 103 against 19 would have claimed a bigger win than the job delivered, and an interviewer who asks about variance would find it.

Runtime is the headline, and the other rows explain it. Interviewers trust a runtime claim when the rows under it move the way the mechanism predicts: fewer exchanges, less shuffle written, no spill.

The old plan, on a small sample

The animation runs the old job's shape on 12 order lines and 4 stores, each table read as 3 partitions. It groups by region alone to keep the board small; the real job grouped by region and day. With broadcasting off, Spark must bring each order line to the same task as its store, so both tables move. Watch both inputs get shuffled by store_id, then a third shuffle as groupBy brings each region to a single task.

spark.conf.set("spark.sql.autoBroadcastJoinThreshold", -1)

revenue = (
    orders.join(stores, "store_id")
    .groupBy("region")
    .agg(F.sum("amount").alias("revenue"))
)
revenue.collect()

That run has 3 shuffle exchanges and 4 stages: a stage to scan each table, a stage to join and pre-sum, and a stage to finish the sums. At full size, the orders exchange is the one that carried the 140 GB in the table's Shuffle write row, and the 61 GB of spill came from sorting it. The last section walks through how.

The new plan, same sample

Here is the same sample with the broadcast hint. Spark collects stores on the driver and sends a copy to every executor, so each orders task looks up its stores locally and joins inside the scan. Watch the order rows stay on their tasks until the groupBy.

revenue = (
    orders.join(F.broadcast(stores), "store_id")
    .groupBy("region")
    .agg(F.sum("amount").alias("revenue"))
)
revenue.collect()

One shuffle exchange and 2 stages remain. The shuffle left over carries partial sums, a row per region and day from each scan task, about 1 MB for the whole run. The broadcast has a price too: 3 MB sent to 40 executors is roughly 120 MB of network traffic per run, against 140 GB before.

Show the numbers did not change

A faster job that writes different numbers is a regression, and a correct answer says you checked. Swapping the join strategy should not change results, but 'should' is a claim and a query is evidence. For a week, Priya wrote the new job's output to a second table, store_revenue_candidate, and compared it with the old one, region by region and day by day.

SELECT
  COALESCE(o.region, n.region) AS region,
  COALESCE(o.order_date, n.order_date) AS order_date,
  o.revenue AS old_revenue,
  n.revenue AS new_revenue
FROM store_revenue AS o
FULL OUTER JOIN store_revenue_candidate AS n
  ON o.region = n.region AND o.order_date = n.order_date
WHERE o.revenue IS DISTINCT FROM n.revenue

The FULL OUTER JOIN keeps rows that exist in only one table, and IS DISTINCT FROM treats a NULL against a value as a difference, so a region missing from either version shows up as well as a changed sum.

Know what a failing result looks like before you need one. Had the candidate job lost one region on one night and summed another differently, the query would have returned rows like these:

A NULL on one side means the row exists in only one version, which points at a filter or a join that dropped data. Two values that differ point at the aggregation or at rows counted twice. A difference of a few cents on many rows usually means a column type changed, such as DECIMAL becoming DOUBLE.

Do

• Quote each metric with the Spark UI tab it came from
• Compare several runs on the same input size
• Name the shuffle that remains after the fix
• Show the output matched with a reconciliation query

Avoid

• Pit one lucky run against one bad run
• Give a ratio like 4.5x with no minutes behind it
• Claim savings you never measured
• Skip spill when the slow stage was spilling

About this lesson

This section is part of the The War Story lesson on DataDriven, the home of data engineering. Lessons cover SQL, Python, Spark, data modeling and pipeline architecture, and each section pairs its explanation with worked examples and practice you run as you go.