Multi-Source Data Conflicts: What To Do When Systems Disagree

Why Both Systems Can Be Right (Technically)

Every system is built to serve a specific operational purpose. A CRM records relationships, activities, and contracted commitments — it reflects the business as it was expected to perform. A billing platform records invoices sent and payments received — it reflects the business as it actually performed. A data warehouse aggregates both and tries to present a unified view for analysis. None of these systems is wrong. Each is accurate within its intended scope.

The conflict emerges at the intersection: when an analyst, a dashboard, or a board report asks a question that spans multiple scopes using a single label without specifying which measurement is intended. "Revenue" is the most common example. But "active customers," "inventory available," "tickets resolved," and "leads in pipeline" all suffer from the same structural ambiguity across systems — each system tracks a version of the concept, and the versions differ because the operational purposes differ.

This distinction carries direct operational consequences. An investigation that assumes one system is wrong responds with data correction. An investigation that recognizes both systems are accurate within their respective scopes responds with definitional alignment — agreeing on which measurement the organization wants to report, then applying that calculation consistently from source to destination. These are different interventions with different timelines, different owners, and different outcomes. Misdiagnosing one as the other adds weeks to the resolution and often produces a "fix" that generates the same question next quarter.

The Four Root Causes of Multi-Source Data Conflicts

Four architectural mechanisms produce multi-source conflicts. They require different investigations, different fixes, and different prevention strategies. Each has a specific diagnostic signature that distinguishes it from the others.

Systems close at different times. ERP month-end closes at midnight on the last calendar day. The CRM sync to the warehouse runs at 11 PM. The payment processor posts settlements on a 24-hour delay. An intra-period query captures different slices depending on which system the analyst queries and when. The number will agree once every system has caught up — but during the period when they haven't, the difference looks like a data quality problem.

The signature of temporal mismatch: systems agree at period close but disagree intra-period, or discrepancies correlate with recent high-volume transaction activity. The fix is not declaring one system authoritative. It's aligning reporting windows: defining when each metric is considered final, documenting that cadence explicitly, and building dashboards that surface the "as of" timestamp for every figure they display.

On September 23, 1999, NASA lost the Mars Climate Orbiter. The Mishap Investigation Board determined the cause: Lockheed Martin's navigation software produced thrust data in imperial units — pound-force seconds. NASA's trajectory software expected metric units — Newton-seconds. Two systems operating correctly within their own specifications, using incompatible definitions for the same physical quantity, across a single software interface that nobody had validated end-to-end.^[1] The mission cost $327.6 million. The fix wasn't rerunning the calculations. It was definitional alignment — establishing which unit system governed the interface between the two systems before either ran.

The data warehouse equivalent is structural and constant. "Revenue" as booked, recognized, or collected. "Customers" as total accounts, active accounts, or paying accounts within the last 90 days. Three legitimate business concepts sharing a label. The signature of definitional divergence: the discrepancy is stable, consistent, and roughly proportional over time. It was always there. Nobody noticed, or nobody agreed on which definition should govern shared reporting. The fix is a shared data dictionary with canonical definitions, enforced at the transformation layer before data reaches any shared view.

When a pipeline applies transformation rules between source and destination, it encodes assumptions about the data. If that logic is misconfigured, updated incorrectly after a source schema change, or applied differently across two pipelines drawing from the same source, two downstream views of the same entity will disagree — and neither source system will look wrong. The silent data quality failure pattern applies directly here: transformation logic can produce wrong output without surfacing an error, meaning the pipeline completes successfully while the output diverges from what the business expects.

The diagnostic signature: the conflict appeared after a pipeline deployment or configuration change, or different dashboards that claim to draw from the same source produce different numbers. The fix is transformation lineage auditing — comparing the transformation rules applied in each pipeline, checking pre-transform and post-transform record counts, and identifying where the logic diverges from the canonical definition.

When a connector between a source system and a destination silently stops propagating data — a broken sync job, an expired authentication token, a schema change that causes the connector to skip records — the destination serves stale values while the source has moved on. No error fires. Reports continue displaying last week's state as if it's current. The discrepancy grows with every passing hour until someone notices the downstream number and upstream source have diverged.

