Google Professional Data Engineer
Get started today
Ultimate access to all questions.
As a data engineer, you are currently utilizing a SQL-based tool for visualizing data stored in Google BigQuery. The data visualizations necessitate the use of outer joins and analytic functions, and it is crucial that these visualizations are based on data that is no older than 4 hours. However, business users have reported that the visualizations take too long to generate. Your objective is to enhance the performance of these visualization queries while also minimizing the maintenance overhead associated with the data preparation pipeline. What steps should you take to achieve this?
As a data engineer, you are currently utilizing a SQL-based tool for visualizing data stored in Google BigQuery. The data visualizations necessitate the use of outer joins and analytic functions, and it is crucial that these visualizations are based on data that is no older than 4 hours. However, business users have reported that the visualizations take too long to generate. Your objective is to enhance the performance of these visualization queries while also minimizing the maintenance overhead associated with the data preparation pipeline. What steps should you take to achieve this?
Explanation:
Option A is the correct answer because creating materialized views with the allow_non_incremental_definition option set to true can significantly improve the performance of complex queries, such as those requiring outer joins and analytic functions. By specifying the max_staleness parameter to 4 hours and the enable_refresh parameter to true, the materialized views will ensure that the data is no less than 4 hours old and is periodically refreshed. This approach minimizes the maintenance overhead while providing faster query performance, which addresses the complaint about slow visualizations.