Local ticket NLP with quackformers + duckboost

Support tickets usually mix free-form text with a few structured columns. This vignette is an example of pairing duckboost with other DuckDB extensions to do that kind of NLP work entirely inside DuckDB — local embeddings, SQL labeling, and GBDT training/scoring — without a separate Python/ML runtime or hosted model API.

Step Extension Role
Embed subject + body Community quackformers embed() ≈ all-MiniLM-L6-v2 (384-d)
Optional ANN index Core vss HNSW over FLOAT[384] when the corpus is large
Train / score duckboost Binary or multiclass GBDT on numeric features from those embeddings (+ any other columns)

duckboost only accepts DOUBLE[] features. Text enters the model through embeddings (and optional lexical extras), not as raw VARCHAR.

The worked example routes tickets between two clearly different queues — Technical Support vs Billing and Payments — using a public support-ticket corpus. The same pattern extends to more labels once your taxonomy and prototypes are solid.

NoteDataset

Examples use a balanced English sample (400 tickets per queue) from Tobias Bueck’s Customer Support Tickets corpus (also on Kaggle). The Hugging Face mirror is licensed CC BY-NC 4.0 — non-commercial use with attribution. See Credits.

WarningDocs execution on DuckDB 2.0.0

This repository builds against DuckDB 2.0.0. Community quackformers may lag on the 2.0 CDN while clients catch up, so the numbers on this page use the same MiniLM-L6-v2 model offline via a cached Parquet sample. The SQL in the fences is the recipe for a DuckDB build where INSTALL quackformers FROM community works. Replace the precomputed emb column with embed(text)::FLOAT[384] when you run it yourself.

1. Load tickets from a public URL

On a released DuckDB with httpfs / community autoload:

INSTALL httpfs; LOAD httpfs;
INSTALL quackformers FROM community; LOAD quackformers;

CREATE TABLE tickets AS
SELECT
  row_number() OVER () AS ticket_id,
  subject,
  body,
  queue,
  priority,
  language,
  type,
  concat_ws(E'\n', subject, body) AS text
FROM read_csv(
  'https://huggingface.co/datasets/Tobi-Bueck/customer-support-tickets/resolve/main/aa_dataset-tickets-multi-lang-5-2-50-version.csv'
)
WHERE lower(language) = 'en'
  AND queue IN ('Technical Support', 'Billing and Payments')
  AND subject IS NOT NULL
  AND body IS NOT NULL;
SELECT
  queue,
  count(*) AS n,
  round(avg(len(text)), 0) AS avg_chars
FROM tickets_emb
GROUP BY 1
ORDER BY 1;
┌──────────────────────┬─────┬───────────┐
│        queue         │  n  │ avg_chars │
├──────────────────────┼─────┼───────────┤
│ Billing and Payments │ 400 │ 395.0     │
│ Technical Support    │ 400 │ 400.0     │
└──────────────────────┴─────┴───────────┘

2. Local embeddings with quackformers

CREATE TABLE tickets_emb AS
SELECT
  ticket_id, subject, body, queue, priority, language, type, text,
  embed(text)::FLOAT[384] AS emb
FROM tickets;

embed() returns a 384-d float vector (MiniLM). For a larger in-extension model, quackformers also exposes embed_jina() (768-d). Pair with core vss if you want an HNSW index over emb for nearest-ticket search at larger scale; this page uses direct cosine similarity.

3. Deterministic labels from a fixed label set

Keep a small catalog of labels you control. Embed each prototype once with the same model, then assign every ticket the prototype with the highest cosine similarity. Same prototypes + same embedder ⇒ same labels.

SELECT queue, prototype
FROM label_prototypes
ORDER BY queue;
┌──────────────────────┬───────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────┐
│        queue         │                                                             prototype                                                             │
├──────────────────────┼───────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────┤
│ Billing and Payments │ Invoices, charges, refunds, payment failures, billing disputes, subscriptions, receipts, and account balance questions.           │
│ Technical Support    │ Technical issues with software applications, bugs, crashes, errors, configuration problems, failed features, and troubleshooting. │
└──────────────────────┴───────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────┘
CREATE OR REPLACE TABLE ticket_labels AS
SELECT
  t.ticket_id,
  t.queue AS gold_queue,
  t.priority,
  arg_max(p.queue, array_cosine_similarity(t.emb, p.emb)) AS assigned_queue,
  max(array_cosine_similarity(t.emb, p.emb)) AS best_sim,
  [
    max(array_cosine_similarity(t.emb, p.emb)) FILTER (WHERE p.queue = 'Billing and Payments'),
    max(array_cosine_similarity(t.emb, p.emb)) FILTER (WHERE p.queue = 'Technical Support'),
    len(t.text)::DOUBLE,
    (lower(t.priority) = 'high')::DOUBLE,
    (lower(t.priority) = 'medium')::DOUBLE
  ]::DOUBLE[] AS sim_features,
  (list_transform(t.emb, lambda x: x::DOUBLE))::DOUBLE[] AS emb_features
