Snowflake Architecture Basics

Separation of Storage and Compute

Snowflake's architecture decouples three layers: storage, compute, and cloud services. Unlike traditional databases where storage and compute scale together on fixed hardware, Snowflake stores data centrally in cloud object storage (S3, Azure Blob, or GCS) while compute runs on elastic virtual warehouses that spin up and down independently.

This separation enables powerful flexibility. You can suspend warehouses overnight to save credits while data remains available. You can run a large transformation on an XL warehouse without affecting analysts querying on a separate XS warehouse. You can clone a terabyte table in seconds because clone metadata points to the same micro-partitions without copying bytes.

Understanding this separation is essential for cost control and workload isolation.

Storage Layer and Micro-Partitions

Snowflake automatically divides tables into micro-partitions— contiguous units of storage roughly 50–500 MB compressed. Metadata about each micro-partition (min/max values, row counts, NULL counts) enables aggressive pruning at query time.

When you filter WHERE order_date = '2024-01-15', Snowflake skips micro-partitions that cannot contain that date. Good clustering and partition-friendly filter patterns dramatically reduce bytes scanned and therefore cost.

Storage is billed separately from compute. Data stored in Snowflake incurs monthly storage charges; time travel and fail-safe add retention costs. Monitor storage growth from raw landing zones, undropped staging tables, and abandoned dev schemas.

Compute Layer: Virtual Warehouses

Virtual warehouses are MPP compute clusters that execute queries and DML. They come in sizes from X-Small to 6X-Large, each doubling compute resources and credit consumption per hour.

Key properties:

• Warehouses are independent—no shared compute between them
• Multi-cluster warehouses auto-scale for concurrency (Enterprise edition)
• Suspend when idle to stop credit burn; auto-suspend settings matter

Queries compete for resources within a warehouse. A runaway transformation can slow interactive dashboards on the same warehouse—isolate workloads by warehouse assignment.

Cloud Services Layer

The cloud services layer handles authentication, metadata, query optimization, transaction management, and infrastructure coordination. Most organizations consume cloud services credits within the free daily allowance (typically 10% of daily compute).

You interact with this layer indirectly through the Snowflake web UI, JDBC/ODBC drivers, and SQL commands like SHOW and INFORMATION_SCHEMA queries.

Data Sharing and Marketplace

Snowflake's architecture enables secure data sharing without copying data to consumers. Provider accounts share read-only access; consumers query shared data in their own account. This pattern supports vendor data feeds, cross-org analytics, and data products.

Understand sharing implications for governance: shared data still requires classification and access review on the consumer side.

Key Takeaways

• Storage (micro-partitions), compute (virtual warehouses), and services are independently scaled and billed.
• Micro-partition pruning rewards filter-friendly table design and clustering.
• Isolate workloads using separate warehouses to prevent interference.
• Cloning and time travel leverage storage architecture—use them for dev and recovery.

Reflection

How does your team separate dev and prod workloads today? Are transformations and interactive queries sharing the same warehouse?

Designing a Warehouse Strategy

Virtual warehouses are the primary cost and performance lever in Snowflake. A thoughtful warehouse strategy balances responsiveness, isolation, and credit consumption.

Workload-Based Warehouse Assignment

Assign warehouses by workload type, not by team alone:

Workload	Warehouse profile	Notes
Interactive BI	XS–S, auto-suspend 60s	Low latency, bursty
dbt transformations	M–L, auto-suspend 300s	Sustained, batch
Data science	L–XL, manual suspend	Variable, heavy
ETL ingestion	S–M, dedicated	Predictable schedule

Mixing BI and heavy ETL on one warehouse creates unpredictable latency. Executives refreshing dashboards compete with nightly merges.

Sizing and Scaling

Start smaller and scale up when queries queue consistently. Doubling warehouse size doubles credit burn per hour but often reduces total runtime for heavy queries—total cost may be similar with better SLA.

