How to speed up repeated BigQuery queries on petabyte tables?
Short answer
D Create a materialized view based off of the query being run.
Question
Key Takeaways
The question tests matching workload requirements — repeated queries, filtering, simple aggregations, freshness — to BigQuery's materialized views, and the trap is confusing them with cached results or scheduled queries.
This Professional Data Engineer question tests BigQuery materialized views, which precompute and periodically cache filtered and aggregated results of a base query to accelerate repeated workloads. Community consensus is unanimous (100% votes) that creating a materialized view is the correct answer.
Common mistake: Choosing A (scheduled query) because it seems to precompute data at intervals; however, scheduled queries create static tables that go stale between runs and lack automatic refresh and smart query routing, whereas materialized views incrementally refresh and are automatically used by the query engine.
Expert Analysis
Why the Answer Is Correct
A BigQuery materialized view is a precomputed view that periodically caches the results of a query for increased performance and efficiency, exactly matching the need to query a petabyte table multiple times a day. Materialized views support filtering (WHERE clauses) and simple aggregations such as SUM, COUNT, MIN, MAX, and AVG, which aligns with providing simple aggregations to downstream users. They are automatically and incrementally refreshed when the base table changes, so insights stay up to date without manual scheduling. BigQuery also uses smart routing to transparently rewrite queries against the base table to use the materialized view when possible, delivering faster results.
Why the Other Options Are Wrong
Option A (scheduled query) only copies data at fixed intervals, so results become stale between runs and it does not automatically accelerate downstream queries. Option B (cached query results) applies only to identical repeated queries within about 24 hours and is invalidated whenever the underlying table changes, so it cannot serve filtered aggregations to downstream users. Option C (limiting columns) reduces bytes scanned but does not precompute aggregations or provide a maintained, filtered dataset for consumers. None of these options combine precomputation, filtering, aggregation, and freshness the way materialized views do.
Community Comment Notes
The most-liked comment (7 likes) explicitly quotes Google's definition that materialized views are precomputed views that periodically cache query results for increased performance and efficiency, reinforcing option D. Additional comments simply affirm 'Option D. Materialized view' with no dissent, and the vote distribution is 100% for D. There is no community disagreement, making this a high-confidence question.
Official Reference
https://cloud.google.com/bigquery/docs/materialized-views-intro
This link opens the official vendor documentation in a new tab.
Exam Strategy
On the PDE exam, map workload keywords to features: 'repeated queries + simple aggregations + up-to-date' points to materialized views, while 'fixed intervals' suggests scheduled queries and 'identical query reuse' suggests cached results. When a question mentions precomputed, automatically refreshed aggregates for downstream consumers, materialized views are almost always the intended answer.
Community Votes
100% of anonymous learners picked answer D. Votes are pick records left by other test-takers — they are not the verified answer.
Discussion 3 contributions
Compiled from public community threads and reader submissions, lightly edited for clarity. Handles and vote tallies are shown as recorded, and comments reflect the views of individual contributors.
From public community threads 3
-
AllenChen123 👍 7 Answered DCreate a materialized view as query source. Materialized views are precomputed views that periodically cache the results of a query for increased performance and efficiency.
-
Shenbasekhar 👍 1 Answered DOption D. Materialized view
-
Sofiia98 👍 1 Answered Dmaterialized view
Reader comments & corrections
No reader corrections yet — spotted an error or have a note? Share it below.