Snowflake Internals: How the Three-Layer Architecture Actually Works

Most people learn Snowflake as a SQL endpoint: you point a BI tool at it, write SELECT, and a result comes back fast. That works right up until the day a query that used to take 4 seconds takes 90, or your monthly bill doubles with no change in workload, or someone asks why a table you "deleted" rows from yesterday still shows them. Every one of those questions is answered by the same thing — the architecture underneath the SQL.

Snowflake's defining design decision is the separation of storage and compute, mediated by a third layer that most diagrams draw as a small box and most engineers never think about. That third layer — cloud services — is where the optimizer, the metadata, the transaction manager, and the features that make Snowflake feel magical (Time Travel, zero-copy cloning, result caching) actually live. This article walks the full stack: how data is physically stored, how compute reads it, and how the services layer ties it together. By the end, the performance and cost behaviour stops being mysterious.

The Three Layers

graph TD
    subgraph CS["☁️ Cloud Services Layer (the 'brain')"]
        OPT["Optimizer + Compiler\n(Cascades-style, cost-based)"]
        META["Metadata Store\n(FoundationDB)\nmicro-partition stats, schema"]
        TXN["Transaction Manager\nACID, MVCC, locks"]
        SEC["Auth, RBAC, Access Control"]
        INFRA["Infra services\nresult cache, Time Travel, cloning"]
    end

    subgraph CL["🖥️ Compute Layer (Virtual Warehouses)"]
        VW1["Warehouse A (M)\nELT jobs"]
        VW2["Warehouse B (XL)\nBI dashboards"]
        VW3["Warehouse C (S)\nad-hoc analysts"]
    end

    subgraph ST["💾 Storage Layer (cloud object storage)"]
        MP["Immutable micro-partitions\n(columnar, compressed FDN files)\nin S3 / Blob / GCS"]
    end

    OPT --> VW1
    OPT --> VW2
    OPT --> VW3
    META -.partition pruning.-> OPT
    VW1 --> MP
    VW2 --> MP
    VW3 --> MP
    TXN -.consistency.-> MP
          

The architecture is often called multi-cluster shared data. It is neither shared-disk (where compute nodes contend over one storage tier through a bottleneck) nor shared-nothing (where data is permanently partitioned across nodes, and rebalancing is painful). Instead, there is one logical copy of the data in cloud object storage, and any number of independent compute clusters can attach to it. Storage scales with how much data you keep; compute scales with how much work you run; they are billed separately and never block each other.

Layer 1: Storage — Immutable Micro-Partitions

When you load data into a Snowflake table, it is not stored as the rows you inserted. Snowflake transparently reorganizes it into micro-partitions: contiguous, immutable storage units of roughly 50–500 MB of uncompressed data each (typically much smaller compressed), written in a proprietary closed columnar format internally referred to as FDN. Three properties of micro-partitions explain almost everything about Snowflake's behaviour.

Columnar and compressed

Within a micro-partition, data is stored column-by-column, not row-by-row. Each column is compressed independently with an encoding chosen automatically based on the data. Analytic queries usually touch a few columns out of many, so columnar layout means a query reads only the columns it references — the rest are never fetched from object storage. This is the first and largest lever on scan cost.

Immutable

Micro-partitions are never modified in place. An UPDATE, DELETE, or MERGE does not edit existing files — it writes new micro-partitions containing the changed rows and marks the old ones as no longer part of the current table version. The old files physically remain for a while. This single fact is the foundation of Time Travel, zero-copy cloning, and Snowflake's entire concurrency model. It also means that high-frequency row-level updates are expensive: changing one row can rewrite a whole partition.

Self-describing metadata

For every micro-partition, Snowflake records metadata in the cloud services layer: the min and max value of each column, the number of distinct values, the null count, and size. The query optimizer uses these ranges to skip partitions that cannot possibly contain matching rows — partition pruning. You never declare an index; pruning is automatic and driven entirely by this metadata.

How a micro-partition is physically built

