Column mapping

Mapping inconsistent schemas at scale

Automated column mapping for 2,000 files per customer each month. 73% fewer mapping errors in evaluation.

Policy NumberIDPolicy Expiration95.8%31New-RenewalTInsuredTStreet59.0%TCityTStateTZip#Class Code#Line of Business30.9%TPolicy No97.1%IDEffective Date86.6%31Expiry Date92.0%31New-RenewalTInsuredTAddress82.5%TExtra Field 120.0%TStateQZIP Code79.3%QProduct83.8%TClass Code#

Column mapping

Key points

  • Created a new mapping interaction model from users’ spreadsheet workarounds
  • Achieved 100% adoption in business-critical workflows
  • Automated column mapping for 2,000 files per month
  • 2026 upgrade delivered 73% fewer semantic mapping errors in testing

Column mapping

Contents

  1. 01Problem
  2. 02Approach
  3. 03Design decisions
  4. 04Automation
  5. 05Evolution
  6. 06Outcomes
  7. 07Reflections

Column mapping

Project details

Role

Research, origination of solution, detailed design, testing, revision

Team

CTO, Product Manager, Solutions Architect, engineers

Timeframe

2 months / v1 release
2 months / upgrade

Domain

Wholesale insurance data management

User profiles

Data analyst: domain expert with deep knowledge of the data
Technical skill level: competent Excel user

01 / Problem

Hundreds of schemas, no common language

ReportsPartner schemasCommon schemaDownstream useData warehouseModelsPartners

03 / Design decisions

Vertically stacked fields, aligned to a common master

  1. 01Aligned schemas
  2. 02Combined tab/progress bar
  3. 03Completed interface
Traditional approach: Connecting lines
A better approach: Aligned schemas
Collapsing the mapped and unmapped items into tabs doubled the number of schemas which could fit in the view.

03 / Design decisions

Combined tab/progress bar

Open the interactive column mapping detail.

03 / Design decisions

Completed interface

04 / Automation

Suggestions users could trust

Automap previews bulk mappings above a user-set confidence threshold before they are applied.

04 / Automation

Making uncertainty visible

05 / Evolution

Evolution

  1. 01From column names to column contents
  2. 02Evaluating the new mapping model

05 / Evolution

From column names to column contents

In 2026 we initiated a project to identify the type of data in each column, then profile the data inside it, creating a unique data fingerprint for each field.

05 / Evolution

Evaluating the new mapping model

73% fewer semantic mapping errors

Semantic mapping errors across 207 columns · lower is better

Original model
11
New model
3
Estimated counts from an LLM-assisted review of screenshots across four schema variations mapped to Lloyd’s v5.2 (26–27 August 2026).

06 / Outcomes

Less manual work. Better mappings.

Column mapping became a core part of clients’ workflows, with the recent model upgrade improving mapping quality.

  • Consistently cited as a reason for choosing Quantemplate in sales win analysis.
  • Automated column mapping for an average of 2,000 files per customer each month. After initial setup, mappings only need attention when incoming schemas change.
  • 73% fewer mapping errors in evaluation of the upgraded model.

06 / Outcomes

Less manual work. Better mappings.

I cannot adequately express how receptive Tom was to our suggestions for improvement. He would listen, ask questions and make our wishlist items reality; even enhancing our suggestions. Lee Ann Dewell Lang, Quantemplate user-client, James River Insurance

07 / Reflections

Research beyond the current client base

We initially shaped parts of the workflow around clients who wanted to build a master schema from their source files. Later clients, who became the larger cohort, wanted to map to an existing standard schema. Broader market research could have surfaced this need earlier and helped us design for both workflows from the outset.

07 / Reflections

Balance stability with change

Applying a schema from a reference Data Dictionary creates an independent copy. This protects downstream operations from unexpected changes, but makes migration harder when the reference schema evolves.

I’d explore keeping a reference to the source schema while giving users control over when to adopt updates. Stable column IDs, rather than names, could help preserve downstream operations through renaming, though this would need further engineering investigation.

Evidence

Learning from the Excel workarounds
Ship and learn

Completed interface

As the sole designer supporting 11 developers, I prioritised a working implementation: full interactive prototyping this data-heavy interaction would have taken comparable effort to building it. We tested internally, then refined it through customer use, adding search, populate cells and enforce schema functions. As our customer base shifted, we adapted the workflow from assembling a master schema field by field to using an existing dataset.
Design validation

Making uncertainty visible

The yellow gradient performed best among the colour options we tested, including in colour-blindness simulations and testing with a user with colour vision deficiency.
Design exploration

From column names to column contents

I did a full audit of the current mapping behaviour, identified shortcomings in our approach and evaluated different paths for improvement. Together with a domain expert, I modelled different methodologies for weighting data fingerprints and prior usage.

My initial proposal prompted a different approach from a colleague. I tested both across a range of scenarios and found the other model's behaviour more logical, predictable and tunable, so we chose it over my original method.
Model evaluation

How the model was evaluated

Schema variations, reviewed with LLM assistance

What was assessed

This evaluated the matching model’s output, not the usability of the interface.

Test data

I created four schema variations to map to Lloyd’s v5.2, with discrepancies and lexical variation in column names. The accompanying data was synthetic, used only to allow Quantemplate to detect the correct data types.

Method

An LLM-assisted assessment of the mappings both models produced, carried out on 26–27 August 2026. The error counts are estimates from an LLM-assisted review of screenshots across the four schemas.

Findings

Across the four schemas, only three mappings from the new model were assessed as incorrect, even with the Automap confidence threshold set to 0%. It also handled repeated and numbered fields more consistently, reducing incorrect matches between similar-looking fields with distinct meanings.