Six Levels of Information Integration: Where Definitions Meet Implementation

In Part 1, we established a Definition hierarchy - Term, Concept, Characteristic, Domain, Value, Instance - and argued that these form the foundation of every Data Warehouse. Without them, there is no reliable way to determine whether data from different systems refers to the same thing. Semantic Clarity - every entity, attribute, and value clearly defined and broadly accepted - is what makes integration possible rather than accidental.

But Definitions are only the beginning. The real test comes when we apply them to actual source systems, each with its own data models, key structures, value sets, and formats. This is where most teams discover how much they underestimated the work.

This article breaks integration into six levels, each building on the previous one. When you skip a level of integration, the Data Warehouse loses some of its ability to break dependencies between source of data and usage of data, and the Data Warehouse becomes more brittle to change.

Level 1: Common Model Integration

The first integration challenge is deceptively simple: which table should each record go into?

This maps directly to Concept Definitions. Every table represents a defined Concept - Customer, Account, Transaction, Product. A record can only be loaded if it fits that Concept's Definition: what kind of thing is this?

Source data models rarely align one-to-one with the common model. A generic "Party" table might mix individuals, organizations, and internal departments. The integration team must examine the data, determine which records belong to which Concept, and route them accordingly. A single source table usually contains several Concepts and relationships - all needing to be broken out into the common model.

This requires examining source data against Concept Definitions, and often surfaces ambiguities needing resolution before loading begins. A well-defined Concept catches anomalies early. A vague one lets misclassified records through - records that look correct until someone reports across sources and gets nonsensical results.

The failure mode is misclassification: records loaded into tables where they do not belong, contaminating the Concept they were mixed into. Once misclassified data enters a common entity in the model, that entity will give you incorrect answers to your questions.

Level 2: Key Integration

With records routed correctly, the next question: do any represent the same real-world instance? Two systems might both send Deposit Account data into the same table. Are account 12345 in System A and account 12345 in System B the same account? We want Key Integration where instances are genuinely shared, but must avoid Key Collision where they are not. Make a key unique per source when it should not be, and you fail at Key Integration. Fail to make it unique when it needs to be, and you get Key Collision.

This maps to Instance identification. The answer depends on knowledge outside the data itself: how instances move through the organization's process landscape and IT systems.

Two banking examples illustrate why - and why the same setup can require opposite answers.

Example 1: Same key, different accounts. Two systems send Deposit Account data, both using numeric identifiers ranging from 1 to 99,999,999. An integration team might match on account number. But these systems never share accounts - they generate numbers independently. The overlap is coincidental. Matching would merge unrelated accounts, writing one account's information onto another. The correct answer: enrich the key - add the source system identifier to make it unique per source.

Example 2: Same key, same account. Again, two systems send Deposit Account data. But here they share accounts: when status changes from "Normal" to "Overdrawn," management transfers from System 1 to System 2, and the account number stays the same. Without key integration, the Data Warehouse would hold two records for one account. The correct answer: integrate on business key.

Nearly identical setup. Opposite answers. The difference is process knowledge - understanding how instances move through the IT landscape and whether business keys are shared or independently generated.

To approach Key Integration reliably, the team needs to understand:

• Whether instances of this Concept are shared between source systems
• Whether lifecycle events cause instances to move between systems
• Whether that movement preserves or changes the business key

Each source system pairing requires its own analysis. Two systems not sharing instances for one Concept says nothing about another. Incorrect key integration does as much damage as skipping it - merging records that should be separate, or duplicating those that should be merged. Gathering this process knowledge is one of the most time-consuming parts of integration, because it lives in the heads of business process owners and system administrators, not in the data.

When this goes wrong, the result is false identity or false distinction: records incorrectly merged or kept separate, producing duplicate or chimeric instances that corrupt downstream analysis.

Level 3: Attribute Integration

With records in the right tables and instances identified, next come the Characteristics (attributes) themselves.

This maps to Characteristic Definitions - each attribute is its own Concept, as established in Part 1, needing its own Definition of what value it holds and at what granularity.

The critical principle is atomicity. The Data Warehouse must store data at the most granular level its Definition allows. The temptation is to assume "raw" source data is already atomic - that it matches the attribute Definition without transformation. This assumption frequently breaks.

Recall the Account Balance example from Part 1. The Data Warehouse defines it as: the balance at a specific point in time, representing the monetary value of settled transactions, not including accrued interest. But a source system includes accrued interest in its balance figure.

Loaded without transformation, the Data Warehouse would mix balances including accrued interest with balances excluding it - same attribute name, same column. Reports would look correct but produce wrong conclusions, because the numbers being compared do not mean the same thing.

This is "getting the eggs back out of the omelette." Once definitionally mismatched data is loaded, separating it ranges from difficult to impossible. The only reliable prevention is precise Definitions applied before loading, not after.

The failure mode is semantic contamination: values with different meanings mixed into the same attribute, producing calculations and comparisons that look valid but are not.

Level 4: Consolidation Integration

When the first three levels are in place, a new scenario emerges: two systems provide the same attribute for the same instance. The same customer appears in both, and both provide that customer's address.

