Beyond the Black Box: Building a Schema Inference Engine Enterprises Can Audit

How deterministic gating, agentic reasoning, and adversarial review turn legacy column mapping from a months-long manual exercise into a governed, measurable pipeline.

In digital transformation, data migration and integration remain notoriously friction-heavy. For enterprise property & casualty (P&C) insurance carriers, migrating legacy policy administration systems or onboarding new commercial policy data requires mapping hundreds of raw, unstructured source columns to canonical target schemas.

Teams have historically had two options, and both are bad. Map by hand, and you spend months of senior engineering time on work that is mostly clerical. Automate with a first-generation AI tool, and you trade that cost for a subtler one: unverified semantic mappings that compile clean SQL, silently swap policy effective dates for claim occurrence dates, and corrupt financial transactions on ingest.

We built a production-grade Schema Inference Engine to take a third path. By combining deterministic rule engines, multi-tiered agentic workflows, and adversarial critic validation, we reduced schema retrieval misses and tool misuse by over 80%.

Designing for Auditability, Not Just Accuracy

An accuracy number on its own does not clear an enterprise governance review. Three constraints shaped the architecture before any of the accuracy work began.

• Precision over coverage. A mapping that the system declines costs an analyst ten minutes. A confident wrong mapping could require an entire remediation project.

  
  

• Traceable decisions. Every mapping must carry its lineage so the decision can be reconstructed months later by someone who was not in the room. That means prompt version, sampled values, critic verdict, etc.

  
  

• Compute spent where reasoning is needed. Most columns are not hard. Paying for model inference on all of them is waste, and at migration scale that waste becomes a line item someone will eventually challenge.

  
  

Those three constraints produced the five-stage pipeline.

Five Stages: Profile, Map, Critique, Generate, Evaluate

1. Profile

Nothing useful happens before the system knows what it is actually looking at. Every source table is read and normalized into the same shape: inferred types, null rates, distinct counts, sample values, and a set of pattern flags. Because profiling produces an identical structure regardless of origin, everything downstream is source-agnostic.

2. Map

Each profiled column is matched against the canonical target model, and this stage deliberately begins cheap. A deterministic rule engine scores every column on name similarity, type compatibility, and suffix patterns. Where those signals agree and the score clears 0.70, the column resolves immediately: no model, no cost. Many columns land here.

Columns that the deterministic pass cannot place escalate to a mapping agent: an LLM equipped with tools and the freedom to investigate. It pulls real values, checks them against a catalog describing what the data contains, weighs the evidence, and reasons toward an answer. It makes decisions not just based on what the column name tells it but what its data tells it as well.

The important design decision is that this split is not drawn by hand in advance. It emerges from the profile. Columns the rules can place never consume an expensive reasoning step; ambiguous ones always do. Effort goes where the difficulty is.

3. Critique

A separate adversarial reviewer re-examines the least certain mappings and challenges them independently. Its most useful job is contested targets: when two source columns compete for the same field and their confidences are nearly tied, the critic compares them head to head rather than reviewing each in isolation. Where a target legitimately admits two mappings, both are kept rather than one silently discarded.

4. Generate

Approved mappings need transformation logic, not just a destination. This stage produces the SQL expression for each column: the cast, the cents-to-dollars division, the date parse, the null handling, etc.

It is also where row-level shape matters. Knowing what each column means still leaves open what a row means. Which columns form the natural key, and which one carries recency, determines how a source’s revision history collapses into one clean record per entity. That inference runs on profile statistics alone: a near-unique, non-null, ID-shaped column is the key; a low-cardinality sequence integer beside it is the version. It feeds directly into the generated SQL.

5. Evaluate

Finally, the proposed mapping is scored against a ground truth catalog: a hand-curated answer key that records, for every column in a reference source, the correct canonical target, the correct transformation, and whether the column is one of the genuinely hard cases.

Scoring produces three things: precision and recall over mapped columns; a separate score restricted to the ambiguous columns to which reasoning is applied; and a single continuous loss value that penalizes confident errors more heavily than uncertain ones.

That loss is what makes the next section possible. Without a number to move, there is nothing to tune.

Two Failure Modes We Had to Engineer Around

Batch testing during early development surfaced two problems that prompt changes alone could not fix.

Cross-column anchoring

In batched evaluations, early column assignments biased later ones inside the same context window. We eliminated this in the mapping agent through strict single-column state isolation. For the batch critic, we implemented a per-column isolation fallback: batched checks run for cost efficiency, but any assignment showing malformed reasoning or low confidence instantly triggers an isolated single-column re-run.

Skipped verification

The agents frequently skipped value verification when a column’s name pattern looked familiar from earlier in the batch — exactly the shortcut the system exists to prevent. We made tool verification mandatory for every column in a batch before final acceptance, and randomized batch ordering to break position-dependent anchoring.

Both fixes cost tokens. Both were worth it. The failure they prevent is the confident wrong answer, which is the only kind of error that really matters here.

The Self-Tuning Loop

Because Stage 5 emits a continuous loss, the pipeline can be tuned programmatically rather than by hand-editing prompts and hoping. Our eval harness runs iterative optimization passes across three sets of variables.

• Rule weight calibration. Adjusting the confidence thresholds on the Stage 2 deterministic gate, which sets the boundary between free and expensive.

  
  

• Human-in-the-loop feedback. Every edit and override an analyst makes during review is captured and fed back as labeled training data and as dynamic few-shot context for edge cases.

  
  

• Tool and prompt refinement. Tuning system prompts and tool schemas to enforce verification behavior on low-confidence columns.

One dependency is worth naming plainly: this loop only runs where a ground truth catalog exists. Curating one for a new source is real work. The payoff is that the engine then adapts to a new domain portfolio with minimal developer intervention, but the answer key comes first.

Bringing the Engine into the IDE

An engine that lives behind an API does not change how analytics engineers actually work. We packaged the execution framework into a VS Code extension wired to workspace Model Context Protocol (MCP) servers, so the loop closes inside the editor rather than across a spreadsheet, a dbt project, and a database client.

An engineer connects to raw client files or Snowflake staging tables from the sidebar and profiles a source in one click, with distinct value distributions, type inferences, and pattern flags rendered beside their code. Triggering inference over a target model highlights mapped fields in real time; expanding the trace panel shows the agent’s reasoning steps, its value catalog lookups, and the critic’s objections.

Stage 4’s generated SQL renders side by side with the target dbt model, editable in place. Dry-run queries execute on Snowflake through MCP, verifying type compatibility and join constraints against real test data. And when an engineer overrides an edge-case mapping, that decision is logged as labeled ground truth automatically. The correction becomes training signal without anyone having to file it.

What This Changes for the Business

• Time to value. Onboarding cycles that ran in months are validated and executed in days, which changes what a carrier can realistically take on in a quarter.

  
  

• Auditability. Every mapping carries a full lineage trace: prompt version, sampled values, critic verification log. Risk and compliance functions get a reconstructable decision rather than an assertion.

  
  

• Cost control. Reserving model reasoning for genuinely ambiguous fields, with batched critic passes and isolated fallbacks, keeps token spend proportional to actual difficulty rather than to column count.

The goal is not to remove human judgment from schema mapping. It is to apply that judgment where it matters most. By using deterministic rules for clear cases and routing ambiguity through agentic reasoning, adversarial review, and auditable controls, enterprises can turn schema inference into a governed, measurable process. For P&C carriers managing complex legacy environments, that means accelerating migration without sacrificing the traceability and oversight the business requires.