Python UDFs, Stages, External Functions and Stored Predictions

1.Scalar and vectorized Python UDFs

You can also deploy a model inside Snowflake as your own Python user-defined function. By default, a Python UDF processes one row at a time. A vectorized UDF receives a batch of input rows as a pandas DataFrame and returns a pandas array or Series. This can perform better when your code works efficiently on batches, and it fits naturally with libraries that already take DataFrames, which most model predict calls do. Queries don't change: you call a vectorized UDF exactly as you would a scalar one, and the UDF framework handles the batching.

A UDF can also depend on files that its handler reads, such as a serialized model or a configuration file. Upload the file to a stage, then reference it with the IMPORTS clause when you create the function. The function owner needs the READ privilege on the stage.

CREATE FUNCTION add_one_to_inputs(x NUMBER(10, 0), y NUMBER(10, 0))
  RETURNS NUMBER(10, 0)
  LANGUAGE PYTHON
  RUNTIME_VERSION = 3.12
  PACKAGES = ('pandas')
  HANDLER = 'add_one_to_inputs'
AS $$
import pandas
from _snowflake import vectorized

@vectorized(input=pandas.DataFrame)
def add_one_to_inputs(df):
 return df[0] + df[1] + 1
$$;

Inside the handler, arguments are accessed by position: df[0] is the first argument and df[1] the second. The array you return must be the same length as the input DataFrame. Instead of the decorator, you can set _sf_vectorized_input = pandas.DataFrame on the handler. Each handler call has a 180-second limit. If your code can only process a limited number of rows, cap the batch with max_batch_size, or _sf_max_batch_size as an attribute. If it can handle batches of any size, leave the setting unset. Setting it doesn't request larger batches.

Sources12

2.Moving files with stage commands

Model artifacts and data files reach Snowflake through stages. PUT uploads one or more files from a local file system to an internal stage: a named stage (@name), a table stage (@%table) or your user stage (@~). The related commands are GET, LIST and REMOVE. Use LIST to see which files are on a stage.

To make a staged file available to a UDF or stored procedure, follow three steps: choose or create a stage, upload the file, and reference it with the IMPORTS clause when you create the function or procedure. PUT compresses files by default. If you omit AUTO_COMPRESS = FALSE, the staged file gets a .gz extension, and you must use that name in IMPORTS. PUT cannot target an external stage, so use the cloud provider's utilities there.

PUT file://<absolute_path_to_file>/<filename> internalStage
    [ PARALLEL = <integer> ]
    [ AUTO_COMPRESS = TRUE | FALSE ]
    [ SOURCE_COMPRESSION = AUTO_DETECT | GZIP | BZ2 | BROTLI | ZSTD | DEFLATE | RAW_DEFLATE | NONE ]
    [ OVERWRITE = TRUE | FALSE ]

LIST @my_stage;

Sources324

3.Pre-built ML Functions and storing predictions

You don't always need to deploy your own model. Snowflake ML Functions, such as Classification, are pre-built in Snowflake. You train one and call its PREDICT method in SQL. These models are separate from the Model Registry, and those trained with ML Functions do not appear in it. Some model types, such as Cortex Fine-Tuned LLMs, appear in the registry's Snowsight UI but are not managed by the registry API.

You can use PREDICT output directly in a query. To store it, wrap the call in CREATE TABLE ... AS SELECT. The prediction column holds structured values, so later queries can pull out fields such as predictions:class and per-class probabilities.

CREATE OR REPLACE TABLE my_predictions AS
SELECT *, model_multiclass!PREDICT(INPUT_DATA => {*}) AS predictions FROM prediction_purchase_data;

Predictions from a model you logged in the Model Registry can be stored too. mv.run returns a DataFrame of the same type you passed in, so you can persist the result from Snowpark. One option is a dynamic table that applies the model to incoming rows and keeps the predictions refreshed, as in the example below. For large jobs, run_batch runs batch inference on Snowpark Container Services and writes its results to an output stage, which you then read from there.

CREATE OR REPLACE DYNAMIC TABLE logins_with_predictions
    WAREHOUSE = my_wh
    TARGET_LAG = '20 minutes'
    REFRESH_MODE = INCREMENTAL
    INITIALIZE = on_create
    COMMENT = 'Dynamic table with continuously updated model predictions'
AS
SELECT
    login_id,
    user_id,
    location,
    event_time,
    MODEL(ml.registry.mymodel)!predict(l.user_id, l.location) AS prediction_result
FROM logins_raw;

Sources567

4.External functions for externally hosted models

If a model is hosted outside Snowflake, an external function lets SQL call it. An external function is a UDF with no code of its own. Its database object stores the proxy service URL, and an API integration (created with CREATE API INTEGRATION) holds the security details. The remote service must expose an HTTPS endpoint, accept JSON input and return JSON output. Examples are an AWS Lambda function, an Azure Function, or an HTTPS server on EC2. To SQL users, an external function looks like any other UDF.

Because the remote service is opaque code outside Snowflake, it can use libraries that internal UDFs can't reach, such as commercial third-party machine-learning scoring libraries, and it can be written in languages such as Go or C#. The same service can also be called from other software that uses the same interface. Separately, the Model Registry lets you import a model from an external provider into Snowflake instead of calling it remotely.

External functions have limits. They must be scalar, returning one value per input row, and they can't be stored procedures. Large inputs are sent in batches of rows, each with its own ID, and each batch's response can be at most 10MB. External functions carry more overhead than internal UDFs and usually run more slowly. They can't be shared through Secure Data Sharing or used in a COPY transformation. Data also leaves Snowflake: a third-party provider could keep copies of what you send. On top of warehouse and data-transfer costs, the remote service provider may charge you.

Sources87

Exam traps

Each one states something that sounds right. Open it to see what is actually true.

1. 1.An external function can wrap a remote model as a table function or a stored procedure.Why is that wrong?

   External functions are currently scalar only: one value per input row. Stored procedures cannot be written with the feature.

   Covered in External functions for externally hosted models

2. 2.An external function performs about the same as an internal Python UDF.Why is that wrong?

   External functions add overhead and are opaque to the optimizer, so they usually run more slowly than internal UDFs.

   Covered in External functions for externally hosted models