DataSimplePractical GuideLog inOpen DataSimple
DataSimple
Chapter 19More

ML Models

Back to the index · Previous: What is tested · Next: Agent Studio

Step by step with screenshots on one Gold table: Demo ML — a Practical Guide.

How it works#

ML Models learns from the history in a lake table to predict one of its columns: will this customer leave (a category), how many days will this order be late (a number). You choose the table, the column to predict and the columns to learn from; DataSimple trains, tests and keeps the result as a numbered version. A version in Production can then add predictions to any table with the same columns, explain each prediction, and warn you when new data no longer looks like the data it learned from.

Four rules hold for everything on this page:

  • Your access. Training and scoring read the table as you, through the same SQL engine as a notebook. Grants, row filters and column masks apply; a model never sees a row you could not see.
  • Plain models. A model is stored as readable numbers (coefficients or decision trees), not as a program. Loading a model never runs code.
  • Every step is kept. Each training run, its test scores, the table version it learned from and who ran it stay in the model's history. Nothing is deleted when you train again.
  • Scoring writes like a notebook. The output table is written with save_table, as you, so your write grants apply and lineage records source table → model → predictions table.

Model types#

Model type Predicts Good for
Linear / Logistic number / category a clear, explainable first model
Lasso both many columns: it sets the ones that do not help to zero
Elastic net both many related columns (prices and discounts, units and transactions)
Decision tree both rules people can follow
Random forest both a strong all-round model
Extra trees both like a random forest, faster, less thrown by noisy columns
Gradient boosting both usually the most accurate on tables
K-means groups groups of similar rows segments: customers, stores, days, products

Train (AutoML) trains every type for the kind of answer on the same rows and keeps the best one as the new version. The others stay in Experiments, where you can compare them and register any of them too. Lasso and elastic net pick how strongly they hold back their coefficients by testing on the training rows.

Every model type is stored as a formula, a set of trees or a set of group centres, so a whole table is scored in SQL on the cluster.

Training learns from up to 20,000 rows, sampled evenly across the table; 20% of them are held back to test the model. Scoring always covers the whole table.

Steps#

  1. Left rail → ML & AI → ML Models → New model.
  2. Pick the layer and table, the column to predict, and the kind of answer (a category, or a number).
  3. Untick columns that must not be used: identifiers (order_id), and anything only known after the answer (a refund date when you predict returns).
  4. Name the model (churn_by_customer) and press Create and train (AutoML). Training takes from a few seconds to a few minutes; the page updates by itself.
  5. Versions shows each version with its scores in plain words ("right 91% of the time", "explains 86% of the variation") and the columns that matter most. Press Promote to make one the Production version.
  6. Score a table: choose the table to score (the training table, or a newer one with the same columns) and the output table, by default gold.<model>_predictions. The output has every source column plus prediction, probability (for categories), model_name, model_version and scored_at. It is replaced on every run.
  7. Explain:
    • Explain 5 rows shows, for rows of the table, what pushed each prediction up (teal) or down (grey).
    • Try your own values scores a row you type, in milliseconds, without reading or writing the lake.
  8. Monitor → Check now compares today's data with the training data, column by column (PSI: under 0.1 stable, 0.1 to 0.25 some change, over 0.25 a large change). When the table still holds the answer column, it also measures accuracy now. A large change or a drop in accuracy of more than 0.1 raises an alert on the model; Acknowledge it once you have retrained or decided it is fine.
  9. Datasets lists the training snapshots: table, table version, rows in the table and rows used. Read the exact training data back with time travel (SELECT * FROM silver.customers VERSION AS OF 42).

Compare runs#

Experiments opens with two charts above the runs table:

  • Score by training session. Each training click is one session. Grey dots are every model type's test score; the teal line joins the best of each session; the Production version is labelled. A type far below the rest (one that failed to fit) sits on the bottom edge with a count, so it does not squash the useful scores; its value stays in the tooltip and the table. Hover or focus a session for every type's score; click it to compare its types.
  • Model types in the chosen session. The types, best first, on the same rows and the same test rows, so the comparison is fair. Hover a type for its other scores and training time, and how far it is from the best. For a k-means model this chart shows the separation for each number of groups tried instead.

Score axes are zoomed to the scores, not started at zero: they compare close values with dots, not bars.

In the Lakehouse tree#

Models appear in Lakehouse under the schema of the table they learn from, in a Models folder beside Delta, Iceberg, Parquet and Views (datasimple → gold → Models). The row shows the Production version; hover for its score. Click a model to open it in ML Models.

Groups of similar rows (k-means)#

K-means has no column to predict. It finds groups of rows that look alike: busy superstore days and quiet neighbourhood days, big regular customers and occasional ones.

  1. New model → Kind of answer: Groups of similar rows.
  2. Number of groups: Find the best tries 2 to 10 groups and keeps the fewest whose separation is close to the best; or pick a number.
  3. Tick the columns that describe a row; leave out IDs and dates. Numbers are put on the same scale first; text columns count as "same or different".
  4. Press Create and find groups.

The version shows:

  • Separation (silhouette, -1 to 1): how clearly rows sit in their own group rather than the next one. Above 0.25 is usable; above 0.5 is clear.
  • Separation by number of groups: the score for every number tried.
  • Columns that separate the groups most.
  • What each group looks like: rows per group and the typical value of each column (the average for numbers, the most common value for text).

