Breaking Down Data Silos: The Case for Systems Integration

Four Shapes, and What Separates Them

"Make the systems share data" describes at least four different builds, and they do not cost, fail, or age alike. The criterion that separates them is direction of writes: how many systems are allowed to change a given record, and what happens to a record in flight when one end is unavailable. Tooling is a secondary question, and the answer to it usually falls out of this one.

A build that only reads can be thrown away and rebuilt without consequence. A build that writes into a system of record has to be correct on the first attempt and on every retry, because a duplicate order or a double-posted invoice is a business event, not a log line. That difference, not the number of systems involved, decides how much design and testing a connection earns.

Four shapes cover almost everything:

• One-way push from the owning system. The CRM creates the customer, the ERP receives it, nothing flows back. The narrowest build and the easiest to reason about, because there is exactly one writer and conflicts cannot arise. It also does the least: everything downstream of the push is still somebody opening a second system to look.
• Two-way sync on a shared record. Both systems can change the same fields, so the design has to answer what happens when both change them between runs. This is where deciding which system owns a kind of data stops being enough and has to become per field: the CRM may own the contact email while the ERP owns the credit terms on the same account.
• A reporting copy that pulls from everything and writes back to nothing. It answers the "nobody can see the whole picture" complaint and it cannot corrupt a source system, so it is the safe option, and it will not stop a single person from re-keying because nothing about the entry work changes. Note that the copy still has to decide that a CRM account and an ERP customer are the same company, so the key matching problem below arrives either way.
• Process orchestration, where one workflow spans systems: quote approved in the first, order created in the second, invoice raised in the third, and a compensating step for every stage that can fail after a later stage has already committed. The most useful shape and the only one that holds state of its own, which means it needs its own monitoring and its own restart procedure rather than inheriting either from the systems it connects.

When Building the Connection Is the Wrong Move

Integration is the right answer to most of the frictions above, and there are specific situations where building the connection makes things worse. Each of them is recognizable in advance, which is the only reason to list them.

The one that catches the most projects is a contested definition. Two systems report different customer counts because two departments define an active customer differently, and no connection settles that. Wire them together and the integration becomes the visible place where the disagreement lives, so it gets blamed for a problem it only exposed. Settle the definition first, in writing, with the people who use both numbers.

Other signs the connection should wait:

• One of the systems is being replaced. If either end is on a replacement roadmap inside the year, build a scheduled export instead. Mapping work done against a system you are retiring gets paid for twice: once when you rebuild against the new one, and again during cutover, when both connections have to run against the same records at the same time.
• The volume does not justify the maintenance. A dozen records a month costs somebody a few minutes; the connection that automates it costs credential rotation, monitoring, and a re-test every time either vendor ships a release. The break-even is not a record count, it is whether anyone downstream sits blocked while waiting.
• The data is wrong at the source. Integration moves what is there. Duplicate accounts, half-filled address fields, and free-text values in a field that should have been a list become every other system's problem the moment they flow. Cleanup first is not a nicety, because the alternative is doing the same cleanup later in both systems at once.
• The handoff itself is broken. Automating a bad process makes it faster and much harder to change, since the workaround people used to apply by hand now requires a ticket against the integration. Fix the order of the steps before hard-wiring it.
• Nobody will own the exceptions. Every integration leaves a residue of records it could not handle. If no named person works that queue on a schedule, the queue is where the silo comes back, with the added disadvantage that nobody knows it is there.

Identity Is the Hard Half of Mapping

Mapping, as usually practiced, is a field exercise: this field goes to that field, this date format becomes that one. The harder half is identity. Before any field mapping matters, the two systems have to agree on which record is which, and neither was designed to answer that question about the other.

Natural keys are the tempting shortcut and all of them leak. Company name fails on "Acme Inc" against "Acme, Inc." and on the division that trades under a different name. Email fails because people change jobs and departments share inboxes. Phone numbers arrive formatted six ways. Tax ID is the strongest and is frequently empty. Any rule built on human-entered text has both a false positive rate and a false negative rate, and each costs something different: a false positive merges two customers into one, a false negative creates a second copy of one customer.

The durable answer is a cross-reference. At the moment a link is first established, write each system's record ID into a dedicated field in the other, and stop matching on text for that pair forever after. That table is the most valuable thing the integration owns, which is worth knowing before choosing a tool that keeps it somewhere you cannot export.

Three events break a cross-reference, and most builds ignore all three until the first one happens:

