I did something that could have disrupted our production queries: I suspended all our Snowflake warehouses during a maintenance window. This action might have caused loading errors in our dashboards. Surprisingly, everything loaded perfectly, with results returning in mere milliseconds. This experience was not a fluke; it marked my first real "aha moment" about how fundamentally different Snowflake's architecture is from that of traditional data warehouses.
After three years as a Snowflake architect, I've realized that truly understanding virtual warehouses isn't just about knowing T-shirt sizes or auto-suspend settings. It's about understanding an architecture that challenges everything we learned from legacy systems.
This is Part 1 of a series where I'll share what I've learned in the trenches. Let's start with the fundamentals. ## The Discovery: Queries Without Compute? Here's what happened: Our BI team ran their morning reports. Warehouses were suspended. Queries succeeded. No compute credits consumed. How?
**Three layers of caching saved us:
1.Result Cache (24 hours) - If someone ran the exact same query in the last 24 hours with no data and parameters (e.g., timezone ) changed then Snowflake returns the cached result. No warehouse needed. No cost.
USE WAREHOUSE COMPUTE_WH;
USE DATABASE SNOWFLAKE_SAMPLE_DATA;
USE SCHEMA TPCH_SF10;SELECT distinct l_partkey FROM LINEITEM;
When you run it for the first time, it will scan the required data as shown below:
When you execute the query again, it will utilize the result cache, as demonstrated below:
2.Metadata Cache - Simple aggregations like COUNT(*) or MIN/MAX on clustered columns? Often answered from metadata alone.
SELECT COUNT(*) AS lineitem_rows FROM LINEITEM;
3.Local Disk Cache - When a warehouse does spin up, frequently accessed data sits in SSD cache for ultra-fast retrieval
If I add another column to the existing query, it will attempt to use the local warehouse cache.
SELECT distinct *l_orderkey, * l_partkey FROM LINEITEM;
This taught me something critical:
In Snowflake, your warehouse isn't your database. It's just a compute engine you rent when you need it.
Please refer to this article, which provides a great starting point for understanding caching in Snowflake.
Snowflake Community
community.snowflake.com
What Actually Is a Virtual Warehouse?
Think of virtual warehouses as ephemeral compute clusters that live completely separately from your data. Traditional warehouse: Compute + Storage = One monolithic system Snowflake warehouse: Compute ← (network) → Storage (separate, scalable independently) This separation is Snowflake's superpower. Your data lives in cloud storage (S3, Azure Blob, GCS). Warehouses are just compute resources that: - Spin up in seconds - Process queries - Shut down automatically - Scale independently of storage You can have 10 warehouses querying the same table simultaneously. Or zero warehouses with your data perfectly safe.
The T-Shirt Sizing Reality Snowflake offers warehouse sizes from X-Small to 6X-Large.
After architecting systems at Agilent, here's what I've learned:
X-Small (1 credit/hour): - Perfect for: Dev/test, lightweight ETL, ad-hoc queries - What surprised me: Handles 80% of analytics workloads just fine - Common mistake: Over-provisioning because "bigger is safer"
Medium to Large (4-8 credits/hour): - The sweet spot for most adhoc complex production workloads - We scale up based on actual performance, not assumptions - One architect rule: If you need Larger size, first ask if you can optimize the query - Real example: We reduced a Medium job to X-Small by fixing a Cartesian join and using clustering
The counterintuitive truth: A bigger warehouse doesn't always mean faster queries. Sometimes it's a sign of inefficient SQL.
Auto-Suspend: The Feature That Saves Careers (and Budgets) Set AUTO_SUSPEND = 60 (seconds) on every warehouse.
No exceptions. In my first month, I found warehouses left running overnight resulting in wasted spend. The dev team didn't realize that unlike traditional databases, Snowflake warehouses don't shut down automatically when queries finish.
My standard configuration:
AUTO_SUSPEND = 60 -- Suspend after 1 minute of inactivity AUTO_RESUME = TRUE -- Restart automatically when needed
One minute feels aggressive, but remember: warehouses restart in seconds, and the result cache covers most repeat queries anyway.
Real impact: This simple change cut our development environment costs by 20%.
Note
- For DevOps, DataOps, and Data Science use cases, Snowflake recommends setting auto-suspension to approximately 5 minutes because the cache is not as important for ad-hoc and unique queries.
- For query warehouses, for example BI and SELECT use cases, Snowflake recommends setting auto-suspend to at least 10 minutes to maintain the cache for users.
Auto-Resume: Trust It When AUTO_RESUME = TRUE,
suspended warehouses wake up automatically when queries arrive. Resume time? Usually 3-5 seconds based on size. Early in my Snowflake journey, I was nervous about this. What if a critical dashboard times out during resume? Three years later: I've never seen this cause a production issue. The resume is fast, and users don't notice. Your anxiety about suspended warehouses is costing you money.
The Misconceptions I Had (And Still Hear)
Misconception #1: "Keep warehouses running for better performance" Reality: Cold start is 3-5 seconds. Keeping a Medium warehouse running 24/7 wastes ~$2,000/month on Enterprise edition.
Misconception #2: "Bigger warehouses = faster queries"
Reality: Badly written queries stay slow regardless of size. Fix the SQL first.
Misconception #3: "We need one big warehouse for everything" Reality: Multiple specialized warehouses provide better isolation and cost control.
Misconception #4: "Warehouse size determines storage capacity" Reality: Storage is completely independent. An X-Small can query petabytes.
The Architect's Perspective: How This Changes Design Understanding warehouses changed how I design data systems:
1. Workload Isolation Instead of one shared warehouse, we run: - ETL_WH (X-Small, auto-suspend 60s) - ANALYTICS_WH (Small, auto-suspend 60s) - REPORTING_WH (Small, multi-cluster for BI tools) - DEV_WH (X-Small, auto-suspend 30s)
Why? A poorly optimized dev query can't impact production reports. Each team pays for what they use.
2. Cost Attribution Tagged warehouses let us track costs by department, project, or team. Finance loves this.
3. Right-Sizing Strategy Start small. Scale up only when performance metrics justify it. We monitor query execution time and queuing, not gut feelings.
4. Embrace Suspension Our warehouses spend 90% of their time suspended. That's not a problem—it's efficient architecture. ## What's Coming in This Series Over the next few weeks, I'll dive deeper: Part 2: Warehouse Optimization & Cost Control (multi-cluster warehouses, scaling policies, cost monitors) Part 3: Advanced Patterns (workload management, query acceleration, clustering, search optimization) Part 4: Monitoring & Troubleshooting (the queries I run daily, how to spot inefficiencies) Part 5: Iceberg Lakehouse Architecture
The Bottom Line
After three years architecting on Snowflake, here's what I tell every new team: Virtual warehouses aren't databases. They're temporary compute resources you rent by the second. The moment you internalize this, everything else clicks into place.
That suspended warehouse isn't "off"—your data is still there, your caches are warm, and your next query is just 3 seconds away from full compute power. Stop treating Snowflake like it's Oracle or HANA. Start treating warehouses like the elastic, ephemeral resources they are.
What's been your biggest "aha moment" with Snowflake?
Comment your cache gotcha! —I learn as much from your experiences as you might from mine.
Let me know when you drop a warehouse from Snowflake. Will the result cache still work?
Found this helpful? Follow me for Part 2 on warehouse optimization and cost control strategies.
Top comments (0)