Density Clustering with DBSCAN: Find Groups and Call the Rest Noise

Objective

KMeans has one assumption that quietly ruins results: every point belongs to a cluster. Ask for three clusters and you get three, whether or not the data has three groups, and every outlier is dragged into whichever centroid is nearest, pulling it off-centre.

DBSCAN makes a different bargain. It finds regions that are dense, and everything that is not near enough to anything else is labelled noise rather than forced into a group. For store locations, sensor readings, or user behaviour with genuine one-offs in it, that is usually the honest answer.

Step 1: Two real groups and some scatter

Ninety observations: two tight clusters and nine points that belong to neither.

-- Drop first so the recipe is re-runnable.
DROP TABLE IF EXISTS recipe_dbscan_points;

CREATE TABLE recipe_dbscan_points (
  point_id  INT PRIMARY KEY,
  x         DOUBLE,
  y         DOUBLE
);

INSERT INTO recipe_dbscan_points (point_id, x, y) VALUES
  (1,11,11), (2,12,12), (3,13,13), (4,14,14), (5,15,10), (6,16,11),
  (7,10,12), (8,11,13), (9,12,14), (10,13,10), (11,14,11), (12,15,12),
  (13,16,13), (14,10,14), (15,11,10), (16,12,11), (17,13,12), (18,14,13),
  (19,15,14), (20,16,10), (21,10,11), (22,11,12), (23,12,13), (24,13,14),
  (25,14,10), (26,15,11), (27,16,12), (28,10,13), (29,11,14), (30,12,10),
  (31,13,11), (32,14,12), (33,15,13), (34,16,14), (35,10,10), (36,11,11),
  (37,12,12), (38,13,13), (39,14,14), (40,15,10), (41,55,61), (42,50,55),
  (43,51,56), (44,52,57), (45,53,58), (46,54,59), (47,55,60), (48,50,61),
  (49,51,55), (50,52,56), (51,53,57), (52,54,58), (53,55,59), (54,50,60),
  (55,51,61), (56,52,55), (57,53,56), (58,54,57), (59,55,58), (60,50,59),
  (61,51,60), (62,52,61), (63,53,55), (64,54,56), (65,55,57), (66,50,58),
  (67,51,59), (68,52,60), (69,53,61), (70,54,55), (71,55,56), (72,50,57),
  (73,51,58), (74,52,59), (75,53,60), (76,54,61), (77,55,55), (78,50,56),
  (79,51,57), (80,52,58), (81,14,68), (82,23,81), (83,32,94), (84,41,17),
  (85,50,30), (86,59,43), (87,68,56), (88,77,69), (89,86,82), (90,5,5);

Step 2: Ground truth — confirm the shape before clustering

SELECT
  COUNT(*)                                                                      AS total_points,
  SUM(CASE WHEN x < 25 AND y < 25 THEN 1 ELSE 0 END)                            AS lower_left,
  SUM(CASE WHEN x BETWEEN 45 AND 60 AND y BETWEEN 50 AND 65 THEN 1 ELSE 0 END)  AS upper_middle,
  SUM(CASE WHEN NOT (x < 25 AND y < 25)
            AND NOT (x BETWEEN 45 AND 60 AND y BETWEEN 50 AND 65)
           THEN 1 ELSE 0 END)                                                   AS neither
FROM recipe_dbscan_points;

Expected: 90 points — 41 lower-left, 41 upper-middle, 8 in neither. Those eight are the ones KMeans would have to put somewhere.

Keep that count in mind: in Step 5 DBSCAN settles on 41 / 40 / 9. The one-point difference is a boundary case that my hand-drawn box calls part of a region and density clustering calls noise. Neither is "wrong" — it is exactly the judgement you are delegating to the algorithm.

Counting with SUM(CASE …) rather than GROUP BY on a CASE expression is deliberate. GROUP BY 1 by ordinal is not supported and fails with "Column 'x' not found in available columns: group_0". Conditional sums do the same job in one row and work everywhere.

Step 3: Cluster by density

CREATE EXPERIMENT recipe_dbscan_exp AS
  SELECT x, y FROM recipe_dbscan_points
WITH (
  task_type           = 'clustering',
  algorithms          = ['dbscan'],
  optimization_metric = 'silhouette',
  max_trials          = 5,
  random_seed         = 42
);

No target_column and no n_clusters. Both omissions are the point. There are no labels, and unlike KMeans you do not tell DBSCAN how many groups to find — it discovers that from the density itself. If you know the number of groups in advance, KMeans is the better tool.

Step 4: Read the result

{
  "status": "success",
  "best_score": 0.8479,
  "score_basis": "internal_validation",
  "total_trials": 2,
  "trials_attempted": 3,
  "failed_trials": [],
  "optimization_metric": "silhouette",
  "ignored_options": []
}

best_score 0.8479 is a silhouette, not an accuracy. It measures how separated the clusters are: 1.0 is perfect separation, 0 means they overlap completely, and negative means points are closer to a neighbouring cluster than their own. 0.85 is strong separation.

There is nothing to be accurate about — no labels exist — so never present a silhouette as a hit rate. score_basis: internal_validation is the engine telling you the same thing.

Note total_trials 2 against trials_attempted 3 with an empty failed_trials: one parameter combination produced no usable clustering and was discarded rather than erroring. That is normal for DBSCAN, whose results depend sharply on its neighbourhood radius.

Step 5: Assign every point, including the noise

DEPLOY MODEL recipe_dbscan_model FROM EXPERIMENT recipe_dbscan_exp;

PREDICT cluster_id USING recipe_dbscan_model AS
  SELECT x, y FROM recipe_dbscan_points;

Expected: 90 rows of (x, y, cluster_id) with three distinct values:

-1 is the whole reason to use DBSCAN. Those nine points are not a small third cluster and not an error; they are the engine declining to invent a group for them. The counts track the regions from Step 2 (41 / 41 / 8) to within a single boundary point, which is the check that the clustering found the real structure rather than an artefact.

Cluster ids are arbitrary integers. 0 and 1 carry no meaning and may swap between runs — never store the number as if it were a category name. -1 is the one id with a fixed meaning.

Step 6: Separate the signal from the one-offs

PREDICT cluster_id USING recipe_dbscan_model AS
  SELECT x, y FROM recipe_dbscan_points WHERE x < 25 AND y < 25;

Expected: every row comes back as cluster 0. Filtering to a region you believe is one group and finding a single cluster id confirms the model agrees with you — and if it does not, that disagreement is the interesting result.

In production the two halves go to different places: the clustered rows feed segmentation, and the -1 rows feed a review queue. Those nine points are often the most valuable rows in the table.

Cleanup (Optional)

DROP TABLE IF EXISTS recipe_dbscan_points;

Use it from your agent

• REST/SDK: POST /v1/query/execute with the CREATE EXPERIMENT text, then materialise the Step 5 assignments into a table your application reads.
• Choosing between them: KMeans when you know how many groups you want and every row must belong to one; DBSCAN when the number is unknown and "none of them" is a valid answer.
• Why in-DB: the assignments are produced beside the rows they describe, so there is no export and no join back against a stale copy.

Key Concepts Learned

• -1 means noise. DBSCAN refuses to assign points that are not in a dense region, and that refusal is information.
• You do not specify the cluster count. Density determines it; that is the core difference from KMeans.
• A silhouette is not an accuracy. It measures separation, with no ground truth involved.
• Cluster ids are arbitrary except -1. Do not persist 0 or 1 as a meaningful label.
• Fewer trials than attempted is normal here. Some radii produce no usable clustering and are dropped without an error.