Mechanics to Cost, With a Number
Concepts covered: Shuffle Optimization
An interviewer asking for a number wants an estimate built from defaults you can defend, with the arithmetic visible so they can correct an input instead of rejecting the answer. Three settings supply most of those numbers, and all 3 are defaults you can quote without a cluster in front of you.
The 3 defaults that size a job
| Setting | Default | What it sizes | Cost when it is wrong |
|---|---|---|---|
| spark.sql.files.maxPartitionBytes | 128MB | Tasks in each file scan | Too small: thousands of short tasks and scheduling overhead. Too large: fewer, longer tasks and more memory per task |
| spark.sql.shuffle.partitions | 200 | Tasks in every stage after a shuffle | Too few: gigabytes per task and spill. Too many: millions of tiny shuffle blocks |
| spark.sql.autoBroadcastJoinThreshold | 10MB | Whether a join shuffles at all | Below it: the small side is copied to every executor. Above it: both sides shuffle and sort |
Each default converts a mechanism into a count. File size over 128MB gives the scan's tasks. The shuffle default is the number of partitions Spark plans after every shuffle, whatever the volume. With adaptive execution enabled, the default since Spark 3.2, Spark can merge small partitions down from that number once the map stage has run, but coalescing never splits a large one: a 2GB reducer for a window or an aggregate stays 2GB. That makes 200 the default to question first on a large job. The broadcast threshold decides what is often the largest single cost in a join: whether the big side moves at all.
One job, worked end to end
Change the March job so it numbers each rider's trips in pickup order, a window partitioned by rider_id. A window gets no partial aggregation, so every row that survives the scan crosses the shuffle. The cluster runs 50 executors with 4 cores each, and a sample run shows about 400GB of Shuffle Write on the map stage.
ranked = trips.selectExpr(
"*",
"row_number() over (partition by rider_id order by pickup_ts) as trip_seq",
)| Quantity | Arithmetic | Result |
|---|---|---|
| Scan tasks | 500GB / 128MB | 4000 |
| Task slots | 50 executors x 4 cores | 200 |
| Scan waves | 4000 / 200 | 20 |
| Data per reducer at 200 | 400GB / 200 | 2GB |
| Data per reducer at 2000 | 400GB / 2000 | about 200MB |
| Shuffle blocks at 2000 | 4000 x 2000 | 8,000,000 |
| Average block at 2000 | 400GB / 8,000,000 | about 52KB |
Read the table as 2 problems trading against each other. At the default, each reducer sorts 2GB for the window, which will likely spill to disk unless every task has well over 2GB of execution memory. Raising spark.sql.shuffle.partitions to 2000 brings each reducer near 200MB, a size that usually sorts in memory, but multiplies the shuffle into 8 million blocks averaging 52KB, and many small remote reads move fewer bytes per second than a few large ones. A common target is 100MB to 200MB per shuffle partition, and the interviewer wants to hear that both ends of the dial cost something.
The join decision outweighs the rest
Go back to the original March job and its 6MB cities table. Whether that join broadcasts is the first number to state and the first to verify, because the 2 strategies move data at different scales. A broadcast costs driver memory to collect the small side, then a 6MB copy to each of the 50 executors. A sort-merge join shuffles both inputs and sorts both, and the large side dominates: every completed trip from the 500GB scan is hashed on city_id and crosses the network, hundreds of GB against the broadcast's megabytes.
- 300MB
- 0 bytes
Set those 2 figures beside the window job's 400GB shuffle: a sort-merge join on the March trips would add a shuffle on that scale, while the broadcast stays in megabytes. That sets the order of the answer: confirm the join strategy, then size the shuffle partitions, then think about scan waves.
joined = trips.join(cities.hint("broadcast"), "city_id")
joined.explain()About this lesson
This section is part of the The Architecture Recital 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.