Google Professional Data Engineer Question 193
Select 2Google Cloud PlatformYou are a Data Engineer troubleshooting a poorly performing query in BigQuery. The query joins two large tables with billions of rows, and the execution time is significantly higher than expected. Which actions should you take to optimize the query?
- A
Review and optimize the join condition to ensure it uses indexed fields or fields with high cardinality.
- B
Use partitioning and clustering on the tables to limit the amount of data scanned during the query.
- C
Enable caching in BigQuery to store intermediate results and reduce computation time.
- D
Use the EXPLAIN statement to analyze the query execution plan and identify bottlenecks.
- E
Break the query into smaller subqueries to simplify execution and improve performance.
Show answer and explanation
Correct answers: B, D
Explanation
In BigQuery, optimizing poorly performing queries often involves reducing the amount of data processed and ensuring efficient execution strategies. Partitioning and clustering reduce data scans, which can improve performance for large datasets. Additionally, using the EXPLAIN statement helps identify inefficiencies in the query execution plan, enabling targeted optimizations.
- A. Incorrect.
BigQuery does not use traditional indexing. While ensuring join conditions use fields with high cardinality is important, BigQuery's performance is not directly improved by indexing fields.
- B. Correct.
Partitioning and clustering help reduce the amount of data scanned during query execution, which can significantly improve performance for large datasets.
- C. Incorrect.
BigQuery does not support manual caching for query optimization. Temporary result caching happens automatically, but it is not a direct optimization strategy for poor-performing queries.
- D. Correct.
The EXPLAIN statement provides valuable insights into the query's execution plan, helping identify areas that require optimization, such as inefficient joins or unnecessary data scans.
- E. Incorrect.
Breaking queries into smaller subqueries may add unnecessary complexity and does not directly improve performance. Query optimization focuses more on reducing data processed and improving execution strategies.