Distributed training as a multi-node ML Job: code¶
Every file in the Distributed training as a multi-node ML Job recipe. To run the recipe, download distributed-training.zip, unzip it, and run the scripts from the distributed-training/ folder as the recipe describes. Comments in the files that say “see README” refer to the recipe page; the zip includes it as README.md.
notebooks/¶
notebooks/01_distributed_training.ipynb¶
Jupyter notebook, shown as Python with Markdown cells as comments.
# %% [markdown]
# # Distributed XGBoost training with Snowflake ML Jobs
#
# Train a gradient-boosted classifier across **multiple nodes** of a Snowpark Container Services
# compute pool using Snowflake's distributed `XGBEstimator`. You launch the run from this notebook
# as a **Snowflake ML Job**. Snowflake provisions a multi-node Ray cluster on the compute pool,
# shards the training data across the nodes, and trains one XGBoost model collectively. The finished
# model is logged to the **Model Registry** and used for batch inference from a warehouse.
#
# Before you run it:
#
# 1. Run `sql/01_setup.sql` and `sql/02_data.sql` as described in the recipe README. They create the
# role, warehouse, compute pool, database, stage, and the `LOAN_APPLICATIONS` table.
# 2. Run the notebook locally (Jupyter or VS Code) after `pip install -r requirements.txt`, with a
# Snowflake connection for your own user (for example
# `SNOWFLAKE_DEFAULT_CONNECTION_NAME=<your connection>`). It can also run as a Snowflake Notebook
# in a Workspace, where it reuses the active session.
#
# The notebook switches to the recipe role, so it needs nothing beyond what `sql/01` granted.
# %%
from datetime import datetime
import matplotlib.pyplot as plt
from IPython.display import Code
from sklearn.metrics import roc_auc_score
from snowflake.ml import jobs
from snowflake.ml.registry import Registry
from snowflake.snowpark import Session
from src.helpers import (
key_log_lines,
metrics_frame,
plot_feature_importance,
plot_lift_by_decile,
)
# %% [markdown]
# ## Configuration
#
# Object names match `sql/01_setup.sql` and are fixed. `TARGET_INSTANCES` sets the number of
# compute pool nodes the job spans (one head node plus `TARGET_INSTANCES - 1` workers); keep it at
# or below the pool's `MAX_NODES` (3). The hyperparameters are safe to change.
# %%
DATABASE = "COOKBOOK_DISTRIBUTED_TRAINING"
SCHEMA = "ML"
ROLE = "COOKBOOK_DISTRIBUTED_TRAINING_ADMIN"
WAREHOUSE = "COOKBOOK_DISTRIBUTED_TRAINING_WH"
COMPUTE_POOL = "COOKBOOK_DISTRIBUTED_TRAINING_POOL"
STAGE = "JOBS_STAGE"
SOURCE_TABLE = f"{DATABASE}.{SCHEMA}.LOAN_APPLICATIONS"
LABEL_COL = "IS_DEFAULT"
ID_COL = "LOAN_ID"
TEST_PCT = 20 # deterministic hash split: about 20% of rows held out
MODEL_NAME = "LOAN_DEFAULT_XGB_DISTRIBUTED"
VERSION_NAME = f"V_{datetime.now():%Y%m%d_%H%M%S}"
TARGET_INSTANCES = 2
HYPERPARAMS = {"n-estimators": 150, "max-depth": 6, "learning-rate": 0.1}
# %% [markdown]
# ## Step 1: Connect to Snowflake
#
# Inside a Snowflake Notebook the active session is reused. Locally, `Session.builder.getOrCreate()`
# reads your connection from `~/.snowflake/connections.toml` (set `SNOWFLAKE_DEFAULT_CONNECTION_NAME`
# to choose one). No credentials live in the notebook. The session then switches to the recipe role,
# warehouse, and schema.
# %%
try:
from snowflake.snowpark.context import get_active_session
session = get_active_session()
except Exception:
session = Session.builder.getOrCreate()
session.use_role(ROLE)
session.use_warehouse(WAREHOUSE)
session.use_schema(f"{DATABASE}.{SCHEMA}")
print(session.sql("SELECT CURRENT_ROLE(), CURRENT_WAREHOUSE(), CURRENT_SCHEMA()").collect()[0])
# %% [markdown]
# ## Step 2: Explore the Training Data
#
# `LOAN_APPLICATIONS` holds 1M synthetic loan applications with 18 numeric features and a binary
# `IS_DEFAULT` label. Rows are split by a hash of `LOAN_ID`, so the training job and this notebook
# always agree on which rows are in the hold-out set.
# %%
session.sql(f"""
SELECT
IFF(MOD(ABS(HASH({ID_COL})), 100) < {TEST_PCT}, 'TEST', 'TRAIN') AS SPLIT,
COUNT(*) AS N_ROWS,
ROUND(AVG({LABEL_COL}), 4) AS DEFAULT_RATE
FROM {SOURCE_TABLE}
GROUP BY 1
ORDER BY 1 DESC
""").to_pandas()
# %% [markdown]
# ## Step 3: How Distributed Training Works
#
# ```
# This notebook Recipe compute pool (Container Runtime)
# +--------------+ submit_file() +-------------------------------------------+
# | job config | ------------------> | Head node (rank 0) Worker node(s) |
# | + script | | +----------------+ +----------------+ |
# +--------------+ | | Ray head |<-->| Ray worker | |
# ^ | | XGB worker 0 | | XGB worker 1 | |
# | job.result() | +-------+--------+ +--------+-------+ |
# +---------------------------- | | DataConnector shards | |
# +----------+-----------------------+---------+
# v v
# Snowflake table: LOAN_APPLICATIONS
# ```
#
# 1. `submit_file()` uploads the script to `JOBS_STAGE` and starts `TARGET_INSTANCES` containers
# on the compute pool. Together they form one Ray cluster.
# 2. Inside the job, `DataConnector.from_dataframe()` reads the table in parallel.
# 3. `XGBEstimator.fit()` starts one XGBoost worker per node. Each worker trains on its data shard,
# and the workers sync histogram statistics every boosting round (XGBoost's Rabit/collective
# protocol). The result is a single model trained on all of the data.
# 4. The head node evaluates the booster, logs it to the Model Registry, and returns a summary dict
# through `__return__`.
#
# **When to use it:** data that doesn't fit in one node's memory, or boosting runs that are too slow on a single node. For small data,
# plain open-source `xgboost` on one node is simpler and often faster, because coordinating the
# nodes adds overhead.
#
# The full training script is shown below.
# %%
Code("src/train_xgb_distributed.py", language="python")
# %% [markdown]
# ## Step 4: Submit the ML Job
#
# Key `submit_file` arguments:
#
# | Argument | Purpose |
# |----------|---------|
# | `compute_pool` | SPCS pool that runs the job |
# | `stage_name` | Where the payload (script and runtime files) is uploaded |
# | `args` | Command-line arguments passed to the script |
# | `target_instances` | Number of nodes in the job's Ray cluster |
#
# The call returns immediately with an `MLJob` handle. The job runs asynchronously on Snowflake,
# so you can close this notebook and reattach later with `jobs.get_job(job_id)`.
# %%
script_args = [
"--table",
SOURCE_TABLE,
"--model-name",
MODEL_NAME,
"--version-name",
VERSION_NAME,
"--test-pct",
str(TEST_PCT),
]
for k, v in HYPERPARAMS.items():
script_args += [f"--{k}", str(v)]
job = jobs.submit_file(
"src/train_xgb_distributed.py",
COMPUTE_POOL,
stage_name=STAGE,
args=script_args,
target_instances=TARGET_INSTANCES,
session=session,
)
print(f"Submitted job: {job.id}")
print(f"Status: {job.status}")
# %% [markdown]
# ## Step 5: Monitor the Job
#
# `job.wait()` blocks until the job finishes. While it runs, you can check it with `job.status` and
# `job.get_logs()`, from this notebook or after reattaching with `jobs.get_job(job_id)`. The first run takes a few extra
# minutes while the compute pool starts its nodes.
# %%
final_status = job.wait()
print(f"Final status: {final_status}")
print(key_log_lines(job.get_logs()))
# %% [markdown]
# ## Step 6: Review the Results
#
# `job.result()` returns the dictionary that the script assigned to `__return__`. It includes the
# cluster size, training time, hold-out metrics, and per-feature gain.
# %%
result = job.result()
print(
f"Nodes: {result['n_nodes']} | CPUs: {result['n_cpus']} | "
f"Train rows: {result['n_train_rows']:,} | Test rows: {result['n_test_rows']:,} | "
f"Training time: {result['train_seconds']}s"
)
metrics_frame(result["metrics"])
# %%
plot_feature_importance(result["feature_importance"])
plt.show()
# %% [markdown]
# The top features (`NUM_DELINQUENCIES`, `CREDIT_SCORE`, `LOAN_TO_INCOME`, `INTEREST_RATE`) are the
# main drivers built into the synthetic data generator (`sql/02_data.sql`), so the model has learned the real signal.
# Accuracy is only modestly above the majority-class baseline (one minus the default rate, about 82%). For an imbalanced risk problem
# like this one, **ROC AUC** and ranking quality are the better measures (see Step 8).
# %% [markdown]
# > **A note on scale:** this 1M-row dataset is deliberately small so the recipe runs quickly, and
# > it fits easily on one node. Here, single-node open-source XGBoost trains in seconds (see the
# > trial times in the [Hyperparameter tuning](../../hyperparameter-tuning/README.md) recipe), while the
# > distributed run spends most of its time on cross-node coordination. The workflow on this page
# > stays the same when the table grows to hundreds of millions of rows. At that point a single
# > node runs out of memory, and adding nodes is what makes training possible.
# %% [markdown]
# ## Step 7: Inspect the Model in the Registry
#
# The job logged the booster with its metrics, so every training run is versioned and auditable.
# %%
registry = Registry(session=session, database_name=DATABASE, schema_name=SCHEMA)
model = registry.get_model(MODEL_NAME)
mv = model.version(VERSION_NAME)
print(f"{model.name} versions: {[v.version_name for v in model.versions()]}")
print("Logged metrics:", {k: round(v, 4) for k, v in mv.show_metrics().items()})
mv.show_functions()[0]["name"]
# %% [markdown]
# ## Step 8: Batch Inference from a Warehouse
#
# `mv.run()` scores a Snowpark DataFrame inside the warehouse, so no data leaves Snowflake. Here we
# score the hold-out split and check two things: that predicted risk ranks borrowers correctly
# (lift) and that the predicted probabilities match observed default rates (calibration).
# %%
test_df = session.sql(f"SELECT * FROM {SOURCE_TABLE} WHERE MOD(ABS(HASH({ID_COL})), 100) < {TEST_PCT}")
scored = mv.run(test_df, function_name="predict")
scored_pdf = scored.select(ID_COL, LABEL_COL, '"output_feature_0"').to_pandas()
scored_pdf = scored_pdf.rename(columns={"output_feature_0": "DEFAULT_PROBABILITY"})
print(f"Scored {len(scored_pdf):,} rows in the warehouse")
print(f"Warehouse-scored ROC AUC: {roc_auc_score(scored_pdf[LABEL_COL], scored_pdf['DEFAULT_PROBABILITY']):.4f}")
# %%
fig, lift = plot_lift_by_decile(scored_pdf, "DEFAULT_PROBABILITY", LABEL_COL)
plt.show()
lift
# %% [markdown]
# In a typical run the highest-risk decile defaults far more often than the lowest (roughly 60% vs 1%), and the predicted
# and observed bars line up closely. The model is well calibrated and ready for thresholding
# (for example, routing the top deciles to manual review).
#
# You can also call the model directly from SQL:
#
# ```sql
# WITH mv AS MODEL COOKBOOK_DISTRIBUTED_TRAINING.ML.LOAN_DEFAULT_XGB_DISTRIBUTED VERSION <VERSION_NAME>
# SELECT LOAN_ID, mv!PREDICT(*):output_feature_0::FLOAT AS DEFAULT_PROBABILITY
# FROM COOKBOOK_DISTRIBUTED_TRAINING.ML.LOAN_APPLICATIONS
# LIMIT 10;
# ```
# %% [markdown]
# ## Key Takeaways
#
# - **ML Jobs separate where you write code from where it runs.** You develop anywhere, and the
# work runs on a managed, multi-node Container Runtime close to your data.
# - **`XGBEstimator` is a drop-in for multi-node XGBoost.** You pass a `DataConnector`, the input
# columns, and the label, and it handles sharding and worker coordination.
# - **`target_instances` is the scaling knob.** It sets the node count without changing your code.
# - **Register from inside the job.** The model is versioned with its metrics, and you don't need
# to download artifacts.
# - **Use distributed training when the data or the training time justifies it.** On small data,
# single-node training avoids the coordination overhead.
#
# ## Next Steps
#
# - [Hyperparameter tuning](../../hyperparameter-tuning/README.md): find better XGBoost parameters with parallel HPO
# - Raise the `GENERATOR` row count in `sql/02_data.sql` and raise `TARGET_INSTANCES` to see how training time scales
# - Set `XGBScalingConfig(use_gpu=True)` on a GPU compute pool for GPU-accelerated boosting
# %% [markdown]
# ## Cleanup
#
# The job service and the model live in the recipe database. Run `sql/99_teardown.sql` as described
# in the README to remove them together with the compute pool, warehouse, and role.
notebooks/requirements.txt¶
# Notebook (notebooks/01_distributed_training.ipynb), run locally. The ML Job itself runs on
# the Snowflake Container Runtime, which already includes Ray and XGBoost.
snowflake-ml-python==2.5.0
snowflake-snowpark-python==1.55.1
pandas==2.3.3
scikit-learn==1.9.1
matplotlib==3.11.2
ipython==9.17.1
notebooks/src/__init__.py¶
notebooks/src/helpers.py¶
"""Notebook helpers for the distributed training recipe."""
import re
import matplotlib.pyplot as plt
import pandas as pd
LOG_PATTERN = re.compile(
r"Ray cluster|Distributed training finished|Test metrics|Registered|"
r"\[\d+\]\s+(train|valid|validation)"
)
def key_log_lines(logs: str, max_lines: int = 40) -> str:
"""Keep only the informative lines from an ML Job log."""
lines = [line for line in logs.splitlines() if LOG_PATTERN.search(line)]
return "\n".join(lines[-max_lines:])
def metrics_frame(metrics: dict) -> pd.DataFrame:
"""Format a metrics dict as a one-column table."""
return pd.DataFrame.from_dict(metrics, orient="index", columns=["value"]).round(4)
def plot_feature_importance(importance: dict, top_n: int = 15, title: str = "Feature importance (gain)"):
"""Horizontal bar chart of the top-N features by XGBoost gain."""
s = pd.Series(importance).sort_values().tail(top_n)
fig, ax = plt.subplots(figsize=(8, 0.35 * len(s) + 1))
s.plot.barh(ax=ax, color="#29B5E8")
ax.set_title(title)
ax.set_xlabel("Average gain")
fig.tight_layout()
return fig
def plot_lift_by_decile(df: pd.DataFrame, score_col: str, label_col: str):
"""Observed default rate per predicted-score decile (decile 10 = highest risk)."""
d = df[[score_col, label_col]].copy()
d["decile"] = pd.qcut(d[score_col].rank(method="first"), 10, labels=range(1, 11))
summary = d.groupby("decile", observed=True).agg(
predicted=(score_col, "mean"),
observed=(label_col, "mean"),
n=(label_col, "size"),
)
fig, ax = plt.subplots(figsize=(8, 4))
x = range(1, 11)
ax.bar(
[i - 0.2 for i in x],
summary["observed"],
width=0.4,
label="Observed default rate",
color="#11567F",
)
ax.bar(
[i + 0.2 for i in x],
summary["predicted"],
width=0.4,
label="Mean predicted probability",
color="#29B5E8",
)
ax.set_xticks(list(x))
ax.set_xlabel("Predicted risk decile")
ax.set_ylabel("Rate")
ax.set_title("Calibration and lift by decile")
ax.legend()
fig.tight_layout()
return fig, summary.round(4)
notebooks/src/train_xgb_distributed.py¶
"""Distributed XGBoost training entrypoint for a Snowflake ML Job.
This script runs on the Snowflake Container Runtime across every node assigned to
the job (``target_instances``). It:
1. Reads train/test splits from a Snowflake table with ``DataConnector``.
2. Trains a binary classifier with Snowflake's distributed ``XGBEstimator``,
which shards data and boosting work across all Ray workers in the cluster.
3. Evaluates the booster on the hold-out split.
4. Logs the model (with metrics) to the Snowflake Model Registry.
The dictionary assigned to ``__return__`` is returned to the caller by
``MLJob.result()``.
"""
import argparse
import re
import time
import numpy as np
import ray
import xgboost as xgb
from sklearn.metrics import (
accuracy_score,
f1_score,
log_loss,
precision_score,
recall_score,
roc_auc_score,
)
from snowflake.ml.data.data_connector import DataConnector
from snowflake.ml.modeling.distributors.xgboost import XGBEstimator, XGBScalingConfig
from snowflake.ml.registry import Registry
from snowflake.snowpark import Session
LABEL_COL = "IS_DEFAULT"
ID_COL = "LOAN_ID"
# --table is interpolated into SQL, so accept only an unquoted DB.SCHEMA.TABLE.
TABLE_NAME = re.compile(r"^[A-Za-z_][A-Za-z0-9_$]*(\.[A-Za-z_][A-Za-z0-9_$]*){2}$")
def parse_args() -> argparse.Namespace:
parser = argparse.ArgumentParser(description="Distributed XGBoost training")
parser.add_argument("--table", required=True, help="Fully qualified source table")
parser.add_argument("--model-name", required=True)
parser.add_argument("--version-name", required=True)
parser.add_argument("--test-pct", type=int, default=20)
parser.add_argument("--n-estimators", type=int, default=150)
parser.add_argument("--max-depth", type=int, default=6)
parser.add_argument("--learning-rate", type=float, default=0.1)
args = parser.parse_args()
if not TABLE_NAME.match(args.table):
parser.error("--table must be an unquoted DATABASE.SCHEMA.TABLE name")
return args
def split_query(table: str, test: bool, test_pct: int) -> str:
"""Deterministic hash split so the notebook can rebuild the same test set."""
op = "<" if test else ">="
return f"SELECT * FROM {table} WHERE MOD(ABS(HASH({ID_COL})), 100) {op} {test_pct}"
def main() -> dict:
args = parse_args()
session = Session.builder.getOrCreate()
resources = ray.cluster_resources()
n_nodes = len([n for n in ray.nodes() if n["Alive"]])
print(f"Ray cluster: {n_nodes} node(s), {int(resources.get('CPU', 0))} CPUs")
train_df = session.sql(split_query(args.table, test=False, test_pct=args.test_pct))
test_df = session.sql(split_query(args.table, test=True, test_pct=args.test_pct))
feature_cols = [c for c in train_df.columns if c not in (LABEL_COL, ID_COL)]
params = {
"objective": "binary:logistic",
"eval_metric": ["logloss", "auc"],
"tree_method": "hist",
"max_depth": args.max_depth,
"eta": args.learning_rate,
"subsample": 0.8,
"colsample_bytree": 0.8,
}
estimator = XGBEstimator(
n_estimators=args.n_estimators,
params=params,
# Defaults to one worker per node using all of its CPUs.
scaling_config=XGBScalingConfig(use_gpu=False),
)
start = time.time()
booster = estimator.fit(
DataConnector.from_dataframe(train_df),
input_cols=feature_cols,
label_col=LABEL_COL,
eval_set=DataConnector.from_dataframe(test_df),
verbose_eval=50,
)
train_seconds = round(time.time() - start, 1)
print(f"Distributed training finished in {train_seconds}s")
# Evaluate on the head node with the open-source booster.
test_pdf = DataConnector.from_dataframe(test_df).to_pandas()
y_true = test_pdf[LABEL_COL].to_numpy()
y_prob = booster.predict(xgb.DMatrix(test_pdf[feature_cols]))
y_pred = (y_prob >= 0.5).astype(int)
metrics = {
"roc_auc": float(roc_auc_score(y_true, y_prob)),
"log_loss": float(log_loss(y_true, y_prob)),
"accuracy": float(accuracy_score(y_true, y_pred)),
"precision": float(precision_score(y_true, y_pred)),
"recall": float(recall_score(y_true, y_pred)),
"f1": float(f1_score(y_true, y_pred)),
}
print("Test metrics:", {k: round(v, 4) for k, v in metrics.items()})
# Log the booster to the Model Registry next to the source table, so it can be
# served from SQL or Python.
database, schema, _ = args.table.split(".")
registry = Registry(session=session, database_name=database, schema_name=schema)
mv = registry.log_model(
booster,
model_name=args.model_name,
version_name=args.version_name,
sample_input_data=test_pdf[feature_cols].head(100),
metrics={**metrics, "train_seconds": train_seconds, "n_nodes": n_nodes},
comment="Loan default classifier trained with distributed XGBEstimator",
target_platforms=["WAREHOUSE"],
)
print(f"Registered {mv.model_name} version {mv.version_name}")
importance = booster.get_score(importance_type="gain")
return {
"metrics": metrics,
"train_seconds": train_seconds,
"n_nodes": n_nodes,
"n_cpus": int(resources.get("CPU", 0)),
"n_train_rows": int(train_df.count()),
"n_test_rows": len(test_pdf),
"feature_importance": {k: float(v) for k, v in importance.items()},
"positive_rate_test": float(np.mean(y_true)),
"model_name": mv.model_name,
"version_name": mv.version_name,
}
if __name__ == "__main__":
__return__ = main()
sql/¶
sql/01_setup.sql¶
-- =============================================================================
-- 01_setup.sql
-- Distributed training: role, warehouse, compute pool, database, schema, stage.
--
-- The first block runs as ACCOUNTADMIN and only creates or grants to this
-- recipe's own COOKBOOK_ objects:
-- COOKBOOK_DISTRIBUTED_TRAINING_ADMIN owns the database and runs the job
-- COOKBOOK_DISTRIBUTED_TRAINING_POOL multi-node CPU pool for the ML Job
-- The role is granted to the user running this script. Everything after the
-- first block runs as that role.
--
-- Idempotent.
-- =============================================================================
SET cookbook_db = 'COOKBOOK_DISTRIBUTED_TRAINING';
SET cookbook_wh = 'COOKBOOK_DISTRIBUTED_TRAINING_WH';
SET cookbook_pool = 'COOKBOOK_DISTRIBUTED_TRAINING_POOL';
SET cookbook_role = 'COOKBOOK_DISTRIBUTED_TRAINING_ADMIN';
SET me = '"' || CURRENT_USER() || '"';
USE ROLE ACCOUNTADMIN;
CREATE ROLE IF NOT EXISTS IDENTIFIER($cookbook_role)
COMMENT = 'Cookbook distributed-training: owns the data, job payloads, and model.';
-- Granting the role to SYSADMIN lets ACCOUNTADMIN, which inherits SYSADMIN,
-- drop what it owns in 99_teardown.sql without relying on secondary roles.
GRANT ROLE IDENTIFIER($cookbook_role) TO ROLE SYSADMIN;
GRANT ROLE IDENTIFIER($cookbook_role) TO USER IDENTIFIER($me);
-- The warehouse runs the data generation, the job's SQL, and warehouse inference.
CREATE WAREHOUSE IF NOT EXISTS IDENTIFIER($cookbook_wh)
WAREHOUSE_SIZE = MEDIUM AUTO_SUSPEND = 60 AUTO_RESUME = TRUE INITIALLY_SUSPENDED = TRUE
COMMENT = 'Cookbook distributed-training';
GRANT USAGE ON WAREHOUSE IDENTIFIER($cookbook_wh) TO ROLE IDENTIFIER($cookbook_role);
-- The ML Job runs on this pool. MAX_NODES must be at least the job's
-- target_instances (the notebook uses 2). Each CPU_X64_M node has 8 vCPU and
-- 32 GB. The pool bills per active node and suspends after 10 idle minutes.
CREATE COMPUTE POOL IF NOT EXISTS IDENTIFIER($cookbook_pool)
MIN_NODES = 1
MAX_NODES = 3
INSTANCE_FAMILY = CPU_X64_M
AUTO_RESUME = TRUE
AUTO_SUSPEND_SECS = 600
INITIALLY_SUSPENDED = TRUE
COMMENT = 'Cookbook distributed-training: distributed XGBoost ML Job';
GRANT USAGE, MONITOR ON COMPUTE POOL IDENTIFIER($cookbook_pool) TO ROLE IDENTIFIER($cookbook_role);
CREATE DATABASE IF NOT EXISTS IDENTIFIER($cookbook_db)
COMMENT = 'Cookbook distributed-training';
-- Owning the database covers CREATE TABLE, STAGE, SERVICE (the ML Job), and
-- MODEL on its schemas.
GRANT OWNERSHIP ON DATABASE IDENTIFIER($cookbook_db) TO ROLE IDENTIFIER($cookbook_role) COPY CURRENT GRANTS;
-- ---------------------------------------------------------------------------
-- From here on, run as the recipe role.
-- ---------------------------------------------------------------------------
USE ROLE IDENTIFIER($cookbook_role);
USE WAREHOUSE IDENTIFIER($cookbook_wh);
USE DATABASE IDENTIFIER($cookbook_db);
CREATE SCHEMA IF NOT EXISTS ML
COMMENT = 'Loan data, ML Job payloads, and the distributed model';
CREATE STAGE IF NOT EXISTS ML.JOBS_STAGE
ENCRYPTION = (TYPE = 'SNOWFLAKE_SSE')
COMMENT = 'ML Job payloads uploaded by submit_file';
SELECT CURRENT_ACCOUNT(), CURRENT_REGION(), CURRENT_ROLE(), CURRENT_WAREHOUSE(), CURRENT_DATABASE();
sql/02_data.sql¶
-- =============================================================================
-- 02_data.sql
-- Synthetic loan-default data: 1M rows, 18 numeric features, label IS_DEFAULT.
--
-- Generated entirely in Snowflake with GENERATOR, so nothing leaves the account.
-- Categorical fields are integer-encoded so XGBoost can use them directly. The
-- label is drawn from a logistic function of the features plus noise, which
-- gives a learnable signal and a default rate of roughly 15 to 20 percent.
--
-- Runs as the recipe role. Idempotent (CREATE OR REPLACE).
-- =============================================================================
SET cookbook_db = 'COOKBOOK_DISTRIBUTED_TRAINING';
SET cookbook_wh = 'COOKBOOK_DISTRIBUTED_TRAINING_WH';
SET cookbook_role = 'COOKBOOK_DISTRIBUTED_TRAINING_ADMIN';
USE ROLE IDENTIFIER($cookbook_role);
USE WAREHOUSE IDENTIFIER($cookbook_wh);
USE DATABASE IDENTIFIER($cookbook_db);
USE SCHEMA ML;
CREATE OR REPLACE TABLE LOAN_APPLICATIONS
COMMENT = 'Synthetic loan applications with a binary IS_DEFAULT label'
AS
WITH base AS (
SELECT
SEQ8() AS LOAN_ID,
UNIFORM(21, 75, RANDOM(1))::INT AS AGE,
ROUND(EXP(NORMAL(11, 0.5, RANDOM(2))), 0) AS ANNUAL_INCOME,
ROUND(UNIFORM(1000, 50000, RANDOM(3))::FLOAT, 0) AS LOAN_AMOUNT,
CASE UNIFORM(0, 2, RANDOM(4)) WHEN 0 THEN 12 WHEN 1 THEN 36 ELSE 60 END AS LOAN_TERM_MONTHS,
ROUND(UNIFORM(3::FLOAT, 25::FLOAT, RANDOM(5)), 2) AS INTEREST_RATE,
LEAST(850, GREATEST(300, ROUND(NORMAL(680, 70, RANDOM(6)), 0)))::INT AS CREDIT_SCORE,
ROUND(UNIFORM(0::FLOAT, 0.6::FLOAT, RANDOM(7)), 3) AS DEBT_TO_INCOME,
UNIFORM(0, 40, RANDOM(8))::INT AS EMPLOYMENT_YEARS,
UNIFORM(1, 30, RANDOM(9))::INT AS NUM_CREDIT_LINES,
IFF(UNIFORM(0, 100, RANDOM(10)) < 80, 0, UNIFORM(1, 6, RANDOM(11)))::INT AS NUM_DELINQUENCIES,
UNIFORM(0, 8, RANDOM(12))::INT AS NUM_INQUIRIES_6M,
ROUND(UNIFORM(0::FLOAT, 1::FLOAT, RANDOM(13)), 3) AS CREDIT_UTILIZATION,
UNIFORM(0, 2, RANDOM(14))::INT AS HOME_OWNERSHIP,
UNIFORM(0, 5, RANDOM(15))::INT AS LOAN_PURPOSE,
IFF(UNIFORM(0, 100, RANDOM(16)) < 15, 1, 0) AS HAS_COSIGNER,
UNIFORM(0, 120, RANDOM(17))::INT AS MONTHS_SINCE_LAST_DELINQ,
ROUND(EXP(NORMAL(8.5, 1.2, RANDOM(18))), 0) AS SAVINGS_BALANCE,
UNIFORM(0::FLOAT, 1::FLOAT, RANDOM(19)) AS _U
FROM TABLE(GENERATOR(ROWCOUNT => 1000000))
),
scored AS (
SELECT
*,
ROUND(LOAN_AMOUNT / ANNUAL_INCOME, 4) AS LOAN_TO_INCOME,
-2.9
+ (-0.012) * (CREDIT_SCORE - 680)
+ 3.0 * (DEBT_TO_INCOME - 0.3)
+ 1.6 * (LOAN_AMOUNT / ANNUAL_INCOME - 0.25)
+ 0.08 * (INTEREST_RATE - 14)
+ 0.35 * NUM_DELINQUENCIES
+ 0.12 * NUM_INQUIRIES_6M
+ 1.2 * POWER(CREDIT_UTILIZATION - 0.3, 2) * SIGN(CREDIT_UTILIZATION - 0.3)
+ (-0.03) * LEAST(EMPLOYMENT_YEARS, 15)
+ (-0.5) * HAS_COSIGNER
+ IFF(HOME_OWNERSHIP = 2, -0.3, 0)
+ IFF(LOAN_PURPOSE = 4, 0.4, 0)
+ IFF(AGE < 25, 0.3, 0)
+ IFF(LOAN_TERM_MONTHS = 60, 0.25, 0)
+ (-0.15) * LN(1 + SAVINGS_BALANCE / 10000)
+ NORMAL(0, 0.5, RANDOM(20)) AS _LOGIT
FROM base
)
SELECT
LOAN_ID, AGE, ANNUAL_INCOME, LOAN_AMOUNT, LOAN_TERM_MONTHS, INTEREST_RATE,
CREDIT_SCORE, DEBT_TO_INCOME, EMPLOYMENT_YEARS, NUM_CREDIT_LINES,
NUM_DELINQUENCIES, NUM_INQUIRIES_6M, CREDIT_UTILIZATION, HOME_OWNERSHIP,
LOAN_PURPOSE, HAS_COSIGNER, MONTHS_SINCE_LAST_DELINQ, SAVINGS_BALANCE,
LOAN_TO_INCOME,
IFF(_U < 1 / (1 + EXP(-_LOGIT)), 1, 0) AS IS_DEFAULT
FROM scored;
-- Expect 1,000,000 rows and a default rate between 0.15 and 0.20.
SELECT COUNT(*) AS n_rows, ROUND(AVG(IS_DEFAULT), 4) AS default_rate
FROM LOAN_APPLICATIONS;
sql/99_teardown.sql¶
-- =============================================================================
-- 99_teardown.sql
-- Removes everything this recipe created.
--
-- Stopping the compute pool ends any ML Job still running on it. Dropping the
-- database removes the table, the job payload stage, the job services, and the
-- registered model and its versions. Then the warehouse and the role are
-- dropped, which also removes the role's grants.
-- There are no account parameters to restore.
--
-- Run as ACCOUNTADMIN. Safe to rerun.
-- =============================================================================
SET cookbook_db = 'COOKBOOK_DISTRIBUTED_TRAINING';
SET cookbook_wh = 'COOKBOOK_DISTRIBUTED_TRAINING_WH';
SET cookbook_pool = 'COOKBOOK_DISTRIBUTED_TRAINING_POOL';
SET cookbook_role = 'COOKBOOK_DISTRIBUTED_TRAINING_ADMIN';
USE ROLE ACCOUNTADMIN;
ALTER COMPUTE POOL IF EXISTS IDENTIFIER($cookbook_pool) STOP ALL;
DROP COMPUTE POOL IF EXISTS IDENTIFIER($cookbook_pool);
DROP DATABASE IF EXISTS IDENTIFIER($cookbook_db);
DROP WAREHOUSE IF EXISTS IDENTIFIER($cookbook_wh);
DROP ROLE IF EXISTS IDENTIFIER($cookbook_role);
-- Each of these should return no rows.
SHOW COMPUTE POOLS LIKE 'COOKBOOK_DISTRIBUTED_TRAINING%';
SHOW DATABASES LIKE 'COOKBOOK_DISTRIBUTED_TRAINING%';
SHOW WAREHOUSES LIKE 'COOKBOOK_DISTRIBUTED_TRAINING%';
SHOW ROLES LIKE 'COOKBOOK_DISTRIBUTED_TRAINING%';