Snowpro Advanced Architect · Free Practice Question Medium
Question 2
- A hey require manual refresh using ALTER MATERIALIZED VIEW ... REFRESH.
- B They cannot be queried directly, only used by the optimizer for query rewrite.
- C They automatically refresh, incurring compute costs for changes in the base table.
- D They can only be created on a single base table.
- E They do not support joins or certain complex SQL constructs (e.g., UDTFs, external functions).
Reveal correct answers
Correct answers: C, D, E
Explanation
✅ They can only be created on a single base table.
✅ They do not support joins or certain complex SQL constructs (e.g., UDTFs, external functions).
✅ They automatically refresh, incurring compute costs for changes in the base table.
Explanation:
Materialized Views in Snowflake provide precomputed query results for improved performance but come with certain limitations compared to standard tables or Dynamic Tables:
Single base table restriction → Materialized Views must be based on a single table, limiting their flexibility for complex queries.
Limited SQL support → They do not support joins, UDTFs, or external functions, restricting their usability for advanced transformations.
Automatic refresh incurs compute costs → Snowflake automatically updates Materialized Views when the base table changes, consuming additional compute resources.
❌ Why the other options are incorrect?
They require manual refresh using ALTER MATERIALIZED VIEW ... REFRESH.
→ Incorrect, because Materialized Views refresh automatically, unlike standard views that require manual updates.
They cannot be queried directly, only used by the optimizer for query rewrite.
→ Incorrect, because Materialized Views can be queried directly, just like regular tables.
For more details, check the official Snowflake documentation on Materialized Views.
Discussion
Think the marked answer is wrong, or have a better explanation? Share it below — comments appear after review.
