Direct answer
Mapping data across ERP, CRM, spreadsheets and operational databases requires more than connecting systems. Profile the data, identify candidate keys, validate entity matches and record the evidence and confidence behind each relationship.
The hard problem is often business identity, not connectivity
Connecting two systems can be straightforward when they expose usable interfaces. Understanding whether a supplier record in one system refers to the same organisation in another is more difficult.
Different applications may use different identifiers, naming conventions, date formats and definitions. A supplier might be represented by a legal name in an ERP, a short name in a spreadsheet and a local code in a procurement application. Joining records on a convenient text field can silently produce missing or incorrect relationships.
Begin with evidence about the data
Profile the source fields before choosing a matching strategy. Inspect uniqueness, null rates, formats, duplicates, value distributions and relationships already enforced by the source systems.
| Signal | What it may indicate | What to do next |
|---|---|---|
| High uniqueness in a stable identifier | Possible candidate key | Validate its meaning and scope |
| Repeated names with different identifiers | Possible duplicate entities or legitimate branches | Check domain rules and supporting attributes |
| Many null foreign keys | Incomplete capture or weak integration | Identify the system responsible for the relationship |
| Different identifiers for apparently identical entities | Cross-system identity problem | Establish matching rules and review samples |
| One record matches many others unexpectedly | Incorrect join or different entity granularity | Inspect cardinality and business semantics |
| Relationships change over time | Historical or effective-dated relationship | Model validity periods rather than overwriting history |
Build relationships in stages
- Start
Inventory sources and schemas
Profile fields and data quality
Identify candidate keys and business entities
Generate candidate relationships
Score and validate matches
- Decision
Confidence and business rules sufficient?
- No→ step 7: Human review or improved rules
- Yes→ step 8: Record relationship and provenance
Human review or improved rules
↺ repeat from step 5
Record relationship and provenance
Expose through governed analytics
- Outcome
Monitor drift and unmatched records
Do not confuse a statistical match with a business fact
Matching algorithms can generate candidates using identifiers, names, addresses and other attributes. The correct threshold depends on the consequences of a false match. Merging two supplier identities incorrectly can be more damaging than leaving a relationship unresolved.
Store provenance: which sources contributed to the relationship, which rule or method proposed it, when it was validated and whether a human approved it. Where the relationship can change over time, retain the relevant history.
Make relationship quality observable
Track unmatched records, ambiguous matches, unexpected cardinality, duplicate rates and changes in key distributions. Re-run quality checks when source systems change. A relationship model is not finished merely because the initial join returns rows.
The Neuromotion perspective: trustworthy cross-system intelligence requires a semantic understanding of entities and relationships, not just pipelines that move data.
Next step: Select one business question that currently requires manual reconciliation. Trace the entities and identifiers involved across every source before designing the integration.