Classify and Enrich Rows with In-Warehouse AI Functions

Tier: Core · Workflow area: Unstructured data processing

That convenience is also the trap: a function you can call in one line can burn a real budget across a hundred-million-row backfill, and a misclassification rate you never measured becomes a permanent feature of the table. This playbook is the disciplined version: sample-audit first, cost-model second, backfill last.

When to reach for this

• •
  Text columns already live in the warehouse and need classification, sentiment, translation, or short summarization.
• •
  The team is SQL-first and standing up an external LLM pipeline would be organizational overhead.
• •
  Governance prefers data never leaving the warehouse perimeter.

Reach for an external API pipeline instead when you need a custom Pydantic-grade schema contract, multi-step prompting, or a model the warehouse doesn't offer.

The workflow

1. 1.

   Prototype on 100 rows, not the table. Every warehouse AI function bills per row/token — develop on a sample:

   Copy

   -- Snowflake Cortex example: multilabel ticket classification
   SELECT
     ticket_id,
     ticket_text,
     AI_CLASSIFY(
       ticket_text,
       ['billing', 'bug', 'feature_request', 'account_access', 'other']
     ) AS category
   FROM support.tickets
   LIMIT 100;
   

   Expected output: a category column with labels drawn only from your list. The closed label list is your allowed-values guardrail — never use a free-generation function where a classify function exists.

2. 2.

   Sample-audit the misclassification rate before scaling. Export 200 stratified rows (make sure rare labels are represented), have a human label them, and compute per-label precision/recall:

   Copy

   CREATE TABLE tmp.audit_sample AS
   SELECT * FROM classified_sample
   QUALIFY ROW_NUMBER() OVER (PARTITION BY category ORDER BY RANDOM()) <= 40;
   

   If overall accuracy is below the bar (agree it with consumers first — 90% is a common floor for routing use cases), iterate: rename ambiguous labels, add label descriptions, or split "other" into real categories. Label wording is your prompt here.

3. 3.

   Build the cost model per million rows. Run the function on a known row count, read the warehouse's usage/credit view, and extrapolate:

   Copy

   -- Snowflake: credits consumed by AI functions
   SELECT * FROM SNOWFLAKE.ACCOUNT_USAGE.CORTEX_FUNCTIONS_USAGE_HISTORY
   ORDER BY start_time DESC LIMIT 10;
   

   Produce a one-line answer: "AI_CLASSIFY on avg-300-token tickets ≈ $X per 1M rows." Compare against the external-API alternative (tokens × batch pricing) — warehouse-native usually wins on simplicity, not always on unit cost.

4. 4.

   Gate the backfill. Wrap the classification in a pipeline (dbt model or task) that enforces the validation-gate pattern even in SQL: label must be in the allowed set (defensive check even though the function promises it), null/failure rows routed to a review table, and a low-confidence band if the function returns scores:

   Copy

   INSERT INTO staging.ticket_categories
   SELECT ticket_id, category, classified_at
   FROM classified
   WHERE category IN ('billing','bug','feature_request','account_access','other');
   
   INSERT INTO review.ticket_categories_failed
   SELECT * FROM classified
   WHERE category IS NULL
      OR category NOT IN ('billing','bug','feature_request','account_access','other');
   

5. 5.

   Backfill incrementally with a cap. Chunk by date or ID range, with a hard stop after each chunk to check spend and label distribution. Never fire a single UPDATE over the full history.

6. 6.

   Monitor label drift. Schedule a daily check on the label distribution of new rows; a sudden jump in one label usually means upstream text changed (new ticket form, new language) — not that users suddenly love billing.

Verify before you trust it

• •
  Audit sample accuracy ≥ your agreed bar, with per-label precision/recall (aggregate accuracy hides a broken rare label).
• •
  Cost model validated: actual spend on the first backfill chunk within ~20% of the projection before you run the rest.
• •
  Allowed-values check returns zero violations on the landed table.
• •
  Label distribution of the backfill roughly matches the audit sample's — a big divergence means the sample wasn't representative.

Pitfalls

• Backfilling millions of rows before auditing. The most expensive way to discover a 20% misclassification rate is at full scale, twice (once to run it, once to redo it).
• Free-generation where classification fits.AI_COMPLETE-style functions returning open text create an unbounded label set; use the classify/filter-shaped functions with a closed list.
• Ignoring per-label failure. 95% overall accuracy can mean 40% on the one label your escalation flow depends on.
• Re-running on every dbt build. AI function calls in a model that fully refreshes daily re-bills the whole table daily; make the enrichment incremental on new/changed rows only.
• No failure routing. Warehouse AI functions can return nulls or errors on odd inputs (empty text, wrong language); without a review table those rows silently vanish from the enrichment.

Tools

• Snowflake Cortex AISQLAI_CLASSIFY, AI_FILTER, AI_COMPLETE; usage visible in account-usage views for the cost model.
• BigQuery AI functionsAI.GENERATE family / AI.GENERATE_TABLE for schema-shaped output; sandbox tier works for practice.
• Great Expectationsallowed-set and distribution expectations on the enrichment table as a standing regression test.

Client-safe variant

Warehouse-native AI functions are often the easiest option to clear with a client, because data never leaves their warehouse's security boundary — but confirm rather than assume: some clients' agreements with their warehouse vendor exclude AI features, and some regions route AI function calls to endpoints in other geographies. Get the client's platform team to confirm which AI functions are enabled and where inference runs. Run everything under a client-owned role with a dedicated warehouse/reservation so AI spend is separately meterable, present the cost model and audit results before requesting backfill approval, and leave behind the drift monitor plus the review-table process so the enrichment stays owned after you roll off.