FROM tickets_emb t
CROSS JOIN label_prototypes p
GROUP BY t.ticket_id, t.queue, t.priority, t.text, t.emb;

SELECT
  round(avg((assigned_queue = gold_queue)::INT), 3) AS prototype_match_rate,
  round(avg(best_sim), 3) AS mean_best_cosine
FROM ticket_labels;
┌──────────────────────┬──────────────────┐
│ prototype_match_rate │ mean_best_cosine │
├──────────────────────┼──────────────────┤
│ 0.833                │ 0.326            │
└──────────────────────┴──────────────────┘

Prototype matching is a deterministic baseline / weak labeler. When agents already set queue (as in this corpus), use gold labels for supervised training and keep Stage 3 for unlabeled tickets or low-confidence review (for example a small margin between the top two cosine scores).

4. Train duckboost on embedding features

Map the two queues to {0, 1}, hold out a stratified test split, and fit a binary native model on the full 384-d embedding (plus you can concatenate structured columns). Compact alternative: train only on the prototype cosine scores in sim_features.

CREATE OR REPLACE TABLE ticket_labels AS
SELECT
  t.ticket_id,
  t.queue AS gold_queue,
  (t.queue = 'Technical Support')::DOUBLE AS y,
  (list_transform(t.emb, lambda x: x::DOUBLE))::DOUBLE[] AS features
FROM tickets_emb t;

CREATE OR REPLACE TABLE ticket_model_rows AS
SELECT l.*, s.split
FROM ticket_labels l
JOIN (
  SELECT ticket_id, split
  FROM duckboost_initial_split('ticket_labels', prop := 0.75, strata := gold_queue, seed := 7)
) s USING (ticket_id);

CREATE TABLE models AS
SELECT duckboost_train(
  y, features,
  MAP {
    'backend': 'native',
    'task': 'binary',
    'n_estimators': '80',
    'max_depth': '4',
    'learning_rate': '0.1',
    'colsample_bytree': '0.5',
    'seed': '7'
  }
) AS model
FROM ticket_model_rows
WHERE split = 'train';

SELECT
  round(duckboost_evaluate_agg(model, y, features, MAP {'metric': 'accuracy'}), 3) AS test_accuracy,
  round(duckboost_evaluate_agg(model, y, features, MAP {'metric': 'logloss'}), 3) AS test_logloss
FROM models, ticket_model_rows
WHERE split = 'test';
┌───────────────┬──────────────┐
│ test_accuracy │ test_logloss │
├───────────────┼──────────────┤
│ 0.83          │ 0.386        │
└───────────────┴──────────────┘

A few scored rows with class probabilities:

SELECT
  gold_queue,
  round(duckboost_predict(model, features), 0) AS pred_tech,
  round(duckboost_predict_proba(model, features)[2], 3) AS p_technical
FROM models, ticket_model_rows
WHERE split = 'test'
ORDER BY ticket_id
LIMIT 8;
┌──────────────────────┬───────────┬─────────────┐
│      gold_queue      │ pred_tech │ p_technical │
├──────────────────────┼───────────┼─────────────┤
│ Technical Support    │ 1.0       │ 0.956       │
│ Technical Support    │ 1.0       │ 0.983       │
│ Billing and Payments │ 0.0       │ 0.001       │
│ Billing and Payments │ 0.0       │ 0.011       │
│ Billing and Payments │ 1.0       │ 0.535       │
│ Technical Support    │ 0.0       │ 0.299       │
│ Billing and Payments │ 1.0       │ 0.711       │
│ Billing and Payments │ 0.0       │ 0.005       │
└──────────────────────┴───────────┴─────────────┘

5. Scoring and export

Predict with duckboost_predict / duckboost_predict_proba, or emit plain SQL with duckboost_to_sql for inference sessions that do not load duckboost. Rebuilding features still needs quackformers (or a stored emb column) wherever you score new tickets.

Adapting this to your tickets

  1. Keep a small label_prototypes table for your taxonomy (queues, priority bands, issue types, …).
  2. Embed ticket text with quackformers (embed or embed_jina).
  3. Assign weak labels from prototype cosine when gold labels are missing; prefer gold labels when agents already set them.
  4. Feed emb::DOUBLE[] and any structured columns into duckboost_train.
  5. Optionally index embeddings with core vss for nearest-neighbor triage at larger scale.
  6. For many overlapping queues, start with a coarse split (as here) or hierarchical routing before a flat multiclass over dozens of labels.

Credits