Snowflake Architecture: Storage, Compute & Services

By the end of this lesson you will be able to draw Snowflake's three layers from memory, explain what each one does, and say why separating storage from compute changes how teams work and pay.

The three layers

Snowflake's design has three layers that work together but scale separately:

+--------------------------------------------------------------+
|  CLOUD SERVICES                                              |
|  sign-in and security, query parsing and optimising,         |
|  metadata, transactions, infrastructure management           |
+--------------------------------------------------------------+
|  COMPUTE (virtual warehouses)                                |
|  [ LOAD_WH ]     [ ANALYTICS_WH ]     [ FINANCE_WH ]         |
|  each one is an independent group of machines                |
+--------------------------------------------------------------+
|  DATABASE STORAGE                                            |
|  tables kept as compressed, columnar micro-partitions        |
|  in cloud object storage (for example Amazon S3)             |
+--------------------------------------------------------------+

A good everyday picture is a big railway station. The storage layer is the goods godown where all parcels are kept. The compute layer is the porters: you can call more porters on a busy festival day and send them home at night. The cloud services layer is the station master's office, which checks tickets, plans who goes where, and keeps the register of every parcel.

Layer 1: database storage

When you load data, Snowflake reorganises it into its own internal format. You never see the files and you cannot open them directly; you only reach the data through SQL.

Three ideas describe this format:

• Columnar: values from the same column are stored together. A query that reads two columns out of twenty only reads those two.
• Compressed: similar values next to each other compress very well, so storage is usually much smaller than the raw files.
• Micro-partitions: each table is split into many small, immutable chunks. Snowflake's documentation describes each micro-partition as holding between 50 MB and 500 MB of uncompressed data. "Immutable" means a micro-partition is never edited. An UPDATE writes new micro-partitions and retires the old ones.

For every micro-partition, Snowflake records metadata such as the minimum and maximum value of each column and the number of distinct values. This metadata is what makes queries fast, as the next example shows.

How metadata lets Snowflake skip data

Step 1: Imagine the 15 orders in the practice database are split into three micro-partitions in date order. This query simulates that with NTILE(3), which deals rows into three equal groups, and then builds the metadata Snowflake would keep:

WITH parts AS (
  SELECT order_id, order_date,
         NTILE(3) OVER (ORDER BY order_date) AS micro_partition
  FROM orders
)
SELECT micro_partition,
       COUNT(*) AS row_count,
       MIN(order_date) AS min_date,
       MAX(order_date) AS max_date
FROM parts
GROUP BY micro_partition
ORDER BY micro_partition;

micro_partition | row_count | min_date   | max_date
----------------+-----------+------------+-----------
1               | 5         | 2025-01-05 | 2025-02-14
2               | 5         | 2025-02-28 | 2025-04-01
3               | 5         | 2025-04-10 | 2025-05-20
(3 rows)

You should see three partitions of five rows each, each with its own date range.

Step 2: Now a query asks for orders placed on or after 5 April 2025 (WHERE order_date >= '2025-04-05'). Before reading any data, Snowflake compares the filter with the metadata and keeps only the partitions whose range could contain matching rows:

WITH parts AS (
  SELECT order_id, order_date,
         NTILE(3) OVER (ORDER BY order_date) AS micro_partition
  FROM orders
),
metadata AS (
  SELECT micro_partition, MIN(order_date) AS min_date, MAX(order_date) AS max_date
  FROM parts
  GROUP BY micro_partition
)
SELECT micro_partition, min_date, max_date,
       CASE WHEN max_date >= '2025-04-05' THEN 'scan' ELSE 'skip' END AS decision
FROM metadata
ORDER BY micro_partition;

micro_partition | min_date   | max_date   | decision
----------------+------------+------------+---------
1               | 2025-01-05 | 2025-02-14 | skip
2               | 2025-02-28 | 2025-04-01 | skip
3               | 2025-04-10 | 2025-05-20 | scan
(3 rows)

Two of the three partitions are skipped without being read. This is called pruning, and it is the main reason Snowflake does not need the indexes you create in MySQL. On a real table with thousands of micro-partitions, good pruning can turn a slow query into a fast one. You will study it in depth in Micro-partitions, Pruning & Clustering Keys.

Layer 2: compute (virtual warehouses)

A virtual warehouse is a group of cloud machines (CPU, memory and local SSD disk) that runs your queries. Despite the name, it holds no data of its own. It reads micro-partitions from storage, processes them and returns results.

Important facts:

• Warehouses come in T-shirt sizes, from X-Small upwards. Each size up has roughly double the compute of the size below.
• A warehouse uses credits only while it is running. It can suspend itself when idle and resume when a query arrives.
• Many warehouses can read the same tables at the same time without slowing each other down.

CREATE WAREHOUSE analytics_wh
  WAREHOUSE_SIZE = 'XSMALL'
  AUTO_SUSPEND = 60
  AUTO_RESUME = TRUE;

status
-----------------------------------------
Warehouse ANALYTICS_WH successfully created.
(1 row)

This warehouse suspends after 60 seconds without queries and starts again automatically when someone runs one. The next two lessons cover warehouses fully.

Layer 3: cloud services

The cloud services layer is the brain that coordinates everything. It runs all the time and handles:

• Authentication and access control: who you are and what you may see.
• Query compilation and optimisation: turning your SQL into an efficient plan, including pruning decisions.
• Metadata management: table definitions, micro-partition statistics, Time Travel history.
• Transactions: making sure concurrent changes stay consistent.

Some queries are answered by cloud services alone, with no warehouse at all. For example, SELECT COUNT(*) FROM orders on a whole table can often be answered from metadata, because Snowflake already knows how many rows each micro-partition holds.

Why separation matters

Compare the old way with Snowflake:

Common mistakes

• Thinking a warehouse stores data. It does not. Dropping a warehouse never deletes a table.
• Creating indexes out of habit. Standard Snowflake tables have no CREATE INDEX. Pruning and clustering do that job.
• Leaving a warehouse running. Without auto-suspend, a warehouse keeps using credits even when idle.

Key points

• Snowflake has three layers: database storage, compute (virtual warehouses) and cloud services.
• Storage is columnar, compressed and split into immutable micro-partitions with min/max metadata.
• Pruning uses that metadata to skip micro-partitions that cannot match a filter.
• Virtual warehouses are compute only; they use credits while running and can auto-suspend.
• Cloud services handle security, optimisation, metadata and transactions.
• Separating storage and compute lets teams scale and pay for each independently.

WITH parts AS (
  SELECT item_id, product_id, NTILE(4) OVER (ORDER BY item_id) AS micro_partition
  FROM order_items
)
SELECT micro_partition, MIN(product_id) AS min_product, MAX(product_id) AS max_product,
       CASE WHEN 9 BETWEEN MIN(product_id) AND MAX(product_id) THEN 'scan' ELSE 'skip' END AS decision
FROM parts
GROUP BY micro_partition
ORDER BY micro_partition;