Google Professional Data Engineer Exam Questions

Service accounts authenticate applications running on your virtual machine instances to other Google Cloud Platform services. For example, if you write an application that reads and writes files on Google Cloud Storage, it must first authenticate to the Google Cloud Storage API. At a minimum, service accounts used with Cloud Dataproc need permissions to read and write to Google Cloud Storage, and to write to Google Cloud Logging.

By default, prefer field promotion. Field promotion avoids hotspotting in almost all cases, and it tends to make it easier to design a row key that facilitates queries.

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.

'In BigQuery, materialized views are pre-computed views that cache a query's results, enhancing performance and efficiency... They periodically refresh to capture changes from the underlying base tables, allowing BigQuery to read only the updated data. Materialized views improve query performance by storing precomputed results, which reduces the need to process raw data repeatedly. This caching mechanism speeds up retrieval times, especially for complex queries.' (3Source: Optimizing Query Performance with BigQuery Materialized Views)

'Smart tuning: BigQuery automatically rewrites queries to use materialized views whenever possible. Automatic rewriting improves query performance and reduces costs without changing query results.' (Source: Use materialized views)