Training Models in Snowflake: Stored Procedures, Tuning, Sampling, UDTFs and External Functions

1.Training with Python stored procedures

The most direct way to train a model in Snowflake is a Python stored procedure. Inside it, nested Snowpark queries load and transform the dataset. The data is then pulled into the procedure's memory for preprocessing and fitting, and the fitted model is written to a stage. Training can need a lot of memory, so Snowflake provides Snowpark-optimized warehouses for running single-node training with custom code.

CREATE OR REPLACE WAREHOUSE snowpark_opt_wh WITH
  WAREHOUSE_SIZE = 'MEDIUM'
  WAREHOUSE_TYPE = ' ? '
  MAX_CONCURRENCY_LEVEL = 1;

The guidelines are specific. Set WAREHOUSE_SIZE = MEDIUM so the warehouse has one Snowpark-optimized node. Make it multi-cluster if you need concurrency. Keep other workloads off it. The procedure's nested queries can run on a separate warehouse, chosen with session.use_warehouse(), which you can size independently to match the data. The procedure itself declares its libraries in PACKAGES:

CREATE OR REPLACE PROCEDURE train()
  RETURNS VARIANT
  LANGUAGE PYTHON
  RUNTIME_VERSION = 3.12
  PACKAGES = ('snowflake-snowpark-python', 'scikit-learn', 'joblib')
  HANDLER = 'main'
AS $$

Sources1

2.Partitioning: train/validation hold-out and cross validation

The body of train() shows how to partition the data. It holds out a test split first, and then runs cross validation only on the rows that remain:

def main(session):
 # Load features
 df = session.table('MARKETING_BUDGETS_FEATURES').to_pandas()
 X = df.drop('REVENUE', axis = 1)
 y = df['REVENUE']

 # Split dataset into training and test
 X_train, X_test, y_train, y_test = train_test_split(X, y, test_size=0.2, random_state = 42)

 # Preprocess numeric columns
 numeric_features = ['SEARCH_ENGINE','SOCIAL_MEDIA','VIDEO','EMAIL']
 numeric_transformer = Pipeline(steps=[('poly',PolynomialFeatures(degree = 2)),('scaler', StandardScaler())])
 preprocessor = ColumnTransformer(transformers=[('num', numeric_transformer, numeric_features)])

 # Create pipeline and train
 pipeline = Pipeline(steps=[('preprocessor', preprocessor),('classifier', LinearRegression(n_jobs=-1))])
 model = GridSearchCV(pipeline, param_grid={}, n_jobs=-1, cv=10)
 model.fit(X_train, y_train)

Three details in this code matter. First, train_test_split runs before any preprocessing, and random_state = 42 makes the split repeatable. Second, the StandardScaler and polynomial features sit inside the Pipeline, and the whole pipeline is fitted with model.fit(X_train, y_train). The 20% test rows therefore never influence the scaler or the 10-fold cross validation. Third, the procedure saves the model to @ml_models with session.file.put and then returns an R² score for both train and test. The test score is the hold-out estimate. The same separation appears in the HPO API on the container runtime, where dataset_map pairs a "train" and a "test" DataConnector and passes both to the training function.

Sources12

3.Hyperparameter tuning and optimization metric selection

In train(), GridSearchCV is given an empty param_grid={}. It cross-validates one configuration and searches nothing. Real tuning needs a search space. For that, Snowflake ML's HPO API on the container runtime runs trials in parallel and can scale across nodes of a compute pool. A *grid search* evaluates every combination of values you define. Sampling functions draw values instead: uniform, loguniform (suited to learning rates, which span orders of magnitude), randint and choice.

search_space = {
    "n_estimators": tune.uniform(50, 200),
    "max_depth": tune.uniform(3, 10),
    "learning_rate": tune.uniform(0.01, 0.3),
}

A TunerConfig then states what counts as "best": the metric being optimized, the mode (maximize or minimize), the search algorithm, the number of trials, and the concurrency. Choosing the metric means matching it to the task and to the direction of improvement. Log loss is a loss, so you minimize it. AUC, precision, recall and F1 are scores, so you maximize them. F1 is the documented balance between precision and recall when classes are uneven. For RMSE, the regression metric named in the exam guide, the sources here have no Snowflake-specific guidance. Treat it by the same rule: an error you minimize.

Sources23

4.Down-sampling and up-sampling

Class imbalance is usually handled by changing the training data. Snowflake's built-in tool for down-sampling is the SAMPLE (or TABLESAMPLE) clause. It can sample a fraction of a table or a fixed number of rows:

With a fraction, the number of rows you get back depends on table size and probability. A seed makes the sample deterministic, which matters if you want to reproduce a training run. Apply the hold-out lesson from train() here as well. Resampling changes the training data, so it belongs after the split and only on the training portion. The test split should keep the real class balance. Up-sampling (repeating or synthesizing minority rows) is not covered by the sources for this lesson. That gap is real, and this lesson does not fill it from memory.