It's worth being concrete about what "columnar, compressed, self-describing" means at write time. When rows arrive, Snowflake groups them into a micro-partition and, within that unit, splits them into columns stored contiguously. Each column is then encoded independently — Snowflake picks the encoding per column based on the data it sees (dictionary encoding for low-cardinality strings, run-length for sorted/repeated values, delta for monotonic sequences, and so on) and layers a general-purpose compressor on top. Because every column is isolated, a query for three columns out of fifty reads only those three columns' byte ranges within each surviving partition — column projection and partition pruning compound.

Two consequences follow that trip people up. First, the unit of immutability is the partition, not the row: updating a single row means reading the partition it lives in, producing a new partition with that row changed, and retiring the old one. A workload of frequent single-row updates therefore generates enormous write amplification — the classic "why is my MERGE so slow / so expensive" answer. Second, because metadata is per-partition, a column with high global cardinality but good local clustering (values grouped within partitions) prunes well, while the same column scattered randomly across partitions prunes terribly even though the data is identical. Physical layout, not logical schema, decides pruning.

graph LR
    Q["Query:\nWHERE order_date = '2026-03-15'"] --> PR{Optimizer checks\nmin/max per partition}
    PR -->|"min=2026-03-14\nmax=2026-03-16\nMAYBE"| MP2["Micro-partition 2\n✅ SCAN"]
    PR -->|"min=2026-01-01\nmax=2026-01-31\nNO"| MP1["Micro-partition 1\n⏭️ PRUNED"]
    PR -->|"min=2026-06-01\nmax=2026-06-30\nNO"| MP3["Micro-partition 3\n⏭️ PRUNED"]
          

Clustering: why query patterns matter

Pruning only helps if the values you filter on are physically co-located in a small number of partitions. Data loaded in roughly time order is naturally well-clustered on time columns — a filter on a recent date hits few partitions. But if you filter on a column whose values are scattered across every partition (say, customer_id on a table loaded by date), the min/max range of every partition spans the whole key space, nothing prunes, and you full-scan.

For very large tables (hundreds of GB to TB) with a query pattern that doesn't match natural load order, you can define a clustering key. Snowflake then runs a background service (Automatic Clustering) that incrementally re-sorts micro-partitions on that key. It costs credits and should be reserved for large, frequently-filtered tables where pruning is provably poor.

-- Diagnose clustering health before reaching for a clustering key
SELECT SYSTEM$CLUSTERING_INFORMATION('fct_orders', '(order_date)');
-- Look at "average_overlaps" and "average_depth":
-- high values mean partitions overlap heavily on this key → poor pruning.

-- Define a clustering key only if the diagnosis justifies it
ALTER TABLE fct_orders CLUSTER BY (order_date, customer_region);

Table Types: Where the Bytes Actually Live

"A Snowflake table" is not one thing. The storage model above describes the default — but Snowflake now offers several table types that change who owns the bytes, what format they're in, and what workloads they suit. Choosing the wrong one is a common and expensive mistake, so it's worth understanding each.

graph TD
    T{Where do the bytes live?\nWhat workload?}
    T -->|Snowflake-owned FDN| MAN["Managed (permanent) tables\nfull FDN micro-partitions\nTime Travel + Fail-safe\nthe default"]
    T -->|Snowflake-owned, short-lived| TRT["Transient / Temporary\nno Fail-safe (cheaper)\nstaging & scratch"]
    T -->|Your storage, open format| ICE["Apache Iceberg tables\nParquet + Iceberg metadata\nin your S3/GCS/Azure"]
    T -->|Your storage, read-only| EXT["External tables\nquery files in place\nno DML, slower"]
    T -->|Row store, OLTP| HYB["Hybrid tables (Unistore)\nrow engine, PK/FK, fast point ops"]
    ICE -->|Snowflake catalog| ICEM["Snowflake-managed:\nauto compaction,\nnear-native performance"]
    ICE -->|external catalog| ICEX["Externally-managed:\nread/write via REST catalog"]
          

Managed (permanent) tables — the default

Everything described so far — FDN micro-partitions, automatic pruning, up to 90 days of Time Travel, 7 days of Fail-safe — is a managed permanent table. Snowflake owns and optimizes the storage; you get the best query performance and the full recovery story. This is the right default for almost all warehouse data.

