DNN / MLP Training

Gnok's DNN trainer is a shallow / deep multi-layer perceptron trained end-to-end through the same CREATE MODEL ... AS SELECT pipeline as the rest of the trained-in-engine algorithms. It supports three tasks (regression, binary classification, multi-class) and three hidden-layer activations (ReLU, tanh, sigmoid), with arbitrary depth controlled by the hidden_layers option.

Aspect	Value
`TYPE '<algo>'` aliases	`'dnn'`, `'mlp'`, `'neural_network'`, `'neural-network'`
Tasks	`regression`, `binary`, `multiclass`
Hidden-layer activations	`relu` (default), `tanh`, `sigmoid`
Output non-linearity	identity (regression) / sigmoid (binary) / softmax (multiclass) — task-driven
Predict returns	`FLOAT64` (regression + binary) / `INT32` argmax (multiclass)
Distributed training	`partitions = N`; spreading partitions across workers is service-managed

Quick start

Regression

CREATE MODEL revenue_pred(DOUBLE, DOUBLE, DOUBLE) RETURNS DOUBLE
  TYPE 'dnn'
  OPTIONS (
    task = 'regression',
    hidden_layers = '32,16',
    activation = 'relu',
    learning_rate = 0.05,
    max_iters = 200
  )
  AS SELECT revenue, ad_spend, seasonality, avg_basket
     FROM marketing_data;

-- Predict
SELECT campaign_id, revenue_pred(ad_spend, seasonality, avg_basket) AS forecast
FROM new_campaigns;

Binary classification

CREATE MODEL fraud_dnn(DOUBLE, DOUBLE, DOUBLE, DOUBLE) RETURNS DOUBLE
  TYPE 'mlp'
  OPTIONS (
    task = 'binary',
    hidden_layers = '16,8',
    activation = 'relu',
    learning_rate = 0.05,
    l2_lambda = 1e-4,
    max_iters = 300
  )
  AS SELECT
       is_fraud::DOUBLE AS y,
       amount::DOUBLE   AS x0,
       merchant_score::DOUBLE AS x1,
       hour_of_day::DOUBLE AS x2,
       account_age_days::DOUBLE AS x3
     FROM transactions;

-- Predict — returns a probability in [0, 1]
SELECT txn_id, fraud_dnn(amount, merchant_score, hour_of_day, account_age_days) AS p
FROM live_transactions
WHERE fraud_dnn(amount, merchant_score, hour_of_day, account_age_days) > 0.85;

Multi-class classification

CREATE MODEL ticket_router(DOUBLE, DOUBLE, DOUBLE) RETURNS INT
  TYPE 'dnn'
  OPTIONS (
    task = 'multiclass',
    num_classes = 4,
    hidden_layers = '64,32,16',
    activation = 'relu',
    learning_rate = 0.03,
    max_iters = 500
  )
  AS SELECT
       category_id::DOUBLE AS y,
       sentiment_score::DOUBLE AS x0,
       length_norm::DOUBLE AS x1,
       account_tier::DOUBLE AS x2
     FROM labeled_tickets;

-- Predict — returns an INT32 class id in [0, num_classes)
SELECT ticket_id, ticket_router(sentiment, length_norm, tier) AS routed_to
FROM incoming_tickets;

Options reference

Option	Default	Description
`task`	`regression`	One of `regression` (alias: `regress`, `reg`) / `binary` (alias: `binary_classification`, `bce`) / `multiclass` (alias: `multi_classification`, `softmax`, `categorical`).
`num_classes`	required for multiclass	Class count K ≥ 2. Ignored for the other tasks. Labels must be integers in `[0, K)`.
`hidden_layers`	`"16"`	Comma-separated unit counts, one per hidden layer. Examples: `"32"` (one layer of 32), `"16,8"` (two layers), `"64,32,16"` (three layers). Zero-unit layers are rejected.
`activation`	`relu`	Hidden-layer activation: `relu`, `tanh`, or `sigmoid` (alias: `logistic`). The output non-linearity is task-driven and not controlled by this option.
`learning_rate`	`0.05`	Batch gradient descent step size. Must be > 0.
`l2_lambda`	`0.0`	L2 regularisation coefficient. Must be ≥ 0.
`max_iters`	`100`	Maximum training iterations (epochs).
`tolerance`	`1e-4`	Weight-delta tolerance for early termination. Training stops when the per-iteration weight delta drops below this.
`seed`	`828927569933`	Initial-weight seed. Set explicitly when comparing training runs.
`partitions`	`1`	Training parallelism. See the Distributed training section.

Architecture

