Google Professional Data Engineer Question 182
Select 2Google Cloud PlatformYour organization processes large amounts of data daily using Google BigQuery. Some queries are consistently run with complex calculations on raw data, resulting in slow query performance and increased costs. How can you optimize performance and reduce costs while maintaining data accuracy?
- A
Use BigQuery's scheduled queries to precalculate and store derived fields in a new table.
- B
Implement a caching layer outside of BigQuery to store query results for reuse.
- C
Create a materialized view in BigQuery with the precalculated fields.
- D
Use BigQuery's partitioning and clustering features to optimize query performance instead of precalculating fields.
- E
Precalculate fields during data ingestion and store them in the source system before loading into BigQuery.
Show answer and explanation
Correct answers: A, C
Explanation
To optimize performance and reduce costs in BigQuery when dealing with repeated complex calculations, precalculating fields is a best practice. This can be achieved by using scheduled queries to store derived fields in new tables or by creating materialized views. Both solutions reduce the need for repetitive calculations, leading to faster query performance and cost savings. While other options like partitioning and clustering are helpful for optimization, they do not directly address the issue of repetitive calculations.
- A. Correct.
Using BigQuery's scheduled queries to precalculate and store derived fields in a new table is an effective way to optimize query performance and reduce costs. This eliminates the need to perform complex calculations repeatedly.
- B. Incorrect.
While a caching layer can improve performance, it is not specific to BigQuery and does not address the problem of repeated complex calculations within BigQuery.
- C. Correct.
Creating a materialized view with precalculated fields is a valid solution in BigQuery. It allows for optimized queries by precomputing results and storing them for fast retrieval.
- D. Incorrect.
Partitioning and clustering can improve query performance, but they do not replace the need to precalculate fields for repeated complex calculations. These techniques address data organization rather than computation.
- E. Incorrect.
Precalculating fields during data ingestion in the source system is not always feasible due to the need for real-time or raw data processing in BigQuery. This also moves computation away from BigQuery, which may not be ideal.