Snowpark vs SQL: When Python Wins and When It Is Just a Slower Way to Write SQL

A team I worked with rewrote their entire silver layer from dbt SQL into Snowpark Python. The reasoning was reasonable on its face: the engineers were Python people, the SQL had grown into thousand-line models, and Snowpark promised the same engine with a better authoring experience.

Six weeks later the nightly run had gone from 40 minutes to just over three hours and the warehouse had been promoted twice. The DataFrame code was clean. The problem was four helper functions that had been Python all along — date parsing, a currency normalizer, a fuzzy name match, and a categorization rule — and each had become a Python UDF invoked several hundred million times.

That is the whole story of Snowpark versus SQL in one incident. The DataFrame API is not slower than SQL, because it is SQL. The Python you write inside UDFs is slower than SQL, sometimes by two orders of magnitude, because it leaves the vectorized engine and runs row by row in a sandboxed interpreter.

Knowing which of those two things you are doing is the entire skill.

What Snowpark actually does

The Snowpark DataFrame API is a lazy query builder. Every filter, select, join, and group_by appends to a logical plan. Nothing executes until you call an action — collect, show, to_pandas, save_as_table, count. At that point the plan is compiled into a single SQL statement and sent to your warehouse.

flowchart TD
  df["Snowpark DataFrame operations"] --> plan["Lazy logical plan"]
  plan --> action{"Action called?"}
  action -->|"no"| plan
  action -->|"yes"| sqlgen["Compiled to one SQL statement"]
  sqlgen --> wh["Executed by the virtual warehouse"]
  udf["Python UDF in the plan"] --> sandbox["Per-row Python sandbox inside the warehouse"]
  sandbox --> wh

DataFrame operations become SQL. Python UDFs become a sandbox the SQL calls, and that is the expensive path.

So a Snowpark transform and the equivalent SQL produce the same plan, run on the same warehouse, prune the same micro-partitions, and cost the same credits. If someone tells you Snowpark is slower than SQL for a straightforward aggregation, they have a UDF or a collect() they have not noticed.

Prove it to yourself on any pipeline you own. This is the first thing I run when someone reports a Snowpark performance problem:

from snowflake.snowpark import Session
from snowflake.snowpark.functions import col, sum as sf_sum

session = Session.builder.configs(connection_params).create()

orders = (
    session.table("analytics.silver.orders")
    .filter(col("ORDERED_AT") >= "2026-04-01")
    .group_by("CUSTOMER_ID")
    .agg(sf_sum("AMOUNT_CENTS").alias("TOTAL_CENTS"))
)

# Nothing has executed yet. This prints the SQL that will run.
print(orders.queries["queries"][0])
# Or the full plan, including any temp objects Snowpark will create:
orders.explain()

If the printed SQL is what you would have written by hand, the Snowpark version is free. If it contains a subquery per DataFrame operation, a cascade of CTEs, or a temporary table you did not ask for, you have found your problem.

The UDF boundary is where the money goes

A scalar Python UDF is invoked once per row. Each invocation serializes the input, crosses into a Python sandbox running inside the warehouse node, executes your function, and serializes the result back. Snowflake’s vectorized columnar engine cannot optimize across that boundary, and it cannot push the predicate through it.

Here is the pattern that cost that team three hours a night, and its SQL equivalent:

from snowflake.snowpark.functions import udf
from snowflake.snowpark.types import StringType

# Slow: one Python call per row, hundreds of millions of times.
@udf(name="normalize_region", is_permanent=False, packages=[])
def normalize_region(region: str) -> str:
    if region is None:
        return "UNKNOWN"
    r = region.strip().upper()
    if r in ("US", "USA", "UNITED STATES"):
        return "NA"
    if r in ("UK", "GB", "GREAT BRITAIN"):
        return "EMEA"
    return r

df = session.table("analytics.silver.orders").with_column(
    "REGION_NORM", normalize_region(col("REGION"))
)

