Most organisations have more data than they can use, spread across systems that do not talk to each other. The organisations that pull ahead are those that learn to connect the dots. Here is how to do it without creating new problems in the process.

Most organisations have more data than they can use, spread across systems that do not talk to each other. The organisations that pull ahead are those that learn to connect the dots. Here is how to do it without creating new problems in the process.

The era of the single source of truth is largely a myth. Most organisations of any scale operate across multiple data systems e.g. a CRM for customer relationships, an ERP for operations, a finance platform for transactions, marketing analytics tools, and an increasing array of external data feeds. Each system captures a different slice of reality. No single system captures all of it.

The era of the single source of truth is largely a myth. Most organisations of any scale operate across multiple data systems e.g. a CRM for customer relationships, an ERP for operations, a finance platform for transactions, marketing analytics tools, and an increasing array of external data feeds. Each system captures a different slice of reality. No single system captures all of it.

The promise of combining these sources is compelling: a richer, more complete picture of customers, markets, and operations that enables better decisions at every level. The reality is that data integration is one of the most consistently underestimated challenges in analytics. Done well, it is transformative. Done poorly, it creates a new layer of unreliability that is harder to detect than the problems it was supposed to solve.

The promise of combining these sources is compelling: a richer, more complete picture of customers, markets, and operations that enables better decisions at every level. The reality is that data integration is one of the most consistently underestimated challenges in analytics. Done well, it is transformative. Done poorly, it creates a new layer of unreliability that is harder to detect than the problems it was supposed to solve.

Start with the question, not the data

The most common mistake in data integration projects is starting with the data rather than with the decision the data is meant to inform. Organisations invest in connecting systems because connectivity feels like progress, but without first establishing what questions the integrated data needs to answer and what decisions it will support, the exercise risks being pointless.

The most common mistake in data integration projects is starting with the data rather than with the decision the data is meant to inform. Organisations invest in connecting systems because connectivity feels like progress, but without first establishing what questions the integrated data needs to answer and what decisions it will support, the exercise risks being pointless.

Starting with the question makes the integration much easier in several ways. It clarifies which data sources actually need to be combined, often a smaller set than initially assumed. It defines what fields and time periods are relevant. And it establishes a clear standard for what 'good enough' looks like, which prevents integration projects from expanding indefinitely in search of perfect completeness.

Starting with the question makes the integration much easier in several ways. It clarifies which data sources actually need to be combined, often a smaller set than initially assumed. It defines what fields and time periods are relevant. And it establishes a clear standard for what 'good enough' looks like, which prevents integration projects from expanding indefinitely in search of perfect completeness.

Before beginning any data integration work, it is worth articulating the specific analytical questions the project is intended to answer, the decisions those answers will inform, and the minimum viable dataset required to answer them reliably. Anything outside that scope is scope creep.

The entity resolution problem

At the heart of most data integration challenges is entity resolution: the problem of determining when two records in different systems refer to the same real-world entity.

At the heart of most data integration challenges is entity resolution: the problem of determining when two records in different systems refer to the same real-world entity.

What does this mean in practice?

What does this mean in practice?

In practice, this means recognising that 'Acme Ltd', 'ACME Limited', and 'Acme Limited UK' are all the same company. That 'J. Smith' in one system and 'John Smith' in another are the same person. That a product listed under one SKU in a warehouse management system corresponds to a different SKU in the e-commerce platform.

In practice, this means recognising that 'Acme Ltd', 'ACME Limited', and 'Acme Limited UK' are all the same company. That 'J. Smith' in one system and 'John Smith' in another are the same person. That a product listed under one SKU in a warehouse management system corresponds to a different SKU in the e-commerce platform.

Entity resolution is a deceptively hard problem. Rule-based matching, looking for exact or near-exact string matches, handles simple cases but fails on abbreviations, spelling variations, and naming conventions that differ systematically across sources. More sophisticated approaches use probabilistic matching, which assigns confidence scores to potential matches and flags uncertain cases for human review.

Entity resolution is a deceptively hard problem. Rule-based matching, looking for exact or near-exact string matches, handles simple cases but fails on abbreviations, spelling variations, and naming conventions that differ systematically across sources. More sophisticated approaches use probabilistic matching, which assigns confidence scores to potential matches and flags uncertain cases for human review.

For organisations working with public sector data specifically, entity resolution is a particular challenge. Supplier names vary across procurement notices (the same company may appear under its trading name, its registered name, its parent company name, or an abbreviation) and there is no universal identifier that links records across different government data sources.

For organisations working with public sector data specifically, entity resolution is a particular challenge. Supplier names vary across procurement notices (the same company may appear under its trading name, its registered name, its parent company name, or an abbreviation) and there is no universal identifier that links records across different government data sources.

Platforms, like Arcamus, that have invested in resolving these inconsistencies provide significantly cleaner data than those that surface raw records without normalisation.

Platforms, like Arcamus, that have invested in resolving these inconsistencies provide significantly cleaner data than those that surface raw records without normalisation.

Entity resolution is where most data integration projects quietly break down.

Handling conflicting data

When two data sources contain different values for the same field on the same entity, a decision must be made about which value to trust. This is a question that technical processes alone cannot answer — it requires human judgment about the relative reliability of each source.

When two data sources contain different values for the same field on the same entity, a decision must be made about which value to trust. This is a question that technical processes alone cannot answer — it requires human judgment about the relative reliability of each source.

