GCP Professional Data Engineer Practice Question

Your analytics team has built a materialized view named mv_sales that pre-aggregates SUM(revenue) grouped by sale_date and region_id from the retail.sales_fact table. A Looker Studio dashboard issues the following SQL:

SELECT region_id, SUM(revenue) AS revenue
FROM `retail.sales_fact`
WHERE sale_date BETWEEN '2024-01-01' AND '2024-01-31'
GROUP BY region_id;

Under which condition will BigQuery automatically rewrite this query to read from mv_sales even though the dashboard does not mention the view?

Only if the dashboard explicitly replaces retail.sales_fact with mv_sales in the FROM clause.
Only if mv_sales is stored in the same dataset as sales_fact and both tables are clustered on sale_date and region_id.
When the session parameter ENABLE_MATERIALIZED_VIEW_REWRITE is set to TRUE; otherwise BigQuery never rewrites queries automatically.
When the materialized view holds the same aggregate functions and a superset of the query's GROUP BY columns, and the query's filters are compatible with those used to build the view.

Report Issue

Answer Description

BigQuery's optimizer rewrites a query to use a materialized view when it can prove that the view's result completely satisfies the query. Because mv_sales contains the same aggregate function (SUM(revenue)) and a superset of the grouping columns (sale_date, region_id), the optimizer can derive the requested result by further aggregating the materialized view (dropping sale_date) and applying the narrower date filter. No explicit reference to the view, special session flag, or identical clustering is required. The rewrite fails if the query requests columns or aggregates that are not present in the view, or if the query explicitly disables materialized-view usage.

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.