Deep Neural Network (DNN / MLP)

TYPE 'dnn' (aliases: mlp, neural_network, neural-network) trains a fully-connected feedforward network with configurable hidden-layer widths, activation, and batch gradient descent hyperparameters. The task option selects the output head: regression (mean squared error), binary_classification (sigmoid + BCE), or multiclass (softmax + cross-entropy). Compare an MLP with simpler baselines on held-out data, especially for dense features with nonlinear interactions.

When to use it

• Inputs are dense embeddings or pre-projected features where smooth, learned non-linearities matter.
• Multi-task setups where the same hidden representation feeds different heads via repeated training.
• A regression target with mild non-linearities that linear regression underfits.

When NOT to use it

• Plain tabular data with mixed scales — gradient boosting is faster to train and usually as accurate.
• Very small datasets (< few thousand rows) — an MLP will overfit; use logistic / tree-based.
• You need interpretability — a DNN's per-feature contribution isn't a single weight.

Syntax

CREATE MODEL <name>(DOUBLE, DOUBLE[, ...]) RETURNS DOUBLE
TYPE { 'dnn' | 'mlp' | 'neural_network' | 'neural-network' }
OPTIONS (task = '<head>', ...)
AS SELECT <label_or_target>, <f1>, <f2>, ... FROM <source>;

The first SELECT column is the label/target; remaining columns are features. The model signature contains one DOUBLE per feature, excluding the label.

Options

Option	Default	Type	Range	What it does
task	regression	string	regression / regress / reg / binary / binary_classification / bce / multiclass / multi_classification / softmax / categorical	Output head
num_classes	(required when multiclass)	int	>= 2	Class count for the multiclass head
hidden_layers	16	string	comma-separated positive ints, no zero-width layers	Hidden-layer widths, e.g. '64,32' builds two hidden layers of 64 and 32 units
activation	relu	string	relu / tanh / sigmoid (alias logistic)	Activation function for hidden layers
learning_rate	0.05	float	> 0	batch gradient descent step size
l2_lambda	0.0	float	>= 0	L2 weight decay
max_iters	100	int	>= 1	Maximum training iterations
tolerance	1e-4	float	>= 0	Convergence threshold on weight delta between iterations
seed	828927569933	u64	non-negative decimal integer	RNG seed for weight init
partitions	1	int	>= 1	Training partitions; see execution and memory limits

Examples

These fragments assume the named source tables exist. Match training and inference feature order and preprocessing. For a complete dataset and runnable script, follow the linked tutorial.

Regression:

CREATE MODEL ltv(DOUBLE, DOUBLE, DOUBLE) RETURNS DOUBLE
TYPE 'dnn'
OPTIONS (
    task = 'regression',
    hidden_layers = '64,32',
    activation = 'relu',
    learning_rate = 0.01,
    l2_lambda = 0.0001,
    max_iters = 1000,
    seed = 42
)
AS SELECT
    lifetime_value AS label,
    signup_age_days, sessions_30d, avg_session_minutes
FROM customer_features;

Multiclass classifier:

CREATE MODEL intent(DOUBLE, DOUBLE, DOUBLE, DOUBLE) RETURNS INT
TYPE 'mlp'
OPTIONS (
    task = 'multiclass',
    num_classes = 5,
    hidden_layers = '128,64,32',
    activation = 'tanh',
    learning_rate = 0.005,
    l2_lambda = 0.001,
    max_iters = 2000,
    tolerance = 1e-6,
    partitions = 4
)
AS SELECT
    CAST(intent_id AS DOUBLE) AS label,
    f1, f2, f3, f4
FROM intent_features;

Output shape

• task = 'regression': a single DOUBLE predicted value.
• task = 'binary_classification': a single DOUBLE in [0, 1] — the probability of class 1.
• task = 'multiclass': one INT class ID in [0, num_classes), selected by argmax; no probability vector is exposed by this UDF.

Tuning notes

• Standardise features to roughly [-1, 1] before training. MLP batch gradient descent is sensitive to feature scale.
• Start with one or two hidden layers ('64' or '64,32'); deeper networks only help with much more data.
• If training loss oscillates, halve learning_rate. If it plateaus too fast, increase to 0.05–0.1.
• Add l2_lambda (start 0.0001) when the train/validation gap widens.
• For multiclass, softmax head is the only mode — num_classes is required and labels must be 0..num_classes-1 (cast from integer columns).

Convergence and quality

TrainingMetrics reports iterations (epochs actually run) and converged: true/false. Treat converged = false with iterations == max_iters as "increase the budget or relax tolerance". EVALUATE MODEL emits rmse/mae for regression heads and accuracy/precision/recall/f1 (plus macro_* for multiclass) for classification heads.

Related

• Practical tutorial

• Supported model types — Deep neural network

• DNN / MLP training — extended walkthrough

• Logistic regression — linear-decision-boundary alternative

• EVALUATE MODEL