Snowpark Python: DataFrames, UDFs & Stored Procs Running Inside Snowflake

snowpark python is the runtime that lets you write Python — DataFrames, UDFs, stored procedures, ML training loops — and have every line of it execute inside a Snowflake virtual warehouse instead of on a client machine. The promise is straightforward: stop pulling 10 TB of data out over the network into a pandas notebook, push the Python down to where the data already lives. The implementation is more interesting: a sandboxed CPython interpreter inside the warehouse, a lazy DataFrame API that compiles to SQL, vectorized UDFs that ship Apache Arrow batches across the FFI boundary, and stored procedures that wrap a whole pipeline as a callable SQL object.

This guide is the senior-DE walkthrough you wished existed the first time an interviewer asked "how is snowpark different from PySpark?" or "when do snowpark udf and snowpark stored procedure beat pulling the data into a Python notebook?" It maps the four axes interviewers actually probe — client vs server-side python, the snowpark dataframe lazy execution model, the UDF / UDTF flavour grid, and the sproc + snowpark ml orchestration story — and finishes with a snowpark vs spark decision tree calibrated for 2026 warehouse-centric stacks. Each section ships a teaching block followed by a Solution-Tail interview answer — code, a step-by-step trace, an output table, then a concept-by-concept breakdown of why it works.

When you want hands-on reps immediately after reading, drill the SQL practice library →, pivot to the ETL practice rooms → for warehouse-side orchestration patterns, and shore up the windowed-aggregate axis with the aggregation library →.

---

On this page

• Why Snowpark exists — server-side Python in the warehouse
• Snowpark DataFrame API
• Python UDFs and UDTFs
• Stored procedures and Snowpark ML
• Snowpark vs Spark vs notebooks — choosing the right runtime
• Cheat sheet — Snowpark Python recipes
• Frequently asked questions
• Practice on PipeCode

---

1. Why Snowpark exists — server-side Python in the warehouse

Snowpark moves Python compute next to the data — the runtime is Snowflake, the client is a thin SDK

The one-sentence invariant: Snowpark Python is a client SDK and a server-side Python runtime; the SDK builds a logical plan locally, but every DataFrame, UDF, and stored procedure executes inside a sandboxed CPython interpreter that the Snowflake virtual warehouse spawns alongside its SQL engine. Once you internalise "client builds the plan, warehouse runs the Python," every other interview question — cold-start cost, UDF performance, package management, security model — collapses to a consequence of that one fact.

The four axes interviewers always probe.

• Client vs server. The Snowpark client is a thin Python package (snowflake-snowpark-python on PyPI). It builds a logical plan from your df.filter(...).select(...) chains and ships the plan as SQL to the warehouse. The Python that runs on a UDF or sproc executes inside Snowflake's sandbox, not on the client. Mixing those two layers in your head is the most common interview mistake.
• DataFrame API. Snowpark DataFrames are lazy — like PySpark, like Polars-lazy, like Ibis. Operations build a logical plan; df.show() or df.collect() triggers compile-to-SQL and execution on the warehouse. No data leaves Snowflake until you call a terminal op that materialises rows on the client.
• UDFs. Snowpark supports three Python UDF flavours — scalar, vectorized (Arrow batches in), and UDTF (returns a table). Each has a different cost profile. Scalar UDFs are convenient but row-at-a-time; vectorized UDFs amortise the FFI boundary across a pandas Series; UDTFs let you emit multiple rows per input row.
• Stored procedures. A snowpark stored procedure is a Python function whose body runs inside Snowflake when you call it from SQL. It can take a Session argument and use the Snowpark DataFrame API to orchestrate multi-step pipelines server-side. Owner's-rights vs caller's-rights changes the security model and the data access.

The "move compute to data" thesis.

• For decades the default pattern was: dump table to S3 → copy to a Python machine → run pandas / sklearn / PyTorch → write results back. The egress, the network hop, and the duplicate disk footprint dominated the cost.
• Snowpark inverts the data flow: leave the data in Snowflake, push the Python code to the warehouse, run it inside the same compute that already has the rows in cache. For workloads where the input is multi-TB and the output is a few MB (aggregations, feature engineering, scoring), the savings are enormous.
• The trade-off is portability: code written against Snowpark only runs on Snowflake. Code written against PySpark runs on EMR, Databricks, GCP Dataproc, or a local laptop. For teams already committed to Snowflake as the warehouse, Snowpark is the lower-friction answer; for teams that want multi-cloud or multi-engine portability, that lock-in is a deal-breaker.

The 2026 reality — what changed since the 2021 launch.

• Snowpark ML went GA — feature store, modelling primitives, and a model registry that lets you call models as SQL functions (PREDICT(...)) once registered.
• Snowpark Container Services lets you run arbitrary container images (FastAPI, PyTorch, Triton inference servers, GPU workloads) on a managed pool of warehouse-adjacent compute.
• pandas-on-Snowpark (Modin-backed) ships a near-drop-in import modin.pandas as pd that compiles pandas operations to Snowpark plans and runs them on the warehouse.
• Polars-on-Snowpark is in private preview — same lazy-compile idea, Polars-flavoured API.
• External Access Integrations finally let UDFs and sprocs reach out to the public internet over a controlled allowlist — unlocking REST calls, secret retrieval, and webhook patterns from inside the warehouse.

What interviewers listen for.

• Do you say "the SDK builds a plan, the warehouse runs the Python" in the first sentence? — senior signal.
• Do you describe DataFrame ops as lazy compile-to-SQL rather than as an in-memory operation? — required answer.
• Do you mention UDF cold-start cost and vectorized UDFs as the mitigation unprompted? — senior signal.
• Do you push back on "Snowpark replaces Spark" with "they target different deployment shapes and portability profiles"? — senior signal.

Worked example — same row count, two runtimes

Detailed explanation. A simple Hello World — count rows per category from a Snowflake table — looks deceptively similar in pandas, PySpark, and Snowpark. The DSL is almost identical. The runtime is wildly different: pandas pulls the whole table to a Python process, PySpark runs on a Spark cluster reading the table over a connector, Snowpark compiles the chain into SQL and the warehouse runs it. Only Snowpark keeps the data inside Snowflake the entire time.

Question. Compute count(*) per category from a Snowflake table events using pandas, PySpark, and Snowpark. Highlight where each runtime actually executes the work and where the data lives during the computation.

Input.

category	event_id
A	e1
A	e2
B	e3
C	e4
A	e5

Code.

# pandas — pulls the whole table over the wire to the client
import pandas as pd, snowflake.connector
conn = snowflake.connector.connect(...)
df = pd.read_sql("SELECT category, event_id FROM events", conn)
out = df.groupby("category").size().reset_index(name="cnt")
# rows live on the client; warehouse only served the SELECT

# PySpark — Spark cluster reads via the Snowflake connector
from pyspark.sql import SparkSession
spark = SparkSession.builder.config("spark.jars.packages", "net.snowflake:spark-snowflake_2.12:...").getOrCreate()
df = spark.read.format("snowflake").options(**sf_opts).option("dbtable", "events").load()
out = df.groupBy("category").count()
# rows live in Spark executors; Snowflake exports, Spark aggregates

# Snowpark — DataFrame chain compiles to SQL, warehouse runs everything
from snowflake.snowpark import Session
from snowflake.snowpark.functions import col, count
session = Session.builder.configs(connection_params).create()
df = session.table("events")
out = df.group_by("category").agg(count("*").alias("cnt"))
out.show()
# rows never leave Snowflake; warehouse compiles and executes SQL

Step-by-step explanation.

1. The pandas path issues a SELECT against Snowflake, streams every row back to the client, and runs the groupby on the client's RAM. If the table is 10 TB, this is catastrophic. If it is 10 MB, it is fine.
2. The PySpark path uses the Snowflake connector to push down a SELECT (Spark can push some predicates and projections), then materialises the rows in Spark executors and runs the groupBy on the Spark cluster. The data leaves Snowflake but lives inside the Spark cluster, not on the driver.
3. The Snowpark path builds a logical plan in the client SDK. df.group_by(...).agg(...) returns a new lazy DataFrame; no rows move. When out.show() fires, the SDK compiles the plan into SQL like SELECT category, COUNT(*) FROM events GROUP BY category and executes it on the warehouse. Only the first 10 result rows (the show() default) come back to the client.
4. In all three cases the final answer is identical. The difference is where the compute ran and how many bytes crossed the network. Snowpark sent ~100 bytes of SQL out and received ~100 bytes of result back; pandas sent ~100 bytes of SQL out and received the entire table back.
5. The architectural pull is obvious for any input ≥ a few GB: keep the data in the warehouse and push the Python down. Snowpark is the runtime that lets you do that without rewriting the orchestration in SQL.

Output.

category	cnt
A	3
B	1
C	1

Rule of thumb. If the input is bigger than the output by 10x or more, every runtime except Snowpark pays the network egress cost. Push the Python to the warehouse and only ship results back.

Worked example — Session lifecycle and credential flow

Detailed explanation. The first object you create in any Snowpark program is a Session. Junior engineers treat it as "the connection"; senior engineers treat it as "the binding between client code and a specific warehouse + role + database + schema + Python sandbox version." Misunderstanding the Session is the source of half the Snowpark production bugs.

Question. Build a Snowpark Session from a connection params dict, switch warehouses mid-program, and explain what changes on the server side when you do.

Input — connection params.

key	value
account	acme-xy12345
user	data_eng
password	env:SNOWFLAKE_PASSWORD
role	DATA_ENGINEER
warehouse	TRANSFORM_WH
database	ANALYTICS
schema	RAW

Code.

from snowflake.snowpark import Session

connection_params = {
    "account":   "acme-xy12345",
    "user":      "data_eng",
    "password":  os.environ["SNOWFLAKE_PASSWORD"],
    "role":      "DATA_ENGINEER",
    "warehouse": "TRANSFORM_WH",
    "database":  "ANALYTICS",
    "schema":    "RAW",
}

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

# Switch warehouse mid-program — heavier compute for one step
session.use_warehouse("LARGE_TRAINING_WH")
session.sql("ALTER WAREHOUSE LARGE_TRAINING_WH RESUME").collect()

# Run a heavy aggregation on the big warehouse
big_agg = session.table("orders").group_by("region").count()
big_agg.write.save_as_table("region_counts", mode="overwrite")

# Switch back for cheap follow-up work
session.use_warehouse("TRANSFORM_WH")

Step-by-step explanation.

1. Session.builder.configs(...).create() opens an authenticated channel to Snowflake. The connection is per-process; reusing one Session for the lifetime of a script is the recommended pattern. Each DataFrame op binds to this Session implicitly.
2. The Session carries a current role, warehouse, database, schema, exactly mirroring SQL USE statements. Switching warehouses with use_warehouse(...) changes which compute cluster runs subsequent queries — the Session object is the same, the compute target is different.
3. Snowflake auto-suspends idle warehouses. The first query after a suspend pays the resume cost (typically a few seconds for an XS, longer for an XL). If you switch to LARGE_TRAINING_WH and it has been idle, your use_warehouse is cheap but the next query stalls until the warehouse resumes. The explicit ALTER WAREHOUSE ... RESUME warms it up first.
4. For a snowpark udf or stored procedure registered against the Session, the registration binds the UDF to the current database and schema. If you later call the UDF from a different schema, you must qualify the name. This is a common source of "function not found" errors after a schema switch.
5. Best practice: create one Session at program start, parameterise warehouse and role for the heavy steps, and close the session at program end with session.close() (or rely on the destructor in interactive notebooks).

