Views and materialized views in Snowflake - Practice Problems & Coding Challenges

Challenge - 5 Problems

❓ service_behavior

intermediate

Materialized View Refresh Behavior

In Snowflake, what happens when you query a materialized view that is not yet refreshed after the base table has changed?

A The query automatically triggers a full refresh of the materialized view before returning data.

B The query returns the data from the last refresh, ignoring recent changes in the base table.

C The query returns an error indicating the materialized view is stale.

D The query returns the latest data from the base table directly, bypassing the materialized view.

💡 Hint

Think about how materialized views store data separately and when they update.

Materialized views in Snowflake return data from their last refresh. They do not automatically refresh on query, so recent changes in the base table are not reflected until a manual or scheduled refresh occurs.

❓ Architecture

intermediate

Choosing Between Views and Materialized Views

You have a large table that updates frequently. You want to optimize query speed for a complex aggregation that is used often. Which approach is best in Snowflake?

A Create a copy of the table with precomputed aggregation and update it manually.

B Use a temporary table to store aggregation results for each query.

C Create a materialized view with the aggregation to speed up queries but refresh it regularly.

D Create a standard view with the aggregation query to always get fresh data.

💡 Hint

Consider trade-offs between query speed and data freshness.

Materialized views store precomputed results and speed up queries but require refresh to update data. This balances performance and data freshness better than standard views or manual copies.

❓ security

advanced

Access Control on Views vs Materialized Views

In Snowflake, if a user has SELECT privilege on a base table but no privileges on a materialized view built on that table, what happens when the user tries to query the materialized view?

A The query fails with an access denied error on the materialized view.

B The query succeeds because the user has access to the base table data.

C The query succeeds but returns no rows.

D The query triggers a privilege escalation warning but returns data.

💡 Hint

Think about how Snowflake enforces privileges on objects.

In Snowflake, privileges are required on the object being queried. Having access to the base table does not grant access to the materialized view. Without SELECT privilege on the materialized view, the query fails.

❓ Configuration

advanced

Materialized View Refresh Scheduling

Which Snowflake feature allows automatic refresh of materialized views without manual intervention?

A Using Snowflake Tasks to schedule refresh commands.

B Setting the materialized view property AUTO_REFRESH = TRUE.

C Enabling continuous data protection on the base table.

D Configuring the warehouse to auto-scale during queries.

💡 Hint

Think about how to automate SQL commands in Snowflake.

Snowflake does not have an AUTO_REFRESH property for materialized views. Instead, Snowflake Tasks can be used to schedule SQL commands that refresh materialized views automatically.

🧠 Conceptual

expert

Impact of Materialized Views on Storage and Compute Costs

Which statement best describes the cost implications of using materialized views in Snowflake compared to standard views?

A Materialized views have no impact on storage costs but increase compute costs due to refresh overhead.

B Materialized views reduce both storage and compute costs by compressing data more efficiently than base tables.

C Materialized views increase compute costs because queries always trigger a full refresh.

D Materialized views increase storage costs because they store data, but reduce compute costs by speeding up queries.

💡 Hint

Consider what materialized views store and how they affect query processing.

Materialized views store precomputed data, increasing storage usage. However, they reduce compute costs by allowing faster query execution. Refreshing the view does consume compute, but queries do not always trigger full refreshes.