Answer-first summary for fast verification
Answer: Create a materialized view to aggregate the base table data. Include a filter clause to specify the last one year of partitions.
The most appropriate option to ensure that the query results always include the latest data from the tables, while also reducing computation cost, maintenance overhead, and duration, is option A. Materialized views in BigQuery are precomputed views that periodically cache the result of a query for increased performance and efficiency, which makes them beneficial for heavy and repetitive aggregation queries. By including a filter clause to focus on the last year of partitions, the materialized view is only storing and updating the relevant data, optimizing storage and refresh time. Moreover, materialized views are maintained by BigQuery and automatically updated at regular intervals, ensuring the view includes the latest data, providing fresh results when queried.
Author: LeetQuiz Editorial Team
Ultimate access to all questions.
Consider a scenario in which you manage a table with millions of rows of sales data partitioned by date. This data is queried frequently—several times per minute—by various applications and users. The primary queries involve aggregating values using the AVG, MAX, and SUM functions, and do not require any joins with other tables. The focus is on computations over the past year of data, although you must retain the full historical data in the base tables. Your goal is to ensure that your query results always reflect the most recent data while minimizing computation costs, maintenance efforts, and query duration. What approach should you take to achieve this?
A
Create a materialized view to aggregate the base table data. Include a filter clause to specify the last one year of partitions.
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.
C
Create a view to aggregate the base table data. Include a filter clause to specify the last year of partitions.
D
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.
No comments yet.