Data integration process: where pipelines break

Finance asks why March revenue in the warehouse is 2% lower than in NetSuite. The answer takes a week to find, because the number passed through four pipelines on its way to the dashboard. A nightly file from the billing system, a change feed from the orders database, a webhook from the payment provider and a scheduled load from the ERP. Each one reported success, and one of them dropped every credit note issued after 11 p.m.

Most data integration problems look like this. The steps work on their own. The trouble sits between them, where nobody owns the handoff and nothing checks it.

This post walks through the seven steps of data integration, where each one breaks, and what to fix first. For the definition, see what data integration is.

The seven steps, and where each one breaks

1. Capture changes from databases. Read every insert, update and delete as it happens. A nightly query on a modified date never sees a deleted row, so the warehouse keeps it forever. Why change data capture pipelines fall behind.
2. Move file feeds onto APIs. Replace the nightly file drops that many core processes still depend on. The file had rules nobody wrote down, such as a missing row meaning “deleted”, and the new API feed never learns them. Why batch file feeds outlive every migration plan.
3. Deliver events in real time. Send one event from a source system to every app that needs it. One slow app holds up the rest, the sender retries, and everyone gets the event twice. What webhooks are, and why they fire twice.
4. Load business apps into the warehouse. Land changed records raw, then model them. A sync that overwrites current state can’t answer what changed, and deletes and merges vanish. Why the warehouse copy drifts.
5. Serve data through one API. Give apps and agents one stable way to read customer, order and account data. Without it, a field renamed in one source breaks every app that called it. What API integration is.
6. Handle changes to source structure. Spot new, renamed and removed fields before a pipeline runs. A renamed picklist value fails nothing, and reports grouped by it go wrong without an error. Why one renamed field breaks five integrations.
7. Monitor quality and freshness. Check the tables that drive decisions after every load, with an owner for each. An alert to a shared channel gets muted, and the stale number reaches the board deck. Why bad data reaches the dashboard first.

Why pipelines break between the steps

Most failures are silent. A missed delete, a truncated file, a renamed value and a stale table all produce a successful run. The pipeline only reports what it was told to watch for, and the expensive failures are the ones nobody predicted.

Nobody owns the handoff. The database team owns the source, the data team owns the warehouse, and the analytics team owns the dashboard. The change feed between them belongs to whoever built it, and that person has often moved on.

Batch and real time get mixed without a plan. The same customer arrives through a nightly file and a webhook. The two disagree for a few hours every day, and reports run during those hours show whichever one landed last.

Every pipeline is built differently. One is a script on a server, one runs in a legacy integration platform, one was built by a contractor in 2019. Each fails in its own way, and nobody can see all of them at once.

What to fix first

1. Find the pipelines that feed decisions. Start from the dashboards finance and leadership use, and trace each number back to its sources. Those pipelines get fixed first.
2. Add reconciliation before adding anything else. Compare row counts and totals between source and target after every run. A missed change sends no error, so this is the only way to see it.
3. Give every table an owner. A named person, not a team or a channel, so an alert reaches someone who can act.
4. Capture changes from the log. Replace modified-date polling on the databases behind the most important tables.
5. Watch the schema of every source. Catch renames and removals before the next run, and tell the owners of whatever depends on them.
6. Move the riskiest file feeds next. Run the API version in parallel and compare the two until they agree across a month end.

Moving off a legacy platform

Many of these pipelines run on an integration platform bought a decade ago, and the people who built them have left. Most teams move one use case at a time. Yext moved more than 100 services off its legacy platform in three months:

“With Tray, we can build integrations 3x faster than with our legacy platform, giving us the agility we needed.”

For what that kind of move involves, read why iPaaS migration got faster and how to reduce integration technical debt. For why AI agents make old pipelines harder to live with, read AI is exposing the limits of legacy data pipelines.

Where data integration meets the rest of the business

The warehouse is where most of this data ends up, and it is rarely where people use it. Reverse ETL sends what the warehouse works out back to the CRM and the other tools teams work in, and customer data matching gives every record one customer ID to join on.