Standardize Messy Columns with Classify-Don't-Generate

Tier: Foundation · Workflow area: Unstructured data processing

This playbook uses an LLM to close the remaining gap — but with one non-negotiable rule: the model classifies into a closed reference list, it never invents values. Generated strings are how you end up with a 201st country.

When to reach for this

• •
  A dimension column has hundreds of variant spellings of what should be a few dozen canonical values.
• •
  You have (or can build) a reference list of allowed canonical values — the target vocabulary exists.
• •
  The regex/fuzzy-match baseline has plateaued and the residue is genuinely ambiguous ("Alphabet" vs "Google LLC").

Skip the LLM when a deterministic mapping table already covers the distinct values — an LLM call per row for a solved lookup is pure cost.

The workflow

1. 1.

   Profile before you prompt. Pull the distinct values and their frequencies. You standardize distinct values, not rows — a 50M-row table often has only 3,000 distinct company strings.

   Copy

   SELECT company_name_raw, COUNT(*) AS rows
   FROM raw.customers
   GROUP BY 1 ORDER BY 2 DESC;
   

2. 2.

   Run the deterministic baseline first. Exact match, casefold, punctuation-strip, then fuzzy match against the reference list. Record the fix-rate — the LLM only sees survivors, and the baseline is the number the LLM must beat to justify its cost.

3. 3.

   Define the schema with the reference list embedded as allowed values. Confidence and an explicit "no match" escape hatch are part of the contract:

   Copy

   from pydantic import BaseModel
   from typing import Optional
   
   class Standardized(BaseModel):
       raw_value: str
       canonical_value: Optional[str]  # must be from the reference list, or None
       confidence: float               # 0.0 - 1.0
       reasoning: str
   

4. 4.

   Classify the residue in batches, as a loop over the API. Ground every request in the reference list so the model maps, never invents:

   Copy

   import anthropic
   
   client = anthropic.Anthropic()
   REFERENCE = load_reference_list()  # canonical values, e.g. from the dim table
   
   results = []
   for value in unresolved_values:
       response = client.messages.parse(
           model="claude-opus-4-8",
           max_tokens=1024,
           thinking={"type": "adaptive"},
           messages=[{
               "role": "user",
               "content": (
                   "Map this raw company name to ONE canonical value from the "
                   f"reference list below, or null if none applies.\n"
                   f"Raw value: {value}\n"
                   f"Reference list: {REFERENCE}\n"
                   "Never output a value that is not in the list."
               ),
           }],
           output_format=Standardized,
       )
       results.append(response.parsed_output)
   

   Expected output per value: a Standardized object like {"raw_value": "I.B.M. Corp", "canonical_value": "IBM", "confidence": 0.97, ...}.

5. 5.

   Apply the validation gate — schema, allowed values, confidence, review queue. The SDK already enforced the schema; you still enforce membership and confidence:

   Copy

   accepted, review_queue = [], []
   for r in results:
       if r.canonical_value is not None and r.canonical_value not in REFERENCE:
           review_queue.append(r)          # invented value — hard reject
       elif r.confidence >= 0.85 and r.canonical_value:
           accepted.append(r)
       else:
           review_queue.append(r)          # low confidence or no match
   

   Accepted rows land in a value_mapping table; the review queue goes to a human, and every human decision is appended to the mapping table so it never has to be asked again.

6. 6.

   Backfill through the mapping table, not per-row calls. Join raw → value_mapping → canonical in SQL. The LLM ran once per distinct value; the 50M-row update is a plain join.

7. 7.

   Sample-audit before trusting the fix-rate. Hand-check 100 random accepted mappings. If more than 2–3 are wrong, raise the confidence threshold and rerun the audit before backfilling.

Verify before you trust it

• Membership checkSELECT COUNT(*) of mapped values not present in the reference dimension must be zero.
• Audit sample≥97% accuracy on 100 randomly sampled accepted mappings, checked by a human.
• Fix-rate vs baselinereport "deterministic fixed X%, LLM added Y%, Z% sent to review" — if Y is small, the LLM step may not be worth keeping.
• Idempotencyrerunning the pipeline on the same input must produce the same mapping table (the mapping table itself guarantees this if you only ever append).

Pitfalls

• Letting the model generate instead of classify. Without the embedded reference list, you will get well-formatted, confident, invented canonical values that pass schema validation.
• Standardizing rows instead of distinct values. Per-row calls on a large table cost 1000x more than per-distinct-value calls for the same outcome.
• Trusting self-reported confidence blindly. Calibrate it: plot audit accuracy by confidence band once, then set the threshold where accuracy meets your bar.
• A reference list that is itself dirty. If the dim table has duplicates ("IBM" and "IBM Corp" both canonical), the model's coin-flips are your fault — dedupe the target vocabulary first.
• Silent re-runs that flip mappings. Append-only mapping tables with a decision source (baseline, llm, human) keep the audit trail and stop churn.

Tools

• Anthropic/OpenAI structured outputsschema-enforced classification; the loop above works with either provider.
• Snowflake Cortex AISQL (AI_CLASSIFY)the same classify-don't-generate pattern without leaving SQL, when data can't move.
• Great Expectationsset-membership and null-rate expectations on the standardized column as a permanent regression test.

Client-safe variant

On client engagements, treat raw values as potentially sensitive: company and person names can be client-confidential even when the schema is not. Get written approval for which columns may be sent to an external API, and send only the distinct values, never surrounding row context. If no external API is approved, run the identical workflow against a locally hosted model via vLLM or Ollama — the reference-list prompt, validation gate, and audit sample are unchanged — or use the client's own warehouse-native AI functions (Cortex/BigQuery) so data never leaves their perimeter. Deliver the mapping table plus the audit report, and document the fix-rate versus the deterministic baseline so the client can judge the LLM step's ongoing value.