The Devil Is in the Data: Why Integrations Fail Before They Are Built

Your integration isn’t failing because of the software.

It’s failing because of your data.

I say the same sentence in every kickoff meeting I run: the devil is in the data. You can have the most sophisticated NetSuite setup, the cleanest Salesforce design, the most elegant billing platform on the market — and if you have not been ruthlessly detailed about your data strategy, the project will fail. I have watched companies spend months perfecting workflows and screens, then go live with corrupted customer records, in-flight orders nobody can find, and revenue that will not reconcile.

A brilliant design isn’t worth much if the underlying data can’t be trusted.

What master data actually is, and why it decides your sequence

Three terms, because people use them interchangeably and then build the wrong thing.

Master data is the set of persistent records your business transacts with and about: customers, vendors, items, employees, subsidiaries, the chart of accounts. These records don’t happen. They exist, and they keep existing.

Reference data is the smaller controlled lists that master records and transactions point at: picklist values, payment terms, currencies, tax codes, statuses, GL segments. Small, boring, and responsible for more failed syncs than anything else on this page.

Transactional data is the events: quotes, orders, invoices, vendor bills, payments, journal entries. Every one of them is a pointer. An invoice is meaningless without a customer. A vendor bill is meaningless without a vendor and an account.

Sequencing is simply the order in which those objects move between systems — and it is not a scheduling preference. It is dictated by the pointers. Master data first, reference data underneath it, transactions last. You don’t hang cabinets before the framing exists, and you don’t sync a bill before the vendor.

That’s the whole architecture. Most integration failures I see are just this rule, violated.

The failure I see most: transactions before the records they reference

We recently worked with a company whose internal team had spent months building an integration between their operational system and their ERP. Good engineers. Real effort. When it came time to go live, everything failed.

They were sending vendor bills before the vendors existed downstream. The integration was trying to create bills for vendors the ERP had never been told about, so the ERP rejected every transaction — it couldn’t match a vendor reference. Not some. Every one.

Months of work, dead on arrival, because the steps were in the wrong order.

That mistake is the most common integration failure I see: focusing on moving transactions without mapping the master data dependencies first. Nobody was careless. They just started with the thing the business could feel — bills — instead of the thing the bills depended on.

Before you move a single transaction, answer four questions for each transaction type:

1. What master data does this transaction reference?
2. Does that master data already exist downstream?
3. If it fails, can we safely retry without creating duplicates?
4. What fields need to be populated, and which source data will drive them — including segmentation?

Question three is the one teams skip, and it is the one that turns a bad hour into a bad quarter. An integration that can’t be safely re-run is an integration that will be re-run anyway, at 11pm, by someone who is guessing.

Once those four are answered, the vendor bill sequence looks like this:

• Sync vendors.
• Engage approval workflows as needed.
• Then sync vendor bills with all the desired fields, including GL segments.
• Set up monitors and alerts to catch when things drift out of sync.

We helped that client correct it — vendors first, then vendor bills — and everything is back on track. But the correction cost real time that the original sequence would have cost nothing to get right.

Document the sequence and the dependencies before you build, or you will debug it in production while bills are bouncing and your finance team is panicking.

What does a real pre-integration checklist contain?

This is the list I’d defend in front of a steering committee. It is ordered on purpose. Doing item five before item three is how good teams produce clean-looking garbage.

1. Name one system of record per object. For customers, vendors, items, contracts, pricing — one system owns the record and the others receive it. Two owners is not a design; it’s a future argument with an audit trail.

2. Profile the data on both sides before you scope the build. Counts, blanks, formats, orphans, records with no owner. Teams assume they can just migrate what they already have. Mapping fields between systems is rarely one-to-one, and you don’t truly have the data you need until you’ve mapped it end to end.

3. Deduplicate before you map, not after. Build a proactive process for identifying and removing duplicates early — matching rules, a merge protocol, and a named human who adjudicates the ambiguous ones.

4. Reconcile field values, not just field names. Mapping Status to Status proves nothing if the two systems disagree about what statuses exist.

5. Map every required downstream field, including segments. A field that is optional upstream and mandatory downstream is a silent go-live failure waiting for the first real transaction.

6. Answer the four dependency questions above, per transaction type.

7. Write the load sequence down and circulate it. If it lives in one developer’s head, it is tribal knowledge, and tribal knowledge does not survive a resignation.

8. Decide retry and error behavior explicitly. What gets queued, what gets alerted, what gets dropped, and who is watching.

9. Test three ways. Process testing walks every workflow end to end. Historical data testing migrates your data and then validates it — you don’t hope for clean data, you prove it. Integration testing runs the scenarios the systems will actually meet in production. Miss any of the three and you’re rolling the dice.

10. Build the ongoing controls before go-live, not after. Monitors, sync exception reviews, and a reconciliation between the two systems in the monthly close.

Picklists: the small thing that eats the schedule

Take picklists — the controlled value lists behind a dropdown. Your CRM has one set of values. Your ERP expects something different. Sales has been adding new options for three years because it was one click and nobody said no.

If you don’t reconcile that upfront, you create chaos downstream. Orders that don’t sync. Reports that don’t match. A weekly meeting about why two systems disagree about the same deal.

Reconciling field values is a tedious afternoon with a spreadsheet and two system admins in the room. Skipping it is a month of post-go-live triage. I have never once regretted spending the afternoon.

Duplicates, duplicate invoices, and the phone call

Unclean data leads to duplicate customers, duplicate invoices, and very angry phone calls.

I’ve watched the chain run start to finish. The same customer exists twice in the system under slightly different names — a legal entity and a DBA, or one with a comma and one without. The integration has no idea they’re the same company, so it treats them as two. It runs. Now there are two invoices going to one client.

They call, furious, wondering why they’re being charged twice.

Your finance team scrambles to issue credits and rebill. Your credit and collections data is wrong until someone untangles it. And your credibility with that customer takes a hit that no amount of clean architecture buys back.

That entire sequence was determined weeks earlier, by a decision not to deduplicate.

The integration that “works” and still loses money

Sequencing failures announce themselves. Drift doesn’t.

During a business process review, we compared a client’s AP aging in their ERP against the same report in the connected AP application. The two didn’t tie — by over a million dollars in unsynced transactions. They had no idea. Nobody had ever told them to monitor for synchronization issues or to reconcile the two agings, so exceptions had quietly accumulated for years.

This is why two controls belong in the monthly close for any company running an integrated AP solution: review the synchronization exception report, and reconcile the AP aging between the two systems. They take minutes. They catch the problem while it is still a variance and not a restatement.

An integration is not a project you finish. It is a piece of your control environment.

The ruling

Enter data once, then let it flow downstream. That only works if the data is clean, the owner of each record is decided, and the sequence is written down before anyone starts building.

So do the unglamorous part first. Profile both sides. Kill the duplicates. Reconcile the values. Map the required fields. Sequence the objects by what references what, and prove it in test before you prove it in production.

Get your data right, or nothing else matters.