Correct Logic, Frozen

Here is the failure this question is built to catch. After writing a correct query and checking the result out loud, the candidate hears the tail and stops. What follows is one of 3 things: silence, a recital about Spark being distributed and lazy that never touches the query, or a jump to remedies such as adding executors or caching before any operation has been named. Each leaves the interviewer with the same note: knows SQL, cannot read a plan. The fix is a frame you can produce for any transform in 4 sentences, built from the test in the last section.

3 shapes of the freeze

The third row is the one to fear, because it adds an error to a correct query. A filter on 10 TB reads 10 TB, and that read is the scan's cost, which exists for every query over the table. It moves nothing between partitions. Naming it as the expensive part tells the interviewer you cannot tell a scan from a shuffle, and the rest of the tail will probe that until it is confirmed.

The 4-sentence frame

Here is the frame filled in for the running example, as you would say it. Read it for rhythm: every sentence names an operation and a column, and nothing in it is a remedy.

The filter on status is narrow and stays in the scan's stage. The first wide operation is the groupBy, a hash exchange on category, and each task pre-sums before it. The orderBy on revenue adds a range exchange, so the plan has 2 shuffles and 3 stages. In the Stages tab I would read Shuffle Write for stage 1, which should be small because each task sends one partial total for each category it saw, never the orders themselves.

When the frame gets a follow-up

The interviewer will push on 1 of the 4 sentences, and the push stays inside the plan you described, so the frame has already told you where you are. 'What if there are 2 billion completed orders?' pushes on the second sentence: the partial aggregate still caps each task's output at one total for each category, so the exchange stays small, and the scan is what grows. 'What if 1 category is 90% of orders?' pushes on the same exchange: the final sum for that category lands on a single reducer, and the partial sums keep it to a row per task, so the aggregate survives it, where a join on that column would send every one of those rows to a single task. 'What if we drop the ORDER BY?' pushes on the third sentence: 1 shuffle, 2 stages.

The frozen answer

• starts with a remedy
• speaks about Spark in general
• names no column
• cannot say how many stages

The plan-reading answer

• starts with the first wide operation
• speaks about this query
• names the key of every exchange
• gives a shuffle count and a stage count

One more freeze shows up in the join variant: the candidate writes a correct join and then declares every join a shuffle. Apply the size test from the last section in place of the reflex, and if you can't know the dimension's size, say that the plan depends on it and give the count for each case.

To practice the frame, take any query you have written this week and, with the plan covered, say the 4 sentences before running explain(). Then compare: every Exchange hashpartitioning or Exchange rangepartitioning in the output should match a wide operation you named, and nothing you named as narrow should appear beside one. After a week of this, the first wide operation in a new query shows up as you read it.

About this lesson

This section is part of the SQL at Scale 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.