Skip to main content

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

· 4 min read
Gnok Team

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 copying data to a separate notebook server. Dig into the Python Notebooks reference for the full gnok and gnok.ml API, and ONNX Inference for how registered models execute.