Output (lifecycle trace).

step	active warehouse	active role	server-side effect
create()	TRANSFORM_WH	DATA_ENGINEER	new auth session, queue accepted
use_warehouse("LARGE_TRAINING_WH")	LARGE_TRAINING_WH	DATA_ENGINEER	next query targets large WH
ALTER WAREHOUSE ... RESUME	LARGE_TRAINING_WH	DATA_ENGINEER	warehouse leaves SUSPENDED, billing starts
group_by(...).count()	LARGE_TRAINING_WH	DATA_ENGINEER	SQL compiled + executed on large WH
use_warehouse("TRANSFORM_WH")	TRANSFORM_WH	DATA_ENGINEER	follow-up queries target small WH

Rule of thumb. Treat the Session as a long-lived process binding, not a per-query connection. Switch warehouses inside the Session when one step needs heavier compute; never open multiple Sessions per script unless you really need different role chains.

Worked example — what runs client-side vs server-side

Detailed explanation. A frequent interview probe — "trace exactly which lines of this Snowpark program execute on the client and which on the warehouse" — separates engineers who memorised the API from engineers who understand it. The answer is "almost everything builds a plan locally, terminal ops trigger server-side execution."

Question. Walk through a small Snowpark program and annotate each line with [client] or [server]. Show how df.show() triggers a compile-and-execute round-trip.

Input.

step	code line
1	session = Session.builder.configs(...).create()
2	df = session.table("orders")
3	df2 = df.filter(col("amount") > 100)
4	df3 = df2.group_by("region").agg(sum_("amount").alias("total"))
5	df3.show()

Code.

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

# 1. [client + server] open authenticated channel to Snowflake
session = Session.builder.configs(connection_params).create()

# 2. [client] returns a lazy DataFrame referencing the "orders" table
df = session.table("orders")

# 3. [client] adds a Filter node to the logical plan — no SQL sent
df2 = df.filter(col("amount") > 100)

# 4. [client] adds an Aggregate node — still no SQL sent
df3 = df2.group_by("region").agg(sum_("amount").alias("total"))

# 5. [server] compiles plan to SQL, runs on warehouse, ships rows back
df3.show()

Step-by-step explanation.

1. Line 1 — opening the Session — talks to Snowflake once for auth, then becomes a long-lived client-side handle.
2. Lines 2–4 — table, filter, group_by, agg — are pure plan-building. Each call returns a new DataFrame whose internal AST grows by one node. Zero SQL has been sent to the warehouse yet.
3. Line 5 — df3.show() — is a terminal action. The SDK walks the AST, compiles it to a SQL string roughly equal to SELECT region, SUM(amount) AS total FROM orders WHERE amount > 100 GROUP BY region, sends it to the warehouse, and receives the first 10 result rows.
4. The warehouse query history will show one query for this whole program, not four. The lazy DataFrame collapses the chain into a single SQL statement — the same optimisation Spark and Polars use.
5. If line 5 were df3.collect() instead, the SDK would request all result rows. If it were df3.write.save_as_table("region_totals"), no rows would come back to the client at all — the SQL would be CREATE OR REPLACE TABLE region_totals AS SELECT ... executed entirely server-side.

Output (annotation table).

line	runs where	sends SQL?
Session.create()	client + auth round-trip	yes (login)
session.table("orders")	client (plan)	no
df.filter(...)	client (plan)	no
df.group_by(...).agg(...)	client (plan)	no
df.show()	warehouse (compiles + runs)	yes

Rule of thumb. Anything that returns a new DataFrame is client-side plan-building. Anything that returns rows or writes a table is a server-side execution. Use this rule to predict latency before you run the code.

Senior interview question on the Snowpark runtime model

A senior interviewer often opens with: "You inherit a 200-line Snowpark script that takes 12 minutes to run, and 11 of those minutes are spent in a for loop on the client. What is happening and how do you fix it without leaving Snowpark?"

Solution Using the lazy plan model + pushdown to the warehouse

# DIAGNOSIS — every loop iteration triggers a round-trip
# Anti-pattern: for-each row triggers a separate SQL query
for region in regions:
    df = session.table("orders").filter(col("region") == region)
    out = df.group_by("category").count().collect()   # <-- network hop each iter
    write_row(out)

# FIX 1 — collapse the loop into a single grouped aggregation
df = session.table("orders") \
            .group_by("region", "category") \
            .count()
df.write.save_as_table("region_category_counts", mode="overwrite")
# one SQL query, runs entirely on the warehouse

# FIX 2 — if you truly need per-region custom logic, push it into a SQL CASE
df = session.table("orders") \
            .with_column("bucket", when(col("amount") > 1000, "high").otherwise("low")) \
            .group_by("region", "bucket") \
            .count()
df.write.save_as_table("region_bucket_counts", mode="overwrite")

Step-by-step trace.

Step	Anti-pattern (loop)	Fixed (single plan)
SQL queries per run	1 per region (say 50)	1
Network round-trips	50	1
Warehouse SQL compile time	50 × compile	1 × compile
Client wall clock	11 min	~10 s
Data movement	50 result sets to client	0 (write_table runs server-side)

After collapsing the loop, the 50 small queries become one grouped aggregation that runs entirely on the warehouse and writes the result table without rows ever crossing the network. The 11-minute hot loop becomes a 10-second SQL execution.

Output:

diagnosis	fix	new shape
Per-row client loop	Group all regions in one DataFrame	1 SQL query, server-side write
.collect() per iteration	Replace with write.save_as_table	0 result rows to client
50 network round-trips	Single compile + execute	1 round-trip

Why this works — concept by concept:

• Lazy DataFrames collapse chains — filter, group_by, agg all return new lazy DataFrames; the SDK only compiles when a terminal op fires. Chaining 10 ops costs 1 query, not 10.
• Network is the hidden tax — every .collect() ships rows to the client. Looping .collect() calls turns a single-query workload into N-query workload with N network round-trips, which is the most common Snowpark perf bug.
• Server-side writes — write.save_as_table(...) compiles to CREATE OR REPLACE TABLE ... AS SELECT ... which runs entirely server-side; zero rows move to the client.
• Plan a single graph, not many — the Snowpark mental model is "build one logical plan, fire one terminal op." The Python for loop is the wrong abstraction for warehouse-scale work; lift the iteration into a group_by whenever you can.
• Cost — the anti-pattern is O(N · query latency) where N is the loop iterations. The fix is O(1) compile + O(N) row processing on the warehouse, which is asymptotically the same as the bare SQL.

SQL
Topic — SQL
Warehouse SQL practice problems

Practice →

ETL Topic — ETL ETL orchestration problems

Practice →

---

2. Snowpark DataFrame API

snowpark dataframe is lazy and compiles to SQL — every chain becomes one query on the warehouse

The mental model in one line: a Snowpark DataFrame is a lazy AST that the SDK compiles to a SQL string at the moment you call a terminal op; the warehouse then runs that SQL and the only rows that ever cross the network are the ones your terminal op asked for. Once you say that out loud, the rest of the snowpark dataframe interview surface — pushdown, plan caching, pandas-on-Snowpark, the relational vs imperative split — becomes obvious.

The core ops — relational by design.

• session.table(name) — start a plan from a named table. Returns a DataFrame; no SQL fired.
• session.create_dataframe(rows, schema=...) — start a plan from in-memory Python rows. The rows are sent to the warehouse on first execution.
• session.read.csv(stage_path) / read.parquet(...) / read.json(...) — start a plan from a stage path. Snowpark generates a COPY INTO or a query against a stage-backed external table.
• df.filter(col(...) > x) / df.where(...) — predicate pushdown; compiles to a WHERE.
• df.select(...) / df.drop(...) / df.with_column(...) — projection; compiles to a SELECT list.
• df.group_by(...).agg(...) — compiles to GROUP BY + aggregate functions.
• df.join(other, on=..., how=...) — compiles to a SQL JOIN.
• df.sort(...) — compiles to ORDER BY.
• df.union(...) / union_all — compiles to UNION [ALL].

Terminal ops — what triggers the round-trip.

• df.show(n=10) — compile + run + fetch first n rows to the client.
• df.collect() — compile + run + fetch all rows to the client.
• df.count() — compile to SELECT COUNT(*) FROM (...) and return an integer.
• df.first() — fetch one row.
• df.to_pandas() — fetch all rows and materialise as a pandas DataFrame on the client.
• df.write.save_as_table(name, mode=...) — compile to CREATE OR REPLACE TABLE ... AS SELECT .... No rows return.
• df.write.copy_into_location(stage_path, ...) — unloads to a stage. No rows return.

Pushdown — what the compiler does for you.

• Snowpark compiles a DataFrame chain into a single SQL statement and lets the Snowflake query optimiser do the heavy lifting: predicate pushdown, projection pruning, join reordering, micro-partition pruning.
• A df.filter(col("date") == "2026-06-22") on a clustered table will hit only the relevant micro-partitions; the compiler does not know that explicitly, but the SQL it emits gives the optimiser the information it needs.
• Projection pruning is automatic: if you select("region", "amount") at the end, the compiler emits only those columns and Snowflake's columnar engine reads only the matching micro-partitions.

Pandas-on-Snowpark — the Modin compatibility layer.

• import modin.pandas as pd (with the Snowpark backend configured) gives you a near-drop-in pandas API where every pd.read_*, df.merge, df.groupby, df.pivot_table compiles to Snowpark DataFrame ops and runs on the warehouse.
• Two big wins: existing pandas code ports with minimal rewriting, and the data stays in the warehouse. Two big constraints: not every pandas operator is supported (iterating row-by-row, df.apply with arbitrary Python falls back to a UDF or breaks), and the lazy-vs-eager semantics differ in edge cases.

Best-practice DataFrame patterns.

• Build the whole plan, then call one terminal op. Avoid .collect() mid-pipeline.
• Use with_column for new columns instead of pandas-style assignment. It composes cleanly into the AST.
• Push aggregations as far upstream as possible. Snowflake's planner is good, but explicit is better.
• Cache an intermediate with df.cache_result() when you reuse it 3+ times — Snowpark materialises it to a temporary table.
• Prefer write.save_as_table over to_pandas + to_sql for ETL outputs. One server-side write beats two network hops.

Common interview probes on the DataFrame API.

• "When does a Snowpark DataFrame actually run a query?" — only on terminal ops.
• "What is the relationship between a Snowpark DataFrame and PySpark DataFrame?" — same lazy-plan idea, different runtime; both compile to a logical plan, Snowpark targets Snowflake SQL, Spark targets the Spark engine.
• "How do you avoid pulling 10 million rows to the client?" — never collect() or to_pandas() on the full dataset; aggregate first or save_as_table.
• "What is cache_result() for?" — materialise an intermediate to a temp table; pay one compile + write cost, then re-read cheaply N times.

Worked example — chained DataFrame ops compile to one SQL

Detailed explanation. A typical pipeline reads a table, filters, joins to a dimension, aggregates, sorts, and limits. In pandas that is 6 in-memory operations. In Snowpark it is one SQL query. Tracing the compiled SQL is the cleanest demonstration of the lazy model.

Question. Write a Snowpark pipeline that reads orders, joins to customers, filters by region, aggregates revenue per customer, sorts descending, and limits to the top 10. Show the SQL that Snowpark compiles.

Input — orders.

order_id	customer_id	amount	region
1	c1	50	US
2	c2	30	DE
3	c1	75	US
4	c3	90	US

Input — customers.

customer_id	name
c1	Alice
c2	Bob
c3	Carol

