Integration projects are rarely lost on the connector. Reading from an ERP, a CRM or an operational system is a solved problem, and has been for years. The projects that go badly go badly for reasons that look like edge cases in week one and like the whole project by week twelve.
Here are the six that recur, and the design decision that prevents each.
1. Nobody agreed what identifies a record
Three systems hold what is nominally the same entity — a customer, a student, a site, a supplier. Each has its own primary key. None of them share one. Somewhere in the project, somebody will decide that matching on name and email is good enough, and for about eighty percent of records it will be.
The remaining twenty percent is the project. Duplicates that are not duplicates, merges that quietly destroy history, and a target system in which two real entities have become one and nobody can work out how to undo it.
The decision: settle identity before you write a pipeline. Either there is a system of record that issues the identifier and everything else carries it, or you build an explicit mapping table that is a first-class, reviewed, version-controlled artefact — not a lookup somebody generated once. Fuzzy matching is a way to propose identity, never a way to decide it.
2. Corrections arrive after the fact, and the pipeline has no idea
Most source systems allow backdated changes. An invoice is credited against last month. An enrolment is corrected. A cost centre is reallocated in a period that already closed. If your extract logic is "everything where modified date is since the last run", you will catch the change. If it is "everything created since the last run", you will not — and your target will disagree with the source forever, in a way that gets smaller and harder to notice every month.
The decision: ask, for every source, what a correction looks like and how far back it can reach. Then either extract on a modification watermark, use change data capture, or re-extract a rolling window wide enough to cover the realistic correction period. Whichever you choose, state it in writing. The worst answer is the one nobody asked about.
3. The load is not safely re-runnable
A pipeline fails halfway. Someone re-runs it. Now a proportion of rows exist twice, and a proportion of aggregates are wrong by an amount nobody can compute without going back to the source.
This one is entirely preventable and still extremely common, because append-only loading is the easiest thing to build and the failure only appears under conditions you did not test.
The decision: every load is idempotent. Upsert on a defined key, or replace a defined partition atomically. Re-running a load should be so boring that it is the first thing anyone tries. Once that is true, a failed run is an operational non-event instead of an incident.
4. There is no reconciliation, so the first wrong number is found by a human
This is the one that costs trust rather than time. Integration delivers a target that everyone starts using. Some weeks later a director notices that a total looks wrong. It is wrong. It has been wrong for three weeks. From that moment, every number in the platform is provisional in everyone's mind, and getting that back takes far longer than the original build.
The decision: reconcile after every load, automatically. Row counts and control totals — the sum of the two or three measures that actually matter — compared between source and target. On a mismatch, hold the publish and leave the previous good version live. A visible failure is recoverable. A silent partial load is not, because it gets trusted.
5. The schema changed and nothing told you
A source system gets upgraded. A field is renamed, a type widens, a code list gains a value nobody mentioned. Your pipeline either fails in a confusing way or — worse — succeeds while silently dropping or mis-casting the new data.
The decision: validate against an explicit contract, not against whatever arrives. Assert the fields you expect, their types, their nullability and the code values you handle. Fail loudly on an unexpected structure. It feels pedantic in month one and it is the reason you find out about the upgrade on the day it happens rather than at quarter end.
6. It works, and exactly one person understands it
The pipelines run. The numbers reconcile. Then the person who built them changes role, and within two quarters the estate has become something the organisation is afraid to touch. Every schema change becomes a gamble taken at month-end.
The decision: treat handover as a deliverable with the same weight as the build. Version-controlled transformations, a written runbook per pipeline that says what it does, what it depends on and what to do when it fails, and a real walkthrough with whoever owns it next. If the system cannot survive the person who built it leaving, it is not finished.
The pattern underneath all six
Every one of these is a case of something being assumed rather than agreed and written down: what identifies a record, how corrections arrive, what a valid structure looks like, what a correct load looks like, who owns it afterwards.
That is why integration work starts with contracts rather than connectors. The connector is an afternoon. The agreement is the project.
