Articles
Azure Synapse Interview Questions: SQL Pools, Data Movement, and Distribution Skew
Practice Azure Synapse dedicated SQL pool joins: diagnose data movement, distribution skew, and replicated-table trade-offs with an original warehouse case.
A slow Azure Synapse join is not automatically a bad SQL expression. In a dedicated SQL pool, the expensive work may be moving rows between distributions, or waiting for one distribution that holds a disproportionate share of a hot key. A strong interview answer first identifies the pool, reads the distributed plan, and only then chooses a table design change. The warehouse and numbers below are an original practice case, not a report of an employer's question or a measured Synapse benchmark. Microsoft's MPP architecture guide is the product source for the engine concepts.
For an adjacent warm-up, try Choose Components for a Data Warehouse. Here, the specific task is to explain why a join is slow and show the evidence that would justify your next experiment.
First ask which SQL pool is in scope
Azure Synapse uses the phrase “SQL pool” for different query modes. Dedicated SQL pools store relational tables in a distributed MPP architecture and expose design choices such as hash, round-robin, and replicated table distribution. Serverless SQL pools query data in a data lake with a different physical model. If the interviewer says only “Synapse is slow,” clarify whether the workload is dedicated or serverless before recommending a distribution key. The rest of this exercise explicitly assumes a dedicated SQL pool. A distribution-key answer to a serverless external-data query would be a category error.
The case has three synthetic tables. Sales has 120 million rows and a customer_id join column. Customer has one million rows with one row per customer. Region has 200 rows and is joined through region_id. The principal report groups revenue by region and week, with a selective week filter. The sizes are invented to make the design choices discussable; they are not performance thresholds. On dedicated pools, Microsoft documents 60 distributions and three table distribution options. Use “60” for the real architecture; any four-bucket sketch on a whiteboard is only a teaching simplification.
| Table | Initial hypothetical design | Why it might hurt |
|---|---|---|
Sales | Round-robin | A large join on customer_id may need row movement. |
Customer | Hash on customer_id | Potentially aligned with a revised fact table, but validate uniqueness and skew. |
Region | Round-robin | A tiny dimension may still be moved during joins. |
The first question is not “Which hint fixes this?” It is “Which step dominates, and how is it distributed across nodes?” Microsoft's troubleshooting guidance points to request-step views such as sys.dm_pdw_request_steps and distribution-level statistics. In an interview, name the plan evidence you would collect and the hypothesis it tests. You do not need to claim you ran these views in this invented case.
Explain data movement with a concrete join
If Sales is round-robin and Customer is hash distributed on customer_id, rows with the same customer are not necessarily on the same distribution. A join may require the Data Movement Service to shuffle one side so matching keys meet. A query can look selective in SQL and still pay for moving a large intermediate result. Microsoft's distribution design guide explains how hash distribution can reduce movement for joins on the aligned key. It does not mean that every hash choice is automatically better.
For this report, a candidate could propose a test table Sales_hash hash distributed on customer_id and compare the same query with the same filters, statistics, and service level. If the large Sales–Customer join is the expensive movement step, co-distribution may reduce that movement. The candidate should also ask whether other important queries join Sales by order_id or aggregate by product_id. A key that helps one workload can worsen another. The answer is an experiment tied to the workload, not a universal rule that facts always hash on customers.
The 200-row Region table is a different choice. Microsoft's replicated-table guidance describes copying a small table to each compute node so joins need less movement. Replication can be sensible for a stable small dimension, but it consumes storage and has maintenance costs when the table changes or resources scale. Say why its read benefit is likely relevant to this query, then mention that write and cache behavior need checking. Do not call replication free merely because the table is small.
Now add a filter: the report asks for one week. Does the filter apply before a large shuffle, or after the join has already expanded an intermediate result? Inspect the actual plan and row counts. It is tempting to say “move the WHERE clause earlier,” but a modern optimizer may already push it down; syntax order alone is not the diagnosis. The stronger response is to compare request steps and check whether the filtered fact size matches expectations.
A hash key can remove movement and still create skew
Change the case: 40% of Sales rows have customer_id = 'guest'. If you hash on that column, all rows with the same key land in one distribution. The rest can spread widely, but the hot distribution remains overloaded. The “40%” is a deliberately extreme teaching fixture, not a Synapse observation. The likely symptom is one long-running distribution or step while peer distributions finish. A candidate should ask for per-distribution row counts and request-step duration before claiming skew.
The trade-off is subtle. Hashing Sales on customer_id might eliminate a large shuffle for ordinary customers, yet the guest group may dominate scan, join, or aggregation time. A candidate who says “hash both tables and it is fixed” has solved only the movement hypothesis. A candidate who says “use round-robin instead” may recreate movement. The right next experiment may isolate the hot-key workload, change how anonymous customers are modeled, or choose a different distribution strategy after examining all major queries. The application meaning of guest matters: are these truly one customer, or a placeholder for unrelated people? You should not salt or split the key without checking join correctness.
| Observation from a real plan | Plausible explanation | Next check before a fix |
|---|---|---|
Large ShuffleMoveOperation or equivalent movement step | Join inputs are not co-located | Identify moved relation, join key, and filtered row count. |
| Most distributions finish; one stays busy | Hot key or uneven row sizes | Compare rows and bytes by distribution; inspect top keys. |
Many movement steps after joining Region | Small dimension is repeatedly copied or moved | Test replication and compare write/cache costs. |
| No dominant movement, but scan remains slow | Storage, statistics, predicates, or resource contention | Inspect estimates, data layout, and concurrent workload. |
These are hypotheses, not diagnoses based solely on one symptom. “Equivalent movement step” matters because operation names and plans can vary with query shape. In a live system, collect request ID, elapsed time, rows moved, distribution skew, and the baseline result before changing a production table. If statistics are stale, a design experiment can be misleading. Document the exact query and service level so you compare like with like.
How would you answer the interviewer in two minutes?
Start: “I would confirm this is a dedicated SQL pool, because distribution choices are specific to that engine.” Then state the expected join shape: a large fact, a customer dimension, and a tiny region dimension. Next, inspect the request steps and distribution counts to decide whether time is spent moving rows, scanning, or waiting on one skewed distribution. Only after that propose a controlled change: hash the large fact on the frequent join key if the key is well distributed, and consider replication for the small dimension. Close with a validation criterion: the same query returns the same answer, less data moves, elapsed time improves across representative predicates, and ingest/write costs remain acceptable.
If the interviewer asks, “Why not hash on customer_id immediately?” answer with the guest case. Equal keys must meet for the join, but equal keys also concentrate work. Ask for the cardinality and frequency distribution, and mention competing queries. If the interviewer asks, “Why not replicate Customer too?” say that one million rows may or may not be appropriate depending on table size, change frequency, workload, and pool resources. The row count alone is not enough; do not invent a threshold. If asked about Region, replication is a candidate because it is small and read-heavy in this fixture, but you still benchmark it.
An interviewer may present a SQL text with a join order that looks wrong. Avoid assuming the textual order is the execution order. A distributed optimizer can transform the plan. Use measured request-step information. Similarly, do not declare an index the primary fix without seeing the physical design and scan behavior. In MPP systems, data placement often dominates a large join; this is a testable statement about this query shape, not a law for all Synapse workloads.
What does a fair validation look like?
A good validation has at least four parts. First, correctness: row counts and grouped sums match a known baseline, including guest records and null keys. Second, movement: the plan shows fewer bytes or rows moved in the target join step. Third, timing: repeat the query enough to avoid treating a single warm or cold run as truth, with the same compute configuration and data snapshot. Fourth, operational cost: measure load time, table maintenance, and other representative queries. A narrow speedup that hurts the daily ingest and three other reports may not be a win.
Also ask whether skew is about row count or work per row. One distribution can have fewer rows but a costly string transformation or a large joined result. Partition-level counts help, yet elapsed step time and actual bytes make the story stronger. If a sampled top-key analysis shows 40% guest, the architecture conversation should include data modeling and product semantics. Is a placeholder being used for missing customer identities? Should those records join to a common dimension row? The data answer determines which physical workaround preserves correctness.
The final interview answer should be conditional: “If request steps show a large shuffle on customer_id and no severe hot key, I would test aligned hash distribution. If one distribution dominates because of guest, I would investigate the model and a hot-key strategy before locking that key in. I would benchmark replication for the truly small dimension.” That is more convincing than memorizing a table-design slogan because it names evidence that could prove you wrong.