Code.

from snowflake.snowpark.functions import col, sum as sum_

orders    = session.table("orders")
customers = session.table("customers")

top = (orders
       .join(customers, orders["customer_id"] == customers["customer_id"], "inner")
       .filter(col("region") == "US")
       .group_by(customers["name"])
       .agg(sum_("amount").alias("revenue"))
       .sort(col("revenue").desc())
       .limit(10))

top.show()

Step-by-step explanation.

1. Each chained op adds a node to the logical plan: Join → Filter → Aggregate → Sort → Limit. None of them issue SQL.
2. When top.show() fires, the SDK compiles the plan into one SQL statement roughly equal to: SELECT c.name, SUM(o.amount) AS revenue FROM orders o JOIN customers c ON o.customer_id = c.customer_id WHERE o.region = 'US' GROUP BY c.name ORDER BY revenue DESC LIMIT 10.
3. The warehouse runs this single query. Snowflake's optimiser pushes the WHERE region = 'US' filter into the join input, prunes orders' columns to customer_id and amount, and uses micro-partition pruning if region clustering exists.
4. Only the top 10 rows come back to the client. The full 4-row orders table never leaves Snowflake.
5. The same pipeline written as pd.read_sql(...) would pull every order and customer row to the client, then run the join and aggregation in pandas — a multi-GB workload that Snowpark turns into a 10-row response.

Output.

name	revenue
Alice	125
Carol	90

Rule of thumb. Treat the entire chain as one SQL statement. If a chain feels too long to compile cleanly, materialise an intermediate with cache_result() and split into two stages — but never split because you "want to see the intermediate"; use show() for inspection.

Worked example — pandas-on-Snowpark vs raw Snowpark DataFrame

Detailed explanation. Teams with a heavy pandas codebase can port to Snowpark in two ways: rewrite into the native snowflake.snowpark API or swap import pandas as pd for import modin.pandas as pd with the Snowpark backend. The compiled SQL looks similar; the porting cost is very different.

Question. Compute average order value per region using pandas-on-Snowpark and the native Snowpark API side by side. Highlight where the two diverge.

Input.

order_id	region	amount
1	US	100
2	DE	50
3	US	200
4	DE	80

Code.

# pandas-on-Snowpark (Modin backend)
import modin.pandas as pd
from snowflake.snowpark.modin.plugin import register

register()                                  # binds the Snowpark Modin backend
df = pd.read_snowflake("orders")            # lazy, compiles to Snowpark
avg = df.groupby("region")["amount"].mean().reset_index(name="avg_amount")
print(avg)                                  # triggers compile + execute

# Native Snowpark DataFrame
from snowflake.snowpark.functions import col, avg as avg_
df2 = session.table("orders")
avg2 = df2.group_by("region").agg(avg_("amount").alias("avg_amount"))
avg2.show()

Step-by-step explanation.

1. import modin.pandas as pd returns a Modin DataFrame backed by Snowpark. Every Modin op (read_snowflake, groupby, mean, reset_index) is intercepted by the Snowpark backend and translated into Snowpark DataFrame ops.
2. The print(avg) (or avg.head()) call is the terminal op that triggers compile-to-SQL and execution on the warehouse — the same lifecycle as native Snowpark.
3. The native Snowpark version expresses the same logic with group_by("region").agg(avg(...)). The compiled SQL is essentially SELECT region, AVG(amount) AS avg_amount FROM orders GROUP BY region, identical to the Modin path.
4. The win for Modin is migration: existing pandas code can move to Snowpark with one import change, and falls back to client-side execution only when an unsupported operator is hit.
5. The win for native Snowpark is predictability: every method is documented as compiling to a specific SQL construct; nothing silently falls back to row-by-row Python.

Output.

region	avg_amount
US	150
DE	65

Rule of thumb. Use pandas-on-Snowpark to migrate a legacy pandas codebase with minimal rewrite. Use native Snowpark DataFrames when you are writing new code or when predictable SQL output matters for cost forecasting.

Worked example — cache_result for a re-used intermediate

Detailed explanation. A common anti-pattern in Snowpark: build an expensive intermediate DataFrame, then reuse it in three different downstream pipelines. Because each downstream chain re-compiles from the root, the expensive intermediate is recomputed three times. cache_result() materialises it to a temporary table so subsequent reads are cheap.

Question. You compute an expensive featurisation feats and reuse it for three downstream tasks (model A, model B, dashboard). Show how cache_result() collapses 3× recomputation into 1.

Input — orders (large, 1B rows).

order_id	customer_id	amount	ts
...	...	...	...

Code.

# WITHOUT cache_result — feats is recomputed three times
feats = (session.table("orders")
                .filter(col("ts") > "2026-01-01")
                .group_by("customer_id")
                .agg(sum_("amount").alias("revenue"),
                     count("*").alias("order_count")))

model_a = feats.join(model_a_input, ...).select(...)
model_b = feats.join(model_b_input, ...).select(...)
dashboard = feats.group_by("revenue").count()

model_a.write.save_as_table("model_a_input")
model_b.write.save_as_table("model_b_input")
dashboard.write.save_as_table("dash_revenue_dist")
# Snowflake recompiles "feats" three times, scanning 1B rows three times

# WITH cache_result — feats materialised once to a temp table
feats = (session.table("orders")
                .filter(col("ts") > "2026-01-01")
                .group_by("customer_id")
                .agg(sum_("amount").alias("revenue"),
                     count("*").alias("order_count"))
                .cache_result())                # <-- materialise here

# Now downstream chains read from the temp table, cheap
model_a   = feats.join(model_a_input, ...).select(...)
model_b   = feats.join(model_b_input, ...).select(...)
dashboard = feats.group_by("revenue").count()

Step-by-step explanation.

1. Without cache_result(), each downstream DataFrame compiles a SQL chain that starts at orders and re-applies the filter + group + aggregate. The warehouse plans each as an independent query.
2. Snowflake's result cache can save you only if the exact same SQL is re-issued within the cache window; the three downstream chains have different outer SELECT clauses, so they do not share the result cache.
3. cache_result() runs the upstream chain once, writes the rows to a session-scoped temp table, and returns a DataFrame that references that temp table. Downstream feats references read from the temp table.
4. Total scan of orders: 1× (with cache_result) vs 3× (without). For a 1B-row table this is a 3× cost difference and a similar latency win.
5. Cleanup: temp tables disappear when the Session closes. For longer reuse, write to a real table with save_as_table instead.

Output (cost trace).

step	without cache_result	with cache_result
Scans of orders	3	1
Temp table writes	0	1
Downstream reads	scan from base table	scan from temp table
Wall clock	3× featurisation cost	1× featurisation + 3× temp reads

Rule of thumb. Cache a Snowpark intermediate when (1) the upstream is expensive and (2) you reuse it ≥ 3 times. For 2 uses, the temp table write often costs more than the second recompute; for 4+ uses, cache_result is unambiguous.

Senior interview question on DataFrame execution model

A senior interviewer might ask: "You run df.collect() on a Snowpark DataFrame backed by a 10-billion-row table and your laptop OOMs. What went wrong, and what is the correct pattern for processing all rows without OOMing the client?"

Solution Using server-side writes + iterate result chunks

# ANTI-PATTERN — collect() pulls every row to the client
rows = session.table("big_orders").collect()    # 10B rows -> OOM
for r in rows:
    handle(r)

# FIX 1 — aggregate first, only ship results back
agg = (session.table("big_orders")
              .group_by("region")
              .agg(sum_("amount").alias("revenue"))
              .collect())                       # ~200 rows, fine

# FIX 2 — push the per-row work into a UDF or sproc, write server-side
session.table("big_orders") \
       .with_column("score", my_udf(col("amount"), col("region"))) \
       .write.save_as_table("scored_orders", mode="overwrite")
# zero rows leave the warehouse

# FIX 3 — if the client genuinely needs every row (e.g., file export),
# stream via to_local_iterator() or write to a stage and pull the file
df = session.table("big_orders").select("order_id", "amount")
df.write.copy_into_location("@my_export_stage/big_orders.csv",
                            file_format_type="CSV",
                            single=True)
# csv now lives on the stage; download via GET / stream from S3

Step-by-step trace.

Approach	Rows to client	Memory pressure	Latency
collect()	10B	OOM	n/a
Aggregate first	~200	safe	seconds
UDF + save_as_table	0	safe	minutes (server-side)
Stage export + GET	streamed CSV	low	bandwidth-bound

Choose based on the real requirement. Most "I need all rows" claims dissolve into "I need a summary" or "I need the file on disk." Only the export case justifies materialising the full table outside Snowflake.

Output:

pattern	when to use	client memory
Aggregate then collect	dashboard, report	O(groups)
UDF + save_as_table	scoring, ETL	O(1)
Stage export	file delivery to external consumer	streamed

Why this works — concept by concept:

• collect() is not free — it is cursor.fetchall() over a network. For warehouse-scale tables it is always wrong; the right pattern is to aggregate or write server-side.
• Server-side writes — save_as_table and copy_into_location execute the entire DataFrame on the warehouse and emit results either to a table or a stage, with zero rows on the client.
• UDFs keep the row-by-row logic inside the warehouse — if you need per-row Python, register a UDF rather than looping client-side; the warehouse parallelises across micro-partitions.
• Stage export is the file-delivery pattern — when a consumer genuinely needs a CSV/Parquet file, write to a stage and let them GET it; do not stream rows over the SQL protocol.
• Cost — collect() on N rows is O(N) network + O(N) client memory. Aggregating to k groups first is O(N) warehouse work + O(k) network. For N ≫ k this is the only viable architecture.

SQL
Topic — data manipulation
Data manipulation drills

Practice →

SQL Topic — aggregation Aggregation problems

Practice →

---

3. Python UDFs and UDTFs

snowpark udf has three flavours — scalar for simple, vectorized for hot loops, UDTF when you need rows out

The mental model in one line: a Snowpark UDF is a Python function registered against a virtual warehouse; at call time, Snowflake spawns (or reuses) a sandboxed CPython process inside the warehouse, ships rows in, and collects results back — and the choice between scalar, vectorized, and UDTF determines how much overhead each row pays at the FFI boundary. Once you internalise "scalar is row-at-a-time, vectorized is batch, UDTF emits multiple rows," every snowpark udf interview question collapses to picking the right flavour for the workload.

The three UDF flavours — same registration model, different throughput.

• Scalar UDF. A Python function f(x, y) -> z registered as a SQL function. Called once per row. Pay the Python interpreter overhead and the FFI marshalling on every row. Simple to write; slow for hot loops.
• Vectorized UDF (pandas UDF). A Python function f(s: pd.Series) -> pd.Series decorated with @pandas_udf. Snowflake sends Apache Arrow batches in, your function processes a whole batch at a time. The FFI marshalling cost amortises across (typically) 10K–50K rows. Often 5×–50× faster than scalar.
• UDTF (User-Defined Table Function). A Python class with process(self, row) (and optionally end_partition) that yields zero or more output tuples. Use when one input row produces zero, one, or many output rows — flatten arrays, explode JSON, generate synthetic rows.

Registration patterns.

• Decorator on a function: @udf(name="my_udf", session=session, return_type=IntegerType(), input_types=[IntegerType(), IntegerType()]).
• session.udf.register(...) API: lets you register a UDF without using the decorator — handy for higher-order patterns.
• Anonymous UDF: define inline with udf(my_func, ...) — registered as a temporary UDF for the Session.
• Persistent UDF: is_permanent=True + a stage_location — the UDF survives session close and is callable from any client.