The diagnostic signature: a discrepancy that appeared suddenly after a period of consistent agreement. Not a gradual drift — a step change. The fix is mechanical: check connector health, verify the last successful sync timestamp against what the destination is currently serving, and determine at what point in the pipeline the propagation stopped. Connection health monitoring with visible sync timestamps prevents this from accumulating undetected — which is the architectural response addressed in Section 5.

Where "Single Source of Truth" Advice Falls Short

"Single source of truth" is correct as an organizational principle. Data-driven decisions should trace to a canonical record that the people making those decisions have agreed upon. The principle doesn't fail. The implementation advice that accompanies it — pick one system as authoritative, overwrite the others, call it done — fails consistently, because it mistakes a definitional problem for a data problem.

A reconciliation project that selects the CRM as the authoritative system and overwrites billing platform records hasn't resolved a definitional conflict. It has suppressed one measurement and relabeled the other as correct. Three months later, when the collections team asks why their cash figure doesn't match the revenue line in the board report, the project restarts. The underlying conflict was never addressed because nobody agreed on what "revenue" means before deciding which system measures it correctly.

The U.S. Government Accountability Office's 2020 review of federal agency data quality practices found a consistent failure pattern in agencies with persistent cross-system discrepancies: they applied correction at the reporting layer without addressing definitional or architectural gaps at the source, producing recurring reconciliation cycles rather than durable resolution.^[3] The dynamics in commercial data stacks are structurally identical.

What "single source of truth" requires as an implementation path is three things in sequence: first, a canonical definition for every shared metric — agreed upon by the teams who use the number and documented in a shared data dictionary; second, a transformation layer that applies that definition consistently regardless of which source system the data came from; third, a validation mechanism that catches deviations from the canonical output before they reach the reporting surface. That sequence is a data architecture task. It begins with definitional agreement, not with declaring a winning system.

A Resolution Framework Matched to Root Cause

The resolution strategy has to match the root cause. Applying the wrong strategy to the wrong cause creates either a temporary fix that breaks or a false consensus that persists until someone asks a hard enough question. The framework below maps each cause to its diagnostic signature, the intervention that fails when misapplied, and the intervention that actually closes the conflict.

Two of the four root causes are addressable at the data integration layer, before they reach any analyst or reporting surface. Data integration architecture that applies canonical transformation rules consistently across all connected sources — rather than permitting each pipeline to encode its own interpretation of a shared metric — eliminates definitional divergence and transformation logic divergence at the point where data enters the shared environment. Integration failure is detectable through connection health monitoring: sync timestamps and connector status visible in one place, surfacing stale connections before downstream consumers serve wrong numbers.

Prevention at the Pipeline Architecture Layer

Reactive reconciliation finds conflicts after they've propagated to reports and decisions. Three architectural properties prevent the most common causes from accumulating undetected — and the difference between having them and not having them is the difference between catching a problem at the pipeline boundary and discovering it in a Monday morning meeting.

When different pipelines apply different transformation logic to the same underlying metric, definitional divergence compounds with every pipeline added. One pipeline calculates revenue as booked; another calculates it as recognized. Both feed the same warehouse table. The conflict isn't detectable at the source — it's encoded in the pipeline logic itself. A shared data quality and transformation layer — version-controlled, applied consistently to all sources before any data reaches a shared destination — eliminates this mechanism by design. There's no opportunity for divergent logic when every source passes through the same transformation rules.

Integration failure is invisible to report consumers until the discrepancy grows large enough to notice. By then, decisions have already been made on stale data. Monitoring that surfaces the last successful sync timestamp for each source connection alongside current connector status catches integration failures at the pipeline boundary — where the cost of investigation is an hour of engineering time rather than an afternoon of root cause analysis while business stakeholders wait for a number they can trust.

Transformation logic divergence and silent truncation both produce anomalous record counts relative to historical baselines. A validation mechanism that compares each run's output count against the distribution of prior runs flags the anomaly at load time — before it propagates to downstream joins, aggregations, and the metrics that feed reports. This is the circuit breaker architecture described in depth in the piece on silent data quality pipeline failures. It doesn't require perfect foreknowledge of expected output. It requires that the system remembers what previous runs looked like and treats material deviation as a signal worth investigating before propagating.

All three properties share a structural characteristic: they operate at the pipeline layer, before data reaches any consumer. Building audit-ready data operations requires that canonical definitions and validation are architectural properties of the pipeline — not manual steps in a reconciliation workflow that runs after problems have already surfaced upstream.

Frequently Asked Questions