Decision Tree

TYPE 'decision_tree' (aliases: tree, stump) trains a single CART classification tree using histogram-based split search. The trainer establishes per-feature bounds and uses histograms to choose splits as it grows the tree. Use it as an interpretable baseline or set max_depth = 1 for a stump.

When to use it

• An interpretable baseline you can hand-trace from root to leaf.
• A weak learner inside a manual ensemble.
• Small or medium-sized labelled datasets where a tree's variable-cardinality + missing-value handling beats logistic regression.

When NOT to use it

• Tasks where a single tree underfits or overfits on held-out data — compare random forest and gradient boosting.
• Continuous regression targets — the native trainer is classification-only.
• Very wide feature sets without thoughtful max_depth and min_samples_leaf — the tree will memorise noise.

Syntax

CREATE MODEL <name>(DOUBLE, DOUBLE[, ...]) RETURNS INT
TYPE { 'decision_tree' | 'decision-tree' | 'tree' | 'stump' }
OPTIONS (...)
AS SELECT <class_label>, <f1>, <f2>, ... FROM <source>;

The label column is first; remaining columns are features. Label values must be non-negative integers < num_classes.

Options

Option	Default	Type	Range	What it does
max_depth	6	int	>= 1 (capped at 32)	Maximum tree depth
num_bins	64	int	>= 2	Histogram bins per feature for split search
min_samples_split	2	int	>= 1	Minimum rows needed at a node for it to be eligible to split
min_samples_leaf	1	int	>= 1	Minimum rows that must remain in each child after a split
min_impurity_decrease	0.0	float	>= 0 (finite)	Minimum impurity reduction required to accept a split
num_classes	2	int	>= 2 (capped at 256)	Output class count
ccp_alpha	0.0	float	>= 0	Cost-complexity post-pruning strength (sklearn-canonical)
class_weight	(none)	string	'balanced' only	When 'balanced', per-class weights are auto-computed as n_total / (num_classes × n_class_c)
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.

Minimal binary classifier:

CREATE MODEL fraud_tree(DOUBLE, DOUBLE) RETURNS INT
TYPE 'decision_tree'
AS SELECT
    CAST(is_fraud AS DOUBLE) AS label,
    amount,
    merchant_risk
FROM transactions;

Tuned with cost-complexity pruning and balanced classes:

CREATE MODEL fraud_tree(DOUBLE, DOUBLE, DOUBLE, DOUBLE) RETURNS INT
TYPE 'decision_tree'
OPTIONS (
    max_depth = 10,
    num_bins = 128,
    min_samples_split = 50,
    min_samples_leaf = 20,
    min_impurity_decrease = 0.001,
    ccp_alpha = 0.01,
    class_weight = 'balanced',
    num_classes = 2
)
AS SELECT
    CAST(is_fraud AS DOUBLE) AS label,
    amount,
    CAST(hour_of_day AS DOUBLE),
    merchant_risk,
    velocity_24h
FROM transactions;

Output shape

fraud_tree(f1, f2, ...) returns an INT predicted class id in [0, num_classes) for each row.

Tuning notes

• Cap max_depth first. 6–10 is a sensible starting range; deeper trees overfit small data.
• Raise min_samples_leaf (try 20–100) before raising min_samples_split — leaf-size constraints are more direct overfit control.
• num_bins = 64 is the canonical histogram size; bump to 128 for very fine-grained continuous features.
• For imbalanced labels set class_weight = 'balanced' — this is the only supported value today.
• ccp_alpha > 0 runs sklearn-canonical post-pruning; pick by cross-validating over a small grid (0.0, 0.001, 0.01).

Convergence and quality

The outer training summary does not measure generalization. Evaluate predictions on separate labelled data. EVALUATE MODEL emits accuracy, precision, recall, f1 (and the macro_* variants when num_classes > 2).

Related

• Practical tutorial

• Supported model types — Decision tree

• Random forest — the bagged, feature-subsampled extension

• Gradient boosting — the boosted extension

• EVALUATE MODEL