• Merges. Two CRM accounts merged into one leaves every ERP record pointing at an ID that no longer resolves. Decide up front whether a merge propagates automatically or lands in a queue a person works, and confirm the source system actually emits a merge event; some report it as a delete plus an update, which a naive listener reads as an orphan.
• Deletes. Most integrations carry creates and updates and nothing else, so a delete on the source leaves a record on the target that will never change again and looks current to anyone reading it. Soft deletes are worse, because the record is still returned by the API with a status flag set, and the target keeps a canceled order alive unless the mapping reads that flag specifically.
• Reparenting. A contact moved to a different account, or a site moved to a different customer, changes what a record means without changing its ID. An integration keyed on the child alone copies the field changes and misses the move, which is how tickets end up filed against the wrong company months later.

Freshness Is a Per-Flow Decision

Freshness is the requirement everyone states first and justifies last. The test is not how fresh the data could be, it is what a stale answer costs, and that cost is different for every flow. Justify timing per flow rather than setting it once for the project. Choosing real time as a project-wide default means paying the real-time operating bill on flows where a nightly run would have been indistinguishable to everyone who touches them.

Staleness bites immediately on a customer portal showing order status, on a dispatch board a coordinator is watching, and on an inventory check at the point of sale. It usually does not bite on invoice posting, commission calculation, or anything that resolves at month end. Real time is also an operational commitment and not only a design one: it means somebody owns a queue, somebody gets alerted when it backs up at night, and there is a written way to replay what failed without posting it twice.

Three models, each with a different operating bill:

• Scheduled batch. Runs on a clock, moves a set, and can be re-run. The cheapest to operate, because a failed run is repaired by running it again and the whole window is one artifact you can inspect after the fact. The tradeoff is a period in which the two systems legitimately disagree, and everyone reading either one during that window needs to know it exists.
• Event-driven by webhook. The source calls you when something changes, which gets close to real time without polling, and you inherit the vendor's delivery guarantees along with it. Most deliver at least once, so your endpoint has to be idempotent or a redelivery becomes a second record. Most also give up after a fixed number of retries, so an outage on your side longer than that window is data you never receive and have to backfill by query. Get the retry schedule and the replay window in writing before choosing this.
• Polling on a short interval. No dependency on the vendor firing an event, at the price of two constraints: the source needs a modified-since field you can trust, including for changes made by background processes, and every poll spends against an API rate limit whether or not anything changed. Multiply interval by object count by environment count and compare that against the published limit before committing, because sandbox and production usually share a ceiling.

Prove It Cheaply, and Know What You Are Locked Into

Most of what will go wrong is visible before any code is written. Export both systems to CSV and join them on the key you intend to use. The match rate that join produces is the ceiling for the entire integration. If a fifth of the accounts do not join, that is a fifth of records a person resolves by hand every cycle until something upstream changes, and it is far better to learn that before a tool is selected than after.

The second cheap test is one flow, one direction, low volume, watched by a person long enough to cover a full business cycle including a month end if the flow touches finance. Pick the flow with real volume and the smallest blast radius, contact updates rather than invoice creation. What you are testing is not whether the API responds. It is how many records per hundred land in the exception pile, whether the mapping survives records that real people typed, and whether anybody notices on the day it stops.

What locks you in later is rarely the platform contract. It is the mapping logic and the exception rules, which accumulate for years in whatever format the tool stores them in, and the cross-reference table, which is not reproducible from scratch once the text keys have drifted.

Worth answering while the answers are still free:

• Can the mapping be exported? Rules that exist only as screens in a vendor interface have to be re-derived by reading them one at a time, and nobody budgets for that when they plan a move.
• Who is allowed to change a mapping, and what does a change cost in elapsed time? A platform only a specialist can edit turns a one-field change into a scheduling problem. A platform anyone can edit turns a quiet Tuesday into an outage. Both are workable, but pick knowingly rather than discovering which one you bought.
• Who maintains it in two years, and from which pool do you hire them? Custom code needs somebody who reads that language and that codebase. A low-code platform needs somebody who knows that platform. Those are different hiring markets, and neither is interchangeable with the team you have by default.
• How long are logs kept, and can they be searched by record rather than only by run? Retention decides whether the question "why does this record look like this" is answerable six weeks later, which is when it is usually asked.
• What happens to in-flight records when the connector is switched off, and does the cross-reference table survive a migration to something else? A tool that holds retry state and identity mapping internally is holding the two things you cannot rebuild quickly.