Architecture Deep Dive

Snowflake · Snowflake Fundamentals

Theory

Each layer has a clear job. Storage keeps data once, as immutable micro-partitions plus metadata such as min/max values per column. Compute is a set of virtual warehouses; each warehouse is its own cluster with its own CPU, memory and local disk cache, and it can be started, paused or resized at any time. Cloud Services parses SQL, checks privileges, builds the plan from metadata, coordinates transactions and keeps the result cache.

This is why there is no contention between teams: the ETL warehouse loading data and the BI warehouse running dashboards share only storage, never CPU. A traditional shared-nothing database would make both workloads compete on the same nodes.

Why we use it

Knowing what each layer does tells you which knob to turn. Slow scans point to compute size or pruning, high login or metadata activity points to Cloud Services, and data volume affects storage cost only, not query capacity.

When to use it

Use it when designing warehouse layout (one warehouse per workload), diagnosing performance or cost, and answering exam questions that ask which layer performs a task such as authentication, caching or pruning.

Storage, Compute, Cloud Services in Detail

Storage holds the data once, compute (virtual warehouses) reads it on demand, and Cloud Services coordinates everything in between — parsing SQL, planning queries, managing transactions, and enforcing security.

Why No Contention

Because each layer scales independently, multiple warehouses can read the same underlying storage simultaneously without locking each other out, which is the core reason Snowflake supports high concurrency without manual tuning.

How This Differs From Traditional Databases

A traditional database ties compute and storage to the same server, so scaling one means scaling (and paying for) both. Snowflake's separation is what makes elastic, pay-per-second compute possible.

How it works

  1. Query arrives at Cloud Services
  2. Parse SQL and check the role's privileges
  3. Build a plan using metadata and prune micro-partitions
  4. Is the identical result already in the result cache? Yes: Return the cached result with no warehouse. No: Send the plan to a virtual warehouse.
  5. Warehouse scans storage, using its local disk cache where it can
  6. Result is returned and cached by Cloud Services

Example

-- Multiple virtual warehouses can query
-- the same data simultaneously without blocking

Key points

Snowflake topics

Snowflake Fundamentals

Warehouses & Performance

Staging & SnowSQL

Tables & Data Handling

Cloud Integrations

Storage & Optimization

Security & Governance

Views, Pipelines & Automation

Data Types & SQL Fundamentals

Semi-Structured Data Deep Dive

Advanced Features

Data Sharing & Collaboration

Cost Management & Monitoring

Databases, Schemas & Objects

Performance Deep Dive

Advanced Security & Governance

Stored Procedures & UDFs

Ecosystem & Integrations

SnowPro Certification Prep

T
Tech Town
Log InSign Up Free
Home SQL Python DSA Power BI Snowflake Databricks Spark Gen AI Practice Problems Contest Blog

Mastering Snowflake

The modern data cloud, from architecture to SnowPro certification. Click any topic in the sidebar to open it right here.

{{ sectionCount }}
Sections
{{ topicCount }}
Topics
Free
Forever
Hands-on practice Practice Snowflake SQL Sharpen the SQL you use in Snowflake: 150 query problems in a live editor with instant checking. Start practising →
Section {{ sec.num }}

{{ sec.label }}

Snowflake  /  {{ current.sectionLabel }}  /  {{ current.name }}

{{ current.name }}

Theory

{{ current.def }}

Why we use it

{{ rich.why }}

When to use it

{{ rich.when }}

{{ sec.h }}

{{ sec.body }}

{{ sec.body }}

Diagram

{{ rich.dg.caption }}

{{ tb.title }}
{{ c.v }}
{{ rich.dg.op }}
➜
{{ rich.dg.output.title }}
{{ c.v }}
{{ nd.label }}
{{ nd.sub }}
{{ nd.label }}
{{ nd.sub }}
{{ rich.dg.left.title }}
{{ it }}
{{ rich.dg.right.title }}
{{ it }}
On screen — where to click

{{ rich.ui.title }}

{{ u.text }}
  1. {{ st.n }}{{ st.text }}
{{ rich.ui.title }}
{{ u.text }}
Flowchart — how it works
{{ nd.text }}
{{ nd.text }}
◇ {{ nd.text }}
Yes → {{ nd.yes }}
No → {{ nd.no }}
Syntax
{{ current.syntax }}
Example
{{ current.example }}
Want to run this yourself? Unlike the SQL and Python courses, Snowflake needs a real account — there's no in-browser engine for it. Click Copy above, then paste it into a free Snowflake trial (30 days, no card required) and run it in Snowsight.
Key Points — click to check off as you learn
  • {{ pt.mark }} {{ pt.text }}

Share this topic