Home Microsoft Exams Google Cloud Exams

Loading page

Curious about new developments and updates? Follow our social media.

Get a free certification question every week.

Microsoft Exams

AZ-104 Administrator
AZ-204 Developer
AZ-900 Fundamentals
All Microsoft Exams

Google Cloud Exams

Associate Data Practitioner
All Google Cloud Exams

Terms Privacy Cookies About Pricing

© 2026 StudyCerts. All rights reserved.

StudyCerts is an independent service and is not affiliated with, endorsed by, or sponsored by any certification body or cloud provider.

Loading page

Home
Google Cloud Exams
Professional Cloud Data Engineer
BigQuery features for business intelligence (e.g., BI Engine, materialized views)
A business intelligence team uses Looker Studio to query a 6…

Professional Cloud Data Engineer

professional-cloud-data-engineer

Advanced

Prepare and test your skills

Description
Sample Question
Free Trial
Practice Test

Advanced

Prepare and test your skills

Description
Sample Question
Free Trial
Practice Test

Optimize Bi Engine Reservation Materialized Views

Section: BigQuery features for business intelligence (e.g., BI Engine, materialized views)

Worked example. The correct answer is already marked and every option is explained below, so there is nothing to select here. To answer questions yourself, start the free trial.

...

...

...

...

...

Explanations

...

View source documentation

Related questions

Keep the momentum going with these hand-picked practice scenarios

AdvancedProfessional Cloud Data Engineer

A retail company uses Looker to provide interactive executive dashboards over a large BigQuery dataset that receives continuous real-time transaction updates. Business analysts frequently explore the data by applying diverse, dynamic filters and aggregations across various combinations of store locations, product categories, and transaction timestamps. The engineering team must meet the following requirements: Deliver sub-second query response times for interactive, ad-hoc dashboard exploration without requiring fixed pre-aggregated views. Ensure query results reflect incoming real-time streaming data with zero manual pipeline maintenance. Minimize query execution slot utilization during peak dashboard usage. Which acceleration strategy should the data engineering team implement?

BigQuery features for business intelligence (e.g., BI Engine, materialized views)

AdvancedProfessional Cloud Data Engineer

A global enterprise is designing an event-driven data processing pipeline to ingest high-throughput telemetry streams from Apache Kafka, apply windowed aggregations, and perform machine learning inference before sinking the results into analytical storage. The enterprise has strict data portability and multi-cloud governance policies requiring that: Pipeline business logic and processing code must remain fully portable to on-premises clusters (such as Apache Flink or Apache Spark) or alternative cloud providers without refactoring the pipeline code. The architecture must support a unified programming model for both streaming and batch execution. The execution engine on Google Cloud must be fully managed, horizontally scalable, and natively support open-source execution semantics. Which framework and architecture should the organization implement?

Designing for data and application portability (e.g., multi-cloud and data residency requirements)

AdvancedProfessional Cloud Data Engineer

A connected vehicle platform ingests real-time telemetry from over 500,000 vehicles streaming diagnostic metrics at 1-second intervals. The data engineering team needs to design a high-throughput storage tier in Cloud Bigtable that satisfies the following requirements: Eliminate write hotspots across cluster tablets during peak driving hours. Support low-latency row range scans to retrieve historical telemetry for a specific vehicle over a designated time window. Maintain an instance configuration optimized specifically for query latency under high write volumes. Which row key pattern and cluster target should you implement?

Choosing managed services (e.g., BigQuery, BigLake, AlloyDB, Bigtable, Spanner, Cloud SQL, Cloud Storage, Firestore, Memorystore)

AdvancedProfessional Cloud Data Engineer

Your enterprise data team is architecting a multi-step data processing pipeline on Google Cloud that orchestrates dependencies between long-running BigQuery batch transformation jobs and interactive analytical workloads. The design must satisfy the following technical and operational requirements: Orchestrate complex task dependencies, retries, and data transformations across hybrid services using Python. Prevent worker resource starvation and task timeouts while waiting for long-running BigQuery batch jobs to complete. Ensure end-to-end idempotency so that automated task retries or scheduled pipeline re-executions do not result in duplicate records or inconsistent states. Automatically capture and publish end-to-end data lineage to Dataplex Universal Catalog without requiring custom reporting code. Which orchestration and pipeline architecture should you implement?

Interactive or batch query jobs

AdvancedProfessional Cloud Data Engineer

