GCP Professional Data Engineer Practice Question

A Looker Studio dashboard executes the same query dozens of times per hour: it left-joins a 2-billion-row sales fact table with three small dimension tables, filters on a date range, then aggregates by date and marketing channel. Each refresh currently takes about 25 seconds. The SQL behind the widgets cannot be modified, and you want to speed up the dashboard without scheduling additional batch jobs. Which approach best meets these requirements?

Schedule an hourly job that writes a new denormalized table containing the joined and aggregated data, then update the dashboard to query that table.
Purchase 100 GB of BI Engine capacity so the dashboard queries are cached in memory while leaving the current schema unchanged.
Enable automatic re-clustering on all four tables using the join keys so the existing join executes faster during every dashboard refresh.
Create a materialized view that joins the fact table with the three dimensions, applies the date filter and aggregation, and rely on BigQuery's automatic query rewrite so the dashboard transparently reads from the materialized view.

Report Issue

Answer Description

A BigQuery materialized view can include equality joins to small dimension tables, filters, and aggregations. Because it stores the pre-computed result and is incrementally refreshed, subsequent dashboard queries are served from in-memory cache rather than re-scanning the fact table and performing joins. When the materialized view satisfies a query, the BigQuery optimizer automatically rewrites the incoming SQL, so no changes are needed in Looker Studio. Simply clustering or re-sharding tables may reduce scan costs but still leaves the join and aggregation work for every refresh, and BI Engine alone does not remove that processing or guarantee sufficient cache for a multibillion-row join. Building and maintaining a separate denormalized table would require the very ETL you want to avoid. Therefore, creating and relying on a materialized view that pre-joins, filters, and aggregates the data is the most effective solution.

Ask Bash

Bash is our AI bot, trained to help you pass your exam. AI Generated Content may display inaccurate information, always double-check anything important.