Prepare and test your skills

Prepare and test your skills

Free sample question, answered and explained below. The Professional Data Engineer practice bank has 1,353 more like it. Try 10 free: instant score, no account.
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?
BigQuery slot reservations allocate dedicated computational units (CPU and RAM) to queries under capacity-based pricing. Slot starvation occurs when concurrent queries compete for a limited pool of slots, causing query execution stages to wait in a queued or pending state.
The query execution graph showed high stage wait duration or query-level queue times across many balanced worker slots due to compute quota exhaustion.
Data locality dictates that BigQuery datasets referenced in a query or federated operation must reside in the same region or multi-region to avoid latency penalties and data transfer restrictions.
The failure was related to federated queries spanning mismatched geographic locations, such as attempting to join a European dataset with an external Spanner instance in the US.
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.
NULL 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.
BigQuery search indexes are specialized data structures designed to optimize queries that evaluate unstructured text or semi-structured JSON using functions like SEARCH(). They accelerate point lookups and needle-in-a-haystack text matching.
JOIN ... ON a.id = b.id). Standard join processing utilizes distributed hash join algorithms and internal columnar Capacitor structures.The workload consisted of ad-hoc text search queries scanning large unstructured string columns with text search predicates.
Answer them yourself and get your score the moment you finish. No account needed.
Full access includes
Keep the momentum going with these hand-picked practice scenarios
Last updated Validated