From Fragmented Systems to a Unified Data Foundation

Fragmentation is an integration problem first

Large organizations run long lists of applications: ERP, CRM, core operational systems, SaaS tools, spreadsheets that became systems, and acquired companies that were never fully absorbed. Each holds a partial view of customers, products, assets and transactions. The symptoms are familiar: reports that disagree, the same customer under five identifiers, integration work that consumes a growing share of every project, and AI pilots that spend most of their time finding and reconciling data.

The usual response is a platform decision: a new warehouse, a lakehouse, a data cloud. That decision matters, and our articles on warehouse, lake, lakehouse and mesh and on Snowflake, Databricks and data marts cover it. But a platform is a destination, not a connection. Organizations that migrate to a modern platform without changing how data moves between systems end up with the same fragmentation, now with a faster query engine on top.

Our thesis: a unified data foundation is built flow by flow, not platform by platform. Each important data flow needs the right integration pattern, the core business entities need a small number of shared definitions, and legacy connections need to be retired in a planned order. The platform is where the result lands.

The arithmetic of fragmentation explains why. If every system translates directly to every other, the number of translations grows roughly with the square of the number of systems. Hohpe and Woolf's Enterprise Integration Patterns describes the alternative: a canonical model that every system translates to and from once, so translations grow in a straight line.

Five patterns, each right for a different flow

There is no single best integration technology. There are patterns, each suited to a particular combination of latency, volume, coupling and ownership. Most enterprises need all of them, and most integration estates go wrong by applying one pattern everywhere.

Batch ETL and ELT remain the backbone of analytics. ETL transforms data before loading it; ELT loads raw data into the target platform and transforms it there, which suits modern cloud platforms with cheap storage and elastic compute. Batch is simple, auditable and cheap, and its weakness is latency. It is the right default for reporting, finance and most model training.

Change data capture (CDC) reads changes from a source database and propagates them as they happen. Log-based CDC reads the database's transaction log rather than querying tables. The documentation for Debezium, a widely used open-source CDC project, lists the advantages: all changes are captured, including deletes; latency is low without heavy polling load; and no "last updated" column is needed in the source. CDC is often the least disruptive way to get data out of a legacy system that cannot be changed.

Event streaming publishes business events, such as an order placed or a payment received, to a durable log such as Apache Kafka, where any number of consumers can read them independently. It suits operational processes that must react in seconds and decouples producers from consumers. Its cost is operational complexity and the discipline of designing events as contracts. A common trap is the "dual write", where an application updates its database and publishes an event as two separate steps; a crash between them leaves the two out of step. The transactional outbox pattern, which writes the event to the database in the same transaction and publishes it afterwards, is the standard remedy.

APIs expose data and functions on request. They are right for transactional lookups, such as checking a customer's eligibility, and for exposing a system's capabilities to digital channels. They are a poor way to move large volumes for analytics, because they put query load on operational systems.

Data virtualization queries data where it sits and presents a combined view without copying. It is useful for low-volume, exploratory or compliance-sensitive access where copying data is undesirable. It does not remove the underlying inconsistency between systems; it only hides it until query time, and performance depends on the slowest source.

Canonical models: shared where it pays

A canonical data model is a common format that systems translate to and from instead of translating to each other. Hohpe and Woolf note that it adds overhead when only a few applications are involved, and pays off as their number grows.

The failure mode is ambition. Enterprise-wide canonical models that try to describe every entity in one schema take years, become outdated before they are finished, and are bypassed by delivery teams under deadline. The approach that works is narrower:

• Canonicalize the entities that cross many boundaries: typically customer or party, product or service, location, organization and a handful of reference code lists.
• Leave domain-specific data in domain models. A claims system's internal structure does not need to be canonical; the customer and policy identifiers it shares do.
• Version the canonical model like an API, with backward-compatible changes and a published deprecation policy.
• Govern definitions in the catalog, with a named owner per entity, so the model is a living agreement rather than a diagram.

Our analysis of where lock-in now sits makes a related point: shared business definitions are the most portable and valuable part of a data architecture.

Where master data management touches integration

Canonical models define what a customer record looks like. Master data management (MDM) decides which customer records refer to the same real customer, and which values are authoritative. The two meet at three points:

1. At ingestion, where incoming records are matched against existing master records. Matching rules for names, addresses and identifiers need to be tested on real data, including Arabic transliteration variants in the Gulf.
2. At publication, where the mastered record, the "golden record", is distributed to consuming systems, usually through events or APIs.
3. At write-back, where corrections made in the master are fed back to source systems, or deliberately not, depending on the MDM style.

MDM implementation styles range from a registry that only links identifiers across systems, through consolidation into a central hub for analytics, to coexistence and fully centralized authoring of master data. The integration consequence is direct: a registry needs only identifier cross-references, while centralized authoring requires every source system to accept updates from the hub. Choose the style before designing the flows, not after.

Sequence by data flow, not by system

