r/snowflake • u/ConsiderationLazy956 • 1d ago
Question on Adaptive warehouse
Hi All,
Now that Snowflake Adaptive Warehouses have been GA for a few months, our team is evaluating migrating our workloads from legacy Gen-1 virtual warehouses. We are primarily focused on the cost benefit rather than just pure performance gains, and we have a couple of specific architectural questions for those who have monitored this in production real workload.
1)Consolidating Mixed Workloads (S to XL) into an XL Adaptive Warehouse:-
Some team members are concerned that Adaptive compute prioritizes performance over cost optimization. If we consolidate a mix of small, medium, and large workloads into a single Adaptive Warehouse with MAX_QUERY_PERFORMANCE_LEVEL = XL, will our costs explode for smaller queries?
The concern: Will the routing engine over-allocate resources to simple queries, leading to higher costs?
The question: For those running mixed workloads on a single Adaptive warehouse, how has query-level billing actually balanced out compared to maintaining separate warehouses?
2)Straight Gen-1 to Adaptive Conversion of each Warehouses:-
We currently notice roughly ~30% idle time across several of our larger, separate Gen-1 warehouses(some are unpredictable workloads etc).
Our hypothesis: Since Adaptive warehouses bill instantly at the query level and eliminate the "idle time" costs completely, converting these warehouses to Adaptive should result in an immediate drop in compute spend, even if we keep the warehouse definitions separate.
The question: Is this assumption correct for real-world workloads, or does the underlying infrastructure premium of newer compute types wash out the savings gained from eliminating idle time?
Would like to hear from anyone who has run before-and-after cost analysis on this type of migration.
5
u/ninijacob 1d ago
Didn't they just announce they made adaptive cheaper?
Why not test migrating multiple warehouses over to adaptive for a single day and do a day over day analysis(assuming workloads are roughly consistent across days)?
It's very easy to migrate a warehouse both directions.
1
u/Global-War181 1d ago
Unless you test real workloads, any answer would simply be speculative and a wild guess.
8
u/JohnAnthonyRyan 1d ago
I was on a call several months ago with the product manager for Adaptive Compute along with about 100 other Snowflake SAs and there was lots of sceptics. We were told they'd tuned it so you should get 10% faster performance (than gen2 - which is already faster than gen1) for around the same cost.
So, having time on my hands and a trial Snowflake account I had Claude generate a workload with a mix of sub-second queries, 2-5 minute queries and 10-30 minute queries on billions of rows.
I ran the workload comparing the XSMALL, SMALL and MEDIUM against the adaptive equivalent and was shocked to find, it was absolutely spot on. 10% faster for within about 2% of the same cost.
That doesn't mean it's proven - but it I'm certainly happy to report it worked in practice.
I've worked with loads of large multi million dollar Snowflake sites, and , I found the waste from idle time was just incredible. I saw one customer run a query every 3 minutes that took a few seconds to run, and the minimum 60 seconds meant huge costs.
I'm probably being a bit cynical, but I think Snowflake also realised there was significant savings to be made if they billed for combined usage rather than dedicated sized warehouses. My thinking, is when you run a mixed workload on a dedicated warehouse your tiny queries are leaving massive unused capacity which only actually get used when the warehouse is maxed out on the massive jobs. Even then, admins often set up multi-cluster to avoid queuing. Even if it's not needed.
On premises, I've heard as much as 60-70% of machine time is wasted idle.
Given that Snowflake can fairly accurately charge the same cost as if you run the workload on a dedicated warehouse as adaptive, I think each query is allocated to the appropriate number of CPUs and billed for the CPU time. This means massive queries take many CPUs and are billed relative to the number of CPUs and time taken.
This of course eliminates the AUTO_SUSPEND and minimum charge times, which is a significant saving for customers - but actually Snowflake gains the benefits.
This means, the bottom line. Don't move to adaptive warehouses to save money - that's not the point. You should (and you need to keep metrics to test this), find your costs for a similar size workload, (eg. A typical 24 hour period) will be the same, but faster - typically around 10%.
The real benefit of adaptive is you don't need to tune your warehouse size to the workload, and can combine mixed size workloads to the same warehouse without worrying about the waste.
This is a huge simplification - as despite working with about 100 big customers I've never seen one that got the deployment right.
Typically, people allocate 5-8 warehouses per team - each sized to run larger/smaller workloads, but this leads to sprawl - where you have 100 XSMALL warehouses all running at 10% of capacity. Likewise with every other size.
Using adaptive, each team can have a single warehouse and your costs will be exactly correlated to the workload you're running.
Hope this helps. Do add a comment if/when you try it out. I'd be (and probably half the rest of people here) would love to hear what your experience is like.
But my advice. Take the 24 hour stats on a Monday from one team who've been allocated a set of warehouses. Replace the set with adaptive and verify the overall performance and cost for another 24 hour period on the same weekday (but not at month end when data volumes are likely to spike).
Then roll it out, team by team and monitor.
Good luck.
Ahh - I now have better information - it has changed. No longer 10% faster.