Optimizing BigQuery Aggregations with Materialized Views

You have a table that contains millions of rows of sales data, partitioned by date. Various applications and users query this data many times a minute. The query requires aggregating values by using AVG, MAX, and SUM, and does not require joining to other tables. The required aggregations are only computed over the past year of data, though you need to retain full historical data in the base tables. You want to ensure that the query results always include the latest data from the tables, while also reducing computation cost, maintenance overhead, and duration. What should you do?

  1. Create a materialized view to aggregate the base table data. Include a filter clause to specify the last one year of partitions. Source Reference Answer
  2. Create a materialized view to aggregate the base table data. Configure a partition expiration on the base table to retain only the last one year of partitions.
  3. Create a view to aggregate the base table data. Include a filter clause to specify the last year of partitions.
  4. Create a new table that aggregates the base table data. Include a filter clause to specify the last year of partitions. Set up a scheduled query to recreate the new table every hour.

Community Votes

A
100%

100% of anonymous learners picked answer A. Votes are pick records left by other test-takers — they are not the verified answer.

Community Insight

The exam tests the ability to select materialized views for performance optimization, specifically recognizing that a filter within the materialized view limits the scope of pre-computation without affecting the underlying base table's data retention.

For optimizing frequent aggregation queries on large partitioned tables while preserving full historical data, creating a materialized view with a filter for the relevant date range is the best solution. This approach ensures results are always fresh while significantly reducing computation costs and query latency.

A common error is selecting Option B, which incorrectly applies a partition expiration to the base table, thereby deleting historical data that the question explicitly requires to be kept.

Community Discussion (9 comments)

raaad 👍 11 Selected: A
  • Materialized View: Materialized views in BigQuery are precomputed views that periodically cache the result of a query for increased performance and efficiency. They are especially beneficial for heavy and repetitive aggregation queries. - Filter for Recent Data: Including a clause to focus on the last year of partitions ensures that the materialized view is only storing and updating the relevant data, optimizing storage and refresh time. - Always Up-to-date: Materialized views are maintained by BigQuery and automatically updated at regular intervals, ensuring they include the latest data up to a certain freshness point.
MBNR 👍 1 Selected: A
Answer : A Question has below three requirements , it did NOT talk about STORAGE cost Reducing computation cost: Using Materialized views in BigQuery, query costs can be lower due to faster performance maintenance overhead : Bigquery takes care of data updates and duration: Since the results are precomputed and stored , it takes very less time for the query output
Pime13 👍 1 Selected: A
Option B, creating a materialized view and configuring a partition expiration on the base table to retain only the last one year of partitions, would not meet the requirement of retaining full historical data in the base tables. Partition expiration would delete older data, which is not desirable if you need to keep the full historical data. Option A, on the other hand, allows you to create a materialized view that aggregates the data for the past year without deleting any historical data from the base table. This ensures that you always have access to the latest data while retaining the full history.
JyoGCP 👍 2 Selected: A
Option A
et2137 👍 2 Selected: C
materialized view requires refreshing so it might not fulfill the requirement: "results always include the latest data from the tables". Opt. C will give you the newest data every time you execute the query but it will have to be computed every time
casadocc 👍 1
A We can do aggregations, bit if not specified table will not be partitioned on the view. B partition expiration is not possible, as expiration is the same as base table C It might be the right one, although not specific savings vs the original query, but here we would guarantee accessing only last year data. D not a good one in any sense A and C might be equally good solutions depending on some understandings. Would probably opt for A
Matt_108 👍 2 Selected: A
. Create a materialized view to aggregate the base table data. Include a filter clause to specify the last one year of partitions.
Sofiia98 👍 2 Selected: A
To preserve the historical data
scaenruy 👍 1 Selected: B
B. Create a materialized view to aggregate the base table data. Configure a partition expiration on the base table to retain only the last one year of partitions.

Comments & Corrections

No comments yet — spotted an error or have a note? Share it below.

Log in to comment, report an error, or add a note about this question.

Submitted for moderation before publishing. Keep it helpful and respectful.

Expert Analysis

Why the Answer Is Correct

Option A is the correct choice because BigQuery materialized views pre-compute and cache the results of the aggregation query, drastically reducing computation cost and duration. By including a filter clause for the last year, the materialized view only stores and updates the relevant recent data, optimizing maintenance. Crucially, this filter is applied only to the view definition, ensuring the base table retains its full historical data while the view automatically refreshes to include the latest changes.

Why the Other Options Are Wrong

Option B is incorrect because configuring partition expiration on the base table deletes data older than one year, which violates the requirement to retain full historical data. Option C is incorrect because a standard view does not pre-compute results; it merely runs the underlying query every time, failing to reduce computation cost or duration. Option D is suboptimal because managing a scheduled query creates high maintenance overhead and results in stale data (up to an hour old), whereas materialized views are managed services that provide fresher data automatically.

Community Comment Notes

Comment [1] accurately highlights that materialized views are designed for heavy, repetitive aggregation queries and that filtering ensures the view only maintains relevant data. Comment [6] reinforces why Option B is wrong, noting that partition expiration would delete necessary historical data. Comment [2] correctly points out that while Option C provides fresh data, it lacks the performance benefits of pre-computation required by the scenario.

Official Reference

Exam Strategy

When a question requires reducing computation cost and maintenance overhead while ensuring the latest data is available, look for Materialized Views as the primary solution. Always double-check that options involving partitioning or expiration do not conflict with data retention requirements specified in the prompt.

Related Analysis

Your car factory is pushing machine measurements as messages

S-Grade

dataset.inventory_vm sample records: You have an inventory o

A-Grade

You want to encrypt the customer data stored in BigQuery. Yo

A-Grade

← Back to PDE Study Guide