TopolearnCustom logic and Python data work can run next to the data instead of pulling it out.
Snowflake runs code inside the platform in several forms.
- SQL UDFs:
CREATE FUNCTION f(x NUMBER) RETURNS NUMBER AS 'x * 2'. Simple, fast, and often inlined into the query. - Python, Java, Scala and JavaScript UDFs: the handler runs in a secure sandbox on the warehouse. Python UDFs can use packages from the built-in Anaconda channel via
PACKAGES = ('numpy'). Vectorized Python UDFs receive pandas batches and are much faster than row-at-a-time ones. - UDTFs return a table, one input row producing many output rows.
- Stored procedures (SQL Scripting, Python, Java, JavaScript) run procedural logic and can execute SQL statements: loops, DDL, dynamic SQL, error handling. Call them with
CALL. Procedures run with owner's rights by default or withEXECUTE AS CALLER.
Snowpark is a DataFrame API for Python, Java and Scala. You write session.table("orders").filter(col("amount") > 100).group_by("region").agg(...); nothing executes until an action such as collect() or show(), and then Snowpark compiles the whole chain into SQL that runs on the warehouse. Data never leaves Snowflake unless you to_pandas() it. The same session can register UDFs from Python functions.
SQL Scripting blocks (DECLARE ... BEGIN ... END) can also run anonymously, without creating a procedure, which is handy for admin scripts.
Example
CREATE OR REPLACE FUNCTION net_price(price NUMBER(10,2), vat NUMBER(4,2))
RETURNS NUMBER(10,2)
AS 'price / (1 + vat)';
CREATE OR REPLACE FUNCTION slugify(s STRING)
RETURNS STRING
LANGUAGE PYTHON
RUNTIME_VERSION = '3.11'
HANDLER = 'slug'
AS $$
import re
def slug(s):
return re.sub(r'[^a-z0-9]+', '-', s.lower()).strip('-')
$$;
SELECT net_price(121, 0.21), slugify('Hello, Snowflake World!');
-- 100.00 | hello-snowflake-world
-- Snowpark (Python), run from a client or notebook:
-- df = session.table("orders").filter(col("amount") > 100).group_by("region").count()
-- df.show() # compiled to SQL and executed in SnowflakeRemember
- SQL UDFs for simple expressions; Python UDFs for richer logic
- Vectorized Python UDFs process pandas batches and are much faster
- Snowpark DataFrames are lazy and compile to SQL that runs in Snowflake
Watch out for
- Calling to_pandas() on a huge Snowpark DataFrame and pulling everything to the client.