-- Fast: the same rule, executed by the vectorized engine.
select
  order_id,
  customer_id,
  case
    when region is null then 'UNKNOWN'
    when upper(trim(region)) in ('US', 'USA', 'UNITED STATES') then 'NA'
    when upper(trim(region)) in ('UK', 'GB', 'GREAT BRITAIN')  then 'EMEA'
    else upper(trim(region))
  end as region_norm
from analytics.silver.orders;

On a 300 million row table I have measured roughly a 20x difference between those two on the same warehouse. Not because Python is 20x slower at uppercasing a string, but because the per-row boundary crossing dominates everything.

The rule I give teams: if SQL can express it, SQL should express it. String manipulation, date math, conditional logic, aggregation, window functions, regex — all of it belongs in SQL. A CASE statement that looks ugly is still the right answer.

Vectorized UDFs recover most of the loss

When the logic genuinely needs Python, do not write a scalar UDF. Write a vectorized one, which receives a pandas Series and returns one, so the boundary is crossed once per batch instead of once per row.

import pandas as pd
from snowflake.snowpark.functions import pandas_udf
from snowflake.snowpark.types import PandasSeriesType, StringType, FloatType

# Vectorized: Snowflake hands us batches, we return batches.
@pandas_udf(
    name="score_risk",
    return_type=PandasSeriesType(FloatType()),
    input_types=[PandasSeriesType(FloatType()), PandasSeriesType(FloatType())],
    packages=["pandas", "numpy", "scikit-learn"],
    max_batch_size=10000,
)
def score_risk(amount: pd.Series, tenure_days: pd.Series) -> pd.Series:
    # Any real vectorized work goes here: numpy ops, a loaded model, etc.
    normalized = (amount / 100.0).clip(upper=5000)
    return (normalized * 0.7 + tenure_days.fillna(0) * 0.3) / 1000.0

In my testing, vectorized UDFs land somewhere around 3 to 10 times faster than the scalar equivalent for nontrivial functions. They are still slower than native SQL, so this is the fallback when SQL cannot do the job, not a way to make arbitrary Python cheap.

The other Python-in-warehouse shapes worth knowing: UDTFs return multiple rows per input and can maintain per-partition state, which is the right tool for sessionization or custom tokenization. Stored procedures run once and orchestrate, which is a completely different cost profile from a UDF and is often the correct place for Python.

The four cases where Snowpark genuinely wins

Model inference. Loading a scikit-learn or XGBoost model from a stage and scoring rows in a vectorized UDF keeps the data in Snowflake and skips an export-score-import round trip. This is the strongest argument for Snowpark, and the caching pattern matters:

import sys, os, joblib, threading
from snowflake.snowpark.functions import pandas_udf

_model = None
_lock = threading.Lock()

def _load_model():
    # Load once per warehouse process, not once per batch.
    global _model
    if _model is None:
        with _lock:
            if _model is None:
                import_dir = sys._xoptions["snowflake_import_directory"]
                _model = joblib.load(os.path.join(import_dir, "churn_model.joblib"))
    return _model

@pandas_udf(
    name="predict_churn",
    packages=["pandas", "scikit-learn", "joblib"],
    imports=["@ml_stage/churn_model.joblib"],
    max_batch_size=20000,
)
def predict_churn(features: pd.DataFrame) -> pd.Series:
    return pd.Series(_load_model().predict_proba(features)[:, 1])

Skipping the module-level cache and loading the model inside the function body is the most common mistake here, and it turns a five-minute job into an hour.

Procedural logic that SQL expresses badly. Recursive graph walks, iterative convergence, or a workflow with genuine branching where each step depends on the previous result. SQL can sometimes do these, and the resulting SQL is usually unmaintainable.

Existing Python libraries. A validated parsing library, a geospatial package, a proprietary scoring module. Rewriting it in SQL is a correctness risk that is rarely worth taking.

Orchestration inside Snowflake. A stored procedure that runs seven dependent SQL statements with error handling, driven by a task, is a legitimate and cheap use of Snowpark — the Python runs once and everything expensive stays in SQL. This pairs well with streams and tasks.

The pipeline decision I actually make

