10.3 Snowpark, User-Defined Functions & Stored Procedures

10.3 Snowpark, User-Defined Functions & Stored Procedures

Enterprise data architectures require advanced programmability beyond declarative SQL. Organizations need to execute complex machine learning pipelines, run data science feature engineering, implement procedural administrative workflows, and securely integrate with external microservices. Snowflake provides a comprehensive extensibility framework consisting of Snowpark, User-Defined Functions (UDFs & UDTFs), Stored Procedures, and External Functions.

On the SnowPro Advanced: Architect exam, questions test the deep architectural boundaries of these tools: how Snowpark transpiles DataFrame logic into SQL, when to utilize vectorized batch UDFs over row-by-row functions, how Owner's Rights versus Caller's Rights alter security boundaries, and how External Functions communicate across cloud network perimeters.

---

Snowpark Architecture & Pushdown Execution

Snowpark is a developer framework that brings first-class language runtimes—specifically Python, Java, and Scala—directly into Snowflake's scalable compute engine. Historically, data scientists extracted data from a data warehouse into external client servers or notebooks to run Python/Pandas workflows, incurring massive data transfer overhead, network egress costs, and security compliance risks.

┌────────────────────────────────────────────────────────────────────────────────────────┐
│                              SNOWPARK ARCHITECTURE                                     │
├────────────────────────────────────────────────────────────────────────────────────────┤
│ Client Machine / IDE (Python / Java / Scala):                                          │
│ • Developer writes DataFrame API code: df.filter(...).groupBy(...).agg(...)            │
│ • Lazy Evaluation: Builds an Abstract Syntax Tree (AST) locally                        │
│ • Zero Data Transfer: No data rows travel from Snowflake to the client                 │
├────────────────────────────────────────────────────────────────────────────────────────┤
│                                           │ Action (.collect(), .save_as_table())       │
│                                           ▼ Transpiles AST into Native SQL              │
│ Snowflake Virtual Warehouse Compute:                                                   │
│ • Executes single optimized SQL plan across warehouse cluster nodes                    │
│ • Secure Sandbox Runtimes execute Python/Java/Scala UDFs directly adjacent to data     │
│ • Pre-installed Anaconda Package Repository (numpy, pandas, scikit-learn, pytorch)     │
└────────────────────────────────────────────────────────────────────────────────────────┘

1. Lazy Evaluation and AST Construction

Snowpark DataFrames are evaluated lazily. When a developer chains DataFrame transformations (e.g., df.filter(col('status') == 'ACTIVE').select('id', 'revenue')), Snowpark does not execute any queries or move data. Instead, it constructs a client-side Abstract Syntax Tree (AST) that records the sequence of operations.

2. SQL Transpilation and Warehouse Pushdown

Execution occurs only when the developer triggers an action:

• .collect(): Returns results to the client.
• .count(): Returns the row count.
• .save_as_table('target_table') or .copy_into_table(): Writes data directly to a table.
• .show(): Prints a sample of rows to the console.

Upon encountering an action, the Snowpark client library transpiles the entire AST into a single, deeply optimized Snowflake SQL statement. This SQL statement is submitted to the Cloud Services layer and executed entirely inside the virtual warehouse. The data never leaves the Snowflake security perimeter.

3. Integrated Anaconda Ecosystem & Sandboxing

Snowflake partners with Anaconda to provide a curated, secure repository of thousands of third-party open-source packages (including numpy, pandas, scipy, scikit-learn, and xgboost) pre-installed on Snowflake warehouse compute nodes:

• No External Pip Downloads: Packages are delivered from an internal, secure Snowflake-managed channel, eliminating network exfiltration vectors.
• Secure Sandboxing: Code executes inside secure, isolated sandboxes on virtual warehouse nodes, preventing unauthorized filesystem access or cross-tenant interference.

4. Snowpark-Optimized Virtual Warehouses

Standard Snowflake virtual warehouses provide balanced compute and memory suitable for SQL workloads. However, memory-intensive machine learning training, large language model inference, or complex feature engineering pipelines can trigger memory exhaustion or excessive disk spilling on standard compute.

To address this, Snowflake offers Snowpark-Optimized Warehouses:

• Provide 16x more memory per node than a standard warehouse in the default configuration (RESOURCE_CONSTRAINT = MEMORY_16X).
• Can be configured for other memory sizes and CPU architectures: MEMORY_1X (from XSMALL), MEMORY_16X (from MEDIUM, the default size), and MEMORY_64X (from LARGE), each with an x86 variant.
• Are billed at higher credit rates than standard warehouses of the same size (see the Snowflake Service Consumption Table), so they should be reserved for memory-bound work.

