skip to content

When would you set WAREHOUSE_TYPE = 'SNOWPARK-OPTIMIZED' on a Snowflake warehouse instead of resizing up?

level: middleimportance: nice to knowfreq 20%

answer

  1. a warehouse type, not a warehouse size
  2. some failures are per-node, not per-cluster
  3. adding nodes gives one function no more room
  4. think Python handlers and model training

basics

~20 s

Choose a Snowpark-optimized warehouse when the workload is memory-hungry per node rather than starved of parallelism — large Python UDFs, UDTFs or stored procedures and model training that hit out-of-memory errors. It offers more memory per node at a higher credit rate.

solid answer

~40 s

Snowflake warehouses come in two types, set with `WAREHOUSE_TYPE`. The default `'STANDARD'` scales by adding nodes, which helps scan- and parallelism-bound SQL. `'SNOWPARK-OPTIMIZED'` provisions nodes with substantially more memory each, at a higher credit rate for the same size. Reach for it when the failure is **per-node memory**, not throughput: a Python or Java UDF, UDTF or stored procedure that loads a large object or model into memory and raises an out-of-memory error, or Snowpark ML training on a sizeable dataset. Resizing a standard warehouse up adds nodes and total memory, but a single-node memory ceiling is what those workloads hit, so more nodes does not help. Ordinary SQL analytics — big scans, joins, aggregations — should stay on standard warehouses, since a Snowpark-optimized one costs more for no benefit.

code

sql · 7 lines
sql
CREATE WAREHOUSE ml_wh
  WAREHOUSE_SIZE = 'MEDIUM'
  WAREHOUSE_TYPE = 'SNOWPARK-OPTIMIZED'
  AUTO_SUSPEND   = 60;

-- back to the default type when the memory-heavy work is done
ALTER WAREHOUSE ml_wh SET WAREHOUSE_TYPE = 'STANDARD';

go deeper

for a junior

Know that Snowflake warehouses have a type as well as a size, and that the Snowpark-optimized type provides more memory per node for non-SQL workloads.

for a middle

Explain why adding nodes does not help a single-node memory ceiling, and which workloads — memory-hungry UDFs, procedures, model training — justify the higher credit rate.

for a senior

Show the diagnosis: an out-of-memory failure that persists as you scale the size up points at per-node memory, and route only those workloads to a dedicated warehouse of this type.

for a principal

Own the policy on where in-warehouse compute belongs at all — which Python and ML workloads run inside Snowflake versus an external platform, and what that choice costs per unit of work.

## Two dimensions of "bigger" When a Snowflake workload fails or crawls, the instinctive fix is a larger `WAREHOUSE_SIZE`. That adds *nodes*: more parallel workers, more aggregate memory, more local disk. It is the right fix for work that splits across workers — scans, joins, sorts and aggregations that spill. Some workloads do not fail for lack of parallelism; they fail for lack of **memory on one node**. A Python UDF that loads a large model or builds a big in-memory structure runs inside a single node's memory budget. Doubling the number of nodes gives that function no additional room, so the query keeps failing with out-of-memory errors at every size you try. That is the gap `WAREHOUSE_TYPE = 'SNOWPARK-OPTIMIZED'` exists to fill: nodes with substantially more memory (and a larger local cache) each, at a higher credit rate than a standard warehouse of the same size. ## Setting it ```sql CREATE WAREHOUSE ml_wh WAREHOUSE_SIZE = 'MEDIUM' WAREHOUSE_TYPE = 'SNOWPARK-OPTIMIZED' AUTO_SUSPEND = 60; -- switching an existing warehouse ALTER WAREHOUSE ml_wh SET WAREHOUSE_TYPE = 'STANDARD'; ``` The type is a warehouse property like any other, and a warehouse must be suspended for the change to take effect. Everything else about the warehouse behaves normally: sizes, auto-suspend and auto-resume, multi-cluster settings and resource monitors all work the same way. ## When it is the right call - **Memory-bound UDFs, UDTFs and stored procedures.** A handler in Python, Java or Scala that deserialises a large model, holds a big dataframe, or builds a large lookup structure per partition. - **Snowpark ML training.** Fitting a model over a substantial training set inside the warehouse. - **Workloads whose symptom is an out-of-memory failure that does not go away as you scale the size up.** That non-response to sizing is the diagnostic tell. ## When it is the wrong call - **Ordinary SQL analytics.** Scan-heavy queries, big joins, high-cardinality group-bys: these use aggregate memory across nodes and respond well to a larger standard warehouse. Putting them on a Snowpark-optimized warehouse pays a higher credit rate for capacity they were not short of. - **Concurrency problems.** Many queries queueing needs more clusters, not more memory per node. - **"Just in case" defaults.** Making it the standard warehouse type across an account is a pure cost increase for most workloads. ## Practical notes Available sizes, per-size memory and the exact credit multiplier for this warehouse type have changed across Snowflake releases, so verify the current specifics in your account rather than quoting numbers from memory. The durable knowledge is the shape of the decision: **standard scales out across nodes; Snowpark-optimized scales up the memory of each node**, and you pick based on which resource the workload actually exhausted. ## Interview framing This is a differentiator question — not knowing it is rarely held against a candidate, but recognising the symptom (an OOM in a UDF that ignores size increases) and naming the warehouse type as the lever is a strong signal that you have run non-SQL workloads inside Snowflake rather than only queries.

  • Why does increasing WAREHOUSE_SIZE often fail to fix an out-of-memory error in a Python UDF?
    Because a larger size mainly adds nodes. A UDF handler executes within a single node's memory budget, so extra nodes add parallelism and aggregate memory the function cannot use. The failing constraint is memory per node, which is what the Snowpark-optimized warehouse type raises.
  • Would you run a scan-heavy dashboard workload on a Snowpark-optimized warehouse?
    No. Those queries are parallelism- and aggregate-memory-bound, and a standard warehouse of the same size serves them at a lower credit rate. Snowpark-optimized warehouses cost more per hour, so using them as a default across an account is a straight cost increase for workloads that were never short of per-node memory.

saying these in an interview costs you the question

  • Treating Snowpark-optimized as simply a bigger warehouse size
  • Making it the default warehouse type for all workloads
  • Assuming it also raises query concurrency
  • Believing it costs the same as a standard warehouse of that size

context

resolving links…