Transient and temporary tables

Identical to permanent tables except they carry no Fail-safe period (and temporary tables live only for the session). Dropping Fail-safe removes the 7-day post-Time-Travel storage you'd otherwise pay for, so transient tables are the correct choice for reproducible intermediate data — staging tables, ELT scratch, anything you could simply rebuild. Using permanent tables for throwaway staging is a silent, recurring storage cost.

Apache Iceberg tables

Iceberg tables store their data as Parquet files plus Iceberg metadata in cloud storage you own and control (your S3/GCS/Azure bucket), rather than in Snowflake's internal FDN storage. The draw is open format and interoperability: other engines (Spark, Trino, DuckDB, Flink) can read the same tables without copying. Snowflake supports two flavours, and the distinction matters for performance:

• Snowflake-managed Iceberg — the Snowflake Horizon Catalog owns metadata and transactions, and Snowflake automates maintenance (compaction, snapshot expiry). Performance is close to native FDN tables, and you can still write via Snowflake.
• Externally-managed Iceberg — a separate catalog (e.g. AWS Glue, an Iceberg REST catalog) owns the metadata; Snowflake reads (and, increasingly, writes) through it. More interoperable, but you own the maintenance burden, and performance depends on how well the external table is compacted.

Rule of thumb: managed Iceberg gives near-native performance with open-format portability; externally-managed Iceberg maximizes interoperability at some performance and operational cost. Both substantially outperform the older read-only external-table path.

External tables (read-only)

External tables let you query files (Parquet, CSV, JSON) in place in cloud storage without loading them. They are read-only — no DML — and slower than native or Iceberg tables because Snowflake has less control over layout and statistics. Their niche is querying a data lake's raw landing zone, or one-off access to files you don't want to ingest. For anything queried repeatedly, ingesting to a managed table or using a managed Iceberg table will be faster and usually cheaper overall.

Hybrid tables (Unistore)

Hybrid tables are the outlier: a row-oriented storage engine with an index, row-level locking, and enforced primary/foreign-key and unique constraints — the opposite of the columnar, constraint-free analytical model. They exist to bring operational (OLTP-style) workloads — fast single-row reads and writes, high concurrency on point lookups — onto Snowflake, the capability marketed as Unistore. You'd use a hybrid table for application state or a serving layer that needs millisecond point operations, often joined against analytical columnar tables in the same query. Do not reach for hybrid tables for analytical scans; that's what the columnar engine is for.

Layer 2: Compute — Virtual Warehouses

A virtual warehouse is a cluster of compute nodes (MPP — massively parallel processing) that Snowflake provisions for you on demand. It is the thing you pay for by the second while it runs. Warehouses come in T-shirt sizes; each size up roughly doubles the node count, doubles the credits-per-hour, and — for a single large query that can parallelize — roughly halves the runtime.

The decision that saves the most money: scale up vs scale out

These are different knobs for different problems, and conflating them is the most common Snowflake cost mistake.

• Scale up (bigger size) — makes a single heavy query faster, because more nodes split the work. Use it when one query is slow, spilling to disk, or processing huge volumes. A query that takes 60s on Medium may take 15s on X-Large at the same total credit cost (4× the rate for 1/4 the time) — and finish sooner.
• Scale out (multi-cluster warehouse) — adds more clusters of the same size to absorb concurrency. Use it when many users hit the same warehouse and queries start queuing. Snowflake spins up additional clusters as the queue grows and shuts them down when it drains.

graph TD
    subgraph UP["Scale UP — one big query"]
        S["Medium\n60s, spilling"] --> XL["X-Large\n15s, no spill"]
    end
    subgraph OUT["Scale OUT — many users"]
        Q["100 concurrent\nqueries queuing"] --> MC["Multi-cluster (min 1, max 4)\nauto-adds clusters\nsame size each"]
    end
          

Caching: the three levels that hide work

Snowflake has three distinct caches, and knowing which one served your query explains a lot of "why was it instant this time?" confusion.

