Exam Professional Data Engineer topic 1 question 229 discussion

Just a note, the question saying "data no less than 4 hours old" presumably means "no more than 4 hours old"

Unfortunately the correct answer is B due to the limitations of materialized views, doesn't support any other join than inner and no analytical function is supported

A https://cloud.google.com/bigquery/docs/materialized-views-create#non-incremental In scenarios where data staleness is acceptable, for example for batch data processing or reporting, non-incremental materialized views can improve query performance and reduce cost. allow_non_incremental_definition option. This option must be accompanied by the max_staleness option. To ensure a periodic refresh of the materialized view, you should also configure a refresh policy.

Option A is better than D, since it accounts for data staleness and is better suited for heavy querying, thanks to the allow_non_incremental_definition

A seems right but whats wrong with option D, can anybody please explain?

- Materialized views in BigQuery precompute and store the result of a base query, which can speed up data retrieval for complex queries used in visualizations. - The max_staleness parameter allows us to specify how old the data can be, ensuring that the visualizations are based on data no less than 4 hours old. - The enable_refresh parameter ensures that the materialized view is periodically refreshed. - The allow_non_incremental_definition is used for enabling the creation of non-incrementally refreshable materialized views.

Precomputed Results: Materialized views store precomputed results of complex queries, significantly accelerating subsequent query performance, addressing the slow visualization issue. Allow Non-Incremental Views: Using allow_non_incremental_definition circumvents the limitation of incremental updates for outer joins and analytic functions, ensuring views can be created for the specified queries. Near-Real-Time Data: Setting max_staleness to 4 hours guarantees data freshness within the acceptable latency for visualizations. Automatic Refresh: Enabling refresh with enable_refresh maintains view consistency with minimal maintenance overhead. Minimal Overhead: Materialized views automatically update as underlying data changes, reducing maintenance compared to manual exports or view definitions.