Snowflake Cortex AI: SQL Tutorial with Evaluation

SELECT COUNT(*) AS evaluated_rows,
       COALESCE(COUNT_IF(valid_label),0) AS valid_outputs,
       COALESCE(COUNT_IF(correct_label),0) AS correct_outputs,
       ROUND(100.0*COALESCE(COUNT_IF(correct_label),0)/NULLIF(COUNT(*),0),2)
         AS accuracy_pct
FROM ROUTING_EVALUATION;

SELECT reference_label, predicted_label, COUNT(*) AS cases
FROM ROUTING_EVALUATION
GROUP BY reference_label, predicted_label
ORDER BY reference_label, predicted_label;

SELECT * FROM ROUTING_EVALUATION
WHERE NOT valid_label OR NOT correct_label;

Use the six-case table for debugging, not a production accuracy claim. A score of six out of six on a handcrafted set does not establish how the system behaves on new customers, languages or rare failures. Keep a separate held-out dataset for final evaluation, and do not repeatedly rewrite the prompt against that held-out set.

1. Coverage: did every expected ticket receive exactly one saved output?
2. Format: was the output an allowed label?
3. Correctness: did it agree with an adjudicated reference?
4. Risk: which category confusions cause the greatest routing harm?
5. Review: which records require a human decision?

A useful extension is per-category precision and recall. For billing, a false positive sends an unrelated issue to the payments team; a false negative misses a payment problem. Count both before deciding that overall accuracy is sufficient. Keep model self-reported confidence separate from a probability calibrated against real outcomes.

7.1 Build per-category precision and recall from saved results

Overall accuracy can conceal a weak minority category. Precision asks how often a predicted category is correct; recall asks how many reference cases in that category were found. The following query counts raw evaluation rows without making another model call.

WITH labels AS (
 SELECT column1 AS label FROM VALUES ('billing'),('technical'),('account'),('other')
), counts AS (
 SELECT l.label,
  COALESCE(COUNT_IF(e.reference_label=l.label),0) AS actual_cases,
  COALESCE(COUNT_IF(e.predicted_label=l.label),0) AS predicted_cases,
  COALESCE(COUNT_IF(e.reference_label=l.label AND e.predicted_label=l.label),0) AS true_positives
 FROM labels l CROSS JOIN ROUTING_EVALUATION e GROUP BY l.label
)
SELECT *,ROUND(true_positives/NULLIF(predicted_cases,0),3) AS precision,
 ROUND(true_positives/NULLIF(actual_cases,0),3) AS recall
FROM counts ORDER BY label;

If the model predicts no cases for a label, precision is NULL rather than evidence of perfect performance. Report the sample count next to every metric. Do not average percentages from unequal batches without accounting for their denominators.

7.2 Test the review queue before trusting it

Create a temporary copy with one intentionally invalid result. This checks your validation logic without paying for another inference run or changing the saved predictions.

CREATE TEMP TABLE ROUTING_VALIDATION_DRILL AS
SELECT ticket_id,raw_prediction FROM ROUTING_PREDICTIONS;
UPDATE ROUTING_VALIDATION_DRILL SET raw_prediction='billing because it is an invoice'
WHERE ticket_id=1;
SELECT ticket_id,raw_prediction FROM ROUTING_VALIDATION_DRILL
WHERE raw_prediction IS NULL
 OR LOWER(TRIM(raw_prediction)) NOT IN ('billing','technical','account','other');

Ticket 1 must appear in the invalid-output results. Other rows can appear if their actual model outputs are also invalid. Keep format failures distinct from valid-but-incorrect classifications: they need different fixes. Define a real review action—who checks the ticket, what evidence they see and how the final label is recorded—before connecting routing to a business workflow.

7.3 Check zero-match metrics before interpreting accuracy

Use COALESCE around COUNT_IF so a non-empty all-wrong batch reports zero correct outputs and 0% accuracy. A label with predicted cases but zero true positives has precision 0. If no cases were predicted for a label, precision is undefined; retain NULL for that zero denominator. See the Snowflake COUNT_IF reference.

WITH cases(correct_label) AS (SELECT * FROM VALUES (FALSE),(FALSE))
SELECT COALESCE(COUNT_IF(correct_label),0) AS correct_outputs,
ROUND(100.0*COALESCE(COUNT_IF(correct_label),0)/NULLIF(COUNT(*),0),2) AS accuracy_pct
FROM cases;
-- Expected: correct_outputs 0; accuracy_pct 0.00.
SELECT 0.0/NULLIF(2,0) AS zero_precision,
       0.0/NULLIF(0,0) AS undefined_precision;
-- Expected: 0.0 and NULL.