1. Result cache (cloud services, 24h) — if the exact same query text runs again, the underlying data hasn't changed, and you have access, Snowflake returns the stored result without starting a warehouse at all. Zero compute cost.
2. Local disk / SSD cache (per warehouse) — a running warehouse caches the micro-partition data it has read on its local SSDs. Subsequent queries that touch the same data skip the round trip to object storage. This cache is lost when the warehouse suspends — which is the real trade-off behind aggressive auto-suspend.
3. Metadata cache (cloud services) — simple COUNT(*), MIN, MAX and similar can sometimes be answered from partition metadata alone, with no scan.

Layer 3: Cloud Services — The Brain

The cloud services layer is a multi-tenant, always-on fleet that Snowflake operates. You don't size it or manage it, but it does the most interesting work. It is where a SQL string becomes an execution plan and where the features that distinguish Snowflake from "Postgres in the cloud" are implemented.

The optimizer and query lifecycle

Snowflake's optimizer is cost-based (Cascades-style). It uses the rich per-partition statistics to choose join orders, decide build vs probe sides of hash joins, and — crucially — to prune partitions at compile time so the warehouse is handed the smallest possible scan set. Here is the path a query takes:

sequenceDiagram
    participant C as Client
    participant CS as Cloud Services
    participant MD as Metadata (FoundationDB)
    participant W as Virtual Warehouse
    participant S as Object Storage
    C->>CS: SQL text
    CS->>CS: Parse, bind, authorize (RBAC)
    CS->>MD: Fetch table + micro-partition stats
    CS->>CS: Cost-based optimize + partition pruning
    CS->>CS: Result cache hit? → return, done
    CS->>W: Distribute compiled plan + scan set
    W->>S: Read only needed columns of needed partitions
    W->>W: Parallel execution, cache partitions on SSD
    W->>CS: Result
    CS->>C: Rows
          

Metadata, transactions, and MVCC

All metadata — table definitions, micro-partition catalogs and statistics, and the mapping of which partitions constitute the current version of a table — lives in a transactional key-value store (FoundationDB) in cloud services. Because table state is just "the set of micro-partitions that make up version N," Snowflake gets multi-version concurrency control almost for free: a transaction reads a consistent snapshot (a fixed set of partitions) while writers produce new partitions for the next version. Readers never block writers and vice versa.

What Immutability Buys You: The "Free" Features

Three of Snowflake's most-loved features are not separate subsystems — they fall directly out of immutable storage plus versioned metadata.

Time Travel — and how it actually works internally

Because old micro-partitions aren't deleted immediately, Snowflake can reconstruct any past version of a table within the retention window (default 1 day, up to 90 on higher editions) simply by pointing at the partition set that was current at that time.

-- Query a table as it was 1 hour ago
SELECT * FROM orders AT(OFFSET => -3600);

-- Recover from a bad DELETE by reading the pre-statement version
SELECT * FROM orders BEFORE(STATEMENT => '01a2b3c4-...');

-- Undrop an accidentally dropped table (metadata still points to its partitions)
UNDROP TABLE orders;

The mechanism is worth seeing precisely, because once you understand it the costs and limits become obvious. A table's "version" is just a set of micro-partitions recorded in the cloud-services metadata store (FoundationDB), stamped with the transaction that produced it. Every DML statement produces a new version — a new set of partition pointers — without deleting the partitions the previous version referenced. Time Travel is therefore not a snapshot or a log replay: it is the metadata layer handing the optimizer the partition set that was current at the requested timestamp or statement. The historical partitions already exist in object storage; querying the past is the same scan you'd do on the present, just over a different list of files.

graph LR
    subgraph MD["Cloud-services metadata (versions)"]
        V1["v1 @ t0\n{P1, P2}"]
        V2["v2 @ t1 (UPDATE)\n{P1, P3}"]
        V3["v3 @ t2 (DELETE)\n{P3}"]
    end
    subgraph OBJ["Object storage (immutable partitions)"]
        P1["P1"]; P2["P2"]; P3["P3"]
    end
    V1 --> P1
    V1 --> P2
    V2 --> P1
    V2 --> P3
    V3 --> P3
    Q["AT(timestamp = t0)"] -.resolves to.-> V1
          

