Which Scenario Data engineer decide Materialized views are not useful. Select All that apply.

Which Scenario Data engineer decide Materialized views are not useful. Select All that apply.

A. Query results contain a small number of rows and/or columns relative to the base table (the table on which the view is defined).

B. Query results contain results that require significant processing.

C. The query is on an external table (i.e. data sets stored in files in an external stage), which might have slower performance compared to querying native database tables.

D. The view’s base table change frequently.

View Answer

Answer: A,C,D

Explanation:

Materialized views are designed to store computed results of a query which can then be referenced without recomputing the original expression. This is useful when a query is computationally expensive. However, there are scenarios where using materialized views might not be beneficial. Let’s examine each of the options:

A. Query results contain a small number of rows and/or columns relative to the base table (the table on which the view is defined).

Correct. If the query returns a small result set, the performance benefit from a materialized view would be minimal, if any. In such cases, the overhead of maintaining the materialized view (especially when the underlying data changes) could outweigh the benefits.

B. Query results contain results that require significant processing.

Incorrect. This is a scenario where materialized views can be useful. If a query requires a lot of processing, storing the result in a materialized view can improve subsequent query performance by eliminating the need to redo the same heavy computations.

C. The query is on an external table (i.e. data sets stored in files in an external stage), which might have slower performance compared to querying native database tables.

Correct, at least in the context of Snowflake as of the last update in January 2022. Snowflake doesn’t support materialized views on external tables. So, in this scenario, materialized views would not be applicable.

D. The view ’ s base table change frequently.

Correct. If the base table is updated frequently, the materialized view would need to be refreshed often. Continuously refreshing the materialized view can introduce performance overhead and could negate the advantages of having a materialized view in the first place.

So, the correct scenarios where a data engineer might decide that materialized views are not useful are:

A. Query results contain a small number of rows and/or columns relative to the base table.

C. The query is on an external table.

D. The view ’ s base table change frequently.