Exam Professional Data Engineer topic 1 question 304 discussion

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

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

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.

Option A

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

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

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

To preserve the historical data

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.