What does it mean that Snowflake separates storage and compute?

Table data lives once in cloud object storage, and warehouses are independent compute clusters that read it. Two teams can run heavy queries on the same tables at the same time on separate warehouses without slowing each other down, and a warehouse can be resized or turned off without touching the data.

What is a virtual warehouse in Snowflake?

A cluster of compute that executes queries. It is not where data is stored — despite the name — and you can have many of them against the same database. You are billed per second while one is running, with a 60-second minimum each time it resumes.

Do I need to create indexes in Snowflake?

No, and you cannot. Snowflake automatically stores data in micro-partitions with per-column metadata and prunes them at query time. Where a traditional database needs an index, Snowflake needs good clustering — covered in the micro-partitions lesson.

How is Snowflake billed?

Compute is billed in credits per second of warehouse runtime, and storage is billed per terabyte-month of compressed data. Because compute dominates most bills, auto-suspend on every warehouse is the single most effective cost control.

Snowflake Architecture and Your First Queries

Snowflake splits a data warehouse into three layers that scale independently: storage that holds the data once, compute clusters that read it, and a services layer that handles metadata, security and query planning. Almost everything that surprises people about Snowflake follows from that split.

Three layers

Layer	What it does	What you pay for
Storage	compressed columnar files in S3 / GCS / Azure Blob	TB-month, compressed
Compute	virtual warehouses that execute queries	credits per second, 60s minimum
Cloud services	metadata, optimiser, security, result cache	free up to 10% of compute

The consequence: compute is disposable and data is permanent. Dropping a warehouse loses nothing. That is the opposite of a traditional database and it drives every design decision in this track.

Setting up

These examples use SnowSQL, the command-line client, because its output is copyable. The web UI runs identical SQL.

snowsql -a your_account -u your_user

* SnowSQL * v1.3.2
Type SQL statements or !help
your_user#COMPUTE_WH@(no database).(no schema)>

create warehouse if not exists bookshop_wh
    warehouse_size = xsmall
    auto_suspend = 60
    auto_resume = true
    initially_suspended = true;

create database if not exists bookshop;
create schema if not exists bookshop.raw;

use warehouse bookshop_wh;
use schema bookshop.raw;

+----------------------------------------+
| status                                 |
|----------------------------------------|
| Warehouse BOOKSHOP_WH successfully created. |
+----------------------------------------+
1 Row(s) produced. Time Elapsed: 0.312s

+-------------------------------------+
| status                              |
|-------------------------------------|
| Database BOOKSHOP successfully created. |
+-------------------------------------+
1 Row(s) produced. Time Elapsed: 0.288s

auto_suspend = 60 is the important line. Without it the warehouse runs — and bills — until someone remembers to stop it. Sixty seconds is a sensible default for interactive work.

A table and some rows

create or replace table customers (
    customer_id   number,
    full_name     string,
    country_code  string,
    created_at    timestamp_ntz
);

insert into customers values
    (1, 'Ada Lovelace',      'GB', '2025-11-02 09:14:00'),
    (2, 'Grace Hopper',      'US', '2025-11-04 16:02:00'),
    (3, 'Alan Turing',       'GB', '2026-01-03 11:41:00'),
    (4, 'Katherine Johnson', 'US', '2026-01-08 08:20:00');

+-------------------------+
| status                  |
|-------------------------|
| Table CUSTOMERS successfully created. |
+-------------------------+
1 Row(s) produced. Time Elapsed: 0.421s

+-------------------------+
| number of rows inserted |
|-------------------------|
|                       4 |
+-------------------------+
1 Row(s) produced. Time Elapsed: 0.688s

create or replace table orders (
    order_id     number,
    customer_id  number,
    ordered_at   date,
    status       string,
    amount       number(10,2)
);

insert into orders values
    (1001, 1, '2026-01-04', 'completed', 25.50),
    (1002, 2, '2026-01-05', 'completed', 12.00),
    (1003, 1, '2026-01-07', 'returned',  40.00),
    (1004, 3, '2026-01-09', 'completed',  8.75),
    (1005, 2, '2026-01-11', 'pending',   63.20);

+-------------------------+
| number of rows inserted |
|-------------------------|
|                       5 |
+-------------------------+
1 Row(s) produced. Time Elapsed: 0.594s

Note the types. number(10,2) for money rather than a float — Snowflake’s number is exact, and using float for currency produces the same rounding surprises it does anywhere else. timestamp_ntz means no time zone; Snowflake has three timestamp types and mixing them is a common source of off-by-hours bugs.

Querying

select
    c.country_code,
    count(*)      as orders,
    sum(o.amount) as revenue
from orders o
join customers c on c.customer_id = o.customer_id
where o.status = 'completed'
group by 1
order by revenue desc;

+--------------+--------+---------+
| COUNTRY_CODE | ORDERS | REVENUE |
|--------------+--------+---------|
| GB           |      2 |   34.25 |
| US           |      1 |   12.00 |
+--------------+--------+---------+
2 Row(s) produced. Time Elapsed: 0.847s

Unquoted identifiers are folded to upper case, which is why the column headers shout. Quote them to preserve case — "country_code" — but then every reference must be quoted forever. Most teams write unquoted SQL and let everything be upper case.

The point of separate compute

Create a second warehouse and query the same table from it:

create warehouse if not exists reporting_wh
    warehouse_size = small
    auto_suspend = 300
    auto_resume = true;

