Views and materialized views in Snowflake - Deep Dive

Overview - Views and materialized views

What is it?

Views are virtual tables in a database that show data from one or more tables without storing the data themselves. Materialized views are like views but they store the data physically, so queries can be faster. Both help organize and simplify how you look at data without changing the original tables. They let you reuse complex queries easily.

Why it matters

Without views or materialized views, users would have to write complex queries every time they want to see data in a certain way. This wastes time and can cause mistakes. Materialized views speed up data retrieval by storing results, which is important for big data or slow queries. They make data analysis faster and more reliable, helping businesses make quick decisions.

Where it fits

Before learning views, you should understand basic SQL queries and tables. After mastering views, you can learn about query optimization and indexing. Later, you might explore data warehousing concepts and performance tuning in Snowflake.

Mental Model

Core Idea

A view is a saved question about data that runs fresh each time, while a materialized view is a saved answer stored for quick reuse.

Think of it like...

Think of a view like a recipe card that tells you how to cook a dish every time you want it, and a materialized view like a pre-cooked meal stored in the fridge ready to eat instantly.

┌─────────────┐       ┌─────────────┐
│   Tables   │──────▶│    View     │
│ (Data)     │       │ (Query)     │
└─────────────┘       └─────────────┘
                          │
                          ▼
                   Data shown fresh


┌─────────────┐       ┌─────────────────────┐
│   Tables   │──────▶│ Materialized View    │
│ (Data)     │       │ (Stored Query Result)│
└─────────────┘       └─────────────────────┘
                          │
                          ▼
                   Data shown quickly

Build-Up - 6 Steps

1

Foundation

Understanding basic database tables

Concept: Learn what tables are and how data is stored in rows and columns.

A table is like a spreadsheet with rows and columns. Each row is a record, and each column is a type of information. For example, a 'Customers' table might have columns for name, email, and phone number. Data is stored physically in these tables.

Result

You understand that tables hold the actual data in a database.

Knowing how tables store data helps you see why views and materialized views are built on top of them.

2

Foundation

What is a view in Snowflake

Concept: A view is a saved SQL query that shows data from tables without storing data itself.

In Snowflake, a view is created by writing a SELECT query and saving it with a name. When you query the view, Snowflake runs the saved query fresh each time. For example: CREATE VIEW recent_orders AS SELECT * FROM orders WHERE order_date > '2024-01-01';

Result

You can query 'recent_orders' like a table, but it always shows up-to-date data.

Understanding that views are just saved queries helps you realize they don't take extra storage but can be slower for complex queries.

3

Intermediate

How materialized views differ

🤔Before reading on: do you think materialized views update automatically or only when manually refreshed? Commit to your answer.

Concept: Materialized views store the query result physically and update it automatically when base data changes.

In Snowflake, a materialized view saves the data from a query so it can be read quickly. Snowflake keeps it updated automatically when the underlying tables change. For example: CREATE MATERIALIZED VIEW fast_recent_orders AS SELECT * FROM orders WHERE order_date > '2024-01-01';

Result

Queries on materialized views run faster because data is precomputed and stored.

Knowing materialized views store data explains why they use storage but speed up queries.

4

Intermediate

When to use views vs materialized views

🤔Before reading on: do you think views or materialized views are better for very fast queries on large data? Commit to your answer.

Concept: Views are good for simple or frequently changing data; materialized views are better for complex queries needing speed.

Use views when you want always fresh data and storage is a concern. Use materialized views when queries are slow and data changes less often. Materialized views improve performance but cost storage and maintenance.

Result

You can choose the right tool based on query speed needs and data freshness.

Understanding trade-offs helps you design efficient data access in Snowflake.

5

Advanced

How Snowflake maintains materialized views

🤔Before reading on: do you think Snowflake recomputes the entire materialized view on every data change or only the changed parts? Commit to your answer.

Concept: Snowflake incrementally updates materialized views using change tracking to avoid full recomputation.

Snowflake tracks changes in base tables and updates only affected parts of the materialized view. This incremental maintenance saves time and compute resources compared to rebuilding the whole view.

Result

Materialized views stay current with less cost and faster updates.

Knowing incremental maintenance explains why materialized views can be efficient even with frequent data changes.

6

Expert

Limitations and performance tuning of materialized views

🤔Before reading on: do you think materialized views can be created on any query or are there restrictions? Commit to your answer.

Concept: Materialized views have restrictions on query complexity and require tuning for best performance.

Snowflake limits materialized views to certain query types (e.g., no non-deterministic functions). You can optimize performance by choosing proper clustering keys and monitoring refresh costs. Overusing materialized views can increase storage and maintenance overhead.

Result

You can create efficient materialized views that balance speed and cost.

Understanding these limits prevents costly mistakes and helps design scalable data solutions.

Under the Hood

Views are stored as SQL queries in the database catalog. When queried, Snowflake parses and runs the query on the current data. Materialized views store the query result as a physical table. Snowflake uses internal change tracking to detect updates in base tables and incrementally refreshes the materialized view data to keep it consistent.