User-Defined Functions (UDFs) & User-Defined Table Functions (UDTFs)

A User-Defined Function (UDF) enables custom business logic or mathematical calculations to be invoked directly inside SQL queries.

UDF Classifications

1. Scalar UDFs: Accept zero or more scalar inputs and return exactly one scalar value per row. Can be invoked anywhere a standard scalar function is valid (e.g., SELECT my_udf(col1) FROM tab).
2. User-Defined Table Functions (UDTFs): Accept input parameters and return a set of rows (a virtual table) consisting of zero, one, or multiple rows with one or more columns. Invoked in the FROM clause using the TABLE(my_udtf(...)) syntax.

Supported Languages

Snowflake supports UDF development across five languages:

• SQL: Inlined directly into the query execution plan by the optimizer; fastest performance for relational logic.
• JavaScript: Executes within an internal V8 engine sandbox; ideal for lightweight JSON manipulation.
• Python: Executes within the Anaconda runtime; provides access to data science libraries.
• Java: Pre-compiled .jar files staged on internal stages; excellent for porting legacy enterprise logic.
• Scala: Compiled bytecode running within the Java Virtual Machine (JVM).

In-Line vs. Pre-Compiled Staged Functions

• In-Line UDFs: The function source code is written directly inside the CREATE FUNCTION DDL statement. Snowflake automatically compiles and caches the code.
• Staged (External) UDFs: The compiled artifacts (such as Java .jar files or Python .zip packages) are uploaded to an internal or external Snowflake stage. The CREATE FUNCTION DDL references the staged file using the IMPORTS clause (IMPORTS = ('@my_stage/model.joblib')).

Secure UDFs (CREATE SECURE FUNCTION)

In multi-tenant architectures and data sharing configurations, standard UDF definitions are visible in GET_DDL and metadata views. Furthermore, the Snowflake query optimizer may evaluate where-clause predicates before executing a UDF, potentially exposing sensitive data through error messages or timing attacks.

By designating a UDF as SECURE (CREATE SECURE FUNCTION):

1. The internal source code and implementation details are hidden from unauthorized users and data share consumers.
2. The query optimizer guarantees that security boundaries are strictly enforced: it prevents optimizations that evaluate secure function expressions prior to row-access and view filters.

Vectorized Python UDFs (@pandas_udf)

When writing Python UDFs, the default execution model is row-by-row scalar processing. For high-throughput analytical queries processing hundreds of millions of rows, row-by-row scalar UDFs introduce massive performance bottlenecks.

The Scalar Row-by-Row Bottleneck

In a standard Python UDF:

• Snowflake's C++ database engine must serialize each individual row's data into Python objects.
• The Python worker executes the function on that single row.
• The result is serialized back into C++ data types.
• This per-row call and conversion overhead can dominate execution time for large tables.

The Vectorized Batch Model: pandas DataFrames

Vectorized Python UDFs solve this problem by receiving batches of rows as pandas DataFrames and returning pandas Series or arrays:

Traditional Scalar Python UDF (Row-by-Row):      Vectorized Python UDF (@pandas_udf Batch):      
┌─────────────────┐       ┌─────────────────┐    ┌─────────────────┐       ┌─────────────────┐
│ Snowflake Engine│       │ Python Worker   │    │ Snowflake Engine│       │ Python Worker   │
│ (C++ Database)  │       │ (Process)       │    │ (C++ Database)  │       │ (Pandas / Arrow)│
├─────────────────┤       ├─────────────────┤    ├─────────────────┤       ├─────────────────┤
│ Row 1 ─────────►│──────►│ Process Row 1   │    │ Batch: 10,000   │──────►│ Vectorized      │
│ Row 2 ─────────►│──────►│ Process Row 2   │    │ rows in Apache  │       │ C/SIMD Array    │
│ Row N ─────────►│──────►│ Process Row N   │    │ as a DataFrame  │◄──────│ Math (pandas)   │
│ (N IPC roundtrips & type conversions)     │    └─────────────────┘       └─────────────────┘
└─────────────────┘       └─────────────────┘    (One handler call per batch instead of per row)

Defining a Vectorized Python UDF