Multi-cluster warehouses (Enterprise) add clusters for concurrent query spikes. Useful for BI tools with many simultaneous users; less useful for single large batch jobs.

Monitor QUERY_HISTORY for queuing, spillage to remote disk, and partition scans. Queries spilling indicate undersized warehouses or poorly written SQL.

Auto-Suspend and Auto-Resume

Auto-suspend stops credit consumption when a warehouse is idle. Set aggressive suspend for dev warehouses (60 seconds) and moderate for production ETL (300 seconds) to avoid thrashing on job gaps.

Auto-resume adds latency to the first query after suspend—acceptable for batch, noticeable for interactive. BI warehouses often keep auto-suspend short but not zero.

Resource Monitors

Resource monitors cap credit consumption by warehouse, account, or interval. Set soft limits (notify) and hard limits (suspend warehouse) to prevent runaway costs from rogue queries or infinite loops.

Assign monitors to dev accounts especially—experimentation is the most common source of surprise bills.

Query Optimization Basics

Warehouse performance also depends on SQL quality:

• Select only needed columns, not SELECT *
• Filter early; avoid functions on filtered columns that prevent pruning
• Use appropriate join order and statistics (ANALYZE TABLE where supported)
• Cluster large tables on common filter columns

Review expensive queries weekly using QUERY_HISTORY ordered by total elapsed time or bytes scanned.

Key Takeaways

• Separate warehouses by workload type for isolation and predictable SLAs.
• Right-size warehouses using query history; watch for spillage and queuing.
• Configure auto-suspend and resource monitors to control costs.
• Optimize SQL alongside warehouse sizing.

Reflection

Pull your top five expensive queries from last week. Do they share a warehouse with conflicting workload types?

Snowflake FinOps for Practitioners

Snowflake's elastic model makes overspending easy without visibility. FinOps for Snowflake combines architectural choices, operational discipline, and regular review cycles.

Understanding the Credit Model

Credits bill for compute (virtual warehouses) and cloud services (beyond free allowance). Storage bills per TB per month. Data transfer and replication add ancillary costs.

Warehouse credit rates increase with size. An X-Large warehouse consumes 8 credits per hour; 6X-Large consumes 512. Running oversized warehouses "just in case" burns budget.

Use the Snowflake billing views (WAREHOUSE_METERING_HISTORY, STORAGE_USAGE) or account usage schema to attribute spend by warehouse, user, and query tag.

Query Tags and Cost Attribution

Apply query tags in dbt and orchestration tools:

ALTER SESSION SET QUERY_TAG = 'dbt:prod:fct_orders';

Tags enable chargeback reports: which team, job, or model consumed credits. Without tags, optimization debates lack data.

Storage Lifecycle Management

Implement lifecycle policies:

• Drop obsolete dev tables and clones
• Move cold raw data to lower-cost external stages if query frequency is low
• Set data retention policies for time travel where business allows

Transient tables skip fail-safe retention—use for intermediate results that can be rebuilt.

Caching and Result Reuse

Snowflake caches query results for 24 hours. Identical queries hit cache at zero incremental cost. Structure dashboards to reuse queries where possible.

Warehouse-local disk cache speeds repeated scans of the same micro-partitions. Suspending warehouses clears local cache—balance suspend aggressiveness with cache benefits for repetitive workloads.

Weekly FinOps Review

Establish a weekly fifteen-minute review:

1. Top warehouses by credits
2. Top queries by elapsed time and bytes scanned
3. Storage growth week-over-week
4. Users with anomalous individual spend

Assign actions: resize warehouse, optimize query, add resource monitor, drop unused table.

Key Takeaways

• Attribute spend with query tags and account usage views.
• Right-size warehouses; oversized defaults are a common waste pattern.
• Manage storage lifecycle with drops, transient tables, and retention policies.
• Run a weekly FinOps review with assigned follow-up actions.

Reflection

Does your organization know which dbt models or teams drive the most Snowflake spend? What would you tag first to get visibility?