Prepare and test your skills

Prepare and test your skills

Free sample question, answered and explained below. The Professional Data Engineer practice bank has 1,353 more like it. Try 10 free: instant score, no account.
Your organization uses a Looker dashboard connected to BigQuery to analyze sales performance. The dashboard displays the current month's data and relies on a query that joins a massive sales_fact table with multiple smaller dimension tables.
Users are reporting high dashboard latency, and the repeated execution of these complex joins is driving up analytical costs. You need to optimize the data layout and leverage BigQuery BI Engine to minimize query latency and reduce costs.
What should you do?
This approach correctly utilizes a materialized view to pre-join the data and partitions it to optimize memory usage. However, it only adds the materialized view itself to the BI Engine preferred tables list.
This configuration will fail to accelerate the dashboard queries. BigQuery BI Engine has a strict limitation regarding materialized views: queries against materialized views are only accelerated if both the materialized view and all of its underlying base tables are included in the preferred tables list.
BI Engine automatically inherited or resolved base table dependencies for materialized views, but its current architecture requires explicit inclusion of all referenced tables.
BigQuery BI Engine is a fast, in-memory analysis service that accelerates SQL queries by intelligently caching frequently used data. Materialized views are precomputed views that periodically cache the results of a query, such as complex joins or aggregations.
BI Engine works best with pre-joined or pre-aggregated data. Using a materialized view to flatten the data structure is the recommended best practice for optimizing performance. Furthermore, BigQuery requires that both the materialized view and its base tables be designated as preferred tables for the acceleration to occur, making this the only technically correct configuration.
BigLake tables allow you to query data stored in external object storage (such as Cloud Storage) using open-source formats like Apache Iceberg, while applying BigQuery's fine-grained access controls.
BigQuery BI Engine does not support external tables, which includes BigLake tables. If the data is exported to an external BigLake Iceberg table, BI Engine will completely bypass it, and the dashboard queries will not benefit from any in-memory acceleration.
The organization required interoperability with open-source compute engines (like Apache Spark) on a single copy of data stored in Cloud Storage, and in-memory dashboard acceleration was not a requirement.
sales_fact table by the sales date. Configure a BI Engine reservation and add the logical view to the preferred tables list.A logical view (or standard view) is a virtual table defined by a SQL query. When a query references a logical view, BigQuery executes the view's underlying SQL statement at runtime.
This approach fails on two fronts. First, BI Engine preferred tables do not support views; you cannot add a logical view to the preferred tables reservation list. Second, because logical views do not precompute or store data, the complex joins between the massive fact table and dimension tables would still be executed on the fly every time the dashboard is loaded. This would fail to reduce the analytical costs associated with the query.
The requirement was simply to abstract the complexity of the join logic for end-users without needing to optimize for cost or in-memory acceleration.
Answer them yourself and get your score the moment you finish. No account needed.
Full access includes
Keep the momentum going with these hand-picked practice scenarios
Last updated Validated