In a SQL CREATE FUNCTION, a Python UDF becomes vectorized when its handler is decorated with @vectorized(input=pandas.DataFrame) from the _snowflake module (or has the _sf_vectorized_input attribute). The handler receives one pandas DataFrame whose columns are the arguments by position. In the Snowpark API, the equivalent is pandas_udf or type hints such as PandasSeries.

-- Vectorized Python UDF for loan default risk scoring
CREATE OR REPLACE FUNCTION score_credit_risk(income NUMBER, debt NUMBER, credit_score NUMBER)
RETURNS FLOAT
LANGUAGE PYTHON
RUNTIME_VERSION = '3.12'
PACKAGES = ('pandas')
HANDLER = 'predict_risk'
AS $$
import pandas
from _snowflake import vectorized

@vectorized(input=pandas.DataFrame)
def predict_risk(df):
    income, debt, credit_score = df[0], df[1], df[2]
    debt_to_income = debt / (income + 1.0)
    risk_score = (debt_to_income * 0.6) - ((credit_score / 850.0) * 0.4)
    return risk_score.clip(lower=0.0, upper=1.0)
$$;

Vectorized UDF Architectural Rules for the Exam

1. Matching Length Invariant: The returned Pandas Series must have the exact same number of elements as the input Pandas Series/DataFrame passed into the function. If the batch contains 10,000 rows, the return Series must contain 10,000 elements; otherwise, Snowflake aborts the query with an array length mismatch error.
2. Batch Processing: Rows arrive in batches rather than one call per row, which removes most per-row overhead.
3. Throughput: Vectorized UDFs are typically much faster than scalar Python UDFs for numeric transformations and ML inference, because pandas and NumPy operate on whole columns at once.

Stored Procedures: Architecture & Execution Rights

While UDFs are designed to calculate and return values within a SQL expression, Stored Procedures are designed to execute procedural business logic, control flow, dynamic SQL generation, and administrative operations.

Fundamental Differences: UDFs vs. Stored Procedures

Caller's Rights vs. Owner's Rights (EXECUTE AS)

A cornerstone of Snowflake security architecture tested extensively on the ARA-C01 exam is the privilege and context evaluation model defined by the EXECUTE AS clause.

                                  STORED PROCEDURE SECURITY CONTEXTS
┌────────────────────────────────────────────────────────┐┌────────────────────────────────────────────────────────┐
│       OWNER'S RIGHTS (EXECUTE AS OWNER - Default)      ││             CALLER'S RIGHTS (EXECUTE AS CALLER)        │
├────────────────────────────────────────────────────────┤├────────────────────────────────────────────────────────┤
│ • Executes with the privileges of the procedure OWNER  ││ • Executes with the privileges of the active CALLER    │
│ • Allows users to perform actions they lack direct     ││ • User can ONLY access objects their active role      │
│   privileges for (controlled privilege elevation)      ││   possesses explicit privileges to read/modify         │
│ • CANNOT read or set caller session variables          ││ • CAN read, set, and unset caller session variables    │
│ • Cannot set or unset caller session parameters       ││ • Can set caller's session parameters                 │
│ • Runs only a subset of SQL statement types           ││ • Can run any statement the caller could run           │
└────────────────────────────────────────────────────────┘└────────────────────────────────────────────────────────┘

1. Owner's Rights (EXECUTE AS OWNER — Default)

When a procedure is created without specifying an execution mode (or with EXECUTE AS OWNER):

• Privilege Elevation: The procedure executes with the permissions of the role that owns the procedure (OWNERSHIP privilege), not the caller's role. This allows administrators to delegate specific workflows (such as creating staging tables or onboarding users) to lower-privileged users without granting them broad administrative roles.
• Isolation from Caller Session: For security reasons, an Owner's Rights procedure is restricted:
  • Cannot read, set, or unset the caller's session variables, and cannot set or unset the caller's session parameters.
  • Can execute only SELECT, DML, DDL (with limits on ALTER USER for the current user), GRANT/REVOKE, variable assignment, DESCRIBE/SHOW, and LIST — not statements such as ALTER SESSION or USE ROLE.
  • Callers other than the owner cannot see the procedure body (for example through GET_DDL).

2. Caller's Rights (EXECUTE AS CALLER)

When a procedure is created with EXECUTE AS CALLER:

• No Privilege Elevation: The procedure executes strictly with the privileges granted to the current active role of the caller. If the caller lacks INSERT privileges on the target table, the procedure fails immediately.
• Full Session Context Access: The procedure operates directly inside the caller's session:
  • Inherits the caller's current warehouse, database, and schema.
  • Can view, set, and unset the caller's session variables and session parameters.
  • Can run any statement the caller could run outside the procedure (optionally narrowed with restricted caller's rights grants).

Stored Procedure Execution Rights Comparison Matrix

External Functions: Architecture & Security Boundaries

While native UDFs and Stored Procedures execute inside Snowflake's virtual warehouse compute cluster, organizations often need to invoke external services—such as geocoding addresses via Google Maps API, scoring credit risk via an external custom machine learning API, or validating sensitive records against a proprietary on-premises service. External Functions provide this bridge.

External Function Architecture

An External Function is a scalar UDF that sends row data over HTTPS to an external API proxy and receives calculated results back into the SQL query pipeline.

┌────────────────────────────────────────────────────────────────────────────────────────┐
│                            EXTERNAL FUNCTION ARCHITECTURE                              │
├────────────────────────────────────────────────────────────────────────────────────────┤
│ 1. Virtual Warehouse (Query Execution):                                                │
│    SELECT id, geocode_address(street, city, state) FROM customers;                     │
│    • Batches rows into JSON arrays: {"data": [[0, "100 Main St", "Austin", "TX"],...]} │
├────────────────────────────────────────────────────────────────────────────────────────┤
│                                           │ HTTPS POST Batch Payload                   │
│                                           ▼                                            │
│ 2. Cloud HTTPS Proxy / API Gateway:                                                    │
│    • Amazon API Gateway / Azure API Management / Google Cloud API Gateway              │
│    • API Integration Object: Manages IAM roles and client credentials securely         │
├────────────────────────────────────────────────────────────────────────────────────────┤
│                                           │ Authorized Forward                         │
│                                           ▼                                            │
│ 3. Remote Backend Service:                                                             │
│    • AWS Lambda / Azure Function / GCP Cloud Function / Custom HTTPS Microservice      │
│    • Processes input array and returns matching JSON response:                         │
│      {"data": [[0, "30.2672 N, 97.7431 W"], [1, "..."]]}                               │
└────────────────────────────────────────────────────────────────────────────────────────┘

Core Components of an External Function

1. API INTEGRATION Object: A Snowflake account-level securable object that manages authentication and trust relationships with the cloud HTTPS proxy (e.g., AWS IAM Role ARN, Azure Tenant/Application ID, or GCP Service Account). Storing credentials in the API Integration ensures that database developers never see or manage cloud secret keys.
2. HTTPS Proxy / API Gateway: An intermediate cloud managed service (AWS API Gateway, Azure API Management, or GCP API Gateway) that enforces HTTPS encryption, rate limiting, and access authorization.
3. Remote Service (Backend): The compute service hosting the custom code (AWS Lambda, Azure Functions, GCP Cloud Functions, or an enterprise Kubernetes cluster).

The Batch JSON Data Exchange Contract

To achieve high throughput, Snowflake does not invoke the external HTTP service row-by-row. Instead, the virtual warehouse engine automatically batches rows into a standardized JSON payload:

1. Request Payload Sent by Snowflake

{
  "data": [
    [0, "100 Market St", "San Francisco", "CA"],
    [1, "701 Pike St", "Seattle", "WA"],
    [2, "500 W 2nd St", "Austin", "TX"]
  ]
}

• Each row is represented as an array where element 0 is the row sequence number (0, 1, 2...), followed by the function arguments.

2. Response Payload Returned by the Remote Service

{
  "data": [
    [0, {"lat": 37.7936, "lng": -122.3958}],
    [1, {"lat": 47.6131, "lng": -122.3332}],
    [2, {"lat": 30.2666, "lng": -97.7478}]
  ]
}

• Critical Contract Requirement: The remote service must return an array with the exact same row sequence numbers and the exact same number of rows as the request. If any sequence number is omitted or duplicated, Snowflake aborts the query execution with a data format error.

Architectural Considerations: Latency, Cost & Concurrency

• Network Latency: External functions traverse the public internet or cloud VPC backbones. Latency is measured in milliseconds per batch rather than microseconds for native UDFs.
• Dual Billing: Executing an external function consumes both Snowflake Virtual Warehouse credits and Cloud Provider charges (API Gateway requests, Lambda execution time).
• Concurrency Control: External endpoints can easily be overwhelmed by large scans. Use MAX_BATCH_ROWS on the function and API gateway throttling to protect downstream services.
• Modern alternative: For new designs, a Python/Java/Scala UDF or procedure with an external access integration (Section 3.5) can call external APIs directly from Snowflake without an API gateway, while external functions remain supported.