AI Is Changing Schema Matching in Ways Rule-Based Systems Couldn't

Every AI project assumes the data underneath it is already aligned. It almost never is. Before a model trains, before a dashboard loads, before a retrieval pipeline returns anything useful, someone has to answer a question that sounds trivial and is not: do these two columns actually mean the same thing? Right now a data engineer is staring at two spreadsheets trying to work that out. One column is called cust_id . Another calls it customer_number . A third file, inherited from a team that got acquired last year, went with CustomerRef . None of them agree on whether the value is an integer, a string, or a zero-padded code, and one stores it as a float because Excel got involved at some point, which is usually how these stories begin. That is the job: working out that the three columns with three names and three types are probably the same thing. It happens before the model training. It happens before the board deck. And on a lot of projects it quietly eats more of the week than either of them. People have thrown engineering at this for decades. I should say up front that I did not come to it as a data engineer. I came at it from the model side, which is probably why the recent shift felt so strange to me. The thing that changed schema matching was not a better schema matching algorithm, at least not at first. It was general-purpose language models turning out to be surprisingly good at the judgment calls that always made this work annoying. The unexpected failure mode If you have never had to integrate data from more than one source, this sounds like a non-problem. Just rename the columns. What is the big deal? The big deal is that the work does not scale with effort. It scales with the number of source pairs, and that count grows fast. Two schemas give you one alignment. Ten schemas merged into a warehouse means correspondences across dozens of tables and hundreds of fields, most of them named by people who left the company years ago and took their intentions with them. The case that made this concrete for me was a pair of customer tables where the obvious match was wrong. One system had customer_id . The other had acct_id . A name-based matcher put them together immediately, and even the sample values looked compatible: short numeric identifiers, mostly unique, no obvious formatting issue. But the surrounding columns told a different story. acct_id belonged to a billing account, not a customer, and one customer could have several accounts. Treating those two fields as equivalent would have duplicated revenue downstream, and nothing in the pipeline would have crashed. The numbers would simply have been wrong. That is the unpleasant part. When schema matching fails, it usually fails quietly. The pipeline runs green, the dashboard refreshes, the model trains, and the error surfaces weeks later as double-counted revenue, merged customers, broken cohorts, or a feature that does not mean what its column name promised. Classically the work splits into two jobs, and the distinction matters for everything that follows. Matching is finding the correspondences: this column lines up with that one. Mapping is the harder follow-on: specifying the actual transformation that moves and reshapes the data from source to target. Matching tells you CustomerRef and cust_id are the same attribute. Mapping decides that one is a zero-padded string and the other is an integer, strips the padding, casts the type, handles the weird rows, and produces something that loads cleanly on the other side. Why rule-based systems stopped scaling For a long time this was pure engineering. Matchers like COMA and Similarity Flooding combined name similarity, type compatibility, and structural graph comparison. They did fine when names were clean and structures roughly rhymed, and they struggled on exactly the data where you needed help: badly named, undocumented, business-specific tables patched together over years. Part of the reason is that the match depends on a step that comes before it. Deciding that cust_id and CustomerRef line up gets much easier once you know both columns hold customer identifiers, not just that both are strings or both are 40% unique. Your database will tell you a column is VARCHAR . It will not tell you it holds country names, ISBNs, airport codes, or internal sales territories. This is semantic type detection, and for years it meant hand-written regexes: this pattern is an email, this one a ZIP code. That held up until someone in a new region formatted postal codes differently and the detector quietly started reading them as something else. The research answer was to learn the rules instead of writing them. MIT's Sherlock reframed semantic typing as a classification problem driven by the values themselves rather than the headers, and it beat regex-and-dictionary baselines. Sato then added the table context Sherlock ignored, since a column of integers from 1 to 12 could be a month, a rating, or a sales region depending on its neighbors. A wave of pretrained table models followed. For high-volume typing in a production catalog, that whole family still makes sense: cheap, fast, no API call per column. The limit is structural. These are fixed label sets. If a type was not in the training menu, the model cannot invent it. It cannot look at a strange identifier and reason that it has never seen this label before but this looks like a vehicle identification number. It can only pick from the options it was handed. Embeddings helped but weren't enough The next cheap idea is embeddings. You turn each column's name, and usually a sample of its values, into a vector, then compare vectors by cosine similarity. Columns that mean similar things should land near each other. There is no generated text to parse and no expensive call for every possible pair. Once the vectors exist, matching becomes a nearest-neighbor problem. This is useful, and it is often not enough. Embedding matchers are good at narrowing the search space. If you have thousands of candidate pairs, they can produce a shortlist worth inspecting. They are much less reliable as the final judge, especially when two fields look similar but mean different things in the business. The role I trust them in is retrieval, not decision-making. Benchmark suites like Valentine exist precisely so you can compare these approaches against known ground truth instead of relying on vibes. Reasoning models changed the problem Around 2022 the interface changed. Narayan and collaborators published Can Foundation Models Wrangle Your Data? , and the answer was a qualified yes. Given a few examples in the prompt, a general language model could hold its own against purpose-built systems on entity matching, error detection, and missing-value imputation. The shift was not really about accuracy. It was about framing. Sherlock and Sato treat typing as classification: fixed labels, training data, known categories. An LLM lets you treat it as reasoning. You describe the task, show a few examples, and ask for a judgment: You are matching columns between two database tables. Decide whether the two columns describe the same real-world attribute. Examples: Column A: cust_id Values: 1001, 1002, 1003 Column B: customer_number Values: C-1001, C-1002 Match: yes. Both are customer identifiers, formatted differently. Column A: ship_date Values: 2024-03-01, 2024-03-02 Column B: order_total Values: 49.99, 120.00 Match: no. One is a date, the other is a monetary amount. Now decide: Column A: CustomerRef Values: 1001, 1002 Column B: acct_holder_id Values: 1001, 1002 Rather than a labeled corpus and a fixed list of types, the model reads the names, the values, the examples, and the instructions, then makes a call. It is not always right. But some matches stopped being feature-computation problems and became questions about meaning, which is a genuinely different kind of thing to automate. Mapping is where it gets genuinely interesting Everything above is about matching: deciding two columns correspond. Mapping is harder, and it is where LLMs stop being a smarter matcher and start writing the transformation logic. A mapping has to strip prefixes, cast types, normalize casing, handle nulls, preserve leading zeros where they matter, and avoid breaking one-to-many relationships. SQLMorpher is a clean example of the pattern. It prompts an LLM to write SQL that converts a source table into a target schema. The model never sees the whole table. It gets the source and target schemas, a small sample of rows, sometimes a schema-change hint, and it writes the SQL. The database engine runs that SQL against the full dataset. The model handles the ambiguous design step; the deterministic engine handles execution at scale. The same shape shows up in production systems that synthesize mapping rules in SQL, JSONata, or PySpark, run them through a transformation engine, and validate the output before it becomes load-bearing. That validation step matters more than it looks, because a single mapping bug corrupts data silently. Say two systems both have an amount column and a matcher correctly reports that they correspond. Now the mapper runs. One amount is in cents, the other in dollars. One includes tax, the other does not. One is stored in local currency and the target expects USD. If the model writes a transformation that only casts the type, the output loads cleanly and every number is wrong. This is why I care more about mapping validation than about matching accuracy in isolation. A high-confidence match is not enough. You have to run the transformation on real rows and ask whether the result looks like what the target data should actually be. Where this gets fragile LLM matching is not just non-deterministic in the temperature sense. Setting temperature to zero helps reproducibility within a single model version, but it does not fix the deeper problem: part of your logic now lives in someone else's weights. The column pair that gets a confident yes this quarter can get a no next quarter after a provider ships a model update. Your matching logic drifted, and your code never changed. Prompt sensitivity is the second trap. In the Wrangle Your Data experiments, swapping the in-context examples moved entity-matching performance substantially. Same task, same model, same data, different demonstrations. You can work around it, but it means prompt construction is part of the system, not a detail you get to ignore. Then there is cost. A trained table model can type a table for almost nothing. An LLM reranker costs more with every candidate pair you send it, and across a warehouse with tens of thousands of columns that becomes real latency and real money. And when the model is wrong, it tends to be wrong in the worst possible style: fluently, confidently, and with a plausible explanation attached. The architecture I would build Put those constraints together and the design almost writes itself. It is tiered, and every layer exists to keep the expensive one small. Start with deterministic checks. If names, types, value distributions, and table context make the answer obvious, do not spend reasoning budget on it. Then use embeddings to generate candidates, cutting thousands of possible pairs down to a shortlist. Then, and only then, hand the LLM the hard pairs, with enough context to make a real judgment: column names, sample values, surrounding columns, table descriptions, known business definitions, and constraints from the target schema. Then validate the output deterministically, because the model's confidence is not evidence. For matching, that means confidence thresholds, one-to-one constraints where appropriate, and human review on high-impact fields. For mapping, it means running the generated transformation on real rows and checking types, null rates, uniqueness, distributions, referential integrity, accepted values, and whatever business-specific invariants the target actually requires. Magneto , from NYU's VIDA lab, is a good example of this shape, and the code is on GitHub. It uses a cheap retrieval phase to shortlist candidates and an LLM reranking phase to reason over the smaller set. The specific models matter less than the structure. The reasoning model is never asked to solve the whole problem from scratch. It is asked to spend judgment only where judgment is needed. This is retrieve-then-rerank applied to schema matching, the same pattern that shows up all over modern AI systems: a cheap approximate method for breadth, an expensive model for the hard tail, and deterministic checks wrapped around the output. Where This Goes The next version looks more agentic, mostly because mapping finally gives a model something concrete to test against. Give it a source schema, a target schema, sample rows, documentation, and the ability to write a candidate transformation. Let it run that transformation on a sample, inspect the output, and revise when the validation fails. That loop is far more useful than a one-shot match score. The model proposes a match, writes the mapping, runs it, checks whether the output makes sense, and tries again. A human still reviews the result when the data matters, but now they are reviewing a tested artifact instead of staring at a blank mapping spreadsheet. I find this compelling not because LLMs solve schema matching. They do not. They are expensive, they drift, they are prompt-sensitive, and they can be confidently wrong. But they are useful in the one part of the problem that always resisted automation: the ambiguous middle where names, types, and simple statistics run out. For decades we tried to make that middle mechanical by piling on more rules, better features, and cleaner graph comparison. None of that work was wasted. It became the cheap, deterministic layer the expensive judgment now rests on. So here is the position I will stake. The future of schema matching is not replacing deterministic systems with LLMs. It is shrinking the amount of work a human has to reason about, while wrapping every model decision in validation that does not care how confident the model sounded. The winners here will not build the smartest matcher. They will build the architecture that knows when reasoning is actually worth paying for, and refuses to spend it anywhere else.