Models in SQL

BigQuery ML trains a model with CREATE MODEL and uses it with ML.PREDICT. For an experiment that is all you need. For production, a model needs releases, gates, approvals and monitoring. Pipewright gives a SQL team all of those with YAML and SQL, and no pipeline code.

1. Start a BigQuery ML use case

pipewright new-usecase --name segments --tenant <your-team> --owner <your-team> --from bqml

The use case has a bqml section instead of pipelines. Pipewright provides the training and scoring pipelines; their steps submit BigQuery jobs, so the data and the model never leave BigQuery.

2. Segments with k-means

spec:
  data:
    label: { task: clustering }           # no label: the model finds groups
  bqml:
    model_type: kmeans
    options: { num_clusters: 4 }
    prepare: sql/prepare.sql              # optional: SQL over {rows}, the run's rows
  evaluation:
    gates: [{ metric: davies_bouldin_index, max: 1.5 }]

Clusters are measured with ML.EVALUATE and gated like any metric. Scoring writes each row’s cluster to the inference table.

3. A forecast

spec:
  data:
    label: { task: forecast }
  bqml:
    model_type: arima_plus
    forecast: { horizon: 8, id_columns: [store_id] }   # one series per store
  evaluation:
    gates: [{ metric: mape, max: 0.25 }]

Pipewright holds back the latest period, forecasts it and measures the forecast against what happened, then refits on the whole series before registering. Since 1.2.0, gates can apply to every series, not only the average.

4. Release, train, approve

pipewright validate --env dev && pipewright tables sync --env dev
git add -A && git commit -m "segments and forecast"
pipewright release --env dev
pipewright run --env dev --usecase segments --source-uri bq://<project>.<dataset>.<table>
pipewright model list --env dev --usecase segments

Only a model that passes its gates is registered in Model Registry, as a new version; approval, scoring, monitoring and retrain decisions work as for any model.