Course contents

SnowPro Core COF-C03 Study GuidePart 1: ArchitectureThe Three Layers

The Three Layers

The Three Layers

Definition

Snowflake is organized as three independently scalable layers: Database Storage at the bottom, Query Processing (virtual warehouses) in the middle, and Cloud Services on top. Every "where does X happen" question resolves to one of these three.

Cloud Services
the brain
Authentication & RBAC Metadata management Query parsing & optimization Transaction management Result cache
▾ compiles a plan, hands it down ▾
Query Processing
the muscle
ETL_WH · Large BI_WH · Medium, multi-cluster DS_WH · X-Small each independent, no shared cache
▾ every warehouse reads the same copy ▾
Database Storage
the memory
Compressed columnar micro-partitions S3 / Azure Blob / GCS One copy, infinitely scalable

Layer 1 · Database Storage

When you load data, Snowflake reorganizes it into its internal format: compressed, columnar, immutable files called micro-partitions, stored as blobs in the cloud provider's object storage. Key properties:

  • Columnar, optimized for OLAP. Analytical queries touch few columns across many rows, and columnar layout means Snowflake reads only the columns a query references. This is the opposite of row storage, which suits OLTP systems.
  • Fully managed and invisible. You can't list, download, or modify the blobs. The only access path is SQL.
  • Always compressed, always encrypted. AES-256 encryption at rest, automatic compression per column. You pay storage on the compressed size, a flat rate per TB per month.
  • Effectively infinite. Capacity is the cloud provider's object store, so there's no "disk full" in Snowflake.

Layer 2 · Query Processing (Virtual Warehouses)

A virtual warehouse is a cluster of compute nodes (CPU, memory, local SSD) that executes queries. Warehouses are where almost all of the money goes, and Chapter 2 covers them in depth. The architectural facts that belong here:

  • Each warehouse is an independent MPP cluster. It sees the shared storage layer but shares nothing with other warehouses: no memory, no cache, no queue.
  • Warehouses are stateless against the data. Suspending or dropping one loses nothing but its local cache. Data lives in the storage layer.
  • Compute is billed per second (60-second minimum) only while a warehouse runs.
  • Work that scans or transforms data needs a running warehouse: SELECTs that read table data, all DML (INSERT, UPDATE, DELETE, MERGE), COPY INTO in both directions, stored procedures that read or write table data, and queries on materialized views.

Layer 3 · Cloud Services

The brain. A collection of stateless services, run by Snowflake across availability zones, that coordinates everything:

  • Authentication and access control: logins, MFA, SSO, key-pair auth, OAuth, and the entire RBAC privilege model.
  • Metadata management: statistics on every micro-partition (min/max per column, distinct counts, NULL counts), object definitions, load history.
  • Query parsing and optimization: SQL compiles to an execution plan here, before any warehouse touches it.
  • Infrastructure and transaction management: provisioning warehouse nodes, ACID coordination across concurrent workloads.
  • The result cache lives in this layer, which is why cached results survive warehouse suspension (see The Three Caches).

Because this layer holds rich metadata, some "queries" never need a warehouse at all. An unfiltered SELECT COUNT(*) FROM t, MIN or MAX of a column over the whole table, SHOW and DESCRIBE commands, and DDL like CREATE TABLE or ALTER TABLE ... RENAME are answered from metadata alone. Add a WHERE clause and the rows must be read, so a warehouse runs. On a cost line, these show up as free or nearly free.

The life of a query

Trace one SELECT through the stack and the layer boundaries stop being abstract.

  1. Your client authenticates against cloud services. Role resolved, session established. No warehouse involved yet.
  2. The SQL text arrives and cloud services checks the result cache. Hit: done, zero credits, and nothing below this layer ever wakes up.
  3. Miss: the optimizer parses the text, checks privileges on every object referenced, and compiles a plan using micro-partition statistics. Filter pruning happens here, at compile time, before any compute is touched. Some more pruning can happen while the query runs, for example once a join's values are known, but the plan starts from this list.
  4. The plan is dispatched to your session's warehouse (auto-resuming it if needed). Nodes read partitions, from local SSD when cached, from object storage otherwise, apply the query's filters to the rows, and execute the rest of the plan.
  5. The result returns through cloud services, which writes it into the result cache on the way out.