Anaconda channel + custom packages.

• The default Anaconda channel inside Snowflake ships hundreds of curated Python packages (pandas, numpy, scikit-learn, xgboost, requests, etc.). Declare them with packages=["scikit-learn"] and Snowflake materialises them inside the sandbox.
• For packages not in Anaconda, upload the wheel to a stage and reference it with imports=["@my_stage/my_wheel.whl"]. The sandbox extracts it before invoking your function.
• Native code in custom wheels (anything that links libc / glibc) needs to match the sandbox's manylinux profile. Pure-Python wheels work everywhere; cython/C extension wheels need the right wheel tag.

External Access Integrations — the network escape valve.

• By default the UDF sandbox is network-isolated — no DNS, no outbound HTTP. This is part of the security guarantee that lets Snowflake run your code on shared compute.
• An External Access Integration + Network Rule declares an allowlist of hostnames and ports. The UDF can then requests.get(...) to those endpoints from inside the sandbox.
• Common use cases: pull a feature from an external API, write to a webhook, retrieve a secret from a secrets manager. Pair with secrets to inject credentials without leaking them.

UDF cost model — what you pay per row.

• Scalar UDF: Python interpreter setup amortised per partition; FFI cost per call; serialisation per call. Throughput cap typically ~10K rows/sec/core for non-trivial Python.
• Vectorized UDF: one FFI call per batch (10K–50K rows); pandas/NumPy vectorised compute inside. Throughput typically 100K–1M rows/sec/core.
• UDTF: stateful per partition; FFI cost per process call; output rows emitted to the SQL engine. Cost depends heavily on the input-to-output row ratio.

Common interview probes on UDFs.

• "When do you choose vectorized over scalar?" — any time the per-row Python is amenable to a pandas Series API; the speedup is rarely less than 5×.
• "What is a UDTF for?" — one input row → many output rows; classic example is exploding a JSON array column or generating windows of dates.
• "How do you ship a custom package?" — wheel on a stage + imports=...; for native code, match the sandbox's manylinux profile.
• "How does a UDF call an external API?" — declare an External Access Integration with a network rule allowlist; pair with a secret for credentials.

Worked example — scalar UDF vs vectorized UDF

Detailed explanation. Compute a simple feature — log1p(amount) — over an orders table. The two UDF flavours produce identical results but at very different throughput, and tracing why is the cleanest demonstration of the vectorisation win.

Question. Implement log1p(amount) as a scalar UDF and a vectorized UDF. Show the registration and the SQL invocation, then trace the cost model.

Input.

order_id	amount
1	100
2	250
3	500

Code.

from snowflake.snowpark.functions import udf, pandas_udf, col
from snowflake.snowpark.types import FloatType, PandasSeriesType
import math, pandas as pd

# Scalar UDF — one Python call per row
@udf(name="log1p_scalar",
     return_type=FloatType(),
     input_types=[FloatType()],
     packages=[],
     session=session)
def log1p_scalar(x: float) -> float:
    return math.log1p(x)

# Vectorized UDF — one Python call per Arrow batch
@pandas_udf(name="log1p_batch",
            return_type=PandasSeriesType(FloatType()),
            input_types=[PandasSeriesType(FloatType())],
            packages=["pandas", "numpy"],
            session=session)
def log1p_batch(s: pd.Series) -> pd.Series:
    return pd.Series(np.log1p(s.to_numpy()))    # NumPy vectorised

# Use both
orders = session.table("orders")
out_scalar = orders.with_column("ll", log1p_scalar(col("amount")))
out_batch  = orders.with_column("ll", log1p_batch (col("amount")))
out_batch.show()

Step-by-step explanation.

1. Both UDFs register as SQL functions inside the current schema. The DataFrame layer calls them with log1p_scalar(col(...)) or log1p_batch(col(...)); the compiled SQL is SELECT order_id, log1p_scalar(amount) AS ll FROM orders and similarly for the batch version.
2. At execution, the scalar UDF receives one row at a time. For each row, Snowflake serialises the amount value, calls into the sandbox, the Python function runs math.log1p, the result is serialised back.
3. The vectorized UDF receives a pandas.Series representing an Arrow batch of (typically) 10K–50K rows. The function calls np.log1p once on the whole array, then returns a Series. Snowflake hands the result back as another Arrow batch.
4. For a 100M-row table, the scalar UDF pays ~100M FFI calls. The vectorized UDF pays ~2K–10K FFI calls. The amortisation factor is the batch size; the speedup is typically 10×–50×.
5. The only constraint: the vectorized function must accept a Series and return a Series of the same length (one row out per row in). For variable-length output, use a UDTF.

Output.

order_id	ll
1	4.6151
2	5.5253
3	6.2166

Rule of thumb. Default to vectorized UDF for any non-trivial Python you would write as a scalar UDF. Use scalar only when the function shape forces it (e.g., you genuinely need a per-row external call with no batching).

Worked example — UDTF that explodes a JSON array

Detailed explanation. Snowflake's built-in FLATTEN handles most array explosions in pure SQL. But when the per-element transformation is non-trivial Python — say, parsing a complex sub-structure or running a tokeniser — a UDTF gives you the "explode + transform" combined in one server-side call.

Question. Write a UDTF that takes an events row with a tags VARIANT column (a JSON array) and emits one output row per tag with event_id, tag, tag_length.

Input.

event_id	tags
e1	["sql", "snowpark"]
e2	["python"]

Code.

from snowflake.snowpark.types import (StructType, StructField, StringType,
                                      IntegerType, VariantType)
from snowflake.snowpark.functions import udtf

class ExplodeTags:
    def process(self, event_id: str, tags):
        # tags is a Python list (parsed from VARIANT)
        if tags is None:
            return
        for tag in tags:
            yield (event_id, tag, len(tag))

session.udtf.register(
    handler=ExplodeTags,
    output_schema=StructType([
        StructField("event_id",   StringType()),
        StructField("tag",        StringType()),
        StructField("tag_length", IntegerType()),
    ]),
    input_types=[StringType(), VariantType()],
    name="explode_tags",
    is_permanent=False,
)

# Use it from Snowpark
events = session.table("events")
expanded = events.join_table_function(
    "explode_tags", col("event_id"), col("tags"))
expanded.show()

Step-by-step explanation.

1. The UDTF is a class whose process(self, *args) method is called once per input row. Each call may yield zero or more tuples; each yielded tuple becomes one output row.
2. The registration declares the output schema — Snowflake needs to know the column names and types for the SQL planner. The output_schema here yields three columns: event_id, tag, tag_length.
3. From Snowpark, you call the UDTF with df.join_table_function(name, col(...), col(...)). The compiled SQL is SELECT ... FROM events, TABLE(explode_tags(event_id, tags)) — a SQL table function call.
4. For e1 with two tags, process yields two rows. For e2 with one tag, it yields one row. The output relation has one row per (input row × tag).
5. For a partition_by UDTF, you can also implement end_partition(self) to emit "summary" rows after each partition; that lets you implement custom window aggregations in Python.

Output.

event_id	tag	tag_length
e1	sql	3
e1	snowpark	8
e2	python	6

Rule of thumb. Use UDTFs when one input row legitimately maps to a variable number of output rows. For pure 1:1 transformations, stick with vectorized UDFs.

Worked example — UDF with a custom wheel from a stage

Detailed explanation. Anaconda's curated channel covers the popular packages, but every team eventually needs something niche — a domain-specific tokeniser, an internal client library, a one-off C extension. The standard pattern is: build the wheel, upload to a stage, reference it from the UDF declaration.

Question. Register a UDF that uses a custom pure-Python package acme_tokenizer shipped as a wheel on a stage @my_libs. Show the upload + registration.

Input.

doc_id	text
d1	hello world
d2	snowpark rocks

Code.

# Step 1 — upload the wheel to a stage (one-time, from your laptop or CI)
session.file.put(
    local_file_name="acme_tokenizer-1.2.0-py3-none-any.whl",
    stage_location="@my_libs",
    overwrite=True,
    auto_compress=False)

# Step 2 — declare the UDF and reference the wheel via imports=
from snowflake.snowpark.functions import udf, col
from snowflake.snowpark.types import ArrayType, StringType

@udf(name="tokenize_doc",
     return_type=ArrayType(StringType()),
     input_types=[StringType()],
     imports=["@my_libs/acme_tokenizer-1.2.0-py3-none-any.whl"],
     packages=[],          # nothing from Anaconda needed
     session=session,
     is_permanent=True,
     stage_location="@my_udfs")
def tokenize_doc(text: str):
    import acme_tokenizer
    return acme_tokenizer.tokenize(text)

# Step 3 — call from a DataFrame
docs = session.table("docs")
docs.with_column("tokens", tokenize_doc(col("text"))).show()

Step-by-step explanation.

1. session.file.put(...) uploads the wheel from the client to a Snowflake stage. This is a one-time cost; the file lives on Snowflake-managed storage thereafter.
2. The @udf declaration specifies imports=["@my_libs/acme_tokenizer-1.2.0-py3-none-any.whl"]. When Snowflake spawns the UDF sandbox, it extracts the wheel into the Python path before running the handler.
3. packages=[] means no Anaconda packages are needed. If you also needed pandas, you would write packages=["pandas"]; both lists coexist.
4. is_permanent=True + stage_location="@my_udfs" writes the UDF handler script to a stage, so the UDF survives Session close and is callable from SQL clients other than your script.
5. The function uses import acme_tokenizer lazily inside the handler. Top-level imports also work but cost interpreter startup per partition; lazy import only matters for very large library load times.

Output.

doc_id	tokens
d1	["hello", "world"]
d2	["snowpark", "rocks"]

Rule of thumb. Use Anaconda packages first (packages=[...]) for anything in the curated channel. Use imports=["@stage/wheel.whl"] for internal or niche libraries. Match the manylinux profile for any C-extension wheel.

Senior interview question on UDF performance

A senior interviewer might ask: "Your team has a scalar Snowpark UDF that runs at 5K rows/sec/core. The dataset is 5 billion rows. How do you make this fast without leaving Snowpark or rewriting it into SQL?"

Solution Using vectorized UDFs + warehouse scaling + partitioning

# DIAGNOSIS
# 5K rows/sec/core × 16 cores (Large WH) = 80K rows/sec
# 5B rows / 80K = 62500 sec = ~17 hours. Unacceptable.

# FIX 1 — convert to a vectorized UDF (typically 20× speedup)
from snowflake.snowpark.functions import pandas_udf
from snowflake.snowpark.types import PandasSeriesType, FloatType
import pandas as pd, numpy as np

@pandas_udf(name="score_batch",
            return_type=PandasSeriesType(FloatType()),
            input_types=[PandasSeriesType(FloatType()),
                         PandasSeriesType(FloatType())],
            packages=["pandas", "numpy"],
            max_batch_size=50_000,
            session=session)
def score_batch(a: pd.Series, b: pd.Series) -> pd.Series:
    return pd.Series(np.log1p(a.to_numpy()) * np.sqrt(b.to_numpy()))

# FIX 2 — scale the warehouse vertically for the duration
session.use_warehouse("XLARGE_WH")     # 128 cores

# FIX 3 — partition the input so Snowflake parallelises the UDF call
orders = session.table("orders")
out = orders.with_column("score",
                         score_batch(col("amount"), col("velocity")))
out.write.save_as_table("scored_orders", mode="overwrite")
session.use_warehouse("TRANSFORM_WH")  # scale back down

Step-by-step trace.

