Product wants 200,000 support tickets sorted into reasons (billing, bug, how-to, account, cancellation) so they can chart the trend. Nobody has labelled them. How would you do it in Databricks SQL, and how do you know the labels are right?

Use ai_classify with the 5 labels and a description for each, run it once over the tickets and store the result in a table (the function cannot be used inside a view). Before trusting the chart, label a random sample of 200 by hand, compare, and fix the label descriptions where the two disagree. Then classify only new tickets each day.

1. Write labels a stranger could apply

The descriptions do most of the work. Vague labels such as "account" and "billing" overlap:

'{
  "billing": "Charges, invoices, refunds, failed payments",
  "bug": "Something in the product is broken or behaves wrongly",
  "how_to": "Asks how to do something the product already supports",
  "account": "Login, password, profile, team members, permissions",
  "cancellation": "Wants to cancel, downgrade or close the account"
}'

2. Try it on a sample first

SELECT ticket_id, body,
       ai_classify(body, :labels,
         MAP('version', '2.1',
             'instructions', 'Support tickets from a B2B invoicing app. Choose the main reason.',
             'enableConfidenceScores', 'true')) AS result
FROM support.tickets
TABLESAMPLE (200 ROWS);

In version 2.1 the result is a VARIANT: the label is result:response[0].value, the confidence result:response[0].confidence_score, and result:error_message is null on success. A ticket that cannot be classified returns NULL; count those rather than dropping them silently.

3. Check against people

Have 2 people label the same 200 tickets without seeing the model's answer. Compare:

• Agreement per label: if "account" and "billing" are often swapped, rewrite those 2 descriptions and run the sample again.
• Low confidence: read the tickets under, say, 0.6; they often show a missing label, such as "feature request". Add it rather than forcing tickets into the closest one.
• Where the 2 people disagree with each other, the label itself is unclear, and the model cannot do better than they do.

Report the agreement rate with the chart, so readers know how far to trust a 2-point change.

4. Run it for real and keep it cheap

CREATE TABLE support.ticket_reasons AS
SELECT ticket_id, created_at,
       ai_classify(body, :labels, MAP('version', '2.1', 'instructions', '...')) AS result
FROM support.tickets;

Then classify only tickets not yet in ticket_reasons on a daily schedule. Each call costs model inference on top of the warehouse, so re-classifying 200,000 tickets for every chart refresh wastes money. Pin the version: version 2.1 changed the output shape from 2.0, and a query reading response[0] as a string would break.

5. When to choose something else

• Many tickets already labelled by people: a classifier trained on them may be cheaper and more accurate.
• Several reasons per ticket: set multilabel to true and chart each reason, knowing totals no longer add up to 100%.

What interviewers want: clear label descriptions, a sample checked against human labels before rollout, reading confidence and NULLs, results stored in a table and computed once, version pinning, and saying how accurate the chart is.