Enterprise data integration platform

Context

A group operating across five countries and more than twelve business units, each running its own combination of ERP, student or operational information systems, CRM and finance tooling. Most of those systems were legitimate choices made locally at the time. None of them had been chosen with group-level reporting in mind.

The group needed to see itself as one organisation — comparable numbers across units, on a cadence that matched how often decisions were actually being made.

The business problem

Data moved between systems the way it usually does at this stage: scheduled exports, email attachments, and a shared drive that everybody had learned not to fully trust. Each unit could answer questions about itself. Nobody could answer the same question across all of them without a week of manual assembly, and the answer had a shelf life measured in days.

The deeper problem was not volume. It was that no one could state, for any given number, when it had last been correct or whether last night's load had actually completed.

Constraints

• Source systems could not be replaced, and in several cases could not be modified at all — integration had to work with the APIs and extracts that existed.
• Local business units retained ownership of their own operational data; a central mandate was not available as a shortcut.
• Reporting already in use had to keep working throughout — there was no window in which the group could stop reporting.
• Time zones across five countries meant there was no single overnight batch window that suited everyone.

Approach

The first decision was to treat every source as a contract rather than a connection. Before any pipeline was built, each source got a written schema and a set of expectations — which fields are authoritative, what identifies a record uniquely, what a legitimate change looks like, and how corrections arrive. Most of the eventual failures in an integration estate come from this being assumed rather than agreed.

The second decision was that loads must be idempotent and safely re-runnable. Re-running a load has to be boring. Once that is true, a failed run becomes an operational non-event rather than an incident requiring someone to work out what got half-written.

The third was the reconciliation harness in the diagram: after every load, row counts and control totals are compared between source and target, and late-arriving corrections are explicitly accounted for. On a mismatch the publish is held and the alert names the failing step. A silent partial load is worse than a visible failure, because a silent partial load gets trusted.

What I did

• Profiled and documented each source system, and negotiated the data contract with the team that owned it.
• Designed and built the pipeline estate — extraction via APIs, change capture and file drops, validation, transformation and idempotent loading.
• Built the reconciliation harness and the alerting around it, including the hold-on-mismatch behaviour.
• Staged the rollout unit by unit, running new and old reporting in parallel until the new numbers were trusted.
• Wrote the runbooks and handed operational ownership to the internal team.

Systems and technologies

ERP and CRM platforms, operational information systems, and finance tooling on the source side. Talend, SSIS, Azure Data Factory and Apache Airflow for movement and orchestration; Python and SQL for transformation and validation; SQL Server and SAP HANA on the target side; Git and CI for version control and deployment.