Sources4

5.Training with Python UDTFs and per-partition models

Sometimes the right design is one model per segment, such as per region or per customer group. A Python UDTF fits that shape. The query's OVER (PARTITION BY ...) splits the input, process sees each row of a partition, and end_partition runs after the last row and can yield the partition's result. Snowpark-optimized warehouses can help some UDTF workloads as well as UDFs. The provided UDTF documentation demonstrates the partition mechanics, not a training handler. Treat a UDTF-trains-a-model design as these mechanics applied to fitting, not as a documented recipe.

The documented tool for per-partition training is Many Model Training (MMT). It requires a Snowflake ML container runtime. It partitions a Snowpark DataFrame by a column and trains a separate model on each partition in parallel. Your function receives (data_connector, context), where context.partition_id identifies the partition. MMT serializes the models to a stage for you. XGBoost and scikit-learn are supported out of the box, TorchSerde and TensorFlowSerde cover the deep learning frameworks, and a custom ModelSerde handles anything else.

from snowflake.ml.modeling.distributors.many_model import ManyModelTraining

trainer = ManyModelTraining(train_xgboost_model, "model_stage") # Specify the stage to store the models
training_run = trainer.run(
    partition_by="region",  # Train separate models for each region
    snowpark_dataframe=sales_data,
    run_id="regional_models_v1" # Specify a unique ID for the training run
)

Sources15

6.Training outside Snowflake through external functions

If the compute lives outside Snowflake, for example a GPU service, an external function connects it. An external function is a UDF that holds no code of its own. It is a database object that records how to reach a remote service, and it is called with ordinary dot notation in SQL. The remote service must behave like a scalar function. It accepts JSON input, returns JSON output, exposes an HTTPS endpoint, and returns exactly one row for each row received. Examples are an AWS Lambda function, an Azure Function, or an HTTPS server on EC2.

Snowflake never calls the remote service directly. A proxy service such as Amazon API Gateway or Azure API Management relays the requests, and can add authentication. The security details live in an API integration object. These constraints shape any outside-training design: the data arrives row by row as JSON and each row returns one result. Dynamic tables cannot include external functions, so calls to the remote service belong in queries or procedures.

Sources6

Exam traps

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

1. 1.A bigger Snowpark-optimized warehouse spreads one stored-procedure training job across more nodes, so you should size it as large as possible.Why is that wrong?

   Stored-procedure training is single-node. The guideline is MEDIUM, which gives exactly one Snowpark-optimized node, with multi-cluster only for concurrency.

   Covered in Training with Python stored procedures

2. 2.SAMPLE SYSTEM (n ROWS) is a faster way to get an exact-sized down-sample.Why is that wrong?

   SYSTEM/BLOCK samples whole blocks by probability and accepts no fixed row count. Only BERNOULLI/ROW returns an exact number of rows.

   Covered in Down-sampling and up-sampling

3. 3.An external function can send a batch to an outside trainer and receive any number of rows back, such as a table of fitted parameters.Why is that wrong?

   External functions are scalar. The remote service must return exactly one row for each row it receives, as JSON.

   Covered in Training outside Snowflake through external functions

Practise it for real

Train and persist a regression model inside Snowflake with a Python stored procedure on a Snowpark-optimized warehouse

1. 1.With ORGADMIN, go to Admin » Terms in Snowsight and enable Anaconda packages, if no one has done so for this account yet.

   Why: The procedure uses scikit-learn from Anaconda and calls to_pandas, and neither works until the External Offerings Terms are accepted.

   You should see: The Anaconda section shows the terms as acknowledged.

2. 2.Run CREATE OR REPLACE WAREHOUSE snowpark_opt_wh WITH WAREHOUSE_SIZE = 'MEDIUM' WAREHOUSE_TYPE = 'SNOWPARK-OPTIMIZED' MAX_CONCURRENCY_LEVEL = 1; and use it.

   Why: MEDIUM gives exactly one Snowpark-optimized node, the documented setup for single-node training.

   You should see: The warehouse is created and set as current.

3. 3.Create the train() procedure from the documented example, with PACKAGES snowflake-snowpark-python, scikit-learn and joblib and HANDLER 'main'. You need a MARKETING_BUDGETS_FEATURES table and an @ml_models stage.

   Why: The handler splits 80/20, cross-validates on the training rows, and saves the model to the stage.

   You should see: The procedure TRAIN is created successfully.

4. 4.Run CALL train();

   Why: Runs the training and the hold-out evaluation in one call.

   You should see: A VARIANT with keys 'R2 score on Train' and 'R2 score on Test', and model.joblib uploaded to @ml_models.