Which address do we use?

Two main approaches. The first keeps source-specific versions, deferring the choice to the reporting layer. The second designates one source as authoritative for a given attribute, and its values take precedence.

Priority rules work when there is a clear "system of record." Deferred resolution works better when different contexts legitimately prefer different sources. A customer's billing address might come from the CRM for marketing but from the ERP for invoicing. Neither is wrong - they serve different needs.

Either way, Consolidation Integration requires the previous three levels. Without Common Model Integration, we cannot know the records belong to the same Concept. Without Key Integration, we cannot confirm they represent the same instance. Without Attribute Integration, we cannot confirm the attributes are semantically equivalent. Consolidation without these foundations is guesswork.

What goes wrong here is silent overwrite or silent duplication: authoritative data replaced by lower-quality data without detection, or redundant values stored without resolution, leaving consumers to guess which is correct.

Level 5: Value Integration

With records routed, instances identified, attributes validated, and overlaps resolved, we reach individual value standardization.

This maps to Domain and Value Definitions from Part 1. Some Characteristics draw from Closed Domains - defined value sets where each value carries its own meaning. When sources use different representations for the same values, Value Integration brings them into conformance.

Country codes illustrate this well. The Data Warehouse uses ISO 3166 Alpha-2 codes as its Domain. One source sends "SWE" for a Swedish account, another sends "United Kingdom" for a British account. Value Integration maps "SWE" to "SE" and "United Kingdom" to "GB" - transforming source representations into the common Domain.

Now consider a source sending "EU" as country of account registration. The European Union is not a country by the Definition in use - it does not appear in ISO 3166 Alpha-2. This is a data quality issue, not something the Data Warehouse should invent a mapping for. Store it, flag it as non-conformant, and leave the source system to correct it.

Value Integration is not about force-fitting every value into the common Domain. It is about mapping values that legitimately represent defined concepts and surfacing values that do not. The Definition is both the integration rule and the quality gate.

The failure mode is false equivalence or orphaned values: values incorrectly mapped to the wrong Domain entry, or valid source values left unmapped because the Domain Definition was incomplete.

Level 6: Format Integration

The final level is mechanical but necessary. Once the Closed Domains are semantically integrated, we need to handle the Open Domains, which is all about physical consistency: date formats, decimal precision, character encoding.

If the Data Warehouse stores monetary values with two decimal places, all source data must conform - regardless of whether the source used three decimals or none. If dates use ISO 8601, systems sending DD/MM/YYYY or MM-DD-YY must be transformed.

The least intellectually demanding level, but skipping it breaks downstream consistency. A date stored as "13/02/2026" in one record and "2026-02-13" in another causes calculation errors, sorting failures, and user confusion - even with perfect semantic integration. Decide the format rules once, document them, enforce them for every source.

The result is mechanical inconsistency: semantically correct data that is physically incompatible, breaking calculations, joins, and presentation. The simplest level to get right, but also the most likely to be inconsistently applied - precisely because teams treat it as too trivial to formalize.

What the Six Levels Achieve Together

Each level builds on the ones before it. Common Model Integration needs Concept Definitions. Key Integration needs process knowledge. Attribute Integration needs Characteristic Definitions. Consolidation needs the first three as prerequisites. Value Integration needs Domain and Value Definitions. Format Integration needs standardization.

When all six are applied, the result is a Data Warehouse that is source-system independent. Business users do not need to know how each source represents its data, what key schemes it uses, or what format its dates arrive in. The information is integrated, conformant, and usable throughout the organization - the entire purpose of a Data Warehouse.

Remove any level and that independence breaks: misclassified records pollute concepts, unresolved keys create phantom duplicates, mismatched attributes corrupt calculations, unresolved overlaps confuse consumers, non-standard values block comparison, and inconsistent formats break presentation.

None of this is easy. Definition work requires sustained business participation, organizational negotiation, and time that competes with every other priority. Teams rationally underinvest because the cost is immediate while the benefit is diffuse and long-term. Each level demands people who understand both business semantics and source system specifics - a genuinely scarce combination.

In Part 3, we explore how this integration discipline translates to declarative architectures and AI-driven platforms. If we want AI to help with integration analysis, it needs the same thing humans need: clear Definitions at every level. When those Definitions become executable metadata, the six levels become something a platform can enforce systematically rather than manually.

Count Your People

A simple test for any Data Warehouse organization. Count the data engineers, architects, developers, and DBAs. Then count the people working on Definition management and information integration. In most organizations, the ratio is heavily skewed toward engineering - even though the hardest and most consequential work is integration.

This is not a criticism of data engineering. Pipeline code, infrastructure, and tooling are necessary. But they support the primary purpose: integrated information. The Data Warehouse with excellent engineering and poor Definitions will fail its users. The one with modest engineering and excellent Definitions will at least deliver on its fundamental promise.

The staffing pattern reveals what the organization actually prioritizes - not what leadership says, but where the headcount sits. If the goal is a Data Warehouse that works - genuinely integrated and source-system independent - the investment in Definitions and integration expertise has to match the ambition. The six levels do not implement themselves.