For transformations, SQL through dbt remains my default, and I have not changed my mind about that. It gives me lineage, tests, documentation, and a review process the whole team can read. The dbt testing strategy and incremental patterns are hard to replicate in a Python codebase without building your own framework.

Snowpark enters when a step in that pipeline needs Python. The shape I like is a dbt project that owns the transformations, with one or two Python steps registered as stored procedures and called from a dbt Python model or an Airflow task. The Python is small, isolated, and testable, and the 95 percent of the pipeline that is aggregation stays in SQL.

What I avoid is the middle ground: rewriting SQL transformations as DataFrame chains because Python feels nicer. You get identical performance, you lose the dbt tooling, and you introduce a serialization boundary that a future engineer will accidentally cross with a UDF.

Where teams get this wrong

Calling collect() or to_pandas() in a loop. Each call is a round trip that pulls data to the client. A loop over 500 categories with a collect() inside is 500 queries and 500 transfers. Restructure as one grouped query.

Writing scalar UDFs for logic SQL can express. The single most expensive mistake in Snowpark, and the easiest to fix. Search your codebase for @udf and challenge every one.

Loading a model inside the UDF body. Without a module-level cache, you deserialize the model for every batch. Use the global plus lock pattern above.

Assuming a Snowpark-optimized warehouse fixes it. More memory per node helps memory-bound Python, but it does nothing for per-row serialization overhead and it costs more per credit-hour. Fix the UDF first, then consider the warehouse. The sizing guide applies here the same as anywhere.

Building long DataFrame chains without checking the generated SQL. Snowpark sometimes materializes intermediate results, and a deeply nested plan can compile into something with dozens of nested subqueries. Print the SQL on anything that runs in production.

Treating to_pandas() as a normal operation. It pulls the entire result to the client’s memory. On a small aggregate it is fine; on a fact table it is an out-of-memory error waiting for a data growth spurt.

FAQ

Is Snowpark faster than SQL for large joins?

No. It compiles to the same SQL and runs the same plan on the same warehouse. Any measured difference comes from how the DataFrame chain compiled, not from Snowpark being a different engine.

Should I move my dbt models to Snowpark?

Not for their own sake. dbt gives you lineage, testing, and documentation that a DataFrame codebase would have to reinvent. Move individual models to Python only when they need Python, and keep them as dbt Python models so they stay in the same DAG.

How much slower is a Python UDF, concretely?

For simple logic on hundreds of millions of rows, I have measured 10 to 50 times slower than the SQL equivalent for scalar UDFs, and roughly 3 to 10 times slower for vectorized ones. The gap narrows as the per-row work grows, since serialization becomes a smaller share of the total.

Do I need a Snowpark-optimized warehouse?

Only for memory-hungry Python: training a model in a stored procedure, or a UDF holding a large object in memory. Standard warehouses handle typical inference and DataFrame work fine, and they cost less.

Can I use any Python package?

Anything in the Snowflake Anaconda channel is available directly. Pure-Python packages outside it can be staged as a zip and imported. Packages with unusual native dependencies may not work, which is worth verifying before you design around a library.

What this means for your pipelines

Snowpark is not an alternative to SQL, it is an alternative authoring surface with an optional escape hatch into Python. The authoring surface is free and mostly a team preference. The escape hatch is expensive and should be used deliberately, with a measurement to justify each crossing.

Practically, that gives you a simple review rule. When a Snowpark pull request lands, look for two things: any @udf decorator, and any action called inside a loop. Everything else is SQL with different syntax and will perform accordingly. If a UDF is present, ask whether SQL could express the logic, and if not whether the vectorized form was used. That review takes two minutes and catches nearly every performance problem I have seen in Snowpark code.

Keep the boundary visible in your architecture too. Aggregation, joins, and filtering belong in SQL where the engine can prune and vectorize. Python belongs at the edges — inference, parsing, orchestration — where it does something SQL genuinely cannot. Draw that line explicitly and Snowpark becomes a useful tool rather than a three-hour nightly run nobody can explain.