Volunteer Each Blind Spot

Concepts covered: Catalyst Optimizer

An interviewer probing a recital is searching for the edge of what you know, asking 'and when does that go wrong?' after each layer until the answers run out. Volunteering each layer's blind spot before that question arrives changes the conversation. It shows you have watched the layer fail on real data, and it moves the interviewer from testing recall to discussing a decision, which is the conversation they came to have.

Each layer and the thing it cannot see

The cost model's 3 assumptions

Stale statistics

Statistics are a snapshot from the last ANALYZE TABLE. A dimension that has grown from 6MB to 900MB since then still reads as 6MB, so the planner broadcasts it. Spark falls back to file sizes for a table that was never analyzed, but once a size is stored the planner uses it, and automatic size updates (spark.sql.statistics.size.autoUpdate.enabled) are off by default. The direction of the error decides the damage: an overestimate costs a shuffle you didn't need, while an underestimate can broadcast a table that does not fit in memory.

Uniform values

Without histograms, equality on a column is estimated as 1 over its distinct count, the same rule that put purchases at 4% when they were 0.4%. The rule misses the other way for a common value: a filter on page views is also estimated at 4% when it keeps most of the table, and a plan sized for 80MB meets well over a gigabyte.

Independent predicates

For country = 'JP' AND language = 'ja', the cost model multiplies the 2 selectivities. If each matches 3% of 50 million users, the estimate is 0.09%, or 45,000 rows. The columns describe nearly the same fact, so the real answer sits close to 3%, about 1.5 million rows: a 33 times underestimate, in the direction that ends in a memory error.

Adaptive execution's own blind spots

What AQE measures, and what that leaves out

• No demotion of a static broadcast. AQE promotes sort-merge joins to broadcast; it has no rule that turns a broadcast the static plan already chose back into a shuffle, so a broadcast picked from stale statistics runs as planned.
• No skew fix for aggregations. Skew handling splits oversized partitions of a sort-merge or shuffled hash join. A groupBy on a hot key still sends that key to a single task. Partial aggregation helps only when the partial result is small, as with a count or sum; collect_list still ships every value.
• Bytes stand in for work. Partition coalescing and the runtime broadcast decision both read serialized, compressed shuffle sizes. Data that compresses 10 to 1, or rows that a later UDF or explode expands, look cheap to AQE and cost far more to process.
• The skew split has a price. Cutting a skewed partition into N pieces means the other side's matching partition is read N times. When both sides are skewed on the same partition, the pieces multiply.
• No new shuffle allowed. If splitting a skewed partition would force an extra exchange later in the plan, AQE skips the fix unless spark.sql.adaptive.forceOptimizeSkewedJoin is set.

The skew rule fires when a partition passes 2 tests at once: larger than 5 times the median partition (spark.sql.adaptive.skewJoin.skewedPartitionFactor) and larger than 256MB (spark.sql.adaptive.skewJoin.skewedPartitionThresholdInBytes). Take a join whose median reduce partition is 200MB and whose hot partition is 12GB. The rule cuts the hot side into pieces near the 64MB advisory size, up to about 192 of them, bounded by how many map tasks wrote into it. Each piece is joined against the whole matching partition from the other side, so a 300MB partition there is read around 192 times: roughly 57GB of shuffle reads to fix a 12GB hot spot. The trade usually wins because those reads run in parallel across the cluster, and it explains why a join stage's read total can dwarf what was written to it.

How to volunteer one

The form that works is a sentence per blind spot, carrying the mechanism, the symptom and the place you would confirm it. Keep to the layers the question touched. Reciting every weakness unprompted reads as a prepared speech, while naming the one that bites this query reads as a diagnosis.

The phrasing does the work of 3 follow-up questions. The mechanism answers 'why', the symptom answers 'what would you see', and the UI location answers 'how would you know'. An interviewer who gets all 3 unasked has nothing left to probe on that layer and moves to the next one, or to the fix.

Do

• Pair each blind spot with the UI metric that would confirm it
• Say which way the estimate errs and what that direction costs
• Name the config that changes the behaviour, with its default

Avoid

• List every optimizer weakness before the question reaches that layer
• Call a documented design limit a bug
• Claim AQE fixes skew without saying which operators it covers

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.