Google Professional Data Engineer Question 186
Select 4Google Cloud PlatformYou are a data engineer at a retail company using BigQuery to analyze sales data. To optimize query performance and reduce costs, you created a materialized view on top of a base table that contains millions of daily sales transactions. After deploying the materialized view, you notice that some queries on the materialized view are still taking longer than expected. Which of the following could be potential reasons for this issue?
- A
The materialized view is not being refreshed frequently enough.
- B
The query logic used in the materialized view includes non-deterministic functions.
- C
The materialized view is querying a base table that is frequently updated.
- D
The materialized view includes unsupported SQL constructs, causing it to fall back to querying the base table.
- E
The base table has a large number of partitions, and the materialized view is not leveraging partition pruning.
Show answer and explanation
Correct answers: A, B, D, E
Explanation
Materialized views in BigQuery are designed to optimize query performance by precomputing and storing query results. However, certain factors like infrequent refreshes, the use of non-deterministic functions, unsupported SQL constructs, or a lack of partition pruning can prevent queries from fully benefiting from the materialized view. Understanding these limitations and ensuring the materialized view is properly designed and maintained can help achieve optimal performance.
- A. Correct.
If the materialized view is not refreshed frequently enough, it may not reflect recent changes in the base table, causing queries to fall back to the base table instead of using the precomputed results in the view.
- B. Correct.
Materialized views cannot include non-deterministic functions such as CURRENT_TIMESTAMP or RAND. If such functions are included, queries will not utilize the materialized view and will instead query the base table directly.
- C. Incorrect.
While frequent updates to the base table might impact performance, it is not a direct reason for the materialized view being slower. The materialized view is designed to handle base table updates efficiently.
- D. Correct.
Unsupported SQL constructs in the materialized view, such as certain complex joins or subqueries, can prevent BigQuery from using the materialized view during query execution, leading to a fallback to the base table.
- E. Correct.
If the base table has many partitions but the materialized view does not effectively use partition pruning, queries on the materialized view might scan more data than necessary, impacting performance.