From Data to SQL-Callable Model: An End-to-End Tour of Gnok Notebooks

Gnok Studio now has an in-product Python notebook. You write Python against governed Gnok data, train a model, and register it so it's callable from SQL with ML_PREDICT — the whole train→serve loop, in one place, with no data movement and no separate notebook server to babysit.

This post builds a complete example end to end: read data, explore it, engineer features, train a scikit-learn pipeline, register it, and score new rows from SQL — then let Gnok AI write a cell for us.

The gnok helper

Every notebook kernel comes with a pre-installed gnok helper. You don't pip install it and you don't pass a host or token — it auto-connects as the signed-in user, so row-level security, masking, and vended credentials apply exactly as they do everywhere else in Gnok.

import gnok
from gnok import ml

# A query → pandas
df = gnok.sql("SELECT 1 AS x, 2 AS y").fetch_df()

gnok wraps the standalone pygnok client and adds a gnok.ml namespace. Full reference: Python Notebooks.

1. Read the data

We'll predict whether a customer will churn. Start by pulling features into a DataFrame — read_table for a whole table, or gnok.sql(...) for anything shaped:

customers = gnok.sql("""
    SELECT
        tenure_months,
        monthly_charges,
        total_charges,
        support_tickets,
        churned                      -- 1 = churned, 0 = retained
    FROM analytics.customers
    WHERE total_charges IS NOT NULL
""").fetch_df()

print(customers.shape)
customers.head()

2. Explore

It's a normal pandas DataFrame, so all your usual tools work:

customers.describe()
customers.groupby("churned")[["tenure_months", "monthly_charges"]].mean()

import matplotlib.pyplot as plt
customers.boxplot(column="monthly_charges", by="churned")
plt.title("Monthly charges by churn")
plt.show()

Plots render inline in the cell output.

3. Train a model

A scikit-learn pipeline — scale the features, then a gradient-boosting model. We use a regressor so the model outputs a continuous churn propensity (0–1), which maps cleanly to a SQL FLOAT:

import numpy as np
from sklearn.pipeline import Pipeline
from sklearn.preprocessing import StandardScaler
from sklearn.ensemble import GradientBoostingRegressor
from sklearn.model_selection import train_test_split

FEATURES = ["tenure_months", "monthly_charges", "total_charges", "support_tickets"]
X = customers[FEATURES].to_numpy("float32")
y = customers["churned"].to_numpy("float32")

X_tr, X_te, y_tr, y_te = train_test_split(X, y, test_size=0.2, random_state=0)

model = Pipeline([
    ("scale", StandardScaler()),
    ("gbr", GradientBoostingRegressor(n_estimators=200, max_depth=3, random_state=0)),
]).fit(X_tr, y_tr)

print("R2 (holdout):", round(model.score(X_te, y_te), 3))

4. Register it for SQL

One call exports the fitted pipeline to ONNX, uploads it to an internal stage, and runs CREATE OR REPLACE MODEL — the model is now a SQL function:

ml.register_model(model, "analytics.imported.churn_propensity_v1", X)

A few things worth knowing (the helper handles the rest):

• Use a fully-qualified name (catalog.schema.model). The notebook has no default schema, so an unqualified name would land somewhere ML_PREDICT can't find.
• The input signature (FLOAT × 4) is inferred from X — one float per feature, in column order.
• register_model(..., returns="FLOAT") is the default; a classifier's label output is an integer, so use returns="BIGINT" there.

5. Serve it from SQL

Now anyone — a dashboard, a scheduled query, a BI tool — can score rows. Note the CAST(... AS REAL): bare decimal literals are typed as DECIMAL, which the ONNX input rejects.

SELECT ML_PREDICT(
  'analytics.imported.churn_propensity_v1',
  CAST(tenure_months AS REAL),
  CAST(monthly_charges AS REAL),
  CAST(total_charges AS REAL),
  CAST(support_tickets AS REAL)
) AS churn_score
FROM analytics.customers
ORDER BY churn_score DESC
LIMIT 20;

Materialize the scores for the rest of the business:

CREATE OR REPLACE TABLE analytics.churn_scores AS
SELECT
  customer_id,
  ML_PREDICT('analytics.imported.churn_propensity_v1',
             CAST(tenure_months AS REAL), CAST(monthly_charges AS REAL),
             CAST(total_charges AS REAL), CAST(support_tickets AS REAL)) AS churn_score
FROM analytics.customers;

…or score back from the notebook and write the result:

scored = gnok.sql("SELECT customer_id, ... FROM analytics.churn_scores").fetch_df()
gnok.write(scored.query("churn_score > 0.7"), "analytics.churn_watchlist")

That's the whole loop: the model trained in Python is now ordinary SQL the rest of the platform can call — no model server, no export/import dance.

6. Let Gnok AI write the cell

Open the ✨ Gnok AI panel and describe what you want:

"Read 1,000 rows of analytics.customers into a DataFrame and plot churn rate by tenure bucket."

It returns a ready-to-run cell — click ＋ Add as cell and run it. The assistant knows the gnok helper, so it reaches for gnok.sql / read_table rather than generic boilerplate. When a cell errors, ✨ Fix sends the traceback back to the model and proposes a corrected cell.

Recap

read (gnok.sql / read_table) → explore (pandas) → train (sklearn)
   → register (gnok.ml.register_model) → serve (ML_PREDICT in SQL)

No pip install, no connection string, no data leaving Gnok. Dig into the Python Notebooks reference for the full gnok and gnok.ml API, and ONNX Inference for how registered models execute.