Tree ensembles and model tuning
Compare decision trees, random forests, and boosted trees, then inspect a twelve-variant tuning run.
The practical problem and model
A risk-analytics team wants to compare classifiers for tabular application data. This synthetic example uses income, debt ratio, open accounts, and late payments to predict a binary outcome.
A decision tree routes inputs through threshold tests. A random forest combines multiple trees to reduce dependence on one tree's particular splits. Gradient boosting adds trees sequentially to improve the current model. Feature importance describes how a fitted model uses its inputs; it does not establish that changing an input causes the outcome to change.
The ten-row fixture is intentionally simple. Its credit-themed column names illustrate a tabular classification workflow, not a model suitable for lending decisions.
Before you run
Complete the shared setup. This walkthrough uses synthetic data and recreates its tutorial objects. Use a sandbox tenant or schema, and run the steps in order.
Studio: Tutorial: Tree Ensembles + ML.TUNE — Credit Risk. Download the complete SQL.
Step 1: Create a labelled application fixture
Five rows have each label. All three models train on the same feature ordering.
CREATE CATALOG IF NOT EXISTS tutorial;
CREATE SCHEMA IF NOT EXISTS tutorial.credit;
CREATE OR REPLACE TABLE tutorial.credit.applications (
application_id BIGINT,
income DOUBLE,
debt_ratio DOUBLE,
open_accounts DOUBLE,
late_payments DOUBLE,
defaulted INT
);
INSERT INTO tutorial.credit.applications VALUES
(1, 85000, 0.18, 4, 0, 0), (2, 42000, 0.55, 7, 3, 1),
(3, 120000, 0.10, 3, 0, 0), (4, 31000, 0.62, 9, 4, 1),
(5, 68000, 0.22, 5, 1, 0), (6, 29000, 0.71, 8, 5, 1),
(7, 95000, 0.15, 4, 0, 0), (8, 38000, 0.58, 6, 2, 1),
(9, 110000, 0.12, 3, 0, 0), (10, 27000, 0.78, 9, 6, 1);
Step 2: Train three model families
Depth and leaf-size settings control tree complexity. Forest size and feature subsampling vary the ensemble; boosting also uses a learning rate. Inspect each model's feature-importance output.
CREATE OR REPLACE MODEL tutorial.credit.tree_clf
(DOUBLE, DOUBLE, DOUBLE, DOUBLE)
RETURNS DOUBLE
TYPE 'decision_tree'
OPTIONS (max_depth = 4, min_samples_leaf = 1)
AS SELECT
CAST(defaulted AS DOUBLE) AS label,
income, debt_ratio, open_accounts, late_payments
FROM tutorial.credit.applications;
SHOW FEATURE IMPORTANCE FOR MODEL tutorial.credit.tree_clf;
CREATE OR REPLACE MODEL tutorial.credit.forest_clf
(DOUBLE, DOUBLE, DOUBLE, DOUBLE)
RETURNS DOUBLE
TYPE 'random_forest'
OPTIONS (num_trees = 50, max_depth = 6, min_samples_leaf = 1, feature_subsample = 0.8)
AS SELECT
CAST(defaulted AS DOUBLE) AS label,
income, debt_ratio, open_accounts, late_payments
FROM tutorial.credit.applications;
SHOW FEATURE IMPORTANCE FOR MODEL tutorial.credit.forest_clf;
CREATE OR REPLACE MODEL tutorial.credit.gbt_clf
(DOUBLE, DOUBLE, DOUBLE, DOUBLE)
RETURNS DOUBLE
TYPE 'gradient_boosting'
OPTIONS (num_trees = 100, max_depth = 3, learning_rate = 0.05)
AS SELECT
CAST(defaulted AS DOUBLE) AS label,
income, debt_ratio, open_accounts, late_payments
FROM tutorial.credit.applications;
SHOW FEATURE IMPORTANCE FOR MODEL tutorial.credit.gbt_clf;
Step 3: Compare predictions row by row
Despite their p_tree, p_forest, and p_gbt aliases, these classifier outputs are class decisions, not calibrated probabilities.
SELECT
application_id,
defaulted AS actual,
tutorial.credit.tree_clf(income, debt_ratio, open_accounts, late_payments) AS p_tree,
tutorial.credit.forest_clf(income, debt_ratio, open_accounts, late_payments) AS p_forest,
tutorial.credit.gbt_clf(income, debt_ratio, open_accounts, late_payments) AS p_gbt
FROM tutorial.credit.applications
ORDER BY application_id;
Step 4: Inspect the tuning search and selected model
The grid has 3 × 2 × 2 = 12 combinations. validation_fraction = 0.2 reserves part of the fixture for ranking. The final call uses the selected model under the base gbt_tuned name rather than assuming variant zero is always best.
ML.TUNE tutorial.credit.gbt_tuned
(DOUBLE, DOUBLE, DOUBLE, DOUBLE)
RETURNS DOUBLE
TYPE 'gradient_boosting'
OPTIONS (max_variants = 12, validation_fraction = 0.2)
GRID (
num_trees = [50, 100, 200],
max_depth = [2, 4],
learning_rate = [0.05, 0.1]
)
AS SELECT
CAST(defaulted AS DOUBLE) AS label,
income, debt_ratio, open_accounts, late_payments
FROM tutorial.credit.applications ORDER BY application_id;
SHOW MODELS;
SELECT tutorial.credit.gbt_tuned(50000.0, 0.4, 6.0, 2.0) AS p_default;
What to check in the results
The comparison returns ten application IDs, and all three classifiers matched the fixture labels in the recorded run. Importance rows must name real input features and contain finite, nonnegative values.
The tuning result contains 12 variants with model names, scores, and the selection result. Its held-out partition has only two rows: a perfect score there is weak evidence about future performance. Inspect the reported winner instead of hard-coding a ranking or treating a score of one as production readiness.
Adapt it to real data
Use enough independent validation data to compare model families and a final untouched test set to assess the selected model. Separate records by entity or time where appropriate. Limit the search budget and record exactly which feature transformations and data snapshot produced each variant.
For consequential decisions, model validation must include subgroup performance, stability, explanations appropriate to the use case, and a process for human review. This fixture establishes none of those deployment outcomes.