A network with num_features → hidden_layers[0] → … → hidden_layers[L-1] → output_size shape, where:

num_features is inferred from the SELECT projection (everything after column 0 — the label / target).
output_size is task-driven: 1 for regression and binary classification (sigmoid head); num_classes for multiclass (softmax head).

Every hidden layer applies the configured activation. The output layer applies the task's non-linearity:

Task	Output non-linearity	Loss
`regression`	identity	mean-squared error
`binary`	sigmoid	binary cross-entropy
`multiclass`	softmax	categorical cross-entropy

Forward pass:

z_l = W_l · a_{l-1} + b_l
a_l = activation(z_l)            // hidden layers
a_L = output_nonlinearity(z_L)   // output layer

Backward pass uses standard backpropagation; weights update via batch gradient descent with the configured learning_rate and optional L2 regularisation. The trainer's persistent state captures the architecture + activation + task it was trained with, so predict applies the exact same forward path used during training.

Predict semantics

Task	UDF return type	Interpretation
`regression`	`FLOAT64`	Direct output of the identity head — the regressed scalar.
`binary`	`FLOAT64`	Sigmoid-activated probability in `[0, 1]`. Threshold yourself for a hard 0/1 label.
`multiclass`	`INT32`	Argmax over the softmax output.

The multiclass SQL UDF returns the winning class ID. It does not expose a full probability vector. Regression with a one-hot target is not a substitute for this output contract.

Reproducibility

Fix the data snapshot, feature order, preprocessing, initialization seed, and partition layout when comparing fits. Floating-point reduction order can change across partition counts or execution platforms, so compare with numerical tolerances and held-out metrics. See distributed training.

Distributed training

Set partitions = N to split each training iteration across N partitions of the training input. The per-iteration partial results are merged into one global update per iteration. Floating-point results may vary with partition layout:

CREATE MODEL fast_dnn(DOUBLE, DOUBLE, DOUBLE, DOUBLE) RETURNS DOUBLE
  TYPE 'dnn'
  OPTIONS (
    task = 'binary',
    hidden_layers = '32,16',
    learning_rate = 0.05,
    max_iters = 200,
    partitions = 8
  )
  AS SELECT label, x0, x1, x2, x3 FROM features_lakehouse;

Where Gnok has enabled additional workers for your account, the partitions can run on them; otherwise they run on the query service. The complete training input is still materialized before training. See the Distributed Training page for details.

Versioning

DNN supports the same CREATE MODEL name VERSION 'vN' (...) staged-version pipeline as every other model. Re-train against fresh data without flipping the active version; promote via ALTER MODEL <name> ACTIVATE VERSION 'vN':

CREATE MODEL fraud_dnn VERSION 'v2' (DOUBLE, DOUBLE, DOUBLE, DOUBLE)
  RETURNS DOUBLE TYPE 'dnn'
  OPTIONS (task = 'binary', hidden_layers = '32,16', max_iters = 300)
  AS SELECT label, x0, x1, x2, x3 FROM transactions
     WHERE created_at >= now() - INTERVAL '90 days';

ALTER MODEL fraud_dnn ACTIVATE VERSION 'v2';

See CREATE MODEL VERSION for the cross-algorithm reference.

When to pick DNN vs simpler algorithms

DNN's flexibility comes at a cost: more hyperparameters to tune, longer training time, and worse interpretability than the generalised linear models. Reach for the simpler tool first and only escalate when you actually need the non-linearity.

Use case	Pick
Small `D` (≤ 10), linear-ish target	Linear regression
Small `D`, binary target, mostly linear	Logistic regression
Tabular, non-linear feature interactions	Gradient Boosting
Tabular, low overfit risk, parallel	Random Forest
Need explicit hidden-layer architecture, multi-class K > 2, or smooth (non-axis-aligned) decision boundaries	DNN / MLP

For very deep networks, image / sequence data, or models that need custom autograd ops, train externally (PyTorch, TF) and deploy via ONNX or TorchScript instead of the in-engine trainer.

Limitations

The complete training input is materialized before training, including with partitions. The default training-row cap is 1,000,000 rows.
Early termination uses parameter convergence, not held-out validation loss. Maintain a separate evaluation set.
The trainer accumulates gradients over training batches before each global update. There is no independent mini-batch optimization-size setting.
Native multiclass output is a class ID; arbitrary tensor outputs require a different serving contract.

Run the DNN tutorial or use the algorithm reference for current option defaults.

Quick start​

Regression​

Binary classification​

Multi-class classification​

Options reference​

Architecture​

Predict semantics​

Reproducibility​

Distributed training​

Versioning​

When to pick DNN vs simpler algorithms​

Limitations​