If you’re running heavy machine learning workloads, complex UDFs, or even memory-intensive analytics in Snowflake, you’ve likely encountered the dreaded “out of memory” error code before. Standard warehouses work perfectly for traditional SQL queries, but if you’re taking things to the next level with training ML models or processing massive datasets with custom Python code, you’re going to struggle.
That’s why many turn to Snowpark-optimized warehouses. Think of these as the bulkier version of a standard warehouse. Snowpark-optimized warehouses can handle workloads that need serious memory and computational horsepower.
There’s just one drawback: they cost 1.5 times more per credit hour. So if you’re not using Snowpark-optimized warehouses correctly, you might be burning budget for no performance gain.
Our guide will walk you through everything there is to know about Snowpark-optimized warehouses:
- What Snowpark optimized warehouses are
- If they’re worth the investment
- When to stick with standard warehouses
What Are Snowpark-Optimized Warehouses?
Snowpark-optimized warehouses are specialized compute resources that offer 16 times more memory per node and 10 times more local cache.
Okay, but what does that actually mean? Let’s put this in practical terms. A Medium standard warehouse usually gets you 16GB of memory per node. A Medium Snowpark-optimized warehouse? That gets you 256GB. That means no more having to worry about failing workloads.
Snowpark-optimized warehouses excel at single-node operations that need huge amounts of memory, like training machine learning models with stored procedures, running complex user-defined functions, or performing memory-intensive data transfers.
The real difference between a standard Snowflake warehouse and a Snowpark-optimized one is resource allocation. Standard warehouses optimize for parallel query execution. Snowpark-optimized warehouses, on the other hand, focus on providing maximum resources to individual compute nodes, which is why they’re the perfect choice for operations that aren’t easily parallelized but still need substantial memory.
When Should You Use Snowpark-Optimized Warehouses?
We’ve already touched on this some, but let’s lay out some clear situations when it’s the right choice to invest more in Snowpark-optimized warehouses – and when you’ll end up losing money.
Use Snowpark-optimized warehouses when you’re running:
- Machine learning training workloads: If you’re training models using Snowpark ML or custom algorithms within stored procedures, you’ll want Snowpark’s extra memory to prevent out-of-memory errors and maintain a rapid speed for training.
- Complex UDFs and UDTFs: User-defined functions that use large amounts of data in each row or maintain substantial state need Snowpark’s extra memory – especially if your UDFs are already causing your standard warehouses to spill disk.
- Memory-intensive analytics: Operations like large-scale exports from object storage, complex window functions over massive datasets, or in-memory data transformations.
- Heavy Python dependencies: If your code loads large libraries or datasets from memory (like pandas DataFrame with millions of rows), Snowpark’s added memory means you can avoid constant garbage collection and swap operations.
You should stick with Snowflake’s standard warehouse if you’re running:
- Traditional SQL queries: SELECT, JOIN, GROUP BY, and aggregation queries perform identically on standard warehouses, so no change needed if these are your standard faire.
- Highly parallelizable workloads: If your workload scales horizontally across multiple nodes, stick with a standard warehouse.
- Simple data loading or unloading: COPY INTO operations don’t need Snowpark-level optimization.
- Budget-constrained exploratory work: At 1.5 times the cost, Snowpark-optimized warehouses should only be used for workloads where the performance justifies the expenses.
Ultimately, here’s how you should decide if it’s time to make the move to a Snowpark-optimized warehouse: if you’re encountering memory limitations or benchmarking can prove that the cost increase is justified by the performance gains, it’s time. If your query or workload is still performing well on a standard warehouse with no memory errors, then you should hold off.
Snowpark-Optimized Warehouse Configuration Options
If you do believe you’re ready for a Snowpark-optimized warehouse, you’re in luck. These warehouses come with several configuration options that let you fine-tune them to perfection for your specific workload requirements. The main levers you can pull here are:
- Warehouse sizes
- Resource constraints
- Max concurrency level
- Auto-suspend and auto-resume
Let’s go into a bit more detail on how you can adjust each of these configurations to create the optimal Snowpark-optimized warehouse setup below.
Warehouse Sizes
Snowpark-optimized warehouses support sizes from Medium through 6X-Large. Notice that the X-Small and Small sizes are not available, which makes sense considering the workloads these warehouses are designed for.
Each size increase doubles the compute power and credit consumption, the same as standard warehouses – only the absolute credit cost is 1.5 times higher. That means that a Medium Snowpark-optimized warehouse consumes 6 credits per hour compared to the 4 credits for a standard Medium warehouse.
Resource Constraints
The RESOURCE_CONSTRAINT parameter lets you specify memory and CPU architecture combinations. You can use options such as:
- MEMORY_16X (default): Provides 16x memory compared to standard warehouses
- MEMORY_16X_X86: Same memory as MEMORY_16X but specifically uses x86 architecture
- MEMORY_64X and MEMORY_64X_X86: Provides 64x memory (1TB options)
The x86 variants are useful when your workload depends on specific CPU architectures or when you need compatibility to match certain libraries made for x86 processors.
Max Concurrency Level
The MAX_CONCURRENCY_LEVEL parameter is how to control how many concurrent queries can execute on the warehouse at once. The default is 8, but for optimal resource usage – especially for memory-intensive Snowpark workloads – setting this to 1 is recommended.
ALTER WAREHOUSE snowpark_opt_wh
SET MAX_CONCURRENCY_LEVEL = 1;Setting this to 1 means that one Snowflake operation gets access to all the CPU and memory resources on the node, which prevents resource contention. And in the case of production ML training workloads, it also improves consistency and performance.
Auto-Suspend & Auto-Resume
Though these settings are actually the same as for standard warehouses, they still deserve special attention because of the higher credit costs. Setting an aggressive auto-suspend timeout (60 seconds is a good place to start) can help cut back on unnecessary billing:
AUTO_SUSPEND = 60
AUTO_RESUME = TRUEPro tip: Snowpark-optimized warehouses can take longer to start or resume compared with standard warehouses, so be careful when balancing cost savings with latency tolerance.
How to Create a Snowpark-Optimized Warehouse
Making a Snowpark-optimized warehouse is actually pretty easy. Here’s a basic example:
CREATE OR REPLACE WAREHOUSE ml_training_wh
WITH
WAREHOUSE_SIZE = 'MEDIUM'
WAREHOUSE_TYPE = 'SNOWPARK-OPTIMIZED';For production workloads with specific requirements, you might want to use a more comprehensive configuration like this:
CREATE OR REPLACE WAREHOUSE production_ml_wh
WITH
WAREHOUSE_SIZE = 'LARGE'
WAREHOUSE_TYPE = 'SNOWPARK-OPTIMIZED'
RESOURCE_CONSTRAINT = 'MEMORY_16X_X86'
MAX_CONCURRENCY_LEVEL = 1
AUTO_SUSPEND = 60
AUTO_RESUME = TRUE
INITIALLY_SUSPENDED = TRUE
STATEMENT_TIMEOUT_IN_SECONDS = 3600
COMMENT = 'Optimized for ML model training workloads';You can also create warehouses using the Snowpark Python API if you’re working programmatically:
from snowflake.core import CreateMode
from snowflake.core.warehouse import Warehouse
my_wh = Warehouse(
name="ml_training_wh",
warehouse_size="MEDIUM",
warehouse_type="SNOWPARK-OPTIMIZED"
)
root.warehouses.create(my_wh, mode=CreateMode.or_replace)Note: Resource constraints aren’t currently supported with the Python API, so you’ll need to use SQL to configure those parameters.
How to Modify a Snowpark-Optimized Warehouse
You know how to set up your new Snowpark-optimized warehouse, but you might find it’s not a perfect fit – yet. Here are a couple of tricks you can use to adjust the warehouse.
Just remember: you can modify warehouse properties while the warehouse is running or suspended, but there are important cost considerations you should keep in mind before doing so.
Say you want to change your warehouse type while running, using something like this:
ALTER WAREHOUSE my_warehouse
SET WAREHOUSE_TYPE = 'SNOWPARK-OPTIMIZED'
RESOURCE_CONSTRAINT = 'MEMORY_16X';If you want to modify your warehouse’s type or resource constraint, existing queries continue running on the original configuration, and new queries use the new setup. That means you’ll be billed for both sets of compute resources until the old queries finish.
For cost optimization, you’ll want to suspend the warehouse first:
ALTER WAREHOUSE my_warehouse SUSPEND;
ALTER WAREHOUSE my_warehouse
SET WAREHOUSE_TYPE = 'SNOWPARK-OPTIMIZED'
RESOURCE_CONSTRAINT = 'MEMORY_16X';A couple of other modifications include:
- Modifying memory and CPU architecture:
ALTER WAREHOUSE ml_training_wh
SET RESOURCE_CONSTRAINT = 'MEMORY_64X_X86';- Scaling size up or down:
ALTER WAREHOUSE ml_training_wh
SET WAREHOUSE_SIZE = 'XLARGE';- Converting back to standard: Use this if benchmarking shows that your workload won’t benefit from Snowpark optimization and you want to run on a standard warehouse.
ALTER WAREHOUSE my_warehouse SUSPEND;
ALTER WAREHOUSE my_warehouse
SET WAREHOUSE_TYPE = 'STANDARD'
RESOURCE_CONSTRAINT = 'STANDARD_GEN_2';Snowpark-Optimized Warehouses FAQ
How does billing work for Snowpark-optimized warehouses?
Snowpark-optimized warehouses use the same per-second billing model as standard warehouses (with a 60-second minimum each time they start), but they consume 1.5 times the credits.
Remember: you’ll only get billed for the time you’re actually using, so use aggressive auto-suspend settings.
What’s the regional availability?
Snowpark-optimized warehouses are available in all Snowflake regions across AWS, Azure, and GCP Premium Tier, but the 1TB memory options (MEMORY_64X and MEMORY_64X_X86) are currently in preview and only available on AWS.
Can I use Snowpark-optimized warehouses for regular SQL queries?
Yes, you can, but you probably shouldn’t. Regular SQL queries won’t benefit from the additional memory, which means you’ll be paying 1.5 times as much for the same performance. There are rare occasions when extra memory might help, such as with extremely complex joins on massive datasets, but always test before committing.
Do Snowpark-optimized warehouses support query acceleration?
No, query acceleration is not supported on Snowpark-optimized warehouses. Query acceleration is designed for standard warehouses running traditional SQL workloads, and won’t work on the single-node, memory-intensive setup Snowpark uses.
Why do Snowpark-optimized warehouses take longer to start?
The increased resource provisioning – especially the larger memory allocation – takes additional setup time. Standard warehouses might only take seconds, but Snowpark-optimized warehouses can take 30 to 60 seconds longer.
Automating Your Snowflake Optimization with Yuki
You know how time-consuming and tedious warehouse management and optimization are by this point, which is why many organizations have turned to a plug-and-play solution like Yuki. Yuki will automatically optimize your SQL queries, warehouses, and compute resources based on real-time demand – no development lift needed.
Our customers have seen impressive results:
- 37.6% average cost savings across their Snowflake environments
- 500M+ queries optimized daily with intelligent automation
- 30% fewer clusters needed because of smart resource allocation
Yuki’s automated platform means you don’t have to waste time constantly monitoring query performance, adjusting warehouse sizes, and choosing between standard and Snowpark-optimized warehouses. We’ll take care of those decisions so you can focus on what your business actually needs.
Ready to stop overpaying for Snowflake compute? Get started with Yuki today, and see how much you could be saving.