Several principles can help navigate this. Recency generally takes precedence for fields that change over time, a more recently updated record is usually more reliable than an older one, all else being equal.

Several principles can help navigate this. Recency generally takes precedence for fields that change over time, a more recently updated record is usually more reliable than an older one, all else being equal.

Source authority matters for fields where different systems have different levels of accuracy. A financial system's transaction data is more reliable than a CRM's estimate of transaction value.

Source authority matters for fields where different systems have different levels of accuracy. A financial system's transaction data is more reliable than a CRM's estimate of transaction value.

And confidence scoring, which tracks the provenance and quality history of individual data points, enables more granular conflict resolution than simple precedence rules.

And confidence scoring, which tracks the provenance and quality history of individual data points, enables more granular conflict resolution than simple precedence rules.

Conflicts should also be recorded rather than silently resolved. Knowing that two sources disagreed on a value, and which source won, is important audit information. Silent conflict resolution makes the integrated dataset look cleaner than it is and obscures the downstream uncertainty that the analyst should be aware of.

Conflicts should also be recorded rather than silently resolved. Knowing that two sources disagreed on a value, and which source won, is important audit information. Silent conflict resolution makes the integrated dataset look cleaner than it is and obscures the downstream uncertainty that the analyst should be aware of.

Temporal alignment

Data from different sources is often captured at different points in time, at different frequencies, and with different lag periods. Combining it without accounting for these differences produces analyses that are internally inconsistent, comparing figures that do not actually refer to the same moment.

Data from different sources is often captured at different points in time, at different frequencies, and with different lag periods. Combining it without accounting for these differences produces analyses that are internally inconsistent, comparing figures that do not actually refer to the same moment.

This problem is most visible when combining transactional data (which may be updated in real time) with batch-processed data (which may be refreshed weekly or monthly). An analysis that joins a real-time CRM record to a monthly customer segment file is implicitly assuming that the segment classification is current when it may be up to thirty days old. In fast-moving markets, that assumption can produce material errors.

This problem is most visible when combining transactional data (which may be updated in real time) with batch-processed data (which may be refreshed weekly or monthly). An analysis that joins a real-time CRM record to a monthly customer segment file is implicitly assuming that the segment classification is current when it may be up to thirty days old. In fast-moving markets, that assumption can produce material errors.

The practical response is to be explicit about the temporal assumptions built into any integrated dataset — documenting update frequencies, lag periods, and the implications for the analyses the data will support.

The practical response is to be explicit about the temporal assumptions built into any integrated dataset — documenting update frequencies, lag periods, and the implications for the analyses the data will support.

Building a data integration architecture that scales

For organisations combining more than a handful of data sources, ad hoc integration quickly becomes unmanageable. Each new source adds connections that are difficult to maintain, and changes to any single system can cascade unpredictably.

For organisations combining more than a handful of data sources, ad hoc integration quickly becomes unmanageable. Each new source adds connections that are difficult to maintain, and changes to any single system can cascade unpredictably.

A more sustainable approach is to build integration through a centralised data layer, (whether a data warehouse, a data lake, or a more modern lakehouse architecture) that serves as the single point of ingestion and transformation. Sources feed into this layer in standardised formats; analytical consumers draw from it rather than directly from the source systems.

A more sustainable approach is to build integration through a centralised data layer, (whether a data warehouse, a data lake, or a more modern lakehouse architecture) that serves as the single point of ingestion and transformation. Sources feed into this layer in standardised formats; analytical consumers draw from it rather than directly from the source systems.

This architecture isolates integration complexity from analytical consumption, makes data lineage traceable, and enables quality checks to be applied systematically at the point of ingestion rather than reactively after problems surface in downstream analyses.

This architecture isolates integration complexity from analytical consumption, makes data lineage traceable, and enables quality checks to be applied systematically at the point of ingestion rather than reactively after problems surface in downstream analyses.

Governance: the human side of integration

Technical architecture is necessary but not sufficient. Data integration without governance produces a technically unified dataset that nobody trusts, because nobody is clear on how it was assembled, what its limitations are, or who is responsible for its quality.

Technical architecture is necessary but not sufficient. Data integration without governance produces a technically unified dataset that nobody trusts, because nobody is clear on how it was assembled, what its limitations are, or who is responsible for its quality.

Effective data governance for integrated datasets covers four areas.

Effective data governance for integrated datasets covers four areas.

Ownership

Ownership

Every dataset and every field should have a named owner responsible for its quality.

Every dataset and every field should have a named owner responsible for its quality.

Documentation

Documentation

The source, transformation logic, and known limitations of every element in the integrated dataset should be recorded and accessible.

The source, transformation logic, and known limitations of every element in the integrated dataset should be recorded and accessible.

Quality monitoring

Quality monitoring

Automated checks that flag anomalies — unexpected distributions, missing values, implausible changes — should run continuously rather than only when a problem is reported.

Automated checks that flag anomalies — unexpected distributions, missing values, implausible changes — should run continuously rather than only when a problem is reported.

Change management

Change management

Processes for communicating upstream changes that might affect the integrity of the integrated dataset should be in place before they are needed.

Processes for communicating upstream changes that might affect the integrity of the integrated dataset should be in place before they are needed.

See how Arcamus applies these principles to UK procurement data.

See how Arcamus applies these principles to UK procurement data.