Table of Contents
Question
What is a characteristic of the maintenance of a materialized view?
A. Materialized views cannot be refreshed automatically.
B. An additional set of scripts is needed to refresh data in materialized views.
C. A materialized view is automatically refreshed by a Snowflake managed warehouse.
D. A materialized view can be set up with the auto-refresh feature using the SQL SET command.
Answer
D. A materialized view can be set up with the auto-refresh feature using the SQL SET command.
Explanation
The correct answer is D. A materialized view can be set up with the auto-refresh feature using the SQL SET command.
A materialized view is a database object that contains the results of a query that can be updated as the underlying data changes. Materialized views can improve query performance by pre-computing expensive join and aggregation operations and storing the results in a cache.
The maintenance of a materialized view involves refreshing the data in the materialized view to reflect the changes in the underlying tables. Snowflake supports two modes of refreshing materialized views: manual and automatic.
- Manual refresh: This mode requires the user to explicitly refresh the materialized view using the ALTER MATERIALIZED VIEW … REFRESH command. The user can specify the frequency and timing of the refresh operation using a task or a cron job.
- Automatic refresh: This mode allows Snowflake to automatically refresh the materialized view using a Snowflake-managed warehouse. The user can enable or disable this feature using the SQL SET command. For example, to enable automatic refresh for a materialized view named mv_sales, the user can run:
SET MATERIALIZED_VIEW_AUTO_REFRESH = TRUE;
ALTER MATERIALIZED VIEW mv_sales RESUME;
The other options are incorrect because:
- A. Materialized views cannot be refreshed automatically. This is false because Snowflake supports automatic refresh for materialized views as described above.
- B. An additional set of scripts is needed to refresh data in materialized views. This is false because Snowflake does not require any additional scripts to refresh data in materialized views. The user can either use the built-in ALTER MATERIALIZED VIEW … REFRESH command or enable the automatic refresh feature.
- C. A materialized view is automatically refreshed by a Snowflake managed warehouse. This is partially true, but not a complete answer. A materialized view can be automatically refreshed by a Snowflake managed warehouse, but only if the user enables the auto-refresh feature using the SQL SET command. Otherwise, the user has to manually refresh the materialized view using the ALTER MATERIALIZED VIEW … REFRESH command.
Snowflake SnowPro Core certification exam practice question and answer (Q&A) dump with detail explanation and reference available free, helpful to pass the Snowflake SnowPro Core exam and earn Snowflake SnowPro Core certification.