use warehouse reporting_wh;

select count(*) from orders;

+----------+
| COUNT(*) |
|----------|
|        5 |
+----------+
1 Row(s) produced. Time Elapsed: 0.402s

No copy, no replication, no lag. Two warehouses, one dataset. This is the pattern that makes Snowflake worth its price: give the nightly ETL job a large warehouse, give the BI tool a small one, and a heavy transformation can no longer make dashboards time out.

show warehouses;

+--------------+---------+-------+---------+---------+------------+
| name         | state   | type  | size    | running | auto_suspend |
|--------------+---------+-------+---------+---------+------------|
| BOOKSHOP_WH  | STARTED | STANDARD | X-Small |       1 |           60 |
| REPORTING_WH | STARTED | STANDARD | Small   |       1 |          300 |
+--------------+---------+-------+---------+---------+------------+
2 Row(s) produced. Time Elapsed: 0.201s

Both STARTED and both billing. They will suspend on their own after their idle windows.

The result cache

Run the same query twice:

select country_code, count(*) from customers group by 1;

+--------------+----------+
| COUNTRY_CODE | COUNT(*) |
|--------------+----------|
| GB           |        2 |
| US           |        2 |
+--------------+----------+
2 Row(s) produced. Time Elapsed: 0.611s

+--------------+----------+
| COUNTRY_CODE | COUNT(*) |
|--------------+----------|
| GB           |        2 |
| US           |        2 |
+--------------+----------+
2 Row(s) produced. Time Elapsed: 0.043s

0.611s to 0.043s, and the second one used no compute at all — Snowflake served it from the result cache in the services layer. The cache lasts 24 hours and is invalidated the moment the underlying data changes, so it is always correct, and it works across users: a dashboard refreshed by ten people runs once.

You can turn it off to benchmark honestly:

alter session set use_cached_result = false;

+----------------------------------+
| status                           |
|----------------------------------|
| Statement executed successfully. |
+----------------------------------+

Seeing what you ran

select
    query_text,
    warehouse_name,
    execution_time / 1000 as seconds,
    bytes_scanned
from table(information_schema.query_history())
where execution_status = 'SUCCESS'
order by start_time desc
limit 3;

+--------------------------------------------+----------------+---------+---------------+
| QUERY_TEXT                                 | WAREHOUSE_NAME | SECONDS | BYTES_SCANNED |
|--------------------------------------------+----------------+---------+---------------|
| select country_code, count(*) from cust... | BOOKSHOP_WH    |   0.043 |             0 |
| select country_code, count(*) from cust... | BOOKSHOP_WH    |   0.611 |          2048 |
| select count(*) from orders                | REPORTING_WH   |   0.402 |          1024 |
+--------------------------------------------+----------------+---------+---------------+
3 Row(s) produced. Time Elapsed: 0.522s

BYTES_SCANNED = 0 on the cached query — proof it never touched storage. query_history is where every performance and cost investigation in this track starts.

Practice

1. Create a warehouse that suspends after 60 seconds and confirm the setting.

show warehouses like 'BOOKSHOP_WH';

+-------------+---------+---------+---------+--------------+-------------+
| name        | state   | size    | running | auto_suspend | auto_resume |
|-------------+---------+---------+---------+--------------+-------------|
| BOOKSHOP_WH | SUSPENDED | X-Small |       0 |           60 | true        |
+-------------+---------+---------+---------+--------------+-------------+
1 Row(s) produced. Time Elapsed: 0.188s

SUSPENDED with running = 0 means it is costing nothing. auto_resume = true restarts it on the next query, so suspension is invisible apart from a second or two of startup.

2. Query the same table from two different warehouses.

use warehouse bookshop_wh;  select sum(amount) from orders;
use warehouse reporting_wh; select sum(amount) from orders;

+-------------+
| SUM(AMOUNT) |
|-------------|
|      149.45 |
+-------------+
1 Row(s) produced. Time Elapsed: 0.396s

+-------------+
| SUM(AMOUNT) |
|-------------|
|      149.45 |
+-------------+
1 Row(s) produced. Time Elapsed: 0.288s

Identical results with no synchronisation step, because there is only one copy of the data. Contrast this with read replicas, where the same query can legitimately return different answers depending on replication lag.

3. Run a query twice and compare the elapsed times.

Time Elapsed: 0.611s     ← executed
Time Elapsed: 0.043s     ← result cache

Then insert a row and run it a third time:

Time Elapsed: 0.588s

The cache was invalidated by the write. This is why the result cache is safe to leave on — it can never return stale data, unlike an application-level cache you have to expire yourself.

4. Find your most expensive query so far by bytes scanned.

select query_text, bytes_scanned, execution_time
from table(information_schema.query_history())
order by bytes_scanned desc
limit 1;

+----------------------------------------+---------------+----------------+
| QUERY_TEXT                             | BYTES_SCANNED | EXECUTION_TIME |
|----------------------------------------+---------------+----------------|
| select c.country_code, count(*) as o... |          4096 |            847 |
+----------------------------------------+---------------+----------------+
1 Row(s) produced. Time Elapsed: 0.441s

Bytes scanned is the number to watch, not row count. It is what warehouse time is spent on, and the micro-partitions lesson is entirely about making it smaller.

Next: virtual warehouses — sizing, scaling out, and what actually costs money.