Why designed this way?

Views were designed to save query logic without extra storage, making data access flexible. Materialized views were added to improve performance for expensive queries by storing results. Incremental refresh balances freshness and compute cost, avoiding full recomputation. This design supports Snowflake's cloud architecture for scalable, efficient data warehousing.

┌─────────────┐       ┌─────────────┐       ┌───────────────┐
│ Base Tables │──────▶│   View      │──────▶│ Query runs on │
│ (Data)     │       │ (Stored SQL)│       │ live data     │
└─────────────┘       └─────────────┘       └───────────────┘


┌─────────────┐       ┌─────────────────────┐       ┌───────────────┐
│ Base Tables │──────▶│ Materialized View    │──────▶│ Stored results│
│ (Data)     │       │ (Stored data + SQL)  │       │ refreshed    │
└─────────────┘       └─────────────────────┘       └───────────────┘
                          ▲
                          │
                 Incremental refresh process

Myth Busters - 4 Common Misconceptions

Quick: Do views store data physically in the database? Commit to yes or no.

Common Belief: Views store data just like tables do.

Reality: Views do not store data; they only store the query definition and run it on demand.

Why it matters: Believing views store data can lead to confusion about storage costs and query performance.

Quick: Do materialized views always show the very latest data instantly? Commit to yes or no.

Common Belief: Materialized views always have the freshest data immediately after any change.

Reality: Materialized views update automatically but may have a slight delay due to incremental refresh processing.

Why it matters: Expecting instant updates can cause incorrect assumptions about data freshness in reports.

Quick: Can you create a materialized view on any complex query in Snowflake? Commit to yes or no.

Common Belief: Materialized views can be created on any query, no matter how complex.

Reality: Snowflake restricts materialized views to certain query types and disallows some functions for consistency and performance.

Why it matters: Trying to create unsupported materialized views wastes time and causes errors.

Quick: Do materialized views always improve query speed regardless of data size? Commit to yes or no.

Common Belief: Materialized views always make queries faster no matter what.

Reality: Materialized views improve speed mostly for large or complex queries; for small or simple queries, overhead may outweigh benefits.

Why it matters: Misusing materialized views can increase storage costs and maintenance without real speed gains.

Expert Zone

1

Materialized views in Snowflake use micro-partition metadata to efficiently detect changes, which is a unique cloud-optimized approach.

2

The cost of maintaining materialized views depends on data change frequency and size, so balancing refresh intervals is critical for cost control.

3

Views can be nested (views on views), but materialized views cannot reference other materialized views, which affects design choices.

When NOT to use

Avoid materialized views when data changes extremely frequently or when queries are simple and fast enough without them. Instead, use regular views or optimize base tables with clustering keys and caching.

Production Patterns

In production, teams use materialized views to speed up dashboards and reports that query large datasets with complex joins or aggregations. Views are used to simplify access control and provide consistent query interfaces across teams.

Connections

Caching in web applications

Materialized views are like caching query results to speed up repeated access.

Understanding caching helps grasp why storing query results improves performance but requires refresh strategies.

Functional programming memoization

Materialized views resemble memoization, where function results are saved to avoid repeated computation.

Knowing memoization clarifies how storing results trades storage for speed in databases.

Supply chain inventory management

Views are like just-in-time ordering (fresh data on demand), materialized views like stocked inventory (ready to use).

This connection shows how balancing freshness and readiness applies across fields.

Common Pitfalls

#1 Expecting views to improve query speed by storing data.

Wrong approach:

CREATE VIEW fast_orders AS SELECT * FROM orders WHERE status = 'shipped'; -- expecting faster queries

Correct approach:

CREATE MATERIALIZED VIEW fast_orders AS SELECT * FROM orders WHERE status = 'shipped'; -- stores data for speed

Root cause:

Confusing views with materialized views and not understanding views run queries live.

#2 Creating materialized views on unsupported queries causing errors.

Wrong approach:

CREATE MATERIALIZED VIEW mv AS SELECT CURRENT_TIMESTAMP(), * FROM orders;

Correct approach:

CREATE MATERIALIZED VIEW mv AS SELECT * FROM orders WHERE order_date > '2024-01-01';

Root cause:

Using non-deterministic functions like CURRENT_TIMESTAMP() which are disallowed in materialized views.

#3 Ignoring maintenance costs of materialized views leading to unexpected charges.

Wrong approach:

Creating many materialized views on frequently changing large tables without monitoring refresh costs.

Correct approach:

Carefully selecting materialized views for stable data and monitoring refresh frequency and costs.

Root cause:

Not understanding that materialized views consume compute and storage resources continuously.

Key Takeaways

Views are saved queries that show fresh data each time without storing data themselves.

Materialized views store query results physically and update automatically to speed up queries.

Choosing between views and materialized views depends on data freshness needs and query performance requirements.

Snowflake incrementally refreshes materialized views to balance speed and cost efficiently.

Understanding limitations and maintenance costs of materialized views is essential for effective use in production.