Skip to content

Schema — unsupervised (clustering / anomaly)

Tables an experiment of type=cluster or type=anomaly can publish. Clustering and anomaly use different fact tables. Query the data-kb name (RULE 1).

Contents

  • Clustering: *__cluster_assignments + *__clusters
  • Anomaly: *__anomalies
  • Example queries

Clustering

  • Fact *__cluster_assignments (PK row_index): row_index, the input's declared key column(s) (catalog role key — the answer to "which customer is in which segment"), row_role (train on every row: the fit learned from all of them), cluster_id (FK → clusters), distance? (to cluster centre).
  • Dimension *__clusters (PK cluster_id): cluster_id, label, size, profile?, name_source, ai_name?, ai_description?.
  • Computed, approved, AI-written. label is the name a person approved when the run was released (name_source = 'approved'), else the id-derived Cluster 3 — the same identity the chart axes use. profile is computed: the attributes that most separate the cluster from the dataset average, strongest first, each as <field> <signed z><unit> — for example tenure +2.1sd, balance -1.4sd, Notes 1.8rel (best of 384 dims).
  • sd = standard deviations of that raw column. Written only for an axis that is an affine image of the user's own column.
  • rel = relative enrichment on a derived axis (a rank image, a 0/1 share, a frequency lookup, a text latent). Do not report a rel number as standard deviations of the named column — nobody computed that.
  • (best of N dims) marks a source spread over several axes (a text column's embedding): the number is its strongest axis and the sign of a latent axis means nothing, so the term is written unsigned.
  • It is descriptive, not a significance test: the clusters were chosen to maximise exactly this separation, so no p-value or q-value is attached.
  • Empty profile vs absent profile. An empty string means the computation ran and no attribute distinguishes this cluster (it is below the support floor, or its members are spread like the rest of the data) — that is a finding. The whole column is absent when the fields could not be computed at all (a run fitted by an older engine). Never report "nothing distinguishes any cluster" off a table that has no profile column.
  • ai_name / ai_description are AI-written text, not measurements. The workspace's agent wrote them (the run's clusters.names annotation) from the cluster's statistics and a sample of its member rows, so they can be wrong or invented. Verify against profile and your own aggregates; never quote one as a finding, and never without saying it is AI-written. name_source is one of approved (a person approved the name in label at release — it wins over the others), ai (AI-written), no_evidence (the run's clusters were named and this one had nothing to ground a name on — leave it unnamed rather than naming it yourself) or not_named (no names were written for the run: the workspace's profile does not write them automatically, or nobody asked; this says nothing whatever about the cluster). Both ai_* columns are absent entirely on runs with no names written.

Anomaly

  • Fact *__anomalies (PK row_index): row_index, the input's declared key column(s), row_role (train), anomaly_score (higher = more anomalous), is_anomaly? (bool), rank? (1 = most anomalous), decision_threshold?, threshold_source?.
  • anomaly_score is not a probability. It is a detector-specific, unnormalised quantity whose only guarantee is ordering within this run. No units, no calibration, not comparable across runs or detectors. Do not report it as a likelihood or a percentage, and do not average it across runs. Report ranks, percentiles or counts instead.
  • Run quality is separation, not count(is_anomaly). separation (the run's validation_score in the catalog / experiment summary) is the gap between flagged and typical scores in unflagged-IQR units — dimensionless, so it compares across detectors. The flagged share does not: with a mode='single' detector it restates the operator's contamination setting, so "5% are anomalous" is a knob, not a finding.
  • Operating point. is_anomaly is taken at the run's current cutoff, and threshold_source says whose cutoff that is: fitted (the detector's own) or operator (a threshold committed on the Decision surface after the fit). Quote it when you report a flagged count — an operator can re-cut the same scores at any time, and the count moves with them. The comparison differs by source: fitted is anomaly_score > decision_threshold (the detector's own rule) and operator is anomaly_score >= decision_threshold. That is not pedantry — ties on the cut are routine for hbos / ecod / copod, where ~80% of scores can sit exactly on it, so re-deriving the flag with >= at a fitted cutoff over-counts by that many rows. Read the is_anomaly column; do not recompute it.
  • Which optional columns you get. Five shapes, so check before you query: full population + operator override → is_anomaly, decision_threshold, threshold_source; full population at the fitted cutoff → the same three; full population whose artefact recorded no cutoff → flag + source, no decision_threshold (absent, never a fabricated 0.0); top-K sidecar under an override → all three; top-K sidecar with the engine's own label → flag + threshold_source='fitted', with decision_threshold only when the fitted artefact was still readable. A top-K sidecar with neither carries anomaly_score and rank alone.
  • is_anomaly can be NULL or missing. NULL = the run records no usable label for that row. The whole column is absent when the labelling could not be recovered (a top-K sidecar with no predicted_label); anomaly_score and rank are still exact, so rank by score instead — do not treat "not flagged" as "normal" on that shape.
  • Scope: the catalog scope is full or in_sample. Transductive detectors score only the in-sample fit set — don't claim scores for rows that weren't scored. A table built from the top-K sidecar holds only the top top_k_anomalies rows by score, so WHERE NOT is_anomaly is not a population query there either.

Standard joins

Fact ↔ input on the declared key column(s) (RULE 3); cluster_assignments ↔ clusters on cluster_id.

Example queries

Placeholders <input>, <cluster_assignments>, <clusters>, <anomalies> are the data-kb names; <key> is the input's declared key column (catalog role key).

Which customer is in which segment: 

SELECT a.<key>, a.cluster_id, c.label
FROM "<cluster_assignments>" a LEFT JOIN "<clusters>" c USING (cluster_id)
ORDER BY a.cluster_id;

Cluster sizes + a profiled feature: 

SELECT a.cluster_id, c.label, COUNT(*) AS n, AVG(s.tenure) AS avg_tenure
FROM "<cluster_assignments>" a
JOIN "<input>" s USING (<key>)
LEFT JOIN "<clusters>" c USING (cluster_id)
GROUP BY a.cluster_id, c.label ORDER BY n DESC;

Top-N anomalies with input context: 

SELECT s.*, an.anomaly_score
FROM "<anomalies>" an JOIN "<input>" s USING (<key>)
WHERE an.is_anomaly ORDER BY an.anomaly_score DESC LIMIT 50;

Where do flagged anomalies concentrate: 

SELECT s.region, COUNT(*) AS flagged
FROM "<anomalies>" an JOIN "<input>" s USING (<key>)
WHERE an.is_anomaly GROUP BY s.region ORDER BY flagged DESC;