Free Preparation Discussions
MENU
Google Professional Data Engineer Exam Questions
Free Google Professional Data Engineer Exam Actual Questions
Note: Premium Questions for Professional Data Engineer were last updated On Jul. 29, 2026 (see below)
Your company uses Looker Studio connected to BigQuery for reporting. Users are experiencing slow dashboard load times due to complex queries on a large table. The queries involve aggregations and filtering on several columns. You need to optimize query performance to decrease the dashboard load times. What should you do?
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)
You need to set access to BigQuery for different departments within your company. Your solution should comply with the following requirements:
Each department should have access only to their data.
Each department will have one or more leads who need to be able to create and update tables and provide them to their team.
Each department has data analysts who need to be able to query but not modify data.
How should you set access to the data in BigQuery?
You maintain ETL pipelines. You notice that a streaming pipeline running on Dataflow is taking a long time to process incoming data, which causes output delays. You also noticed that the pipeline graph was automatically optimized by Dataflow and merged into one step. You want to identify where the potential bottleneck is occurring. What should you do?
When Dataflow fuses multiple transformations into a single stage (step), it can make it harder to pinpoint which specific part of that fused stage is causing a bottleneck because internal metrics for individual ParDos within the fused stage might not be as distinct.
Reshuffle Operation (Option D):Inserting a Reshuffle (or GroupByKey followed by ungrouping, which forces a shuffle) operation between logical processing steps in your Beam pipeline prevents Dataflow from fusing those steps. A shuffle operation acts as a barrier to fusion. This materializes the intermediate PCollection and forces data to be redistributed across workers.
Benefit for Debugging:By breaking the fusion, the Dataflow monitoring UI will display distinct steps for the operations before and after the Reshuffle. This allows you to observe metrics like processing time, throughput, and watermarks for each now-separated step, making it much easier to identify which part of your original fused logic is the bottleneck.
Let's analyze why other options are less effective for this specific problem of afused step:
A (Verify service account permissions):While important for overall pipeline health, permission issues usually result in outright failures or errors in logs, not typically a slowdown within a successfully running (albeit slow) fused step.
B (Insert output sinks):Adding actual output sinks (like writing to Pub/Sub or GCS) after each key step would also break fusion and allow you to measure throughput. However, it's a more heavyweight approach than Reshuffle. It introduces I/O overhead and requires setting up and managing these temporary sinks. Reshuffle is a lighter-weight way to achieve the same goal of breaking fusion for diagnostic purposes within the pipeline itself.
C (Log debug information):Logging can be helpful, but if the entire fused step is slow, logs might not easily distinguish which internal operation is the culprit without very careful and verbose logging. Analyzing potentially massive volumes of logs for performance bottlenecks can be less direct than observing stage metrics in the Dataflow UI once fusion is broken.
Using Reshuffle is a standard technique recommended by Google Cloud for debugging performance issues in fused Dataflow stages.
Google Cloud Documentation: Dataflow > Troubleshooting Dataflow pipelines > Common Dataflow errors and troubleshooting steps > Pipeline is slow or stuck. 'Break transform fusion: Certain transforms in your pipeline might be fused together into a single stage for optimization. If a particular fused stage is causing a bottleneck, you can temporarily add Reshuffle transforms between the fused transforms to break them into smaller, separate stages. This allows you to get more visibility into the performance of each individual transform and isolate the bottleneck.'
Apache Beam Documentation: Programming Guide > Pipeline I/O > Reshuffle.'Reshuffle can be used to prevent fusion, and ensure that data is materialized and redistributed.' (While the primary purpose of Reshuffle is often related to data distribution and freshness, a side effect and common use case is to break fusion for monitoring and debugging).
You are designing a data processing pipeline. The pipeline must be able to scale automatically as load increases. Messages must be processed at least once, and must be ordered within windows of 1 hour. How should you design the solution?
You work for a large real estate firm and are preparing 6 TB of home sales data lo be used for machine learning You will use SOL to transform the data and use BigQuery ML lo create a machine learning model. You plan to use the model for predictions against a raw dataset that has not been transformed. How should you set up your workflow in order to prevent skew at prediction time?
https://cloud.google.com/bigquery-ml/docs/bigqueryml-transform Using the TRANSFORM clause, you can specify all preprocessing during model creation. The preprocessing is automatically applied during the prediction and evaluation phases of machine learning
- Select Question Types you want
- Set your Desired Pass Percentage
- Allocate Time (Hours : Minutes)
- Create Multiple Practice tests with Limited Questions
- Customer Support
Deborah Smith
3 days agoKenneth Stewart
24 days agoFrank Lopez
29 days agoTimothy Thompson
1 month agoHarold Gonzalez
2 months agoThomas Smith
2 months agoBrian Sanchez
2 months agoHarold Brown
3 months agoFrank Walker
3 months agoJoseph Johnson
3 months agoSteven Green
3 months agoKimberly Garcia
3 months agoGeorge Moore
2 months agoDonald Peterson
2 months agoCarlene
4 months agoHoa
4 months agoIdella
4 months agoIdella
4 months agoClemencia
5 months agoDiane
5 months agoMelvin
5 months agoGregoria
5 months agoDiane
6 months agoMerlyn
6 months agoSharen
6 months agoLeota
6 months agoTrinidad
7 months agoLacresha
7 months agoTimmy
7 months agoLashaun
7 months agoOllie
8 months agoEdison
8 months agoTawna
8 months agoCoral
8 months agoBrendan
9 months agoRicarda
9 months agoVirgie
9 months agoAnnmarie
9 months agoGolda
10 months agoFranchesca
10 months agoElliott
10 months agoBreana
10 months agoKing
11 months agoCarma
11 months agoJustine
1 year agoLoise
1 year agoStanton
1 year agoFrederica
1 year agoMaia
1 year agoCarolann
1 year agoWinfred
1 year agoTennie
1 year agoJoye
1 year agoSarina
1 year agoOctavio
1 year agoHermila
1 year agoCordelia
1 year agoStanton
2 years agoDetra
2 years agoMaynard
2 years agoDeangelo
2 years agoChristene
2 years agoGilma
2 years agoGwenn
2 years agoRonald
2 years agoShawn
2 years agoDonte
2 years agoAntonette
2 years agoSon
2 years agoDouglass
2 years agoAliza
2 years agoJavier
2 years agoShannon
2 years agoTheron
2 years agoKristofer
2 years agoLauna
2 years agoDerick
2 years agoVerdell
2 years agoFreida
2 years agoVesta
2 years agoLashaunda
2 years agoLon
2 years agoEric
2 years agoErasmo
2 years agoDierdre
2 years agoZack
2 years agosaqib
2 years agoanderson
2 years ago