Step	Before	After fix 1 (vectorized)	+ fix 2 (XL WH)	+ fix 3 (server-side write)
Per-row Python overhead	100%	amortised over 50K	amortised over 50K	amortised over 50K
Rows/sec/core	5K	~100K	~100K	~100K
Cores	16 (Large)	16 (Large)	128 (XLarge)	128 (XLarge)
Total throughput	80K rps	1.6M rps	12.8M rps	12.8M rps
Wall clock on 5B rows	~17 h	~52 min	~6.5 min	~6.5 min

The vectorized UDF moves the FFI cost from per-row to per-batch (50K rows per batch). Scaling the warehouse multiplies cores; partitioning lets Snowflake distribute the UDF call across micro-partitions in parallel. The server-side write avoids shipping results to the client.

Output:

optimisation	dominant cost reduced
Scalar → vectorized	FFI cost per row → per batch
Large → XLarge WH	cores from 16 → 128
In-DataFrame call + save_as_table	zero client network

Why this works — concept by concept:

• Vectorisation amortises FFI — the Python ↔ SQL boundary is the dominant cost in a scalar UDF; sending an Arrow batch across the same boundary once reduces calls by 4 orders of magnitude.
• Warehouse scale is a single knob — XS=1 / S=2 / M=4 / L=16 / XL=128 cores (approximate). Doubling the warehouse size doubles throughput on parallelisable workloads.
• Micro-partition parallelism is automatic — Snowflake parallelises UDF calls across micro-partitions; you don't write any concurrency code. Give the warehouse more cores and it uses them.
• Server-side writes keep results in Snowflake — save_as_table writes the scored rows back to Snowflake without ever materialising them on the client.
• Cost — each optimisation is a multiplicative factor. Vectorisation (~20×) × warehouse scale (~8× from Large to XLarge) × no client materialisation (~minor) compounds into a ~160× wall-clock reduction.

SQL
Topic — data manipulation
Data manipulation challenges

Practice →

SQL Topic — ETL ETL pipeline practice rooms

Practice →

---

4. Stored procedures and Snowpark ML

snowpark stored procedure wraps a Python pipeline as a callable SQL object — snowpark ml adds feature store, modelling, and a registry

The mental model in one line: a Snowpark Python stored procedure is a Python function whose body executes server-side when invoked from SQL; it can use the full Snowpark DataFrame API, call other UDFs, train models, and write back to tables — all without a row of data leaving Snowflake. Snowpark ML layers feature store, modelling primitives, and a model registry on top, so the entire ML lifecycle fits inside the warehouse.

Stored procedure registration patterns.

• @sproc decorator: @sproc(name="run_pipeline", session=session, return_type=StringType(), input_types=[StringType()], packages=["snowflake-snowpark-python", "pandas"]) — the function body becomes a server-side procedure.
• session.sproc.register(...): programmatic equivalent.
• First argument is the Session. Inside the sproc, you receive a Session bound to the calling context; use it to build DataFrames, execute SQL, register temporary UDFs.
• Return type: any SQL-compatible type (STRING, NUMBER, VARIANT, TABLE, etc.). For procedures that return result sets, declare TableType.

Owner's rights vs caller's rights — the security pivot.

• Owner's rights (default). The procedure executes with the privileges of the user who owns the sproc. The caller does not need direct grants on the underlying tables; the sproc owner does. Pattern: a privileged ETL user owns the sproc, an analytics role calls it.
• Caller's rights. The procedure executes with the privileges of the caller. Use this when the procedure should act on whatever the caller can already see — typical for utility procedures, data masking pipelines.
• Snowpark sprocs are owner's-rights by default unless EXECUTE AS CALLER is set. Misunderstanding this is a frequent production access bug — the sproc works for the owner and fails for everyone else.

The sproc invocation lifecycle.

1. CALL run_pipeline('2026-06-22') arrives at the warehouse.
2. The warehouse spawns (or reuses) a Python sandbox process.
3. The Python sandbox imports the procedure's handler, instantiates a Session bound to the calling context, and invokes the handler with the SQL arguments.
4. The handler runs — DataFrame ops, SQL calls, UDFs, writes — all server-side.
5. The handler returns a value; the warehouse marshals it back to the SQL response.

Snowpark ML — feature store, modelling, registry.

• Feature store. Define features as feature_view objects with a refresh schedule. Snowflake materialises them as tables, keeps lineage, and supports time-travel for point-in-time joins.
• Modelling primitives. snowflake.ml.modeling exposes scikit-learn-compatible estimators (LogisticRegression, XGBClassifier) that train inside the warehouse using a distributed worker pool. Same fit/predict API as sklearn.
• Model registry. snowflake.ml.registry.Registry lets you register a trained model under a name and version. Once registered, you can call PREDICT(model_name, ...) from SQL — model inference becomes a SQL function.

Container Services — when the sandbox isn't enough.

• The UDF / sproc sandbox is CPU-only, ~4–8 GB memory, restricted networking. Fine for most pipelines, not enough for GPU workloads, FastAPI services, Triton inference servers, large memory ML.
• Snowpark Container Services runs arbitrary OCI images on a managed pool of warehouse-adjacent compute, including GPU pools. You get a long-running service with HTTP endpoints reachable from inside the warehouse.
• Common pattern: train a large model in a container service, register it to the model registry, then call it from a sproc-orchestrated pipeline like any other model.

Common interview probes on sprocs and ML.

• "What is the difference between a UDF and a stored procedure?" — UDFs are per-row functions used in a SQL SELECT; sprocs are imperative procedures invoked with CALL that can run a whole pipeline.
• "Owner's rights vs caller's rights — when do you pick each?" — owner's rights for privileged ETL where the caller shouldn't need direct grants; caller's rights for utility procedures that should respect the caller's existing privileges.
• "How does Snowpark ML differ from sklearn?" — same API surface, distributed warehouse-side fit, model registered to a registry callable from SQL.
• "When do you need Container Services?" — GPU, long-running services, large memory, FastAPI endpoints, anything the UDF sandbox can't host.

Worked example — sproc that orchestrates an ETL step

Detailed explanation. A daily ETL: read raw events, dedupe by event_id keeping latest, write to a clean table, log a summary. In a notebook this would be 10 lines of Python orchestration; as a sproc it becomes a single CALL invocation that runs entirely inside Snowflake on a schedule.

Question. Write a Snowpark Python stored procedure clean_events(date) that reads raw_events for a given date, dedupes by event_id keeping the latest ts, writes to clean_events, and returns the row count.

Input — raw_events.

event_id	ts	payload
e1	12:00	A
e1	12:05	A'
e2	12:01	B

Code.

from snowflake.snowpark.functions import (col, row_number, lit, count)
from snowflake.snowpark.window import Window
from snowflake.snowpark.types import StringType, IntegerType

@sproc(name="clean_events",
       return_type=IntegerType(),
       input_types=[StringType()],
       packages=["snowflake-snowpark-python"],
       session=session,
       is_permanent=True,
       stage_location="@my_sprocs",
       replace=True,
       execute_as="owner")
def clean_events(session: Session, date_str: str) -> int:
    raw = (session.table("raw_events")
                  .filter(col("event_date") == date_str))

    win = Window.partition_by("event_id").order_by(col("ts").desc())
    dedup = (raw.with_column("rn", row_number().over(win))
                .filter(col("rn") == 1)
                .drop("rn"))

    dedup.write.save_as_table("clean_events",
                              mode="overwrite",
                              column_order="name")
    n = session.table("clean_events").count()
    return n

# Call it from SQL or from Snowpark
session.call("clean_events", "2026-06-22")

Step-by-step explanation.

1. The @sproc decorator registers the function as a permanent procedure under @my_sprocs. The handler script is uploaded; the procedure is now invocable from any SQL client as CALL clean_events('2026-06-22').
2. Inside the handler, the first argument is a Session automatically supplied by the runtime. It binds to the calling user's context — the database, schema, role — but with owner's rights for table access.
3. The DataFrame chain — filter → row_number().over(window) → filter(rn=1) → save_as_table — compiles to one big SQL statement that runs entirely on the warehouse. Zero rows move to the sandbox; the sproc only handles the integer return.
4. session.table("clean_events").count() issues a SELECT COUNT(*) and returns the integer to the procedure's return value, which is then marshalled back to the SQL caller.
5. Because execute_as="owner", callers don't need direct access to raw_events — only to call the procedure. This is the standard "wrap privileged ETL behind a procedure" pattern.

Output (row count returned).

event_id	ts	payload
e1	12:05	A'
e2	12:01	B

clean_events returns 2.

Rule of thumb. Wrap any multi-step Python orchestration that runs on a schedule as a sproc. Trigger it from Snowflake Tasks or from your orchestrator (Airflow, Dagster, Prefect) with a single CALL — no Python compute needs to live in the orchestrator.

Worked example — train and register a sklearn model from a sproc

Detailed explanation. A canonical Snowpark ML pattern: a sproc reads training data from a feature view, trains a scikit-learn model using snowflake.ml.modeling, registers it to the model registry, and returns the model URI. Inference downstream is a SQL PREDICT call.

Question. Write a sproc train_churn_model(version) that trains a logistic regression on the customer_features table, registers the model, and returns the version.

Input — customer_features.

customer_id	tenure_months	mrr	churned
c1	24	100	0
c2	3	50	1
c3	36	200	0

Code.

from snowflake.snowpark.types import StringType

@sproc(name="train_churn_model",
       return_type=StringType(),
       input_types=[StringType()],
       packages=["snowflake-snowpark-python",
                 "snowflake-ml-python",
                 "scikit-learn", "pandas"],
       session=session,
       is_permanent=True,
       stage_location="@my_sprocs",
       replace=True)
def train_churn_model(session: Session, version: str) -> str:
    from snowflake.ml.modeling.linear_model import LogisticRegression
    from snowflake.ml.registry import Registry

    df = session.table("customer_features")

    model = LogisticRegression(
        input_cols=["TENURE_MONTHS", "MRR"],
        label_cols=["CHURNED"],
        output_cols=["CHURN_PROB"],
    )
    model.fit(df)

    reg = Registry(session=session, database_name="ML", schema_name="MODELS")
    mv = reg.log_model(
        model=model,
        model_name="churn_model",
        version_name=version,
        comment="logistic regression on tenure + mrr",
        metrics={"trained_on": df.count()},
    )

    return f"churn_model/{version}"

# Call from a daily Task
session.call("train_churn_model", "v20260622")

# Inference from SQL once registered
# SELECT customer_id, churn_model!PREDICT(tenure_months, mrr) FROM customers;

Step-by-step explanation.

1. The sproc declares the Snowpark ML and scikit-learn packages. Snowflake installs them inside the sandbox at invocation.
2. LogisticRegression(...) from snowflake.ml.modeling accepts column names rather than NumPy arrays. The fit(df) call distributes training across the warehouse: the framework collects the columns into Pandas / NumPy batches on the workers, fits the underlying sklearn estimator, and serialises the model.
3. The model registry call reg.log_model(...) saves the trained model artifact under ML.MODELS.churn_model with version v20260622. The registry maintains versioning, metrics, and lineage.
4. Once registered, the model is callable from SQL via model!PREDICT(...) syntax. The SQL planner treats it like a UDF — micro-partition parallel, vectorized, no notebooks needed for batch inference.
5. The return value ("churn_model/v20260622") is a string that the orchestrator can log or pass to the next pipeline step.

Output (registry record).

model_name	version_name	created_on	metrics
churn_model	v20260622	2026-06-22	{"trained_on": 3}

