Lesson 2.1 — Warehouses, Storage, and the Architecture That Makes Snowflake Snowflake

Snowflake's entire differentiator is one idea: compute and storage are separate, elastic, and independently billed. Internalize this lesson and the rest of the JD's Snowflake requirements (Time Travel, Zero-Copy Clone, stored procs) become details, not concepts.

The three-layer architecture

 ┌───────────────────────────────────────────────────────────────┐
 │  CLOUD SERVICES (metadata, auth, optimizer, txn manager)      │  ← query compilation, RBAC,
 │  Runs on Snowflake's infra. You never size it.                │    transactions, result cache
 └───────────────────────────────────────────────────────────────┘
                               ↕
 ┌───────────────────────────────────────────────────────────────┐
 │  COMPUTE (virtual warehouses)  ←──  YOU size / start / stop   │  ← MPP clusters that execute
 │  ┌────┐  ┌────┐  ┌────┐                                       │    SQL. Many can run at once.
 │  │ WH1│  │ WH2│  │ WH3│   (each is an MPP cluster)            │    Independent.
 │  └────┘  └────┘  └────┘                                       │
 └───────────────────────────────────────────────────────────────┘
                               ↕
 ┌───────────────────────────────────────────────────────────────┐
 │  STORAGE (columnar, compressed, immutable micro-partitions)   │  ← Your tables live here.
 │  Auto-managed. You don't size it. Pay per TB.                 │    Shared by all warehouses.
 └───────────────────────────────────────────────────────────────┘

Three layers, three different cost & scale models:

Why this architecture is a big deal

In traditional on-prem MPP appliances, compute and storage were fused: a single box held both. Scaling compute meant buying more boxes, each with its own disk copy. Workload isolation required hardware.

Snowflake decoupled them. A practical consequence:

Virtual warehouse lifecycle

Here's the warehouse you'll create in the next exercise:

CREATE WAREHOUSE IF NOT EXISTS LEARN_WH
  WITH
    WAREHOUSE_SIZE     = 'XSMALL'
    AUTO_SUSPEND       = 60      -- stop after 60s idle (SAVES CREDITS)
    AUTO_RESUME        = TRUE
    INITIALLY_SUSPENDED = TRUE    -- don't start until first query
    COMMENT            = 'learning / $400 trial';

Storage internals — micro-partitions

Snowflake stores tables as immutable micro-partitions: compressed columnar files of 50–500MB each. You never manage them directly. But two things are worth knowing:

• Pruning. Snowflake tracks min/max and distinct-value counts per column per partition. When you WHERE read_date = '2026-04-10', the optimizer only opens partitions whose min/max range includes that date. On a 2-billion-row table, typical pruning takes you from hours to seconds.
• Clustering key. Optional. If you set one (e.g., CLUSTER BY (read_date, meter_id)), Snowflake background-reshuffles the partitions so pruning works better. Almost always unnecessary on tables <1TB. See glossary.

The result cache — a free speed boost

Snowflake's Result Cache stores query results at the account level for 24 hours. If the exact same query runs against unchanged underlying data, the second execution returns instantly with no warehouse compute billed.

In dashboard-heavy workloads this can wipe out 50–80% of compute cost for free. Don't break it by appending CURRENT_TIMESTAMP() or RAND() to queries without reason.

Mapping to what you know

Key terms from this lesson

Self-check