Skew Diagnosis with Percentiles: Max vs Median, Not Max vs Mean

The straggler poisons the average it is hiding in

Level 6 compared a slow task to the stage average and flagged that this was an imperfect proxy. This lesson is that caveat, promoted to the whole point.

Say a stage runs seven tasks: six finish in about 400 seconds and one takes 2,400. The mean is about 690 seconds, so the straggler looks like it took 3.5 times the average. That understates it badly, because the straggler is one of the numbers being averaged. It drags the mean up toward itself and then hides behind the number it moved.

The median is immune to that. Six tasks at roughly 400 seconds and one at 2,400 gives a median of 400, and 2,400 over 400 is a clean 6x. That ratio, max task time over median task time, is the signal every Spark interview corpus cites verbatim, and it is what the Spark UI's summary-metrics row is showing you when it prints min, 25th, median, 75th, and max side by side.

Table

Same seven tasks, three ways of asking the same question. Only the median-based ratio reports the skew at its true size.

what you compare	the number it gives	why it misleads
max over mean	3.5x	the straggler is inside the mean, pulling it up
max over median	6x	nothing: the median ignores the outlier entirely
max minus mean	1,710s	an absolute number with no scale to judge it against

Computing a median in SQL that has no median function

SQLite has no PERCENTILE_CONT, and that limitation is useful, because the technique that works without it works everywhere:

1. Number the rows within each group, ordered by the value: ROW_NUMBER() OVER (PARTITION BY stage_id ORDER BY duration_ms).
2. Count the rows in the same group: COUNT(*) OVER (PARTITION BY stage_id).
3. Keep the row whose number is the middle one: WHERE rn = (n + 1) / 2.

With 7 rows, (7 + 1) / 2 is 4, the fourth-smallest; with 11 rows it is 6. Integer division does the work, and because the rank comes out of the row count rather than out of your head, the same query is correct for a stage with 5 tasks and a stage with 500. The same shape gives you any percentile: the 75th is rn = (3 * n + 3) / 4, which is the nearest-rank definition of a percentile written with integers.

AQE's rule, with its real numbers

Adaptive Query Execution will split a skewed shuffle partition automatically, but only when both conditions hold: the partition is more than 5 times the median partition, and it is larger than 256 MB. Those are two settings with names worth knowing, spark.sql.adaptive.skewJoin.skewedPartitionFactor (default 5) and spark.sql.adaptive.skewJoin.skewedPartitionThresholdInBytes (default 256 MB). Both matter. A partition 8 times the median but only 40 MB is not worth splitting, so AQE leaves it, and if your job is still slow you are looking at the wrong stage.

Be careful which ratio you are applying. The signal you report to a human is max task TIME over median task time, because time is what the Spark UI puts in front of you and time is what the person asking cares about. The rule AQE acts on is about SIZE: the factor of 5 and the 256 MB floor are both measured in bytes, against the median partition size. Usually the two agree, since a partition that holds five times the data takes roughly five times as long. When they disagree, the size numbers are the ones that decide whether the split happens, and a task that ran long because it spilled is the usual reason they disagree: spilling makes time grow faster than bytes.

The fix ladder

In the order an interviewer wants to hear it:

1. Let AQE split it. Free, already on by default since Spark 3.2 and Glue 4.0, and it is the modern first answer. Confirm it is enabled before you do anything cleverer.
2. Broadcast the small side. Only if the small side fits the band from the module opener: under 10 MB automatically, up to about 100 MB by hint. This removes the shuffle entirely, so the hot key stops being a problem instead of being split.
3. Salt the hot key. Append a small random suffix to the skewed key on the big side, replicate the small side across every salt value, join on the salted key, then aggregate away the salt. It works on any engine and needs no runtime magic, which is why it is still the answer when the first two do not apply.

spill_mb corroborates. A straggler that also spilled was not merely unlucky in scheduling; it genuinely held more data than its share of memory could take, which is the fingerprint of a hot key rather than a slow machine.

Common mistake: treating one slow task as skew without checking the others. If every task in the stage is slow, the stage is just big, and the answer is more parallelism or less data, not salting. Skew is a distribution claim, so it needs a distribution to back it.

Interview nuance: "one task took 40 minutes, the median was 2 minutes, diagnose it" is the detection half of the Amazon EMR question. The expected answer names the ratio first (20x, so this is skew, not size), then the corroborating evidence (spill, and the partition's row count), then the ladder in order. Candidates who jump straight to salting without measuring first lose the point.

On a real platform this differs. The Spark UI hands you the percentiles precomputed in the stage summary-metrics table, so nobody writes this query against a live cluster. You write it against exported history, against Glue job metrics in CloudWatch, or against a query-history table in a warehouse, where the same ROW_NUMBER trick is the only way to get a median out. And in a warehouse, skew shows up as one distribution slice doing all the work rather than one task, which you find with the same ratio over a different table.