The problem
Enterprise datasets are rarely clean. The same field shows up a dozen different ways — states written as abbreviations, nicknames, or trailing parentheticals; durations mixed between days, months, and years. Multiply that across thousands of rows and dozens of columns, and "just clean it in Excel" turns into days of manual, error-prone work every time a new file lands.
Live demo · fabricated sample data
Messy input → canonical output
| column | raw value | normalized | method |
|---|---|---|---|
| state | ny | — | … |
| state | California (CA) | — | … |
| state | TX - Lone Star State | — | … |
| state | FL / Florida Keys | — | … |
| contract_duration | 180 days | — | … |
| contract_duration | 2 yrs | — | … |
| contract_duration | 5-year contract | — | … |
| contract_duration | 24 months | — | … |
Hybrid resolution: fast pattern rules (regex / basic type parsing) run first and handle the majority of values for near-zero cost. Whatever is left unmapped is sent to an LLM along with the target canonical labels — its answer is applied to the dataset and cached back into the rule set so the same value never needs another LLM call.
A hybrid approach, not a single model call
Sending every raw value straight to an LLM works, but it's slow and expensive at scale, and it re-solves the same problem on every run. The pipeline instead treats cleanup as a two-pass process, with the second pass making the first pass smarter over time:
- Pass one — pattern rules. Each column has a small rules file of regexes and keyword matches mapped to canonical labels (plus generic fallbacks for dates and currency). This pass is deterministic, cheap, and handles the bulk of values instantly.
- Pass two — LLM classification. Whatever pass one can't confidently resolve gets batched up — the unique unmapped values plus the target canonical labels — and sent to an LLM for classification. The model returns a value-to-category mapping, which is applied back across every matching row.
- Learn and cache. Every mapping the LLM resolves gets written back into that column's rules file as a new pattern. The next run — even on a completely different file — catches those values in pass one, for free.
Every normalized cell is tagged with how it was resolved — regex, basic, or llm —
so downstream QA can see at a glance how much of a dataset was handled deterministically
versus classified, and spot-check the LLM-touched rows first.
The tech
The pipeline is built in Python on Polars for fast, memory-efficient loading and transformation of large Excel/CSV files. LLM classification runs through both the Anthropic and OpenAI SDKs, with prompt caching on the shared instruction and canonical-label context so repeated calls across many columns stay cheap. It's exposed three ways depending on who — or what — needs it: a FastAPI backend for the web frontend, a Click-based CLI for batch runs, and an MCP server so an AI agent can invoke normalization directly as a tool.
Why it matters
Manual data cleanup doesn't scale, and pure LLM-per-value pipelines don't stay cheap. This hybrid design gets both: pattern rules handle the routine cases for free, the LLM is reserved for genuinely ambiguous values, and every LLM decision permanently improves the rule set. Over repeated runs, the ratio of rule-based to LLM-based resolutions climbs — accuracy stays high, and cost trends toward zero for data shapes the system has already seen.