When Your Data Sources Disagree: Designing a Database That Preserves the Truth
A company is preparing to replace three old systems with one new database.
The CRM says customer 1842 is called Northstar Labs. The billing system says Northstar Laboratories Ltd. A spreadsheet maintained by operations calls the same company North Star Lab.
The addresses do not match either.
The CRM has London. Billing has Manchester. The spreadsheet contains both addresses in different rows.
Then someone asks what sounds like a simple question:
Which record should we put in the new database?
This is where database design stops being an exercise in choosing tables and relationships. Before creating the final database schema, someone has to investigate what the existing data actually means.
When multiple datasets disagree, immediately choosing one as the “source of truth” can destroy useful information. A better approach is to model the disagreement itself, preserve where values came from, and only then decide what the application should treat as canonical.
This is an increasingly important part of database engineering, especially during migrations, integrations, acquisitions, CRM replacements, data warehouse projects, and legacy system modernization.
The First Mistake: Assuming Duplicate Records Are Bad Data
Consider these three source records:
CRM: customer_id=1842, name="Northstar Labs", city="London"
Billing: account_id=B-991, name="Northstar Laboratories Ltd.", city="Manchester"
Operations: client_code=NSL, name="North Star Lab", city="London"
A quick cleanup process might normalize the company names, notice their similarity, merge the rows, and keep whichever address appears most frequently.
That feels efficient. It may also be wrong.
Perhaps London is the operational office while Manchester is the registered billing address. Perhaps the CRM record is three years old. Perhaps the billing record represents a legal entity while operations tracks a physical site.
The apparent inconsistency may not be a data-quality problem at all. It may reveal a distinction the old database architecture never modeled explicitly.
This gives us an important investigative principle:
When data conflicts, first ask what hidden business concept could make both values correct.
Database research often begins with contradictions because contradictions reveal assumptions.
Profile the Data Before Designing the Canonical Schema
When consolidating datasets, resist the temptation to begin by drawing the final ER diagram.
Start by studying the records.
Suppose three systems contain customer information. Before deciding what a customer table should contain, examine questions such as:
- How many records exist in each source?
- Which identifiers are unique?
- Which fields are frequently empty?
- Which values disagree between sources?
- How often do names change while tax IDs remain stable?
- Do addresses represent headquarters, billing locations, delivery sites, or something else?
- Which system updates each field?
- How old are the records?
This is data profiling, but its purpose is larger than finding malformed values. It helps reveal the domain your database schema must represent.
Imagine that 38% of customers have different CRM and billing addresses. That is unlikely to be random corruption. It suggests that one address column on a customer table may be an inadequate data model.
You may actually need:
customer
address
customer_address
address_type
with classifications such as billing, shipping, headquarters, branch, and registered office.
The conflicting source data has taught you something about the correct schema design.
Separate Source Records From Canonical Entities
One of the most useful patterns when merging databases is to avoid treating imported records as if they were immediately trustworthy canonical records.
Instead, model two layers.
The first represents what each external system actually said.
The second represents what your application currently believes.
A simplified model might include:
customer
source_system
source_customer
For example:
customer(id=72, canonical_name="Northstar Laboratories Ltd.")
source_system(id=1, name="CRM")
source_system(id=2, name="Billing")
source_customer(customer_id=72, source_system_id=1, external_id="1842", source_name="Northstar Labs")
source_customer(customer_id=72, source_system_id=2, external_id="B-991", source_name="Northstar Laboratories Ltd.")
This model makes an important distinction.
The source record is evidence. The canonical entity is an interpretation of that evidence.
That separation becomes extremely valuable when a merge decision later turns out to be incorrect.
If raw source identities have been overwritten, reversing the decision can be painful. If source records and mappings are preserved, the database can be corrected without pretending the original data never existed.
Do Not Ask Only “Which System Wins?”
Teams frequently create rules like:
Billing is authoritative.
That rule is convenient but usually too broad.
A system may be authoritative for one attribute and unreliable for another.
Billing might be trusted for:
- legal company name
- tax identifier
- billing address
- payment status
The CRM might be trusted for:
- sales owner
- industry classification
- lead source
- relationship notes
An operations system might be trusted for active delivery locations.
So the real question is not:
Which database is the source of truth?
It is:
Which source should be trusted for this specific fact, under these conditions?
That leads to much more precise database architecture.
Preserve Provenance When It Matters
Suppose the final customer record contains:
legal_name = "Northstar Laboratories Ltd."
Six months later, someone asks why that value was chosen.
If the only answer is “that is what is currently in the database,” the history of the decision has disappeared.
For important data, consider storing provenance: information about where a value originated.
This might be represented with fields such as:
source_system_id
source_record_id
observed_at
imported_at
More sophisticated systems may keep field-level observations:
customer_attribute_observation
with fields resembling:
customer_id
attribute_name
observed_value
source_system_id
observed_at
This should not automatically become the design for every application. Field-level provenance adds complexity and can make querying harder.
But it is useful where disagreements matter: scientific datasets, financial systems, regulatory records, identity resolution, research databases, master data platforms, and systems combining information from many external providers.
Challenge the Assumption That Every Conflict Must Be Resolved
A surprisingly dangerous requirement is:
We need one clean value for every field.
Sometimes the database should admit uncertainty.
Imagine two research datasets describe the date a historical specimen was collected:
Dataset A: 1998-04-12
Dataset B: 1998-04-17
Choosing one date simply because Dataset A has higher priority may create a false fact.
The correct database design may preserve both observations and record that the canonical date remains unresolved.
This distinction matters because a clean-looking database is not necessarily an accurate database.
Consistency can be manufactured by deleting evidence.
Good data modeling sometimes means representing disagreement explicitly.
Entity Matching Is a Database Design Problem Too
Before conflicting values can be reconciled, another question must be answered:
Do these records actually describe the same entity?
Names alone are weak identifiers.
Consider:
Acme Services Ltd.
ACME Services
Acme Service UK
They might be one company, related companies, or completely different organizations.
Investigate stronger evidence:
- tax identifiers
- registration numbers
- verified email domains
- phone numbers
- addresses
- external account identifiers
- parent-company relationships
Do not hide entity matching inside an irreversible import script.
If matching is uncertain, consider storing the mapping decision explicitly. A model might contain a relationship such as:
source_customer_match
with:
source_customer_id
customer_id
match_method
confidence
review_status
This lets automated matching coexist with human review.
It also makes the system easier to investigate when someone asks why two accounts were merged.
A Weak Migration Approach—and What It Destroys
A common migration process looks like this:
- Export all old systems.
- Standardize column names.
- Remove duplicates.
- Choose preferred values.
- Import one clean dataset.
- Delete the intermediate files.
The final database may look excellent.
But the process has potentially destroyed:
- original identifiers
- conflicting values
- record provenance
- evidence used for matching
- information about when values were observed
- the ability to reverse incorrect merges
A better migration treats transformation as a traceable process.
Keep the original source records, or at minimum retain immutable import snapshots. Map them to canonical entities. Document the rules that select preferred values. Record unresolved conflicts instead of silently discarding them.
This may require several additional tables, but those tables often prevent months of confusion later.
Draw the ER Diagram Around Evidence, Not Just the Final Screen
Application interfaces encourage developers to think in final-state objects: one customer, one name, one address, one status.
Real data is usually messier.
When modeling an integration or migration, an ER diagram should capture not only what the application displays but also how the database knows what it knows.
A useful conceptual model might contain:
Customer → Source Customer → Source System
Customer → Customer Address → Address
Customer → Attribute Observation → Source System
Source Customer → Match Decision → Customer
You can sketch these relationships with an ER diagram modeling tool before committing to implementation details.
The exercise often exposes questions that flat migration spreadsheets hide.
For example: Is an external record permanently linked to one canonical customer? Can a mapping be corrected? Can two source accounts represent one customer? Can one source account represent several business entities?
Those are data modeling questions, not cleanup-script details.
Create Explicit Survivorship Rules
When multiple sources provide competing values, the logic used to select the canonical value is sometimes called a survivorship rule.
Instead of an undocumented rule like “take the latest non-empty value,” make the decision deliberate.
A legal name might follow:
verified registry > billing system > CRM > manual import
A phone number might use recency:
most recently verified number
A customer status might not use precedence at all because each system defines “active” differently.
This last case is especially important.
If two systems use the same field name but mean different things, reconciliation is the wrong solution. The schema may need two separate concepts.
For example:
sales_relationship_status
and:
billing_account_status
What looked like conflicting data was actually overloaded terminology.
A Practical Investigation Workflow
When you face conflicting datasets, begin with a small sample rather than trying to solve the entire migration theoretically.
- Select 50–100 entities that appear in multiple systems.
- Place their attributes side by side without resolving anything yet.
- Mark disagreements in names, identifiers, addresses, classifications, and status values.
- Investigate why each disagreement exists.
- Separate genuine errors from legitimate differences.
- Identify which source owns which business facts.
- Model concepts that were previously collapsed together.
- Define reversible matching and survivorship rules.
- Test the proposed database schema against unusual records.
This process turns database research into evidence for schema design.
It is often more productive than starting from a blank diagram and asking stakeholders what fields they want.
For broader modeling work, database design resources can help you think through entities and relationships, but the most valuable input will often come from the contradictions already hiding inside the source data.
The Best Database May Preserve More Mess Than You Expected
Database projects often begin with a desire to clean things up.
That is reasonable. Applications need dependable records.
But cleanup and erasure are not the same thing.
When two systems disagree, the contradiction may reveal historical change, different definitions, different business contexts, different entity boundaries, or an unresolved uncertainty.
A strong database design gives applications a useful canonical view without destroying the evidence underneath it.
So before deciding which value wins, ask a more interesting question:
Why do we have two different values in the first place?
The answer may change much more than your migration script. It may change the entire database schema.
Recent Comments