Optimizing BigQuery Aggregations with Materialized Views

PDE By islever Editorial Last updated

Short answer

A Create a materialized view to aggregate the base table data. Include a filter clause to specify the last one year of partitions.

Question

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. Correct 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.

Key Takeaways

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.

Common mistake: 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.

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

These links open the official vendor documentation in a new tab.

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.

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.

Discussion 9 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 9

  1. raaad 👍 11 Answered 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.
  2. et2137 👍 2 Answered 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
  3. Matt_108 👍 2 Answered A
    . Create a materialized view to aggregate the base table data. Include a filter clause to specify the last one year of partitions.
  4. Sofiia98 👍 2 Answered A
    To preserve the historical data
  5. Pime13 👍 1 Answered 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.
Show 4 more comments
  1. 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
  2. MBNR 👍 1 Answered 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
  3. scaenruy 👍 1 Answered 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.
  4. JyoGCP 👍 2 Answered A
    Option A

Reader comments & corrections

No reader corrections 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.

How to establish consistent BigQuery spend?

S-grade

Triggering Airflow DAGs Reactively in Private Cloud Composer

S-grade

How to resolve Cloud Composer 2 worker pod evictions and task failures?

S-grade

Which Google Cloud database for on-prem PostgreSQL with analytics?

S-grade

← Back to PDE Study Guide