Google Professional Data Engineer Question 192
Select 3Google Cloud PlatformYou are a Data Engineer at a retail company using BigQuery to analyze sales data. Analysts report that a query joining two large tables is taking significantly longer to execute than expected. Upon investigation, you notice that the query is using multiple JOIN operations and filtering on a non-indexed column. What steps can you take to improve query performance?
- A
Use denormalized tables to reduce the need for multiple JOIN operations.
- B
Apply partitioning and clustering to the tables based on frequently filtered columns.
- C
Add LIMIT to the query to fetch only a subset of results.
- D
Ensure that the query uses SELECT * to retrieve all columns for better flexibility.
- E
Check for and eliminate cross joins that may result in a Cartesian product.
Show answer and explanation
Correct answers: A, B, E
Explanation
To troubleshoot and improve the performance of poorly performing queries in BigQuery, it is essential to optimize the data schema, query structure, and execution plan. Denormalizing tables can reduce the complexity introduced by multiple JOINs. Partitioning and clustering help BigQuery scan only the relevant data, reducing processing time. Additionally, avoiding cross joins prevents the query from generating overly large intermediate results. These optimizations collectively enhance query performance.
- A. Correct.
Denormalized tables can help reduce the overhead caused by multiple JOIN operations, improving query performance.
- B. Correct.
Partitioning and clustering optimize data layout, which speeds up filtering and querying, especially when filtering on frequently used columns.
- C. Incorrect.
Adding LIMIT reduces the number of rows returned but does not improve the underlying query execution performance.
- D. Incorrect.
Using SELECT * retrieves unnecessary columns, which can increase query processing time and costs.
- E. Correct.
Cross joins result in a Cartesian product, which can drastically increase the size of intermediate results and slow down query execution.