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…

Question

Options

• ACreate a materialized view to aggregate the base table data. Include a filter clause to specify the last one year of partitions.
• BCreate 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.
• CCreate a view to aggregate the base table data. Include a filter clause to specify the last year of partitions.
• DCreate 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

• A
  82% (14)
• B
  6% (1)
• D
  12% (2)

Explanation

Option A is correct because a materialized view pre-computes and stores aggregation results, so repeated queries (AVG, MAX, SUM) consume far less computation than running against raw millions of rows each time. BigQuery materialized views automatically stay in sync with the base table, satisfying the "always latest data" requirement, and the WHERE filter clause on the last year limits what gets aggregated - keeping full history intact in the base table.

B is wrong because setting a partition expiration deletes historical data from the base table, directly violating the requirement to retain full historical data.

C is wrong because a regular (non-materialized) view is just a saved query - it re-executes the full aggregation on every call, providing no reduction in computation cost or query duration.

D is wrong because a scheduled query only refreshes on a fixed schedule (e.g., hourly or daily), meaning results will be stale between runs - violating the "always includes the latest data" requirement, and adding scheduling maintenance overhead.

Memory tip: "Materialized = Memorized." A materialized view memorizes (pre-stores) the result so queries are fast, but unlike a static table it stays current automatically - giving you freshness without manual refresh jobs, and a filter clause lets you scope it cheaply without touching historical retention.

No community discussion yet for this question.