Snowflake Notes: 2

Snowflake architecture

Snowflake Three-layer architecture:

Snowflake uses a three-layer architecture consisting of a storage layer for data, a compute layer with virtual warehouses for query execution, and a cloud services layer for query optimization, metadata, and security. The key innovation is the complete separation of compute and storage, enabling independent scaling and high concurrency

1. Storage Layer (Data Layer):

• Data stored in columnar format
• Automatically split into micro-partitions
• Stored in cloud storage: Amazon S3 or Azure Blob Storage or Google Cloud Storage

What Snowflake handles for you:

• Compression
• Partitioning
• Metadata tracking

Important:
You never manage files, partitions, or indexes manually.

2. Compute Layer (Virtual Warehouses):

• Independent compute clusters
• Each warehouse: -Executes queries, can scale up/down and can auto-start and auto-stop

Example:

• Warehouse A → dashboards
• Warehouse B → heavy Spark-like ETL

3. Cloud Services Layer (Control Plane)

It’s the Brain of Snowflake.

Responsibilities:

• Query parsing & optimization
• Metadata management
• Access control & security
• Transaction management

Key point:

• This layer does not store data or execute queries
• It coordinates everything

Multi-tenancy in Snowflake Layers:

1. Cloud Services Layer — Fully Multi-tenant

• This layer is shared across many Snowflake customers (tenants).

It handles:

• Query parsing & optimization
• Metadata management
• Authentication & access control

Snowflake can safely share this layer because:

• It doesn’t store customer data directly
• Strong logical isolation is enforced

2. Storage Layer — Multi-tenant (Logically Isolated)

Data is stored in shared cloud infrastructure like:

• Amazon S3
• Azure Blob Storage
• Google Cloud Storage

Important nuance:

• Physically shared infrastructure
• Logically isolated per customer (secure separation)

Snowflake manages:

• Encryption
• Access isolation
• Metadata boundaries

3. Compute Layer (Virtual Warehouses) — Not Multi-tenant

Each customer gets dedicated compute resources

It ensures:

• No performance interference
• Predictable query execution
• Strong workload isolation

Even within the same account:

• Different warehouses don’t share compute

Snowflake architecture layers - cache and the result cache:

The virtual warehouse cache resides in the Compute layer, while the result cache resides in the Cloud Services layer.

Virtual Warehouse Cache → Compute Layer::

This cache lives inside the Virtual Warehouse (compute nodes).

Get Leena Patil’s stories in your inbox

Join Medium for free to get updates from this writer.

Remember me for faster sign in

What it does:

• Stores data blocks (micro-partitions) read from storage
• Cached on local SSD of compute nodes

Why it matters:

• Speeds up repeated queries on same data
• Avoids re-reading from remote storage

Key behavior:

• Cache is lost when warehouse suspends
• Cache is per warehouse (not shared)

Result Cache → Cloud Services Layer::

This cache stores final query results.

What it does:

• Returns results instantly if:
• Same query text
• Same underlying data (no changes)
• Same role/permissions

Why it matters:

• Zero compute cost (no warehouse needed!)
• Extremely fast (milliseconds)

Important:

• Managed by Snowflake’s control plane

Snowflake Cloud Services costs

If your Snowflake Cloud Services costs are higher than expected, it usually means the control plane is doing a lot of work (not compute). That’s a signal to investigate query behavior, metadata operations, and workload patterns.

1. Simple Queries (Session Info / Session Variables)

Examples:

• SELECT CURRENT_USER()
• Queries using session variables

Why it increases cost:

• Even trivial queries still require:
• Parsing
• Optimization
• Metadata lookup

If executed frequently (e.g., by apps), they create high control-plane overhead with almost no compute usage

2. Large, Complex Queries (Many Joins)

Queries with:

• Multiple joins
• Complex execution plans

Why it increases cost:

Cloud Services must:

• Build and optimize execution plans
• Evaluate statistics and metadata

The more complex the query → the more work for the optimizer

3. Single-Row Inserts (Row-by-Row Writes)

Example:

• INSERT INTO table VALUES (...) repeated many times

Why it increases cost:

Each insert triggers:

• Transaction handling
• Metadata updates
• Validation

Compared to bulk load:

• Thousands of tiny operations = massive control-plane overhead

4. Frequent Use of INFORMATION_SCHEMA

Queries like:

• SELECT * FROM INFORMATION_SCHEMA.TABLES

Why it increases cost:

• These are metadata-heavy queries
• Handled primarily by the Cloud Services layer

Often used by:

• BI tools
• Monitoring scripts
• Automation pipelines

5. Frequent Metadata Commands (SHOW, DESCRIBE)

Examples:

• SHOW TABLES
• DESCRIBE TABLE

Why it increases cost:

• Fully handled by Cloud Services (no compute layer)
• Heavy use = direct Cloud Services consumption