Score a table writes gold.<model>_groups: every source column plus prediction (the group number), distance (how far the row is from its group's centre; a large distance is an unusual row), model_name, model_version and scored_at. Explain shows which columns pull a row into its group (teal) and which pull it toward the next closest group (grey). Monitor checks drift; there is no accuracy to measure.

From a notebook#

ds.ml trains and forecasts from a notebook cell. The cell reads the rows itself with read_table, so your grants, row filters and masks apply; the app does the training or forecasting and keeps the result as yours.

Python
# Train (AutoML: every model type for the task, the best kept as a new version)
out = ds.ml.train("net_sales_by_store", "gold.daily_store_sales", target="net_sales",
                  features=["region", "store_format", "transactions", "units_sold"])
out["version"], out["best"], out["words"]

# One model type, made the Production version
ds.ml.train("net_sales_by_store", "gold.daily_store_sales", target="net_sales", model_types="gbm", promote=True)

# Groups of similar rows
ds.ml.train("store_day_groups", "gold.daily_store_sales", task="clustering", groups=4,
            features=["transactions", "units_sold", "net_sales", "avg_basket"])

# Forecast: a Spark DataFrame of history and the next periods (lower_80 / upper_80 = 80% range)
df = ds.ml.forecast("gold.daily_store_sales", date="sales_date", measure="net_sales", grain="month", horizon=3)
ds.ml.forecast("gold.daily_store_sales", date="sales_date", measure="net_sales", by="region",
               save="gold.sales_forecast_by_region")
Argument ds.ml.train
name model name; training the same name again adds a version (only its creator or an admin can)
target column to predict; leave out with task="clustering"
features columns to learn from (default: every other column except _technical ones)
task regression, classification or clustering (default: guessed from target)
model_types one type or a list; default AutoML
groups clustering only: number of groups (default: the best of 2 to 10)
sample_rows rows to learn from, sampled evenly (default 20,000, at most 50,000)
promote True makes the new version the Production version
Argument ds.ml.forecast
date, measure the date column and the column to total (agg="count" needs no measure)
grain, agg day … year (default month); sum (default), avg, count, min, max
horizon periods ahead, 1 to 24 (default 3)
by one forecast per value (the 12 largest)
save write history and forecast to this table (replaced each run)

The run appears in ML Models with its charts like any other. Each cell that calls ds.ml gets a short-lived ticket naming you; it is not a password and expires after two hours.

Forecast#

Quick ML → Forecast a measure totals a measure per period (day to year) and forecasts the next 1 to 24 periods, one series or one per value of a column (the 12 largest).

  1. The history is checked first: enough periods, gaps, a level change, and a period still running (shown as a hollow dot and forecast too, with its value so far).
  2. Several standard methods (trend line, damped trend, trend and season, same period last cycle, and the pretrained forecasting model when it is on) are tested on the latest periods held out; the most accurate is used. Methods tested lists each one's error.
  3. The chart shows the history in grey, the forecast in teal with its 80% range, and a line where the forecast starts. Next periods lists every forecast period with its range.
  4. Forecast and save writes the history and forecast to a table (period, actual, forecast, lower_80, upper_80, is_forecast, method, forecast_at, plus series with One forecast per), as you, replaced on every run; lineage records the source table.

Quick ML#

The ML & AI group in the left rail also has Quick ML, AI Functions and Feature Tables. Quick ML and AI Functions answer questions without creating a model:

  • Anything unusual? Pick a table, a measure, a date column and per day / week / month. Unusual periods are flagged with a robust z-score. With Reason by (store, product, region), each flag also names the value that moved most ("Store = 14: 9,800 vs usual 2,100").

  • What drives this measure? A quick forest ranks the columns that explain a measure. These are associations in your data, not proven causes.

  • AI text functions. Run a language model over a text column:

    Function What it does
    Classify picks one of your labels per row
    Sentiment positive, neutral or negative
    Summarize one sentence
    Extract fields pulls the named fields out as JSON
    Translate rewrites the text in another language

    Preview 20 rows first. Run and save writes up to 2,000 rows (key column, text, <function>_result) to a new table as you. Each run shows its cost and is logged. The texts are treated as data: instructions inside them are ignored.

Feature Tables registers Silver or Gold tables that hold ready-made model inputs, with the key that identifies a row, so people can find and reuse them.

From Sarah, Everything and agents#

Agents can use these tools:

Tool Runs
ml_models_list at once
ml_model_metrics at once
ml_explain at once
ml_anomalies at once
ml_top_drivers at once
ml_train waits for approval
ml_score waits for approval

The training and scoring tools wait for approval like every change an agent proposes (Everything).

In a Dataflow#

To score every night, add a notebook step that calls the API with the notebook's own session, or ask an agent to propose a ml_score action. The output table is an ordinary Gold table: downstream steps, Sarah and DataShare read it like any other.

What it costs#

  • Training runs on the application server; it reads the table through the SQL engine (a few seconds of cluster time).
  • Scoring and monitoring are one query each on the cluster that already runs your notebooks.
  • AI text functions are billed by the language model provider, per row; the cost of every run is shown and logged.

No extra servers are started.

API#

Method and path What it does
GET /api/ml/models models in your workspace
POST /api/ml/models create {name, task, sourceTable, targetColumn, featureColumns?}; task regression, classification or clustering (no targetColumn)
POST /api/ml/models/:id/train {automl?, modelTypes?, groups?}, starts a training job (groups 2 to 10 for clustering)
POST /api/ml/models/:id/versions/:v/status {status: "Production" | "Draft" | "Archived"}
POST /api/ml/models/:id/score {source?, output?, version?}, starts a scoring job
POST /api/ml/models/:id/predict {rows: [...], explain?}, scored at once
POST /api/ml/models/:id/monitor drift and accuracy now
POST /api/ml/functions/anomalies {table, metric, date, grain?, agg?, by?}
POST /api/ml/functions/top-drivers {table, metric}
POST /api/ml/forecast {table, date, measure?, grain?, agg?, horizon?, by?, output?}
POST /api/ml/ai-functions/run {table, column, fn, args, preview?, output?}