A data engineer is troubleshooting a slow-running SQL query in BigQuery that feeds an executive Looker Studio dashboard. When analyzing the Query Execution Graph and stage performance metrics in the Google Cloud console, the engineer observes the following behaviors in Stage S04 (a hash JOIN operation): The Max slot time (4 minutes 12 seconds) is substantially higher than the Average slot time (14 seconds). A single worker processed 85% of the total read records in the stage, while all other worker slots finished almost immediately. The stage produced an unusually high volume of Bytes shuffled to remote distributed memory. What is the primary root cause of this query performance bottleneck, and what is the optimal optimization strategy to resolve it?

The bottleneck is caused by data skew on the join key; optimize the query by filtering out or isolating skewed keys (such as default or NULL values) and clustering or partitioning the tables on frequently filtered join keys.: ### What Data Skew in BigQuery Execution Plans Is **Data skew** occurs when data is disproportionately distributed across join or aggregation keys. In distributed query processing engines like **BigQuery**, operations such as hash joins partition data across worker **slots** based on hash values of the join key. When a single key value (e.g., `NULL`, placeholder values like `'UNKNOWN'`, or default IDs) represents a massive portion of the dataset, all rows sharing that key are routed to a single slot. This creates a severe processing imbalance where one slot becomes overloaded while the remaining slots sit idle. ### How This Explains the Observed Metrics - **Slot Time Disparity (Max vs. Average):** A significant discrepancy between the **Max slot time** and **Average slot time** within an execution stage is the classic hallmark of data skew. The overloaded slot spends excessive time computing matches and materializing records, driving up max slot execution time while average slot execution remains low. - **Worker Record Distribution:** The metric showing a single parallel worker processing 85% of total stage records confirms that the execution stage suffers from uneven repartitioning and data distribution. - **Bytes Shuffled:** When skewed join keys are redistributed across the remote distributed memory and petabit network, excessive data is repeatedly transferred and buffered across execution stages. ### Key Advantages of the Mitigation Strategy - **Isolating Skewed Keys:** Filtering out unneeded `NULL` or generic placeholder keys before the join prevents millions of irrelevant matching attempts on a single worker. - **Table Clustering and Partitioning:** Organizing underlying tables with **clustering** colocates rows with matching join and filter column values in storage blocks, significantly pruning scanned bytes and accelerating distributed aggregations. - **Balanced Slot Utilization:** Evenly distributes compute work across the available BigQuery slot allocation, reducing total query wall-clock execution time. ### Why This Is Optimal Addressing the unbalanced data distribution directly targets the root cause identified in the execution graph. Increasing slot reservations or modifying global timeout settings would merely waste compute capacity without resolving the underlying structural bottleneck.

Bigquery Execution Plan Data Skew Troubleshooting | professional-cloud-data-engineer

Bigquery Execution Plan Data Skew Troubleshooting

Worked example. The correct answer is already marked and every option is explained below, so there is nothing to select here. To answer questions yourself, start the free trial.

A data engineer is troubleshooting a slow-running SQL query in BigQuery that feeds an executive Looker Studio dashboard. When analyzing the Query Execution Graph and stage performance metrics in the Google Cloud console, the engineer observes the following behaviors in Stage S04 (a hash JOIN operation):

The Max slot time (4 minutes 12 seconds) is substantially higher than the Average slot time (14 seconds).
A single worker processed 85% of the total read records in the stage, while all other worker slots finished almost immediately.
The stage produced an unusually high volume of Bytes shuffled to remote distributed memory.

What is the primary root cause of this query performance bottleneck, and what is the optimal optimization strategy to resolve it?

The bottleneck is caused by insufficient reserved slot capacity in the reservation pool; optimize the query by purchasing an on-demand baseline slot allocation increase to prevent slot starvation.

The bottleneck is caused by a missing search index on the dimension table; optimize the query by creating a text search index on the join columns to eliminate table scans.

The bottleneck is caused by data skew on the join key; optimize the query by filtering out or isolating skewed keys (such as default or NULL values) and clustering or partitioning the tables on frequently filtered join keys.

The bottleneck is caused by cross-region network latency during remote table scanning; optimize the query by migrating all referenced datasets to a multi-region location.

Professional Cloud Data Engineer

Bigquery Execution Plan Data Skew Troubleshooting

Related questions