Retention, Fail-safe, and what they cost

Two windows govern how long old partitions survive:

• Time Travel — controlled per object by DATA_RETENTION_TIME_IN_DAYS (default 1; up to 90 on Enterprise edition and above; 0 disables it). Within this window the data is user-queryable via AT/BEFORE, recoverable via UNDROP, and clonable.
• Fail-safe — a fixed, non-configurable 7-day period that begins when the Time Travel window ends. Fail-safe is not user-accessible; only Snowflake operations can recover from it, and only as a last-resort disaster mechanism. You cannot query it, and you cannot turn it off on permanent tables.

The storage-cost implication is direct: the bytes a table consumes equal its current partitions plus every superseded partition still inside the Time Travel window plus the Fail-safe tail. A table churned by frequent updates with a 90-day retention can store many multiples of its logical size. This is the precise reason transient tables exist — they skip Fail-safe entirely — and why high-retention settings on high-churn tables are a classic cost surprise.

Zero-copy cloning

Cloning a table, schema, or whole database is a metadata operation: the clone points at the same existing micro-partitions. No data is copied, so it's instant and free at creation time. Storage diverges only when one side changes — new writes create new partitions owned by that branch (copy-on-write). This is why teams clone multi-TB production databases for testing without a storage bill spike.

-- Instant, no data copied; storage only grows as the clone diverges
CREATE DATABASE analytics_dev CLONE analytics_prod;

Secure data sharing

Sharing data with another Snowflake account is, again, a metadata grant pointing at the same partitions. The consumer queries live data with their own compute; the provider copies nothing and pays no compute for the consumer's queries. This same mechanism underpins the Snowflake Marketplace.

Reading Internals Off the System Tables

Everything above is observable. The QUERY_HISTORY view and the query profile expose exactly how well a query pruned and whether it spilled — the two numbers that explain most performance problems.

-- Find queries with poor pruning or spilling in the last day
SELECT
    query_id,
    LEFT(query_text, 80)                                  AS query,
    partitions_scanned,
    partitions_total,
    ROUND(100 * partitions_scanned / NULLIF(partitions_total,0), 1)
                                                          AS pct_scanned,
    bytes_spilled_to_local_storage  / POWER(1024,3)       AS spill_local_gb,
    bytes_spilled_to_remote_storage / POWER(1024,3)       AS spill_remote_gb,
    total_elapsed_time / 1000                             AS elapsed_s
FROM snowflake.account_usage.query_history
WHERE start_time > DATEADD(day, -1, CURRENT_TIMESTAMP())
  AND partitions_total > 0
ORDER BY pct_scanned DESC, spill_remote_gb DESC
LIMIT 50;

How to read the result:

• High pct_scanned (near 100%) on a large table with a selective filter means pruning failed — the data isn't clustered on what you filter by. Fix the load order or consider a clustering key.
• Any spill_remote_gb > 0 means the warehouse ran out of memory and spilled to remote storage — the single worst thing for performance. Scale the warehouse up so the working set fits in memory.
• Local spill is tolerable in moderation; remote spill is an alarm.

The Mental Model to Keep

If you remember nothing else, remember the cause-and-effect chains:

• Storage and compute are separate → many warehouses, one copy of data, independent scaling and billing. Give each workload its own right-sized warehouse instead of one shared monster.
• Micro-partitions are immutable and self-describing → pruning is automatic but only as good as your clustering; row-level churn is expensive; Time Travel and cloning are nearly free.
• Cloud services does the thinking → optimization, pruning, transactions, and result caching all happen before your warehouse lifts a finger.
• The bill follows warehouse seconds and serverless features → auto-suspend, right-sizing, scale-up-vs-out, and pruning quality are the dials that actually move cost.

That model is enough to debug the slow-query and surprise-bill questions that started this article. In Part 2, we move up the stack to what Snowflake has built on top of this engine — Cortex AI in 2026, where the same governed-data, separated-compute principles now run LLMs and autonomous agents directly against your micro-partitions. Part 3 then looks at pushing data in continuously, with the next-generation Snowpipe Streaming and Dynamic Tables on AWS.