Rule of thumb. Use Snowpark ML's distributed estimators for training data that fits in warehouse memory (single-large-warehouse scale). Use Container Services with a real Spark / Ray / PyTorch cluster only when you need GPU or multi-node parallelism beyond what a warehouse provides.

Worked example — sproc + Container Service for a FastAPI endpoint

Detailed explanation. A common need: a Python service that listens for HTTP requests and returns a prediction by joining warehouse data. The sproc sandbox can't host a long-running server; Container Services can. The pattern is: spin a container with FastAPI + the model, expose it inside the Snowflake VPC, call it from a sproc when needed.

Question. Outline the architecture for a FastAPI inference service deployed on Snowpark Container Services, called from a sproc that bulk-scores rows from a table.

Input — request payload.

customer_id	tenure_months	mrr
c1	24	100
c2	36	200

Code.

# 1. FastAPI app inside a Docker image (built once, pushed to Snowflake image repo)
# main.py
from fastapi import FastAPI
from pydantic import BaseModel
import joblib

app = FastAPI()
model = joblib.load("/model/churn.pkl")

class Row(BaseModel):
    tenure_months: int
    mrr: float

@app.post("/score")
def score(row: Row):
    p = model.predict_proba([[row.tenure_months, row.mrr]])[0, 1]
    return {"churn_prob": float(p)}

# 2. Push image to Snowflake image repo
# docker tag churn-svc:latest <account>.registry.snowflakecomputing.com/...
# docker push ...

# 3. Create the Service (one-time SQL)
# CREATE SERVICE churn_svc
#   IN COMPUTE POOL gpu_xs
#   FROM SPECIFICATION '{...service spec yaml...}';

# 4. Sproc that bulk-scores by calling the service
@sproc(name="bulk_score",
       return_type=IntegerType(),
       input_types=[],
       packages=["snowflake-snowpark-python", "requests"],
       session=session,
       external_access_integrations=["snowflake_internal_eai"])
def bulk_score(session: Session) -> int:
    import requests
    rows = session.table("customer_features").to_local_iterator()
    n = 0
    for r in rows:
        resp = requests.post(
            "http://churn-svc.snowflake.local/score",
            json={"tenure_months": r.TENURE_MONTHS, "mrr": float(r.MRR)},
            timeout=2)
        # write result back ...
        n += 1
    return n

Step-by-step explanation.

