Materialized views for repeated queries in Snowflake - Practice Problems & Coding Challenges

Challenge - 5 Problems

❓ service_behavior

intermediate

How does Snowflake update a materialized view?

When you query a materialized view in Snowflake, how is the data updated?

A The materialized view is updated asynchronously by Snowflake after base table changes, usually within minutes.

B The materialized view is updated only when explicitly refreshed by the user.

C The materialized view never updates; it is static after creation.

D The materialized view is updated automatically and immediately after every base table change.

💡 Hint

Think about how Snowflake balances performance and freshness for materialized views.

Snowflake updates materialized views asynchronously after changes to the base tables. This means the view is refreshed automatically but not immediately, allowing faster query performance while keeping data reasonably fresh.

❓ Architecture

intermediate

Choosing materialized views for query optimization

You have a query that aggregates sales data by region daily. Which scenario best justifies using a materialized view?

A The query runs once a month and scans a small table.

B The query runs multiple times a day on a large table and involves complex joins and aggregations.

C The query is simple and runs only once after data loads.

D The query is ad-hoc and unpredictable in frequency.

💡 Hint

Materialized views help most when queries are frequent and expensive.

Materialized views are most beneficial when queries are run frequently and involve costly operations like joins and aggregations on large datasets. This reduces query time by precomputing results.

❓ security

advanced

Access control on materialized views in Snowflake

Which statement about access control on materialized views in Snowflake is correct?

A Users need SELECT privilege on the base tables to query the materialized view.

B Materialized views inherit all privileges from the base tables automatically.

C Granting SELECT on the materialized view alone allows querying without base table privileges.

D Materialized views cannot have privileges granted directly.

💡 Hint

Consider how Snowflake separates privileges for views and base tables.

In Snowflake, granting SELECT on a materialized view allows users to query it without needing SELECT privileges on the underlying base tables. This simplifies access control.

✅ Best Practice

advanced

Best practice for maintaining materialized views with frequent base table updates

You have a base table updated every minute. What is the best practice to keep the materialized view efficient and fresh?

A Use clustering keys on the base table and monitor refresh lag to balance performance and freshness.

B Avoid materialized views; use direct queries instead for fresh data.

C Create multiple materialized views for each update batch to avoid conflicts.

D Manually drop and recreate the materialized view after each update.

💡 Hint

Think about how to optimize refresh performance and data freshness.

Using clustering keys helps Snowflake efficiently maintain materialized views with frequent updates. Monitoring refresh lag allows balancing query speed and data freshness without manual intervention.

🧠 Conceptual

expert

Materialized view limitations in Snowflake

Which of the following is a true limitation of materialized views in Snowflake?

A Materialized views can be created on external tables.

B Materialized views support all SQL functions and complex subqueries.

C Materialized views automatically update instantly with zero lag.

D Materialized views cannot reference other materialized views.

💡 Hint

Consider restrictions on dependencies and supported features.

Snowflake does not allow materialized views to reference other materialized views. This prevents complex dependency chains and ensures maintainability.