Big-bang replacement of integration estates rarely goes well. Martin Fowler's strangler fig pattern, revised in 2024, describes the alternative for application modernization: build the new alongside the old, move capability across in slices, and retire the legacy piece by piece. The same logic applies to data integration.

The order matters. Observe first: inventory the flows, their consumers and their owners, because most organizations do not know how many point-to-point interfaces they run. Connect next, by putting CDC or events on the systems that feed the most consumers, so new consumers stop creating new direct connections. Conform the core entities to the canonical definitions and master data. Consolidate analytical copies onto one platform. Only then retire the old interfaces, one by one, with consumers moved and reconciled first.

Migration risk: the data can move and the program still fail

Two public incidents show where integration and migration risk actually sits.

TSB, 2018. In April 2018 the UK bank migrated the data for its corporate and customer services to a new IT platform. According to the UK's Financial Conduct Authority, the data itself migrated successfully, but the platform failed immediately, disrupting branch, telephone, online and mobile banking for a significant proportion of its 5.2 million customers until December 2018. In December 2022 the FCA and the Prudential Regulation Authority fined TSB a combined £48.65 million, citing failures to plan and govern the migration and to manage risks from its outsourced IT supplier.

Public Health England, 2020. Between 25 September and 2 October 2020, 15,841 positive COVID-19 test results were left out of England's daily reports and the contact-tracing system. PHE's statement attributed this to data files that exceeded the maximum file size of the automated process loading them into central systems. Records were not rejected with an error; they simply did not arrive.

The lessons are not about technology choice. TSB shows that a migration is a change to how the whole service operates, not a data copy. PHE shows that every batch or file-based flow needs reconciliation: record counts and control totals compared between source and target on every run, with a failure raised when they differ. Silent truncation is the most dangerous integration defect because nothing looks broken.

The Gulf view

In Saudi Arabia, integration is shaped by national platforms as well as enterprise design. SDAIA's National Data Index measures government entities not only on compliance with the NDMO standards but on operational excellence, including active use of national platforms such as the National Data Catalog, the Government Service Bus and the National Data Lake. For public entities, the integration architecture therefore has two audiences: internal consumers and the national data-sharing infrastructure.

The NDMO standards' Data Sharing and Interoperability domain also governs how data is shared between entities, including data sharing agreements. As our lakehouse comparison notes, the standards' wording on data sharing describes ETL into warehouse stores and ELT into a data lake. Entities can satisfy that with a modern design, provided they document how each flow maps to the requirement. Our NDMO implementation guide covers the domain in detail.

For multinational groups in the Gulf, the additional design constraint is residency. CDC and event streams replicate data continuously, so a stream that crosses a border is a continuous cross-border transfer. Flows carrying personal or classified data should be designed to stay in-country, with only permitted aggregates leaving.

Choosing a pattern for each flow

What to do next

1. Inventory your data flows, not just your systems: source, target, pattern, frequency, owner and consumers for each.
2. Count your point-to-point interfaces and identify the ten systems that feed the most consumers.
3. Put CDC or event publication on those systems, so new consumers subscribe instead of building new direct connections.
4. Agree canonical definitions for three to five core entities, owned in the catalog, and version them.
5. Decide your MDM style for customer and product before designing the flows that depend on it.
6. Add reconciliation to every batch and file flow: counts and control totals checked on every run.
7. Retire one legacy interface per sprint once its consumers are moved and reconciled, and track the count down.

Frequently asked questions

What is the difference between ETL, ELT and CDC?

ETL transforms data before loading it into the target; ELT loads it first and transforms it inside the target platform. Both usually run in batches. Change data capture reads individual changes from a source database, often from its transaction log, and propagates them continuously.

What is a canonical data model?

A shared format for core business entities that systems translate to and from, instead of translating directly to each other. It reduces the number of translations as systems are added, but should be limited to entities that genuinely cross many boundaries.

How does master data management relate to data integration?

Integration moves data between systems; MDM decides which records describe the same real-world entity and which values are authoritative. They meet at ingestion (matching), publication (distributing the golden record) and write-back (returning corrections to sources).

How do you reduce risk in a data migration?

Migrate in slices rather than all at once, run old and new in parallel with reconciliation of counts and control totals, rehearse cut-over and rollback, and treat the migration as a change to the operating service, not only a data copy.

Sources and further reading

• Canonical Data Model, Gregor Hohpe and Bobby Woolf, Enterprise Integration Patterns.
• Debezium features, Debezium documentation. Advantages of log-based change data capture.
• Pattern: Transactional outbox, Chris Richardson, microservices.io.
• Strangler Fig Application, Martin Fowler, revised 22 August 2024.
• TSB fined £48.65m for operational resilience failings, Financial Conduct Authority, 20 December 2022.
• PHE statement on delayed reporting of COVID-19 cases, Public Health England, October 2020.
• Data Management and Personal Data Protection Standards, v1.5, NDMO, SDAIA.
• National Data Index, Version 1.1, SDAIA, November 2024.