Snowpark-Optimized Warehouses
Historically, Virtual Warehouses were strictly optimized for SQL queries. However, with the introduction of Snowpark (which allows Python, Java, and Scala code to run directly inside Snowflake), new types of workloads emerged.
Data Scientists training Machine Learning models or Data Engineers processing massive JSON payloads via Python User-Defined Functions (UDFs) found that standard warehouses lacked sufficient RAM. To solve this, Snowflake introduced Snowpark-Optimized Warehouses.
Topics Covered
- Standard vs Snowpark-Optimized
- Cost and Credit Differences
- When to Use Which
- Creating and Monitoring
Standard vs Snowpark-Optimized
A Virtual Warehouse is fundamentally a cluster of EC2 (AWS) or VM (Azure) instances.
- A Standard Warehouse balances CPU and Memory. It is designed to quickly scan storage and perform relational joins.
- A Snowpark-Optimized Warehouse provides 16x more memory per node compared to a standard warehouse of the same size.
If your Python code attempts to load a massive dataset into a Pandas DataFrame inside a standard warehouse, it will likely hit an Out-Of-Memory (OOM) error and crash. A Snowpark-Optimized warehouse provides the massive RAM footprint required to hold those DataFrames in memory.
Cost and Credit Consumption Rates
Because you are provisioning hardware with significantly more memory, Snowpark-Optimized warehouses are more expensive.
A Snowpark-Optimized warehouse consumes credits at 1.5x the rate of a Standard warehouse.
| Warehouse Size | Standard Credits/Hour | Snowpark-Optimized Credits/Hour |
|---|---|---|
| Medium | 4 | 6 |
| Large | 8 | 12 |
| X-Large | 16 | 24 |
Note: Snowpark-Optimized warehouses are not available in sizes X-Small or Small.
When to Use Which
Do NOT use a Snowpark-Optimized warehouse for standard SQL workloads. It will not make your SQL queries run any faster; it will only make them 50% more expensive.
Use Standard Warehouses For:
SELECT,JOIN,MERGE,COPY INTO- Standard BI Dashboards
- Basic Snowpark DataFrame operations that are pushed down to SQL
- Lightweight Python UDFs
Use Snowpark-Optimized Warehouses For:
- Machine Learning Training: e.g., fitting a scikit-learn or XGBoost model on millions of rows.
- Heavy Python UDFs: e.g., using a massive NLP library to analyze text.
- Memory-Intensive Transformations: Operations that cannot be easily pushed down to SQL and require loading huge datasets into executor memory.
Hands-On: Managing Snowpark-Optimized Warehouses
Let's assume our Data Science team in Hyderabad needs a warehouse to train a customer churn model.
(To ensure you have the SYSADMIN role required to create warehouses, run SELECT CURRENT_ROLE();)
1. Creating the Warehouse
You create it exactly like a normal warehouse, but specify the WAREHOUSE_TYPE.
USE ROLE SYSADMIN;
CREATE OR REPLACE WAREHOUSE HYD_DATASCIENCE_WH
WAREHOUSE_SIZE = 'MEDIUM'
WAREHOUSE_TYPE = 'SNOWPARK-OPTIMIZED'
AUTO_SUSPEND = 60
AUTO_RESUME = TRUE
COMMENT = 'Used exclusively for ML Model Training';
2. Altering an Existing Warehouse
You can change a standard warehouse to a Snowpark-Optimized warehouse without dropping it. However, the warehouse must be suspended before you make the change.
-- Suspend the warehouse
ALTER WAREHOUSE HYD_DATASCIENCE_WH SUSPEND;
-- Change it back to standard
ALTER WAREHOUSE HYD_DATASCIENCE_WH SET WAREHOUSE_TYPE = 'STANDARD';
3. Monitoring Memory Usage
If a data scientist complains that their Python code is still crashing with OOM errors on a Medium Snowpark-Optimized warehouse, you can verify this by checking the query history.
USE ROLE ACCOUNTADMIN;
-- Check for queries that spilled to disk or failed due to memory
SELECT
QUERY_ID,
USER_NAME,
EXECUTION_STATUS,
ERROR_MESSAGE
FROM SNOWFLAKE.ACCOUNT_USAGE.QUERY_HISTORY
WHERE WAREHOUSE_NAME = 'HYD_DATASCIENCE_WH'
AND (ERROR_MESSAGE ILIKE '%memory%' OR ERROR_MESSAGE ILIKE '%OOM%')
ORDER BY START_TIME DESC;
If you see memory errors on a Snowpark-Optimized warehouse, the solution is to scale up (e.g., from Medium to Large), which doubles the available RAM per node.
Summary
- Snowpark-Optimized warehouses provide 16x more memory per node.
- They cost 1.5x more credits than standard warehouses.
- Use them strictly for memory-intensive Python/Java/Scala operations like ML Model Training.
- They are created using
WAREHOUSE_TYPE = 'SNOWPARK-OPTIMIZED'.