How do you choose a value for spark.sql.shuffle.partitions on a large job?

## What the setting controls `spark.sql.shuffle.partitions` is the number of output partitions of every exchange in a SQL or DataFrame plan: joins, aggregations, window operations, explicit repartitions by expression. You can see it baked into the physical plan as the second argument of the `Exchange hashpartitioning(...)` node. Its default is **200**, and that number has nothing to do with your data — the same 200 applies to a 10 MB join and a 10 TB one. It is the most consequential single default in Spark, which is why the question is asked so often. One trap worth stating up front: the RDD API does not read this key at all. `reduceByKey` and friends fall back to `spark.default.parallelism` (or an explicitly passed partition count). Mixing the two APIs and tuning only one is a common source of "my setting did nothing". ## Sizing from data, not from habit The usable rule has two constraints that must both hold. **Constraint 1 — bytes per partition.** Take the stage's shuffle-write size from a previous run (the stage detail page reports it) and divide by a target per-partition size. A few hundred megabytes per post-shuffle partition is a workable target band: large enough that per-task overhead is negligible, small enough to sort within a task's slice of execution memory. Spark's own adaptive machinery uses a target on this order for the partitions it coalesces, which is a reasonable anchor. If a stage shuffles 2 TB and you want roughly 256 MB partitions, you need on the order of 8,000 partitions — 40× the default. **Constraint 2 — cores.** The count should be at least the total number of executor cores, or the cluster idles; making it a small multiple (two to four waves) helps the scheduler smooth over uneven task durations, since a straggler at the end of a single wave leaves everything else waiting. When the two constraints disagree — a small dataset on a large cluster — parallelism wins for latency but do not chase it past the point where tasks do less work than they cost to schedule. ## What goes wrong when it is too low Each task must sort and hold a huge partition. Symptoms, in order of appearance: heavy spill in the stage metrics, then task durations in the tens of minutes, then executors killed for exceeding memory, then fetch failures as those executors take their shuffle files with them. Leaving 200 on a multi-terabyte join is the single most common cause of the "my Spark job dies on big data but works on the sample" report. ## What goes wrong when it is too high The cost is not symmetric but it is real. Every map task writes a block for every reduce partition, so blocks in the shuffle scale as map tasks × partitions; at very high counts the reduce side spends its time on per-block fetch overhead rather than on bytes, and fetch wait time balloons while bytes read stay modest. The driver's map-output bookkeeping grows with the same product. And if the stage's output is written directly to storage, you get one small file per partition — the small-file problem, paid for later by every reader. ## Where adaptive execution fits Adaptive query execution, enabled by default since Spark 3.2 via `spark.sql.adaptive.enabled`, measures actual shuffle-write statistics at runtime and can coalesce an over-provisioned partition count down to a sensible one before the reduce stage runs. That changes the risk calculus: setting the number too high is now partially self-correcting, while setting it too low is not — nothing can split a partition you never created (skew handling aside). So with adaptive execution on, the practical advice is to set the count generously and let coalescing trim it, rather than to hand-tune a single perfect number. ## Per-job rather than global One cluster-wide value cannot suit every job, and even inside one job different stages shuffle wildly different volumes. Set it per job from that job's own data volume — `spark.conf.set` at runtime works between actions — and treat any platform-wide default as a starting point for the median job, not a governance decision. ## What interviewers are checking That you know the default is 200 and why that is arbitrary; that you can produce a sizing method involving both data volume and core count; that you can name the failure mode at each end; and that you do not answer "just set it to 2000" without saying what the number came from.