professional-cloud-data-engineer
Prepare and test your skills
Prepare and test your skills
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.
Keep the momentum going with these hand-picked practice scenarios
Want more questions like this?
Get a free certification question every week.
Last updated
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):
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 cross-region network latency during remote table scanning; optimize the query by migrating all referenced datasets to a multi-region location.
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 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 cross-region network latency during remote table scanning; optimize the query by migrating all referenced datasets to a multi-region location.
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.
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.
StudyCerts
StudyCertsNULL or generic placeholder keys before the join prevents millions of irrelevant matching attempts on a single worker.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.
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.