1. The FastAPI app runs as a long-lived process inside a container image. The image bundles the model artifact and dependencies.
2. The image gets pushed to Snowflake's image repository; CREATE SERVICE deploys it on a compute pool (CPU or GPU). The service exposes a stable hostname inside the Snowflake network.
3. The sproc declares an External Access Integration pointing at the service hostname; this gives the sproc network access. (For internal Snowflake-hosted services, the EAI configuration uses the service's internal endpoint.)
4. The sproc iterates rows and calls the FastAPI /score endpoint per row. For higher throughput, batch the requests or pre-vectorise via a vectorized UDF that calls the service with batches of 1K rows.
5. The architecture decouples training (Snowpark ML sproc, write to registry) from serving (Container Service with a stable HTTP endpoint). Bulk scoring uses a sproc; ad-hoc scoring calls the service directly from any client inside the Snowflake VPC.

Output (architecture comparison).

Concern	Sproc only	Sproc + Container Service
Long-lived HTTP server	not possible	yes
GPU support	no	yes (gpu_xs / gpu_l pools)
Custom dependencies	wheel on stage	full OCI image
Memory ceiling	~4–8 GB per sandbox	depends on pool size
Cost	per-query warehouse	per-second service uptime

Rule of thumb. Use Container Services when the workload needs GPU, very large memory, a long-running server, or a stack the UDF sandbox cannot host. For all other cases, the cheaper and simpler answer is a vectorized UDF or a sproc.

Senior interview question on sprocs and security

A senior interviewer might frame this as: "Design a daily ETL pipeline that reads from a privileged raw schema and writes a clean output to an analytics schema. The analytics team owns the output but must not see the raw data. How do you wire this with Snowpark sprocs?"

Solution Using owner's-rights sproc + role separation + scheduled Task

-- One-time setup
USE ROLE SECURITYADMIN;
CREATE ROLE etl_owner;
CREATE ROLE analytics_team;

GRANT USAGE ON DATABASE raw TO ROLE etl_owner;
GRANT USAGE ON DATABASE analytics TO ROLE etl_owner;
GRANT USAGE ON DATABASE analytics TO ROLE analytics_team;
GRANT SELECT ON SCHEMA raw.events TO ROLE etl_owner;
GRANT ALL ON SCHEMA analytics.clean TO ROLE etl_owner;
GRANT USAGE ON SCHEMA analytics.clean TO ROLE analytics_team;
GRANT SELECT ON ALL TABLES IN SCHEMA analytics.clean TO ROLE analytics_team;

USE ROLE etl_owner;

# Register the sproc under etl_owner — owner's rights by default
@sproc(name="daily_clean",
       return_type=IntegerType(),
       input_types=[StringType()],
       packages=["snowflake-snowpark-python"],
       session=session,
       is_permanent=True,
       stage_location="@etl_owner.STAGES.SPROCS",
       replace=True,
       execute_as="owner")
def daily_clean(session: Session, date_str: str) -> int:
    raw = (session.table("raw.events.raw_events")
                  .filter(col("event_date") == date_str))
    cleaned = raw.filter(col("amount") > 0).drop("pii_email", "ip_address")
    cleaned.write.save_as_table("analytics.clean.events", mode="overwrite")
    return cleaned.count()

# Grant call rights to analytics
session.sql("GRANT USAGE ON PROCEDURE daily_clean(STRING) TO ROLE analytics_team")\
       .collect()

-- Daily schedule via Snowflake Task
CREATE OR REPLACE TASK daily_clean_task
  WAREHOUSE = TRANSFORM_WH
  SCHEDULE = 'USING CRON 0 4 * * * UTC'
AS
  CALL daily_clean(TO_VARCHAR(DATEADD(day, -1, CURRENT_DATE)));

ALTER TASK daily_clean_task RESUME;

Step-by-step trace.

Step	Effect
etl_owner has SELECT on raw.events	sproc can read raw
etl_owner has ALL on analytics.clean	sproc can write output
analytics_team has SELECT on analytics.clean	reads output
analytics_team has USAGE on procedure	can CALL but not see source
execute_as="owner"	sproc runs with etl_owner privileges
CREATE TASK schedules daily run	04:00 UTC every day

The analytics team can call daily_clean and read its output, but they cannot directly query raw.events — the sproc is the security boundary.

Output:

concern	how it's enforced
Raw isolation	analytics_team has no grants on raw schema
ETL execution	sproc runs with etl_owner privileges
Daily orchestration	Snowflake Task with cron schedule
Auditability	sproc + Task history queryable

Why this works — concept by concept:

• Owner's rights as a security boundary — the sproc encapsulates privileged access. Callers receive only the result, not the underlying access path.
• Role separation — etl_owner is the privileged role that owns the pipeline; analytics_team is the consumer role with read-only access to the output. Standard Snowflake RBAC pattern.
• Tasks as the scheduler — Snowflake Tasks let you schedule sproc calls without an external orchestrator. For complex DAGs, chain Tasks or wire Airflow with the SnowflakeOperator calling the sproc.
• Auditability — every sproc invocation is logged in QUERY_HISTORY and TASK_HISTORY; the analytics team's reads are also logged. Full audit trail with no extra tooling.
• Cost — sproc compute lives on the warehouse named in the Task; the analytics team's reads live on whichever warehouse they query from. Bill split by role/team naturally.

SQL
Topic — ETL
ETL design problems

Practice →

SQL Topic — SQL SQL warehouse drills

Practice →

---

5. Snowpark vs Spark vs notebooks — choosing the right runtime

snowpark vs spark collapses to "where does the data live, and does it want to stay there?"

The mental model in one line: Snowpark wins when the data lives in Snowflake and the pipeline is allowed to stay there; Spark wins when the workload spans clouds, sources, or engines; local notebooks win when the dataset is small enough to fit on a laptop and exploration speed matters more than reproducibility. Every other comparison — cost, latency, ML maturity, hiring pool — follows from where the bytes already live and how much portability you need.

The five questions senior engineers ask in order.

• Q1 — where does the source data live? If it is already in Snowflake (and likely to stay there), Snowpark is eligible. If it is split across S3 + Postgres + BigQuery + Kafka, Spark's connector ecosystem is the easier story.
• Q2 — what is the destination? If outputs go back to Snowflake (tables, materialised views), Snowpark closes the loop without egress. If outputs go to many sinks (S3 Iceberg, BigQuery, Postgres, Pulsar), Spark's sink connectors win.
• Q3 — what is the compute envelope? Snowpark scales with warehouse size (XS → 6XL); Spark scales horizontally on a cluster. For ≤ tens of TB and well-aggregable workloads, warehouse scaling is simpler. For multi-PB or multi-hour batch jobs, a dedicated Spark cluster amortises better.
• Q4 — what is the language and library surface? Snowpark is Python (+ Java/Scala APIs); Spark is Scala/Python/Java/R/SQL with a much wider library ecosystem (Spark MLlib, Spark ML, GraphX, Spark Streaming, MLflow integration).
• Q5 — what is the ops capacity? Snowpark inherits Snowflake's ops — no clusters, no schedulers, no Spark config tuning. Spark requires either a managed service (Databricks, EMR, Dataproc) or a platform team. For small teams without Spark muscle memory, Snowpark is dramatically simpler.

Snowpark sweet spot — the 2026 reality.

• Pipelines that read from Snowflake, transform, and write back to Snowflake. Zero egress. No new infrastructure. The 80% case for many DE teams.
• Per-row Python that fits a vectorized UDF. Scoring, feature engineering, business logic that's awkward in SQL.
• ML training and inference on data already in Snowflake via Snowpark ML; model registry callable from SQL.
• Daily / hourly batch ETL orchestrated by Snowflake Tasks or external orchestrator (Airflow with SnowflakeOperator).

Spark sweet spot.

• Multi-source ingest pipelines — Kafka + S3 + JDBC CDC + Salesforce + Iceberg in the same job. Spark's connector ecosystem is unmatched.
• Multi-PB batch workloads — TPC-DS-scale joins, full-cluster shuffle workloads.
• Streaming with low latency — Spark Structured Streaming or, increasingly, Flink for sub-second.
• ML training that needs distributed compute beyond a single warehouse — Spark ML, MLlib, or PyTorch-on-Spark.
• Multi-cloud or hybrid stacks — Databricks runs on AWS, Azure, GCP; the same job moves across clouds.

Local notebook sweet spot.

• Datasets that fit on a laptop (≤ a few GB after sampling). pandas / Polars / DuckDB on a SQLite or Parquet snapshot.
• Pure exploration before writing the production pipeline. Notebook-driven hypothesis testing, plot iteration, model prototyping.
• Anything where reproducibility comes later. Notebooks are great for first-pass, terrible for "we run this every day at 04:00."

Cost model — what each runtime actually bills.

• Snowpark. Pure warehouse credits. XS warehouse = ~1 credit/hour idle (auto-suspend), and per-second billing once active. Storage billed separately.
• Spark on Databricks. DBU/hour for the cluster + cloud compute under it; managed-service premium of ~30–50% over raw EC2/GCE.
• Spark on EMR. EC2 hourly + EMR per-instance markup. Cheaper than Databricks; more ops.
• Local notebook. Engineer's laptop. Zero infra cost; high opportunity cost when the workload outgrows it.

Common interview probes on the runtime choice.

• "Why would you pick Snowpark over PySpark for a new pipeline?" — data lives in Snowflake, no egress, no cluster ops, vectorized UDFs cover the per-row Python.
• "When is Snowpark the wrong choice?" — multi-source, multi-cloud, multi-PB, sub-second streaming, GPU-bound training at scale.
• "What about Polars / DuckDB?" — great for local dev and ≤ 100 GB ad-hoc workloads; rarely the production answer when the data is already in Snowflake.
• "How does Snowpark ML compare to Databricks ML?" — both ship distributed estimators + a registry + serving; Databricks is more mature on the ML lifecycle side, Snowpark wins on the "stay in warehouse" axis.

Worked example — decision tree on a new feature pipeline

Detailed explanation. A team needs to compute customer-level features (revenue, order count, churn risk) daily. The data lives in Snowflake's analytics schema. The output table feeds a dashboard and a downstream sklearn model. Walk through the five questions and see which runtime wins.

Question. Apply the 5-question decision tree to the customer-feature pipeline. Show which runtime is picked and why.

Input — pipeline requirements.

dimension	answer
source	Snowflake analytics.orders
destination	Snowflake analytics.customer_features
volume	500 M rows / day
compute envelope	warehouse-fit
ML	sklearn classifier downstream
ops capacity	2 DEs, no platform team

Code.

# Snowpark — sproc that builds the feature table daily
@sproc(name="build_features",
       return_type=IntegerType(),
       input_types=[StringType()],
       packages=["snowflake-snowpark-python"],
       session=session,
       is_permanent=True,
       stage_location="@my_sprocs",
       replace=True)
def build_features(session: Session, date_str: str) -> int:
    orders = session.table("analytics.orders")
    win = orders.filter(col("ts") >= date_str)
    feats = (win.group_by("customer_id")
                .agg(sum_("amount").alias("revenue"),
                     count("*").alias("order_count")))
    feats.write.save_as_table("analytics.customer_features",
                              mode="overwrite")
    return feats.count()

# Schedule via a Snowflake Task at 04:00 daily
# CREATE TASK build_features_task ...

Step-by-step explanation.

1. Q1 — source is Snowflake-only. Snowpark eligible.
2. Q2 — destination is Snowflake. Snowpark closes the loop with zero egress.
3. Q3 — 500 M rows/day is well within an XS or S warehouse's batch window. No cluster needed.
4. Q4 — the per-row Python is pure aggregation (SUM, COUNT) which the planner pushes to native SQL. No need for vectorized UDFs in the pipeline itself; the downstream sklearn model can train via Snowpark ML on the resulting feature table.
5. Q5 — 2 DEs, no platform team. Snowpark is a single SDK import; Spark would require a Databricks subscription or self-managed EMR. Snowpark is the unambiguous pick.

Output.

Question	Answer	Verdict
Q1 source	Snowflake-only	Snowpark eligible
Q2 destination	Snowflake	Snowpark closes loop
Q3 envelope	500 M rows / day	warehouse-fit
Q4 language	Python + sklearn (downstream)	Snowpark + Snowpark ML
Q5 ops	2 DEs, no platform team	Snowpark (no cluster)

Rule of thumb. When Q1 + Q2 both say "Snowflake" and Q5 says "small team", Snowpark wins almost every time. The only counter-arguments are multi-source ingest (Q1) or sub-second latency (Q3).

Worked example — when Spark wins despite Snowpark eligibility

Detailed explanation. Spark sometimes wins even when the data is in Snowflake. The classic counterexample: the pipeline reads from Snowflake plus Kafka plus a daily S3 dump, joins them, and writes to Iceberg on S3 with strict EOS guarantees. Snowpark cannot ingest Kafka directly, cannot write Iceberg-on-S3 transactionally, and forces an external orchestrator.

Question. A pipeline needs to combine Snowflake orders + Kafka clickstream + daily S3 inventory dump, then write to Iceberg with exactly-once semantics. Show why Spark wins despite Snowflake being a source.

Input — sources.

source	type	volume
Snowflake.orders	warehouse	500 M rows / day
Kafka.clicks	stream	1 B events / day
S3.inventory	daily Parquet	50 GB
Iceberg.unified (sink)	data lake	append + EOS

Code.

# Spark — multi-source ingest, Iceberg sink with EOS
from pyspark.sql import SparkSession
from pyspark.sql.functions import col

spark = (SparkSession.builder
        .config("spark.sql.extensions",
                "org.apache.iceberg.spark.extensions.IcebergSparkSessionExtensions")
        .getOrCreate())

orders = (spark.read.format("snowflake")
                   .options(**sf_opts).option("dbtable", "orders").load())
clicks = (spark.readStream.format("kafka")
                .option("subscribe", "clicks").load())
inventory = spark.read.parquet("s3://acme-prod/inventory/daily/2026-06-22/")

joined = (orders.join(clicks, "customer_id")
                .join(inventory, "sku")
                .select(...))

(joined.writeStream.format("iceberg")
                   .option("path", "s3://acme-prod/iceberg/unified")
                   .option("checkpointLocation", "s3://acme-prod/ckpt/unified")
                   .outputMode("append")
                   .trigger(processingTime="1 minute")
                   .start())

Step-by-step explanation.

1. Q1 — sources span Snowflake, Kafka, S3. Snowpark can read Snowflake but does not natively consume Kafka streams. Spark wins on multi-source.
2. Q2 — sink is Iceberg-on-S3 with EOS. Snowpark cannot write Iceberg-on-S3 with strict EOS through Snowflake; Spark + Iceberg + checkpointing gives the transactional sink.
3. Q3 — Kafka source has its own latency and throughput envelope; Spark Structured Streaming handles micro-batch ingest natively.
4. Q4 — Spark's connector ecosystem (Snowflake, Kafka, Iceberg, JDBC, S3) is the broadest in the industry; Snowpark has no equivalent.
5. Q5 — the team is presumed to have Spark capacity (Databricks subscription or self-managed). Without that capacity, the right call is to redesign the architecture: land Kafka into Snowflake via Snowpipe Streaming, ingest the S3 dump via a stage, and reshape the problem so Snowpark can own it.

Output.

Question	Answer	Verdict
Q1 source	Snowflake + Kafka + S3	Spark (multi-source)
Q2 sink	Iceberg-on-S3 EOS	Spark (Iceberg writer)
Q3 envelope	streaming + batch	Spark (Structured Streaming)
Q4 language	Python + Scala connectors	Spark
Q5 ops	Spark team or Databricks	Spark eligible

Rule of thumb. When even one source or sink lives outside Snowflake and EOS or low latency is required, Spark usually wins. The Snowpark "stay in warehouse" pull only matters if the entire pipeline can stay there.

Worked example — local notebook vs warehouse for exploration

Detailed explanation. A common mistake in 2026: spinning up a warehouse for every notebook session when the underlying dataset fits in a laptop. Polars + DuckDB on a 5 GB Parquet snapshot is often 10× faster (and free) versus a paused-then-resumed XS warehouse.

Question. Compare loading a 5 GB Parquet of orders into pandas, Polars, DuckDB, and Snowpark for ad-hoc exploration. Which is best for a 30-minute investigation?

Input.

dataset	format	size	location
orders	Parquet	5 GB	s3://acme-prod/orders/

Code.

# Option A — pandas (laptop)
import pandas as pd
df = pd.read_parquet("orders.parquet")    # ~30s, ~7 GB RAM
df.groupby("region")["amount"].sum()

# Option B — Polars (laptop, lazy)
import polars as pl
df = pl.scan_parquet("orders.parquet")
out = df.group_by("region").agg(pl.col("amount").sum()).collect()  # ~3s

# Option C — DuckDB (laptop, SQL on Parquet)
import duckdb
out = duckdb.sql("""
    SELECT region, SUM(amount) FROM 'orders.parquet' GROUP BY region
""").df()  # ~2s

# Option D — Snowpark (warehouse)
df = session.read.parquet("@stage/orders.parquet")
df.group_by("region").agg(sum_("amount")).show()  # ~10s + warehouse resume

Step-by-step explanation.

1. pandas reads the whole file into RAM and aggregates eagerly; 30 seconds + 7 GB resident memory. Functional but heavy.
2. Polars uses lazy scanning and columnar execution; it only reads the region and amount columns and aggregates in 2–3 seconds. Dramatically faster on the same laptop.
3. DuckDB runs SQL over Parquet files directly with columnar vectorised execution; comparable to Polars in speed, with SQL ergonomics.
4. Snowpark requires staging the file, resuming the warehouse, and shipping the query. For one query, the warehouse resume cost dominates. For 50 queries in the session, the resume cost amortises and Snowpark becomes competitive.
5. The right answer depends on session length. 30-minute investigation on a single dataset → laptop with Polars/DuckDB. Multi-hour cross-table investigation → Snowpark on a warehouse.

Output.

runtime	latency (single query)	RAM	cost
pandas	30s	7 GB	laptop
Polars	3s	2 GB	laptop
DuckDB	2s	2 GB	laptop
Snowpark	10s + resume	warehouse	credits

Rule of thumb. For a 30-minute ad-hoc investigation on a single ≤ 10 GB dataset, the laptop wins on speed and cost. Snowpark wins when the investigation spans multiple tables, needs production data freshness, or needs to be reproducible by a teammate without dataset sharing.

Senior interview question on runtime selection in 2026

A senior interviewer might frame this as: "You join a team that wants to add real-time features to a recommender model. The features need to be available within 1 minute of an event, the source is Kafka, the model lives in Snowpark ML's registry, and the inference must run from a SQL query joining the live features. Walk me through the runtime choice."

Solution Using Snowpipe Streaming + Snowpark sproc + Container Service

Architecture — real-time recommender features in 2026
=====================================================

1. Kafka clicks topic
2. Snowpipe Streaming (low-latency ingest, sub-second to Snowflake table)
3. Snowpark sproc (Task-triggered every 30s): aggregates last 5 min into
   a feature table feature_live.user_features
4. Snowpark ML registry: model "rec_v3" already registered
5. SQL inference:
     SELECT user_id, rec_v3!PREDICT(feature_a, feature_b, ...) AS score
     FROM feature_live.user_features
     WHERE updated_at >= DATEADD(min, -1, CURRENT_TIMESTAMP);
6. (Optional) FastAPI on Container Services for very low-latency per-request
   inference — but only if SQL latency is insufficient.

Why each piece
--------------
- Snowpipe Streaming gets clicks into Snowflake within seconds — no Spark needed.
- Snowpark sproc + Task is the orchestration; cron-like at 30s granularity.
- Snowpark ML registry exposes the model as a SQL function — inference is just SQL.
- Container Service is a fallback for the rare case where SQL latency is too high.

What we avoided
---------------
- A separate Flink job for windowed aggregation (Snowpark sproc + window functions covers it).
- A separate model serving stack (Snowpark ML registry exposes PREDICT in SQL).
- A separate feature store (Snowpark ML feature store handles it natively).
- Network egress (everything stays inside Snowflake until the API layer).

Step-by-step trace.

Requirement	Choice	Why
Kafka ingest	Snowpipe Streaming	sub-second to Snowflake table, no Spark
1-minute latency on features	Snowpark sproc + 30s Task	well within budget
Feature aggregation	Snowpark window functions	native SQL pushdown
Model serving	Snowpark ML registry PREDICT	SQL-native, no separate stack
Ad-hoc inference	SQL query	analyst-friendly
Per-request low-latency	Container Service (FastAPI)	escape hatch only if needed

Five Snowpark answers → Snowpark plus Snowpipe Streaming is the obvious pick. If the latency target had been < 100 ms per request, the architecture would shift toward a Container Service for serving with Snowpark still owning training and batch features.

Output:

verdict	reasoning
Pick Snowpark + Snowpipe Streaming	Kafka → Snowflake → features → SQL inference all stays inside Snowflake
Don't pick Spark	no multi-source / multi-cloud requirement; Snowpipe handles Kafka
Don't pick a separate serving stack	Snowpark ML registry exposes PREDICT in SQL

Why this works — concept by concept:

• Snowpipe Streaming closes the ingest gap — in 2026 Snowflake can ingest Kafka sub-second, removing the historical reason to put Spark in front.
• Sprocs + Tasks are the orchestrator — 30-second cadence Tasks are fine for 1-minute SLA; no separate orchestrator.
• Snowpark ML registry = SQL-native serving — model!PREDICT(...) is a UDF-like call from SQL; no model server to operate for batch / SQL-driven inference.
• Container Services as the escape hatch — keep them in your toolkit for the genuine cases (per-request low-latency, GPU); don't reach for them by default.
• Cost — the architecture compounds: zero egress, one platform, one set of credits, one auth model. Senior signal in the interview is recognising the 2026 reality that Snowflake's surface area now covers the whole pipeline.

SQL
Topic — ETL
ETL pipeline design challenges

Practice →

SQL
Topic — aggregation
Aggregation drills for warehouses

Practice →

---

Cheat sheet — Snowpark Python recipes

• Session in 4 lines. from snowflake.snowpark import Session → session = Session.builder.configs(params).create() → use → session.close(). One Session per process; switch warehouses with session.use_warehouse("WH_NAME").
• Lazy DataFrame chain. session.table("x").filter(...).group_by(...).agg(...).sort(...).limit(10).show() — every op is lazy; only show() / collect() / count() / to_pandas() / write.* trigger SQL execution. Chain freely; the SDK compiles into one query.
• Pandas-on-Snowpark migration. import modin.pandas as pd + register the Snowpark backend, then pd.read_snowflake("table") returns a Modin DataFrame that compiles to Snowpark. Drop-in for most pandas code; unsupported ops fall back or raise.
• Vectorized UDF skeleton. @pandas_udf(return_type=PandasSeriesType(FloatType()), input_types=[PandasSeriesType(FloatType())], packages=["pandas", "numpy"], max_batch_size=50000, session=session) and accept/return pd.Series. Default to vectorized over scalar for any non-trivial Python.
• UDTF skeleton. Implement a class with process(self, *args) yielding tuples and optional end_partition(self); register with session.udtf.register(handler, output_schema=StructType([...]), input_types=[...]). Use when one input row maps to a variable number of output rows.
• Anaconda packages vs custom wheels. packages=["scikit-learn", "pandas"] for the curated channel; imports=["@stage/my_wheel.whl"] for niche libraries. C extensions must match the sandbox's manylinux profile.
• External Access Integration. CREATE NETWORK RULE ... ALLOWED_NETWORK_RULES = (...) + CREATE EXTERNAL ACCESS INTEGRATION ... + attach to UDF/sproc with external_access_integrations=["..."]. Pair with secrets for credentials.
• Sproc deploy via @sproc. @sproc(name="...", return_type=..., input_types=[...], packages=[...], session=session, is_permanent=True, stage_location="@my_sprocs", replace=True, execute_as="owner"). First arg is Session; orchestrate multi-step pipelines server-side.
• Owner's vs caller's rights. execute_as="owner" (default) — sproc runs with the owner's privileges; callers need only USAGE on the procedure. execute_as="caller" — runs with the caller's privileges; use for utility procedures.
• Snowpark ML model registration. from snowflake.ml.registry import Registry → Registry(session, database_name="ML", schema_name="MODELS").log_model(model, model_name=..., version_name=..., metrics={...}). Once registered, call as model_name!PREDICT(...) from SQL.
• Container Services trigger. Build OCI image → push to *.registry.snowflakecomputing.com → CREATE SERVICE ... IN COMPUTE POOL gpu_xs FROM SPECIFICATION '...'. Reach the service from sprocs via External Access Integration or from inside the Snowflake VPC.
• Cache an intermediate. df = df.cache_result() materialises to a session-scoped temp table; subsequent reads are cheap. Use when an upstream chain is expensive and reused 3+ times. For longer reuse, write a real table.
• Anti-pattern alarm. Any for row in df.collect(): ... loop is wrong; lift the iteration into a group_by or a UDF. Any .to_pandas() on a multi-GB DataFrame OOMs the client; aggregate first or write server-side.
• Snowpark vs Spark heuristic. Data lives in Snowflake + outputs to Snowflake + ≤ tens of TB + Python sandbox is enough → Snowpark. Multi-source / multi-sink / multi-PB / GPU at scale → Spark. Sub-second per-request inference → Container Services or external serving stack.

Frequently asked questions

What is Snowpark Python and how does it differ from running Python against Snowflake?

Snowpark Python is two things in one: a client-side SDK (snowflake-snowpark-python) that lets you build lazy DataFrames and register UDFs/sprocs, and a server-side runtime — a sandboxed CPython process — that executes those UDFs, sprocs, and DataFrame chains inside a Snowflake virtual warehouse. The crucial difference from "running Python against Snowflake" via snowflake-connector-python is that connector-based code runs entirely on your client (it just issues SQL); Snowpark moves the compute to the data. A df.group_by(...).agg(...).show() in Snowpark compiles to one SQL statement on the warehouse and returns only the aggregated result; the analogous pandas + connector pattern would pull every row to the client first. The snowpark python runtime is what makes it possible to run sklearn training, vectorized UDFs, and full ETL pipelines without dragging multi-GB datasets across the network.

Snowpark vs PySpark — which is faster?

The honest answer: it depends on whether the data is already in Snowflake. If yes, Snowpark wins almost every time because there is no egress: a DataFrame chain compiles to SQL and runs on the warehouse next to the storage. PySpark either reads through the Snowflake connector (export bytes to Spark, run, write back) or runs on a separate cluster with its own data lake. For pure shuffle-heavy multi-TB joins on dedicated clusters, well-tuned Spark can edge out a single warehouse on raw throughput, but the comparison is rarely apples-to-apples — Snowpark scales by warehouse size (XS → 6XL), Spark scales by adding executors. Empirically, for warehouse-fit workloads where the data lives in Snowflake, Snowpark is faster and cheaper because you avoid the export round-trip and the cluster overhead. The snowpark vs spark interview answer is "Snowpark if the data already lives in Snowflake and the pipeline can stay there; Spark if not."

Are Snowpark UDFs cached, and what is the cold-start cost?

Yes — Snowflake reuses UDF sandboxes across invocations within a query and across queries on the same warehouse when possible. The first call to a UDF in a fresh warehouse pays a cold-start cost: load the Python interpreter, install declared packages (packages=[...]), extract any custom wheels (imports=[...]), import the handler. This is typically a few hundred milliseconds to a few seconds depending on the package set. Subsequent calls on the same warehouse hit the warm cache and pay negligible startup. The practical implications: (1) for batch UDFs that process millions of rows, the cold start is irrelevant; (2) for low-volume or first-of-the-day UDFs, the cold start dominates and is often the right thing to measure when tuning latency; (3) keeping the warehouse warm (do not auto-suspend during business hours) avoids cold starts at the cost of idle credits. Vectorized UDFs amortise both the cold start and the per-row FFI cost across batches of 10K–50K rows, which is why they are the default for any non-trivial Python.

Can I run custom Python packages in Snowpark UDFs and sprocs?

Yes — through three channels. First, the Anaconda channel inside Snowflake ships hundreds of curated packages (pandas, numpy, scikit-learn, xgboost, requests, pyarrow, etc.); declare them with packages=["package_name"] on the UDF/sproc decorator. Second, custom wheels uploaded to a stage are referenced via imports=["@my_stage/my_wheel.whl"]; the sandbox extracts the wheel into the Python path before running the handler. Pure-Python wheels work universally; wheels with C extensions need to match Snowflake's manylinux profile (most manylinux2014_x86_64 wheels work). Third, snowflake-ml-python and other Snowflake-provided packages are first-class — declare them in packages like any other. For packages that need network access (telemetry, fetch-at-import-time), pair the packages declaration with an External Access Integration so the sandbox can reach the relevant hosts.

How do I call an external API from inside Snowpark?

Use an External Access Integration with a network rule allowlist. The default UDF/sproc sandbox is network-isolated — requests.get(...) raises a DNS error because no DNS server is reachable. To open the network: (1) CREATE NETWORK RULE my_rule MODE = EGRESS TYPE = HOST_PORT VALUE_LIST = ('api.example.com:443'); (2) CREATE EXTERNAL ACCESS INTEGRATION my_eai ALLOWED_NETWORK_RULES = (my_rule) ENABLED = TRUE; (3) attach to the UDF/sproc with external_access_integrations=["my_eai"] on the decorator. For credentials, create a secret (CREATE SECRET ... TYPE = GENERIC_STRING ...), grant the integration access to it, and read it from the sandbox via the _snowflake.get_generic_secret_string(...) API. This pattern lets you call payment gateways, ML APIs, internal microservices, or write to webhooks from inside a UDF without leaking credentials or opening the whole internet to the sandbox. Use it sparingly — every external call is a synchronous round-trip and a potential reliability dependency for your warehouse query.

When do I need Snowpark Container Services instead of UDFs or sprocs?

Choose Container Services when any of the four sandbox limits hits: (1) GPU — the UDF/sproc sandbox is CPU-only; Container Services has GPU pools (gpu_xs, gpu_l) for training and inference. (2) Long-running services — UDFs and sprocs are request-scoped; a FastAPI app or Triton inference server runs as a long-lived service in containers. (3) Large memory — the sandbox is bounded (~4–8 GB depending on warehouse); a container can request much larger allocations from the compute pool. (4) Stack the sandbox can't host — anything that needs custom system libraries, a non-Python runtime, or a Linux kernel feature outside the sandbox's allowlist. For everything else — vectorized scoring, ETL orchestration, Snowpark ML training on warehouse-fit data, registry-based SQL inference — a vectorized UDF or sproc is cheaper, simpler, and gives you tighter Snowflake-native integration. The senior signal is recognising Container Services as the escape hatch, not the default.

Practice on PipeCode

• Drill the SQL practice library → to sharpen the warehouse-side SQL that Snowpark DataFrames compile to.
• Rehearse on the ETL practice rooms → for the multi-step sproc + Task orchestration patterns.
• Shore up the aggregation library → for the windowed-aggregate and group-by patterns Snowpark pushes down.
• Stack the data-manipulation drills → for the row-shaping and feature-engineering surface UDFs and DataFrames target.
• For the broader surface, read top data engineering interview questions →.
• Stack the prerequisites with the only 5 skills you need to become a data engineer →.
• Sharpen the Spark axis with the Apache Spark internals course →.
• For ETL system design, work through the ETL system design course →.

Lock in Snowpark Python muscle memory

Docs explain the API. PipeCode drills explain the decision — when vectorized UDF beats scalar, when a sproc beats client-side orchestration, when Snowpark beats Spark for your workload. Pipecode.ai is Leetcode for Data Engineering — pattern-first practice tuned for the production trade-offs senior data engineers actually face.

Practice SQL problems →
Practice ETL problems →