Views and materialized views in Snowflake - Cheat Sheet & Quick Revision

Recall & Review

beginner

What is a view in Snowflake?

A view is a saved SQL query that acts like a virtual table. It does not store data itself but shows data from underlying tables when queried.

beginner

How does a materialized view differ from a regular view?

A materialized view stores the query result physically, so it can return data faster. Snowflake automatically refreshes it when the base tables change.

intermediate

When should you use a materialized view instead of a regular view?

Use a materialized view when you want faster query performance on complex or large data sets and can accept some delay in data freshness.

beginner

What happens when you query a regular view in Snowflake?

Snowflake runs the view's underlying SQL query on the current data each time you query the view, so results are always up-to-date.

intermediate

How do you refresh a materialized view in Snowflake?

Materialized views are automatically refreshed by Snowflake based on changes to the base tables. You can also refresh them manually using the ALTER MATERIALIZED VIEW ... REFRESH command.

What does a regular view in Snowflake store?

A Nothing, it is just a name

B The SQL query only, no data

C Both query and data

D The query result data physically

A regular view stores only the SQL query. It does not store data physically.

Which of the following is true about materialized views?

A They always show real-time data

B They cannot be refreshed

C They store query results physically

D They are slower than regular views

Materialized views store the query results physically to improve query speed.

How do you update data in a materialized view?

A It updates automatically with no action

B By dropping and recreating the view

C By running REFRESH MATERIALIZED VIEW

D By updating the underlying tables only

Materialized views are automatically refreshed by Snowflake as changes occur in the base tables.

Which scenario is best for using a materialized view?

A For complex queries needing faster results

B For simple queries on small tables

C When data freshness is critical

D When you want to save storage space

Materialized views are best for complex queries where faster results are needed.

What happens when you query a regular view?

A It requires manual refresh

B It returns stored data instantly

C It returns cached data only

D It runs the underlying query on current data

A regular view runs its underlying query on the current data every time it is queried.

Explain the difference between a view and a materialized view in Snowflake.

Think about how data is stored and updated.

Describe when and why you would choose to use a materialized view over a regular view.

Consider trade-offs between speed and data freshness.