One query's path through the three layers. 1: A query reaches cloud services, which authenticates the user and resolves the role. 2: Cloud services checks the result cache; a hit returns the stored result with no compute. 3: On a miss the optimizer parses the SQL, checks privileges and prunes micro-partitions using metadata, producing a plan. 4: The plan goes to the warehouse, which resumes if needed and reads only the planned micro-partitions from storage. 5: Rows go back through cloud services, which returns them and stores the result for 24 hours. Cloud services steps are usually free; the warehouse step costs credits.CLOUD SERVICES · THE BRAINCOMPUTE · THE MUSCLESTORAGE · THE MEMORY1 · FREEQuery arrivesauthenticate,resolve role2 · FREEResult cachehit or miss?3 · FREEOptimizerprivileges, pruning,plan5 · FREEReturn resultrows to the client,result cached 24 h4 · CREDITSWarehouse BI_WHruns the planSTORAGEMicro-partitionsonly the planned onesmisshit: stored result, no computeplanreadsrows One query's path through the three layers. 1: A query reaches cloud services, which authenticates the user and resolves the role. 2: Cloud services checks the result cache; a hit returns the stored result with no compute. 3: On a miss the optimizer parses the SQL, checks privileges and prunes micro-partitions using metadata, producing a plan. 4: The plan goes to the warehouse, which resumes if needed and reads only the planned micro-partitions from storage. 5: Rows go back through cloud services, which returns them and stores the result for 24 hours. Cloud services steps are usually free; the warehouse step costs credits.CLOUD SERVICES · THE BRAINCOMPUTE · THE MUSCLESTORAGE · THE MEMORYhit: stored result,no computemissplanreadsrows1 · FREEQuery arrivesauthenticate,resolve role2 · FREEResult cachehit or miss?5 · FREEReturn resultrows to the client,result cached 24 h3 · FREEOptimizerprivileges,pruning, plan4 · CREDITSWarehouse BI_WHruns the planSTORAGEMicro-partitionsonly the planned ones
Figure 1.3 One query, five steps, three layers. The numbers match the list above. Everything in the cloud services lane is usually free; only the warehouse step costs credits, and a result cache hit skips it entirely.

Notice where the intelligence sits. The warehouse never decides anything; it executes a plan handed down from above. That's why suspending warehouses loses no smarts, and why two warehouses of the same size run the same query identically: the plan was made upstairs.

Exam trap · what needs a running warehouse?

Learn the split cold:

  • Needs a warehouse: any SELECT that reads table rows, filtered or not, all DML, COPY INTO (load and unload), a stored procedure that reads or writes table data, querying a materialized view, CREATE TABLE AS SELECT.
  • No warehouse needed: COUNT/MIN/MAX from metadata, SHOW / DESCRIBE / LIST commands, DDL that only touches definitions (ALTER TABLE RENAME, DROP), result-cache hits, and downloading files with GET.

The trap answers pair "altering a table" or "listing files in a stage" (metadata) against "running a procedure that loads data" and "querying a materialized view" (compute). The last two are the correct picks when asked what requires a running warehouse.

Cost model of the three layers

LayerWhat you payHow it's measuredTypical share of the bill
StorageFlat $/TB/monthCompressed bytes, averaged daily, including Time Travel and Fail-safeSmall (often <10%)
ComputeCredits per second, 60s minimum per resumeWarehouse size doubles credits per size stepThe bulk of it
Cloud servicesCredits, but only above a thresholdBilled only for the portion exceeding 10% of daily compute creditsUsually $0

When the 10% stops being theoretical

Most accounts never see a cloud services line because normal query traffic stays far under the threshold, and heavy warehouse use raises the free allowance with it. The patterns that break through are all high-frequency and metadata-heavy: an orchestrator polling SHOW TASKS every few seconds across hundreds of schemas, a microservice doing single-row INSERTs thousands of times a minute (each one pays compilation overhead), or a BI tool refreshing INFORMATION_SCHEMA on every dashboard open. The fix is never a bigger warehouse, since the cost isn't in execution. Batch the inserts, cache the catalog calls, lengthen the polling interval.

Memory aid

"Brain, muscle, memory." Cloud services is the brain (thinks, plans, remembers who you are), warehouses are the muscle (do the heavy lifting, tire out, cost the most), storage is the memory (cheap, permanent, compressed). When a question asks where something happens, classify it: is it thinking, lifting, or remembering?

Key takeaways
  • Three layers, each independently scalable: storage, compute, cloud services.
  • The optimizer, security, metadata, and result cache live in cloud services.
  • Cloud services compute is free up to 10% of daily warehouse consumption.
  • Metadata answers unfiltered COUNT/MIN/MAX and SHOW/DESCRIBE with no warehouse.