Snowflake architecture (storage, compute, services layers) - Step-by-Step Execution

Process Flow - Snowflake architecture (storage, compute, services layers)

User Query

↓

Services Layer

↓

Compute Layer

↓

Storage Layer

↓

Return Data to User

User requests go through the Services layer, which manages tasks and security, then to Compute for processing, and Storage for data. Results flow back the same way.

Execution Sample

Snowflake

-- User runs a query
SELECT * FROM sales_data WHERE region = 'West';

This query is processed by Snowflake's architecture layers to return filtered sales data.

Process Table

Step	Layer	Action	Details	Result
1	Services Layer	Receive query	Parse and validate user query	Query accepted
2	Services Layer	Check permissions	Verify user access rights	Access granted
3	Compute Layer	Allocate warehouse	Assign virtual warehouse for query	Warehouse ready
4	Compute Layer	Process query	Translate query to execution plan	Execution plan created
5	Storage Layer	Fetch data	Retrieve sales_data from cloud storage	Data retrieved
6	Compute Layer	Filter data	Apply WHERE region = 'West'	Filtered data ready
7	Compute Layer	Return results	Send data back to Services layer	Results ready
8	Services Layer	Deliver results	Send data to user	User receives data
9	-	End	Query complete	Execution finished

💡 Query completes after results are delivered to the user

Status Tracker

Variable	Start	After Step 3	After Step 5	After Step 6	Final
Query	User input	Assigned to warehouse	Data fetched from storage	Data filtered by region	Results sent to user
Warehouse	None	Active	Active	Active	Released after query

Key Moments - 3 Insights

Why does the Services layer check permissions before processing?

The Services layer ensures security by verifying user rights before any compute or storage access, as shown in step 2 of the execution_table.

How does Compute layer handle the query without storing data?

Compute processes the query using data fetched from Storage but does not store data itself, demonstrated in steps 4, 6, and 7.

What happens if multiple users run queries at the same time?

Compute layer can allocate multiple virtual warehouses independently, allowing parallel processing without affecting Storage or Services layers.

Visual Quiz - 3 Questions

Look at the execution_table, at which step does the Compute layer filter data?

A Step 4

B Step 2

C Step 6

D Step 8

💡 Hint

Check the 'Layer' and 'Action' columns in execution_table rows

According to variable_tracker, what is the state of the Warehouse after Step 3?

A Inactive

B Active

C Released

D Not assigned

💡 Hint

Look at the 'Warehouse' row under 'After Step 3' column

If the Services layer denies access at Step 2, what happens next?

A Query is rejected and stops

B Query proceeds to Compute layer

C Data is fetched from Storage anyway

D Results are sent to user immediately

💡 Hint

Refer to the purpose of Step 2 in execution_table about permission checks

Concept Snapshot

Snowflake architecture has three layers:
1. Services Layer: Manages security, metadata, and query parsing.
2. Compute Layer: Runs queries using virtual warehouses.
3. Storage Layer: Stores all data centrally in cloud storage.
Layers work independently for scalability and performance.

Full Transcript

Snowflake architecture separates tasks into three layers. The Services layer receives and validates user queries, checking permissions to keep data secure. Then the Compute layer allocates virtual warehouses to process queries without storing data itself. The Storage layer holds all data in cloud storage, accessed by Compute when needed. This separation allows Snowflake to scale efficiently and handle many users at once. The execution flow starts with the user query entering Services, moving to Compute for processing, fetching data from Storage, and returning results back through Services to the user.