A window partitioned by tenant_id pins one node at 100% while others idle — what is happening?

## Why the imbalance exists An MPP engine distributes window partitions by hashing the `PARTITION BY` key and assigning hash buckets to workers. That gives balance only if the key's values are roughly uniform in *row count*. Real business keys rarely are: one tenant is ten thousand times bigger than the median, one product SKU dominates the catalogue, one device id belongs to a test harness that emits continuously. Hashing spreads *distinct values* evenly; it does nothing about the rows behind each value. The crucial asymmetry with aggregation is that a window partition is indivisible. A skewed `GROUP BY` can often be salted — compute partial aggregates on `(key, salt)` in parallel, then combine — because aggregation is associative. A window function generally cannot be salted, because it emits one row per input row and its result depends on the ordering of the *entire* partition. Splitting the hot tenant across two workers would produce two independent sequences, not one. ## The straggler effect Once one worker owns a partition many times larger than average, three things compound. It sorts more rows than anyone else, at superlinear cost. It exceeds its memory budget and spills to local disk, adding write plus read-back I/O. And because the window is a pipeline breaker, downstream operators cannot proceed until the last partition is done, so the stage's wall-clock time is set by the worst worker while the rest of the cluster idles. Cost is billed on the whole cluster for the duration, so a skewed window is expensive in credits as well as in latency. NULL keys deserve a special mention: in most engines NULL hashes to a single bucket, so a column that is NULL for a large fraction of rows creates one gigantic partition. The same happens with sentinel values such as `-1`, `0` or `'unknown'` inserted by an upstream job. ## Confirming the diagnosis Two checks settle it quickly. First, look at the data: group by the partition key, count rows, order descending, and inspect the top values plus the count of NULLs. If the top key holds a large multiple of the median, that is the answer. Second, look at the runtime profile: compare rows processed, peak memory and spill bytes across workers for the window stage. Skew shows as one worker with a far higher row count and nonzero spill while peers are near zero. Rule out the alternatives — a genuinely undersized cluster shows *all* workers busy; a hardware straggler shows an imbalance that does not correlate with a data key and moves between runs. ## What actually helps **Shrink the input first.** Filters and pre-aggregation applied before the window reduce the hot partition proportionally. Rows discarded after the window were still shuffled, sorted and possibly spilled. **Refine the partition key** where the business logic permits. Changing `PARTITION BY tenant_id` to `PARTITION BY tenant_id, event_date` turns one enormous partition into many small ones and usually reflects what the report meant anyway (a running total that resets daily). This is the single most effective fix when it is semantically legal, and the question to ask the analyst is precisely whether the sequence is supposed to span all history. **Isolate the hot keys.** Run the query for the handful of giant tenants separately, possibly with a bigger cluster or more memory, and `UNION ALL` the results with the run over the long tail. Ugly but effective when the key list is stable and small. **Change the operator.** If the window is only being used to pick a winner per key — a top-1 or top-N — an aggregate formulation is partially reducible and does not need the whole partition on one node. That converts a skew problem into a normal aggregation problem. **Give the hot worker room.** Raising the per-operator memory budget or moving to nodes with more memory removes the spill even if it does not remove the imbalance. This is the fix that costs money rather than thought, and it is the right one when the skew is inherent and the query is infrequent. **Do not** reach for a larger cluster as the first move. Adding workers gives the hot partition no extra help; it just adds idle machines to the bill. The only sizing change that helps a skewed window is a *bigger* worker, not more of them. ## The design-level answer If a family of reports partitions by a naturally skewed key, the durable fix belongs upstream: keep the giant tenants in separate physical tables or in their own schedule, agree on a coarser grain for the window, or precompute the metric per tenant per day so no single query ever holds one tenant's full history in one operator.