In the last 10 years or so, there has been a paradigm shift in how we train AI models. In particular, the best practice has shifted from Model-Centric AI, where we endless tweak model architectures and algorithms in pursuit of better performance, to what we now call Data-Centric AI, where we keep the model architecture pretty much fixed but spend the bulk of our efforts on systematically engineering better and higher-quality training data. Here’s the slogan: if you want a better model, fix the data.
Indeed, the easiest way a new staff can make a contribution in an AI frontier lab is to improve the data-processing pipeline or come up with a new training dataset. And the easiest way an AI engineer / data scientist can make a contribution in a company keen on adopting AI is to produce a high-quality curated dataset that can provide enterprise context for an LLM that uses a Retrieval-Augmented Generation (RAG) architecture to answer questions within the company’s walls.
But integrating data from existing, heterogeneous systems to produce high-quality curated datasets for AI can be a hard slog. It is an exercise in reverse-engineering, semantic translation, and governance, all of which can be difficult in organisations with low data maturity. To understand the core issues, let’s take a closer look at the data integration process.
There are essentially three key steps in every data-integration pipeline:
- Schema Mapping and Matching : Identifying which attribute or attribute sets across database tables contain the same type of information;
- Record Linkage : Identifying which records in one or more databases correspond to the same real-world entity (person, business, product, etc), for which deduplication is a special case; and
- Data Fusion : Merging pairs or groups of records that correspond to the same entity in one clean, up-to-date, and consistent record that represents the entity.
Much has been written about Record Linkage and Data Fusion in the literature, but the Schema Mapping and Matching step, despite its criticality, remains a bit of a dark art. The topic is under-taught in universities. The concepts and techniques required to master it are usually scattered across multiple courses in a typical computing curriculum: relational calculus in Database 101, business process modelling in Software Engineering, and ontologies in niche Semantic Web or Formal Logic elective courses. Students rarely get to see how these puzzle pieces fit together in practice. What I am hoping to do in this post is to bring these different pieces together, informed by what I learned building and maintaining analytics data warehouses / lakes in multiple large organisations.
The diagram below represents the integrated ecosystem required to map and match schemas across heterogenous systems. (The diagram was generated by Gemini based on my conversations with it over multiple days.)
To understand this diagram, we first need to clarify concepts like ontologies, data models, process models, metadata standards and how they relate to each other.
When we try to map System A (say, a Customer Relationship Management (CRM) system) to System B (say, a billing platform), we are fundamentally trying to translate between two different worldviews. The most important input into that translation process are the data models for the underlying systems. The data models, usually formulated as Entity Relationship diagrams, come in three levels of abstraction.
- Physical Data Models are the messy raw material we have to work with. They come in the form of hundreds of actual SQL table schemas used inside Systems A and B, and these schemas can have potentially thousands of columns with unintelligible names like CUST_DT_B that we have to somehow map and match.
- Logical Data Models provide some semantic information on the physical data models, mapping, for example, CUST_DT_B to a logical attribute called Customer_Birth_Date with a defined ISO date format.
- Conceptual Data Models, when they are available, provide the business view written by business analysts to align stakeholders across an organisation on terminology and business constructs. Conceptual data models are usually agnostic to the underlying database technology and would simply state, for example, “A Customer is an entity that has a Date of Birth.”
When building a new database, we start with the conceptual data model and then refine it down to the logical and physical data models for implementation on an actual database system. When doing schema mapping and matching as part of data integration, we go in the reverse direction and start from the physical data model, which is always available, and use the logical data model, which almost always exists, and the conceptual data model, which may or may not be there, to reconstruct the meaning of the different tables and attributes. In cases where the conceptual data model for a specific system is not available, standardised conceptual data models for different industries and domains available from vendors like Teradata can partially fill that gap.
To help interpret the data models, we sometimes also have access to ontologies. Built using frameworks like OWL (Web Ontology Language), an ontology captures in a knowledge graph the objects and classes that exist in a domain, and their properties and relationships. Whereas a data model answers the question “what entities, attributes, and business relationships are stored in our system?”, an ontology answers the question “what exists in this domain, and what can be logically inferred about it?” If System A is a hospital billing system and System B is a clinical trial database, an ontology that maps Billing_Target in System A and Trial_Subject in System B to a shared ontological entity Person would allow automatic linking of the two to yield a single view of the different ways a person can interact with the organisation. In short, ontologies give automated systems the semantic context needed to realise that two structurally different schemas are actually referring to the exact same underlying entity.
The last piece of the puzzle are business process models. Whereas data models tell you what the data is and how they are related, business process models tell you when and why it was created. If System A and System B both have an Account_Status field, naively mapping them one-to-one might cause significant integration issues. A BPM diagram might reveal that System A updates the status when a contract is signed, while System B updates it only when funds clear. Understanding this operational context is essential for deciding which system is the true “system of record” during integration.
To top it all off, we also have metadata standards. These define industry best practices. ISO 11179, for instance, dictates how data elements should be named and defined in a registry. It prevents engineers from creating five different logical definitions for a “Customer.” By applying these standards, we ensure that as we map legacy physical schemas, the resulting target schema is universally understood and governed.
In summary, Standards dictate the rules and best practices, Ontologies provide the meaning, Data Models define the structure, and Business Process Models provide the workflow context.
The Data Catalog: Where the Magic and the Grind Happens
In a modern data integration architecture, all these data models and process models are brought inside a Data Catalog Software (like Alation or Collibra), which serves as the central hub shown in the middle of our diagram. (If you have never seen a Data Catalog software, you can try one out using the Open Metadata sandbox.) Modern data catalog software can connect to existing databases and automatically extract physical schemas. Then, guided by ISO standards, conceptual data models, and the semantic context provided by Ontologies (where available), data wranglers can use in-built ML algorithms to profile the data and suggest schema mappings. The ML algorithms might flag that CUST_DT_B in the CRM and DOB in the Billing system have similar data distributions and share the semantic root of “Date of Birth” in the Business Glossary. But the machine can only make suggestions. Data stewards and integration engineers must then review, refine, and iterate on these mappings. In doing that, they may use the operational context derived from Business Process Models to establish record linkage rules (e.g., “Always trust the CRM for the customer’s name, but trust the billing system for their address”). The whole schema mapping and matching process is almost never a one-off set-and-forget process, but an iterative loop of discovery, algorithmic suggestion, and human semantic validation. The end result of that discovery process is a Target Integration Schema that is then fed into the subsequent Record Linkage and Data Fusion steps of the overall Data Integration pipeline.
As mentioned at the start of the post, the painstaking forensic work that is Schema Mapping and Matching is a necessary evil in the engineering of high-quality curated datasets needed by modern AI models. This is the part of the AI lifecycle that has so far defied full automation and it will likely continue to be a key bottleneck in preventing many organisations from getting value out of modern AI.
Mental Models 4 Life 