Google Professional-Data-Engineer Exam With Confidence Using Practice Dumps

The scenario describes slow performance caused by complex queries with aggregations and filtering on a large table. The best way to optimize this type of workload in BigQuery for dashboarding is to pre-compute the needed data.

Materialized Views (MVs) are pre-computed views that cache the results of a query, including aggregations and filters. When a dashboard's query matches the MV's query (or a part of it), BigQuery can use the cached results, which is much faster than running the original complex query against the large raw table, directly improving dashboard load times. They are designed to improve performance and reduce costs for repeating, complex queries.

Correcting other options:

A (Shorter Refresh Interval): This would make the problem worse by triggering the slow, complex queries more frequently.

C (Row-Level Security): This is a security measure, not primarily a performance optimization. While it might slightly reduce the data scanned per user if the table is partitioned on the access column, it doesn't fundamentally speed up the complex aggregation and filtering logic which is the core problem.

D (BigQuery BI Engine): BI Engine is an in-memory analysis service for BigQuery that accelerates many SQL queries, and it is a good general option for BI. However, creating a Materialized View specifically pre-calculates the exact aggregations and filters needed for the slow dashboard, which provides a more targeted and often more dramatic performance improvement for known, complex, and recurring queries than a general-purpose caching service. The combination of MVs and BI Engine is a best practice, but the MV is the most targeted fix for pre-calculating the complex aggregations.

Reference