An e-commerce company is designing an event-driven inventory management pipeline on Google Cloud. The system ingests high-volume inventory mutation events from physical stores and online checkouts through Pub/Sub and processes them using Cloud Dataflow before updating product inventory levels in BigQuery and downstream datastores. The system must meet the following reliability and data integrity requirements: Guarantee inventory count accuracy and consistency despite potential out-of-order and duplicate message delivery caused by transient network retries and at-least-once Pub/Sub delivery. Prevent invalid or malformed messages from stalling the streaming pipeline while preserving them for debugging and compensating corrections. Allow safe recovery and message reprocessing in the event of subscriber-side processing bugs. Which architectural pattern should the data engineering team implement?

Making decisions related to ACID (atomicity, consistency, isolation, and durability) compliance and availability

FundamentalAssociate Data Practitioner

Looker vs Looker Studio Semantic Layer

Compare Looker and Looker Studio for different analytics use cases

Want more questions like this?

Get a free certification question every week.

Last updated August 27, 2026

Are you ready for the Professional Cloud Data Engineer?

50 questions in the full practice test

More BigQuery features for business intelligence (e.g., BI Engine, materialized views) practice questions

Get a free certification question with a full explanation every week.

Professional Cloud Data Engineer

Provider: Google Cloud
Passing score: 70%
Questions: 50
Duration: 90 minutes

Other sections in this exam

Designing data processing systems
Ingesting and processing the data
Storing the data
Preparing and using data for analysis
Maintaining and automating data workloads

Ask Scout

Still curious? Scout, our AI tutor, can explain this concept further and answer your follow-up questions.

A business intelligence team uses Looker Studio to query a 600 GB partitioned and clustered sales dataset stored in BigQuery. Users report that interactive dashboard filters on recent summary metrics (last 30 days, totaling ~20 GB) perform with sub-second response times, but ad-hoc multi-year queries spanning the full 600 GB dataset experience significant latency due to queries partially falling back to standard BigQuery slot execution.

You have a 30 GB BigQuery BI Engine reservation provisioned in the project. You need to ensure consistent, sub-second query performance across both dashboard filters and broader historical aggregation queries while minimizing BI Engine reservation costs.

What should you do?

Export the dataset to Cloud Bigtable and query it from Looker Studio using BigQuery federated external tables

Create partitioned materialized views that pre-aggregate historical sales metrics, allowing BI Engine to accelerate queries via smart tuning within the 30 GB memory reservation

Increase the BI Engine reservation capacity to 600 GB to fit the entire uncompressed sales dataset in memory

Disable BI Engine reservation and configure BigQuery scheduled queries to write hourly results to separate destination tables

Export the dataset to Cloud Bigtable and query it from Looker Studio using BigQuery federated external tables

Create partitioned materialized views that pre-aggregate historical sales metrics, allowing BI Engine to accelerate queries via smart tuning within the 30 GB memory reservation

What BigQuery BI Engine and Materialized Views are

BigQuery BI Engine is a fully managed, in-memory analysis service that accelerates SQL queries by caching frequently queried data in memory and using a vectorized execution engine. When paired with Materialized Views, BI Engine automatically accelerates pre-aggregated summaries without requiring massive memory allocations.

How it meets the requirements

Sub-second latency across interactive and historical queries: Creating partitioned materialized views shrinks large historical tables into compact, pre-aggregated datasets. When users query historical aggregates, BigQuery's optimizer uses smart tuning to reroute queries to the materialized view.
Optimized BI Engine memory sizing: Because the aggregated materialized views are drastically smaller than the raw 600 GB base table, they easily fit into the existing 30 GB BI Engine reservation, eliminating fallbacks to standard execution slots.
Cost-effective: Avoids scaling up in-memory reservations to cover hundreds of gigabytes of raw historical records.

Key advantages

Transparent query rewriting: Analysts can query the base table directly; BigQuery automatically redirects queries to the materialized view if it yields better performance.
Vectorized in-memory execution: Accelerates aggregation and filter operations directly in RAM.
Automatic incremental sync: Materialized views automatically maintain consistency as new data arrives in the base table.
Cost optimization: Minimizes RAM provisioning costs while maximizing interactive dashboard throughput.

Increase the BI Engine reservation capacity to 600 GB to fit the entire uncompressed sales dataset in memory

Disable BI Engine reservation and configure BigQuery scheduled queries to write hourly results to separate destination tables