Skip to main content

KMeans Clustering

TYPE 'kmeans' partitions rows into k clusters by Euclidean distance using Lloyd's algorithm. Initial centroids are chosen by k-means++ (the default) or forgy (the first k valid rows; independent of seed). The trainer runs until the per-iteration centroid movement falls below tolerance or max_iters is hit, whichever comes first.

When to use it​

  • Customer / behavioural segmentation where features are continuous and roughly comparable in scale.
  • Vector quantization or anchor selection upstream of nearest-neighbour search.
  • Compressing a wide feature set into a categorical "cluster id" for downstream joins.

When NOT to use it​

  • Mixed categorical + continuous features without preprocessing — Euclidean distance loses meaning.
  • Highly non-spherical clusters or large density gradients — KMeans assumes isotropic, similarly-sized clusters.
  • Operator does not know k in advance and isn't willing to scan a scree plot. KMeans does not auto-pick k.

Syntax​

CREATE MODEL <name>(DOUBLE[, DOUBLE ...]) RETURNS INT
TYPE { 'kmeans' | 'k_means' | 'k-means' }
OPTIONS (k = <int> [, ...])
AS SELECT <feature_cols> FROM <source>;

The numeric training columns are normalized to DOUBLE; categorical strings must be encoded first. The model emits a cluster id (INT) when called.

Options​

OptionDefaultTypeRangeWhat it does
k(required)int>= 1Number of clusters
initkmeans++stringkmeans++ / k-means++ / k_means_plus_plus / forgyCentroid initialization. forgy reuses the first k valid rows; its result depends on row order
max_iters100int>= 1Maximum Lloyd iterations
tolerance1e-4float>= 0Convergence threshold on max centroid movement between iterations
seed(random)u64anyRNG seed for k-means++ sampling
partitions1int>= 1Training partitions; see execution and memory limits. > 1 routes through the partitioned driver

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:

CREATE MODEL segments(DOUBLE, DOUBLE) RETURNS INT
TYPE 'kmeans'
OPTIONS (k = 4)
AS SELECT recency_days, total_spend FROM customer_features;

SELECT customer_id, segments(recency_days, total_spend) AS cluster_id
FROM customer_features;

Tuned with a fixed initialization seed and four training partitions:

CREATE MODEL behavioural_clusters(DOUBLE, DOUBLE, DOUBLE, DOUBLE) RETURNS INT
TYPE 'kmeans'
OPTIONS (
    k = 8,
    init = 'kmeans++',
    max_iters = 200,
    tolerance = 1e-5,
    seed = 42,
    partitions = 4
)
AS SELECT
    avg_order_value,
    purchase_frequency,
    days_since_last_order,
    sessions_30d
FROM customer_features;

Output shape​

segments(f1, f2, ...) returns an INT cluster id in [0, k) for each row. Handle missing features explicitly in preprocessing; a cluster assignment is meaningful only for the feature representation used to train the model.

Tuning notes​

  • Compare within-cluster squared distances or a silhouette score across candidate k values. output_stddev describes numeric cluster IDs and is not within-cluster error.
  • Fix the data snapshot, order, seed, and partition layout when comparing runs. Cluster IDs are arbitrary labels and may be permuted between fits.
  • Benchmark partition counts on the actual workload; more partitions can add overhead.
  • Standardise feature scales before training — a column ranging [0, 1e6] will dominate one ranging [0, 1].
  • Increase tolerance (e.g. 1e-3) if iterations stall on noise; decrease it for tight convergence.

Convergence and quality​

The trainer reports iterations (actual Lloyd passes performed) and converged: true/false. converged = false with iterations == max_iters means the centroids were still moving more than tolerance when the iteration cap was hit — bump max_iters or relax tolerance.

EVALUATE MODEL emits operational metrics (row_count, latency_ms, output_mean, output_stddev); for clustering the mean / stddev describe the assigned-cluster-id distribution and are useful for spotting degenerate runs (e.g. every row landing in one cluster).

Last updated on