Understanding Snowflake Virtual Warehouses
Source: Dev.to
Part 1 – The Discovery: Queries Without Compute?
I suspended all our Snowflake warehouses during a maintenance window.
Our BI team ran their morning reports, the warehouses were suspended, yet the queries succeeded, returned results in milliseconds, and no compute credits were consumed.
What saved us? Three layers of caching
| Cache Layer | What it does | Example |
|---|---|---|
| Result Cache (24 h) | Returns the exact result of a query that ran in the last 24 h if the query text, data, and parameters (e.g., timezone) are unchanged. No warehouse needed → no cost. | sql\nUSE WAREHOUSE COMPUTE_WH;\nSELECT DISTINCT l_partkey FROM LINEITEM;\n First run → scans data.Second run → hits result cache. |
| Metadata Cache | Simple aggregations (e.g., COUNT(*), MIN/MAX on clustered columns) can be answered from metadata alone. | sql\nSELECT COUNT(*) AS lineitem_rows FROM LINEITEM;\n |
| Local Disk Cache | When a warehouse spins up, frequently accessed data is cached on the warehouse’s SSD for ultra‑fast retrieval. | sql\nSELECT DISTINCT l_orderkey, l_partkey FROM LINEITEM;\n Adding another column forces Snowflake to use the local SSD cache. |
Key Insight – In Snowflake, your warehouse isn’t your database. It’s a compute engine you rent only when you need it.
Snowflake Architecture vs. Traditional Warehouses
Traditional Warehouse: Compute + Storage = One monolithic system
Snowflake Warehouse: Compute ← (network) → Storage (separate, independently scalable)- Data lives in cloud storage (S3, Azure Blob, GCS).
- Virtual warehouses are ephemeral compute clusters that:
- Spin up in seconds
- Process queries
- Shut down automatically (auto‑suspend)
- Scale independently of storage
You can have 10 warehouses querying the same table simultaneously—or zero warehouses with your data perfectly safe.
Sizing & Cost‑Control Lessons
| Size | Credits / hour | Ideal Use‑Case | Surprising Finding |
|---|---|---|---|
| X‑Small | 1 | Dev/Test, lightweight ETL, ad‑hoc queries | Handles ~80 % of analytics workloads |
| Medium–Large | 4–8 | Most complex production workloads | Scaling up should be a last resort; first try query optimisation |
Counter‑intuitive truth: A bigger warehouse doesn’t always mean faster queries – it often signals inefficient SQL.
Auto‑Suspend / Auto‑Resume Settings
-- My standard configuration
ALTER WAREHOUSE
SET AUTO_SUSPEND = 60 -- suspend after 1 minute of inactivity
AUTO_RESUME = TRUE; -- automatically restart when needed- Result: Cutting dev‑environment costs by ~20 %.
- Snowflake recommendations:
- DevOps / DataOps / Data Science: auto‑suspend ≈ 5 min (cache less critical).
- BI / SELECT workloads: auto‑suspend ≥ 10 min (preserve cache for users).
Resume time: Typically 3–5 seconds (size‑dependent). In practice, I’ve never seen this cause a production issue.
Common Misconceptions
| # | Misconception | Reality |
|---|---|---|
| 1 | “Keep warehouses running for better performance.” | Cold start is only 3–5 s. Keeping a Medium warehouse 24/7 wastes ≈ $2,000 / month on Enterprise edition. |
| 2 | “Bigger warehouses = faster queries.” | Bad SQL stays slow regardless of size. Optimise the query first. |
| 3 | “One big warehouse for everything.” | Multiple specialised warehouses give isolation and cost control. |
| 4 | “Warehouse size determines storage capacity.” | Storage is independent – an X‑Small can query petabytes. |
Practical Strategies
1. Workload Isolation
| Warehouse | Size | Auto‑Suspend | Purpose |
|---|---|---|---|
ETL_WH | X‑Small | 60 s | Extract/Transform/Load jobs |
ANALYTICS_WH | Small | 60 s | Ad‑hoc analytics |
REPORTING_WH | Small (multi‑cluster) | 60 s | BI tools, high concurrency |
DEV_WH | X‑Small | 30 s | Development & testing |
Result: A poorly optimised dev query can’t impact production reports; each team pays only for what they use.
2. Cost Attribution
Tag warehouses (e.g., department=finance, project=xyz) to track spend by team or project. Finance loves the visibility.
3. Right‑Sizing Strategy
- Start small.
- Monitor query performance & credit usage.
- Scale up only when metrics show a genuine need.
- Always revisit the SQL before adding more compute.
Further Reading
- Snowflake Caching Overview – A great starting point for understanding caching in Snowflake.
After three years as a Snowflake architect (including time at Agilent), these are the fundamentals that changed my perspective on virtual warehouses. Stay tuned for Part 2, where I’ll dive deeper into query optimisation and multi‑cluster warehouses.
Snowflake Warehouse Best Practices
“Virtual warehouses aren’t databases. They’re temporary compute resources you rent by the second.”
The moment you internalise this, everything else clicks into place.
4. Embrace Suspension
Our warehouses spend 90 % of their time suspended. That’s not a problem—it’s an efficient architecture.
- A suspended warehouse isn’t “off”: your data is still there, caches stay warm, and the next query is only ~3 seconds away from full compute power.
- Stop treating Snowflake like Oracle or HANA. Treat warehouses as elastic, ephemeral resources.
What’s Coming in This Series
Over the next few weeks, I’ll dive deeper into:
- Part 2 – Warehouse Optimization & Cost Control
- Multi‑cluster warehouses
- Scaling policies
- Cost monitors
- Part 3 – Advanced Patterns
- Workload management
- Query acceleration
- Clustering & search optimisation
- Part 4 – Monitoring & Troubleshooting
- The queries I run daily
- How to spot inefficiencies
- Part 5 – Iceberg Lakehouse Architecture
Your Turn
What’s been your biggest “aha moment” with Snowflake?
Found this helpful? Follow me for Part 2 on warehouse optimisation and cost‑control strategies.