Building a Python Pipeline That Self-Heals Broken Column Mappings With an LLM Step

The most tedious failure in any ingestion pipeline is also the dumbest one. A vendor renames cust_id to customer_id, or a partner's export tool decides that Revenue (USD) should now be Total Revenue, and a job that ran cleanly for eight months throws a KeyError at 3 a.m. Nothing is actually broken. The data is all there. Only the labels moved.

The reflex is to add another rename() dictionary and move on. But if you ingest from more than a handful of sources, those dictionaries become their own maintenance burden, and they only cover renames you have already seen. What I want instead is a pipeline that treats an unrecognized column as a problem to be reasoned about, not a crash. An LLM is genuinely good at that reasoning. The engineering challenge is letting it help without letting it quietly map gross_margin to net_margin because the strings look similar.

Layer the matching, don't lead with the model

The single most important design decision is that the LLM is the last resort, not the first pass. Most column drift is boring and resolves deterministically. Send everything to a model and you pay latency and money on columns you could have matched with string equality, and you introduce non-determinism where you did not need it.

I run resolution in three tiers against a canonical schema:

1. Exact and normalized match. Lowercase, strip whitespace, collapse separators. Total Revenue, total_revenue, and total-revenue all collapse to the same key. This alone resolves the majority of real-world drift.
2. Lexical similarity. Use rapidfuzz for token-sort ratio against known aliases. This catches cust_id versus customer_id and abbreviations, with a high threshold so you are not guessing.
3. Semantic match with an LLM. Only the columns that survive the first two tiers, plus a few sample values from each, go to the model.

Define the canonical schema as a real object, not a loose dict. Pydantic works well because it doubles as your validation layer downstream.

from pydantic import BaseModel

class OrdersSchema(BaseModel):
    order_id: str
    customer_id: str
    order_ts: datetime
    revenue_usd: float
    country: str

CANONICAL = {
    "customer_id": ["cust_id", "customerid", "client_id"],
    "revenue_usd": ["revenue", "total_revenue", "gross_revenue"],
    # ...
}

Give the model data, not just names

Column names lie. value, amount, and col_4 tell you nothing. Sample values tell you almost everything. When I build the LLM prompt for the residual columns, I include three to five example rows per unmatched column alongside the target schema and its descriptions.

The prompt is explicit about the job and the constraints:

You are mapping columns from a source file to a fixed target schema.

Target fields (name: description):
- customer_id: unique id for the buyer, alphanumeric
- revenue_usd: order revenue in US dollars, numeric
- order_ts: order timestamp, ISO 8601

Unmatched source columns with sample values:
- "buyer_ref": ["A22-9", "A22-10", "B01-4"]
- "amt": ["49.99", "12.00", "5.50"]

For each source column, return the target field it maps to,
or null if none fits. Return a confidence 0-1 and a one-line reason.
Do NOT map two source columns to the same target.
Return JSON only.

Use structured output. Every major model API in 2026 supports a JSON schema or tool-call constraint, and you should use it so you get parseable objects instead of prose you have to regex. Ask for a per-column confidence and a short reason. The reason is not decoration; it is what a human reviews when the confidence is borderline, and it is what you log.

The guardrails are the product

An LLM that maps columns is a demo. An LLM that maps columns safely is a pipeline. The difference is entirely in what you do with the model's answer.

Threshold and quarantine. I accept a mapping automatically only above a high confidence, say 0.9, and only when the value types agree with the target. Anything below goes to a quarantine table and a Slack message with the model's proposed mapping and its stated reason. The batch does not silently proceed on a guess about a revenue column.

Validate the values, not just the names. This is where Pydantic earns its place. A proposed mapping is only committed if a sample of the source column actually parses as the target type. If the model maps a free-text column to revenue_usd and the values will not cast to float, the mapping is rejected regardless of how confident the model was. Type validation is a cheap, deterministic check that catches the model's most dangerous mistakes.

Enforce uniqueness and completeness. Two source columns cannot map to one target. Every required target field must be filled or the batch fails loudly. These are set operations you run after the model returns, not things you trust it to honor.

Turn each decision into a permanent alias

The feature that makes this pay off is memory. When a human confirms a quarantined mapping, or when the model resolves a column at high confidence, write that alias back into your alias store. The next time buyer_ref shows up, it resolves at tier one for free. The LLM tier should get quieter over time as your alias catalog absorbs the real-world variations. If it stays busy, that is a signal your upstream sources are genuinely chaotic, which is itself worth knowing.

Practically, cache on the tuple of source column name plus a fingerprint of the sample values, so a renamed-but-identical column is a cache hit and you never pay for the same decision twice.

The trade-offs, honestly

This pattern is not free and it is not always right for the job.

• Non-determinism. Even at temperature zero, model outputs can shift across versions. Pin the model version, log every decision with the model id, and treat the alias store as the source of truth so that once a mapping is confirmed, the model is out of the loop for that column forever.
• It hides drift you might want to see. A pipeline that self-heals quietly can mask an upstream partner whose exports are deteriorating. Keep a dashboard of how often the LLM tier fires per source. Self-healing should reduce pages, not reduce your awareness.
• Cost and latency scale with column count, not row count. This is good news. You call the model once per batch for a handful of unmatched columns, not per row. On a wide file that is still cents. On a pipeline with thousands of sources it adds up, which is another reason the deterministic tiers matter.
• It is wrong for adversarial or regulated inputs. If a mismapped column has real financial or compliance consequences, raise the confidence bar to the point where nearly everything goes to human review, or skip the LLM tier for those fields entirely. A model's guess about which column is the tax field is not a control you want to explain to an auditor.

The mental model that keeps this healthy: the LLM is a very good junior analyst who proposes mappings and explains its reasoning, and your deterministic code is the reviewer who checks types, enforces constraints, and keeps the record. Give the model the boring pattern-matching and keep the authority in code. Do that and column drift stops being a 3 a.m. problem and becomes a line item on a review queue you clear over coffee.