The Transform With a Tail
Most SQL interview questions end when the query is right. This one keeps going. Once you've written the aggregate or join the interviewer asked for, the conversation turns to the machine: the table is too large for 1 node, so Spark executes the statement across a cluster, and the interviewer wants to hear what that execution looks like. The tail is the point of the exercise. A query that returns the right rows on a laptop and the same right rows across 2,000 cores is the same query, so the first half tests whether you can write it and the second half tests whether you know what Spark turns it into.
2 halves of the same question
The first half is ordinary SQL correctness: the right filter, the right grouping key, the right join condition, the right order. The second half asks you to read the same statement as a plan. Each clause becomes an operator, some operators keep rows where they are, and some move rows between partitions. The operators that move rows are where the job spends most of its time and memory, since every moved row crosses the network. An interviewer who hears you name those operators, say which column they move rows by, and count how many times rows move, has heard the answer. Everything else in this lesson is practice at saying those 3 things about any transform.
- The SQL half checks
- the filter drops the right rows
- the grouping key matches the question
- the join condition and join type are right
- the ordering is the one asked for
- The scale half checks
- you name each operation that moves rows
- you name the column it moves them by
- you count the shuffles and the stages
- you name what you would open in the Spark UI
The tail wears disguises
The follow-up rarely says the word shuffle. It usually arrives as a question about size or time. Sometimes it is about memory, or about what you would do if the job were slow. Each phrasing is the same request: describe the plan. Read the left column of the table below as the interviewer might say it, and the right column as what they're asking you to produce.
| How the tail is phrased | What it is asking for |
|---|---|
| This table is 10 TB. Does your query still work? | Which operations move rows, and where the plan breaks into stages |
| What happens in the cluster when you hit run? | The stages in order, with the shuffle between each pair |
| Where would this be slow? | The wide operation that moves the most rows |
| Which part of this needs the most memory? | The stage that holds the most rows on a single task: the join or the aggregate |
| How many jobs and stages is this? | The stage count, shuffles plus 1; the Jobs tab can list several jobs for 1 action, such as the sampling job an orderBy runs first |
| What would you look at in the Spark UI? | The SQL tab for the plan, the Stages tab for shuffle sizes and task times |
The model the tail assumes
Spark reads a table as partitions, runs a task per partition, and chains together every operation that can finish on a partition without seeing any other. Filters qualify, and so do column expressions and projections, so all of them run inside the same task as the scan. The chain breaks at an operation whose output for a key needs rows from every input partition: a grouped sum, a sort, a join between 2 large tables, a distinct. There Spark writes each task's rows to local disk keyed by the hash or range of the key, the next stage's tasks fetch their keys over the network, and work resumes. That write and fetch is the shuffle, and the boundary it creates is the stage boundary. A plan with 2 shuffles has 3 stages. A stage starts once every stage whose shuffle output it reads has finished, so the stages of a single chain run in order, while independent stages, such as the scans feeding the 2 sides of a join, can run at the same time. A stage finishes when its slowest task does.
- 200
- 10 MB
At this depth the question asks for correctness in both halves. The interviewer doesn't expect a tuning plan, a partition count argument or a cost estimate in dollars. They expect the query to be right and the description of the plan to be right: the right operations named as wide, the right columns named as keys, the right count of stages, and a named place in the UI to confirm it. A candidate who gets the query right and then says 'Spark would distribute it' has answered half. A candidate who gets the query right and misnames the filter as a shuffle has answered half and added an error. The sections that follow build the full second half for a running example and then a variation with a join.
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.