The problem is not the absence of an MDM tool
A customer has three account numbers. Finance recognizes the legal entity, sales recognizes a trading name, and support recognizes the email domain. A product appears under different SKUs in an ERP, an ecommerce storefront and a warehouse system. A supplier changes its bank details in one application, but another still holds the old information. Each system may be working as designed, yet the company cannot reliably answer a basic question: which records describe the same real-world thing?
Master data management (MDM) addresses that problem. It establishes trustworthy identities, definitions, ownership and change controls for relatively stable business entities such as customers, products, suppliers, locations and employees. The purpose is not to create one enormous database. It is to make critical information consistent enough that operational processes and management reports can depend on it.
A growing company can make substantial progress before purchasing a dedicated MDM platform. The first investments should be in decisions: which domain matters, who is accountable, what identifier is authoritative, how duplicates are resolved, and how exceptions reach a person empowered to act. Software can automate those decisions later. It cannot make them on the business’s behalf.
Start with one costly business failure, not an enterprise-wide cleanup
A company may have thousands of inconsistent fields, but only a small subset creates immediate operational or financial harm. Start by identifying a process that fails when master data is wrong. Examples include duplicate customer accounts inflating pipeline reporting, inconsistent product identifiers causing fulfillment errors, suppliers receiving duplicate payments, or regional sales reports counting the same parent company several times. Select a use case with a measurable owner and a bounded set of systems.
For each candidate, record the affected decision or workflow, the source systems, the approximate number of records, the frequency of failures, the cost of manual correction and the business owner. Avoid claiming a monetary benefit until a baseline is measured. A narrow customer identity initiative that improves invoice reconciliation is more useful than an ambitious ‘single source of truth’ program with no defined consumer.
A practical prioritization score
Score each domain from one to five on business impact, frequency of cross-system use, severity of errors, availability of an accountable owner and implementation feasibility. Weight business impact and cross-system use more heavily than record volume. A high-volume product catalog with stable identifiers may be less urgent than a smaller supplier file that drives payment controls. Revisit the ranking quarterly because acquisitions, new channels and system changes alter the risk.
The phrase ‘system of record’ often hides a difficult truth: one application is rarely authoritative for every attribute. CRM may own account owner and sales territory. ERP may own invoicing entity and tax registration. A customer portal may own preferred contact details. The correct design documents ownership for each critical field, not just for each table.
Create a simple source-of-authority matrix with columns for entity, attribute, authoritative source, permitted editor, update frequency, downstream consumers and conflict-resolution rule. For a customer record, the legal name might come from an approved onboarding process, billing address from ERP, account relationship from CRM and marketing consent from a consent-management system. If two sources disagree, the rule should be explicit and reviewable.
Distinguish operational authority from analytical consolidation. An analytics warehouse may assemble a trusted customer view without becoming the place where salespeople edit customer details. The warehouse can be the governed reporting reference while the operational systems retain write ownership. This avoids creating a second unofficial CRM.
Choose identifiers before attempting clever matching
Reliable master data depends on stable identifiers. A CRM account ID, ERP customer number and ecommerce customer ID each identify a record in its own system. None automatically identifies the real-world customer across all systems. Maintain an identity crosswalk that maps each source-system key to a durable internal master ID. Preserve source names and original keys so every match can be traced and reversed.
A minimal crosswalk might contain master_entity_id, source_system, source_entity_id, relationship_type, match_method, match_confidence, valid_from, valid_to and review_status. Enforce uniqueness on the combination of source_system and source_entity_id for active mappings. Where the business supports multiple roles or relationships, model them explicitly instead of forcing everything into one customer row.
Do not use email addresses, phone numbers, product descriptions or company names as permanent keys. They change, can be shared and may not be unique. External identifiers such as tax numbers can be strong evidence where legally appropriate, but even those require jurisdiction-aware validation and handling of branches, subsidiaries and corrections.
Two subsidiaries under one parent are not necessarily duplicates. A customer that is also a supplier may need two role-specific records linked to the same legal organization. A household and its individual members are distinct entities. Model parent-child and role relationships separately from identity equivalence. Incorrectly merging related but distinct records can be more damaging than leaving a possible duplicate unresolved.
Standardize data, then match conservatively
Standardization is the inexpensive part of the work and should precede matching. Normalize whitespace, punctuation, case and phone formats; validate country codes and postal codes; separate legal names from display names; and preserve the original values. For products, standardize units of measure, manufacturer part numbers, packaging quantities and category codes. For suppliers, normalize legal identifiers and payment-related attributes under appropriate access controls.
Use deterministic matching first where reliable keys exist. For example, an exact verified external ID within the same jurisdiction may be strong evidence, provided the data model allows for branches and historical changes. For less reliable fields, calculate candidate similarities using multiple signals such as normalized name, address, domain and contact details. A match score is evidence for a decision, not the decision itself.
A workable policy uses three bands: automatically link only when evidence is exceptionally strong and business risk is low; send ambiguous pairs to human review; keep weak candidates separate. Thresholds must be calibrated against a labeled sample from the company’s own data. A universal ‘95% confidence’ rule is not defensible because false merges and missed duplicates have different consequences across domains.
Measure the errors that matter
For a reviewed sample, calculate precision (the share of proposed matches that were correct) and recall (the share of true matches that were found). In a supplier-payment workflow, prioritize precision because a false merge could redirect approvals or combine unrelated accounts. In marketing audience deduplication, a different tradeoff may be acceptable. Track false merges and missed duplicates separately, and require a tested unmerge procedure before enabling automated merges.
Create a golden record without creating a second operational system
A golden record is a curated representation of an entity, not necessarily a replacement for every source record. It combines accepted identity links with selected authoritative attributes and documented survivorship rules. Microsoft describes master data assets as consolidated, standardized golden records within its governance approach. The important design choice is how those records are governed and consumed, not whether a particular vendor’s platform produces them.
For each field, define survivorship: source priority, most recently verified value, most recent valid timestamp, or manual approval. ‘Latest value wins’ is dangerous when clocks differ, integrations replay old events, or a low-trust source overwrites a verified field. Keep source lineage and the reason a value was selected. Store effective dates when historical reporting must reconstruct what was believed at a particular point in time.
The first implementation can be a small set of versioned tables and transformations in an existing database or warehouse: source snapshots, standardized records, identity crosswalk, mastered entities, field provenance and exception queue. This is often enough to support trusted reporting. A lightweight review interface may be necessary if stewards must resolve frequent ambiguities; spreadsheets can work briefly for controlled pilots, but become risky when they lack access controls, audit trails and concurrent-edit handling.
Put a human owner behind every exception
MDM fails when unresolved conflicts are merely logged. Assign a business data owner to set policy and a data steward to resolve individual cases. IT or data engineering should operate the pipeline, tests and audit trail, but should not silently decide which legal customer name or supplier bank account is correct. For high-risk attributes, require a second approval and preserve evidence of the decision.
An exception record should show the entity, affected systems, conflicting values, reason for the alert, supporting evidence, priority, assigned steward, due date, decision, approver and downstream action. Typical cases include possible duplicate, conflicting authoritative values, missing mandatory identifiers, unexpected hierarchy changes and failed synchronization. Give stewards clear actions: accept match, reject match, request source correction, defer with reason or escalate.
Set service-level expectations by consequence rather than by data volume. A conflict that blocks invoice posting may need same-day attention; a suspected duplicate in a low-risk reporting segment may wait for weekly review. Track queue age, reopened decisions and the proportion of exceptions resolved at the source. A growing exception backlog is a signal that upstream capture rules or ownership need improvement.
Integrate changes without creating synchronization loops
After records are mastered, decide where corrections should flow. Reporting-only MDM may publish a governed dimension to the warehouse while leaving source applications untouched. Operational MDM may write approved corrections back to CRM, ERP or other systems. The second approach has higher value in some workflows but requires careful API permissions, field-level ownership, conflict detection and rollback.
Use idempotent upserts keyed by source IDs and master IDs; capture change timestamps and deletion signals; distinguish source corrections from mastered outputs; and prevent two systems from repeatedly overwriting each other. If source A owns billing address, source B should not be able to push its stale copy back as authoritative. For asynchronous updates, record an event or change ID so retries do not create duplicate side effects.
This is where the underlying integration design matters. Organizations already using custom APIs and databases can often extend their existing stack rather than introduce a new MDM suite immediately. Actiknow’s system integration and database work covers the adjacent engineering capabilities needed to connect applications and manage data flow, although that service description should not be mistaken for a claim that a packaged MDM product is offered.
Build data quality controls into the pipeline
A one-time deduplication project deteriorates quickly if new records arrive without checks. Introduce tests at ingestion, standardization, matching and publication. Examples include uniqueness of active source keys, valid identifier formats, referential integrity for parent relationships, completeness of required fields, prohibited nulls in mastered keys, unexpected record-count changes and invalid state transitions.
Publish a compact scorecard: percentage of records with a master ID; duplicate-candidate rate; confirmed false-merge rate; completeness of critical attributes; time to resolve exceptions; number of downstream reconciliation breaks; and percentage of changes with source lineage. Show trends by domain and system. A falling duplicate rate can be misleading if matching has stopped finding candidates, so pair volume metrics with sampled quality checks.
Tie these controls to business intelligence services only after the identity rules are stable. A beautiful dashboard cannot repair inconsistent definitions of ‘customer’ or ‘active supplier.’ The value of a mastered dimension is that finance, sales and operations can use consistent keys while still retaining role-specific metrics and reporting views.
A reference architecture for a mid-size business
The smallest useful architecture has six layers. First, extract the required customer, product or supplier records from each source without destroying original identifiers. Second, stage immutable or versioned source snapshots. Third, apply deterministic standardization and validation. Fourth, maintain a match-candidate table and a reviewed identity crosswalk. Fifth, build mastered entities with attribute provenance. Sixth, publish governed outputs to the warehouse, applications or APIs that actually need them.
Keep business rules in version-controlled SQL or application code with tests. Separate development and production, restrict access to sensitive fields, log manual overrides and protect the master ID from accidental reassignment. Make processing restartable: if a nightly job fails halfway through, the next run should not double-count records or lose steward decisions. For larger volumes, incremental change capture can replace full refreshes, but do not add streaming complexity without a real freshness requirement.
Example: customer identity across CRM and ERP
Consider a hypothetical company with a CRM account ‘Northstar Components’ and an ERP billing customer ‘Northstar Components Ltd.’ Sales wants account-level pipeline; finance wants invoice totals by legal entity. The team first maps both source keys to an internal organization ID after reviewing registration and address evidence. It retains the CRM owner and ERP billing status as separate authoritative attributes. Reporting joins opportunities and invoices through the crosswalk, while source systems continue to own their respective workflows.
If the ERP later creates a second billing account for a new legal subsidiary, the process does not automatically merge it with the parent. Instead, the new entity receives its own master ID and a parent relationship. The example illustrates a design pattern, not an Actiknow client engagement or a claim about achieved results.
What to deliver in the first 60 days
Days 1-10: Define the decision and baseline
Choose one domain and two or three source systems. Interview the operational owner, finance or compliance stakeholders and the reporting consumers. Record current duplicate counts, exception workload, reconciliation failures and the decisions affected. Agree on a definition of success and a short list of critical attributes. Deliver an entity glossary, a prioritized problem statement and a named business owner.
Days 11-20: Inventory keys and ownership
Profile representative records and identify missing keys, format variations, one-to-many relationships and historical changes. Build the source-of-authority matrix. Decide which fields may be changed automatically, which require approval and which must remain untouched. Deliver an identity schema, a draft crosswalk and a written match policy with examples of safe and unsafe merges.
Days 21-35: Build and test the first mastered view
Implement standardized staging tables, candidate matching, review decisions and the first golden-record view. Create a labeled validation set covering exact matches, near matches, subsidiaries, shared contacts and intentionally different entities. Test false merges, missed matches and unmerge behavior. Deliver a reproducible pipeline, test results and an initial quality scorecard.
Days 36-50: Run stewardship and reconciliation
Route ambiguous cases to the designated steward. Compare key business totals between existing reports and the mastered view. Investigate material variances rather than smoothing them away. Add audit history, monitoring, retries and access controls. Deliver a working exception queue, ownership instructions and a documented reconciliation process.
Days 51-60: Publish narrowly and decide what scales
Release the mastered view to one reporting or operational consumer. Monitor quality and manual effort. Estimate ongoing stewardship workload and integration maintenance. Review whether a dedicated MDM platform would reduce total cost or address a capability gap. Deliver a go/no-go recommendation for expansion, a backlog and a clear operating model. Sixty days is a planning framework, not a guaranteed implementation duration; scope and data quality determine the actual schedule.
When does a dedicated MDM platform become worth it?
A lightweight approach is attractive when there are few domains, a manageable number of systems, moderate matching complexity and a clear reporting-first use case. It becomes harder to sustain when hundreds of applications need synchronized master data, entity hierarchies change constantly, stewardship teams are large, compliance requires extensive controls, or matching rules are too complex for a small engineering team to maintain safely.
Evaluate the full operating cost, not just licenses. Include implementation, connectors, data migration, identity modeling, stewardship configuration, user training, support, security reviews and vendor dependence. Compare those costs with the maintenance and risk of custom matching code. A platform can improve workflows and governance, but still requires business ownership, field definitions and source corrections.
Useful procurement questions include: Can it model our actual legal-entity and product hierarchies? Can stewards explain and reverse matches? Does it support field-level survivorship and provenance? Can it publish changes without loops? How are access controls and approvals enforced? Can we export crosswalks and decision history? What happens when source identifiers are retired? Test these with representative data before committing.
Common mistakes that make an MDM initiative expensive
Mistake one is trying to clean every domain at once. Mistake two is treating the warehouse as an automatic replacement for operational ownership. Mistake three is merging records on name similarity alone. Mistake four is failing to distinguish a subsidiary from a duplicate. Mistake five is using ‘latest update wins’ without provenance. Mistake six is building a review queue without stewards or response targets. Mistake seven is measuring deduplication volume rather than correctness and business outcomes.
The more subtle mistake is declaring success after a single cleanup. Master data is a process. New integrations, employee turnover, acquisitions, product launches and changing regulations will create fresh conflicts. The lasting deliverable is a repeatable decision system: definitions, controls, monitoring, accountable people and reversible changes.
Executive scorecard: what leadership should review monthly
Review five questions. Are critical entities consistently identified across the systems used by the chosen workflow? Are incorrect merges rare and quickly reversible? Are unresolved exceptions shrinking relative to new cases? Are key business reports reconciling to approved sources? Is manual correction time falling without moving hidden work to another team? Attach owners and targets to each measure after the baseline is established.
The board or executive team does not need to inspect every match rule. It does need evidence that the mastered data improves a real process and that no uncontrolled synchronization can corrupt financial, customer or supplier records. A simple monthly one-page scorecard is more useful than a large catalog of technical data quality indicators.
Frequently asked questions
What is master data management for a mid-size business?
It is the practice of defining, identifying, governing and maintaining important shared entities across systems. The initial scope can be small: one domain, a few source applications, a crosswalk, explicit ownership rules and a repeatable exception process.
Can we do MDM using SQL and an existing data warehouse?
Yes, for many reporting-oriented use cases. SQL transformations, versioned tables, data quality tests and a governed identity crosswalk can provide a reliable mastered analytical view. Complex operational synchronization, stewardship or matching may require additional applications or a dedicated platform.
What is the difference between a master record and a golden record?
A source master record is an authoritative record within a particular operational system. A golden record is a consolidated, governed representation assembled from multiple sources according to identity and survivorship rules. The golden record does not automatically replace source-system ownership.
Should CRM or ERP be our single source of truth?
Usually neither for every field. CRM may own sales attributes while ERP owns financial attributes. Document the authoritative source at the attribute level and use an identity crosswalk to connect the records.
How do we prevent accidental customer merges?
Use strong identifiers where available, conservative matching thresholds, manual review for ambiguous cases, sampled precision checks, decision audit trails and a tested unmerge process. Never treat a shared email domain or similar company name as conclusive proof.
How much data is enough for a pilot?
Choose a representative sample that includes common records and difficult exceptions. The right size depends on diversity and risk, not a universal record count. Validate matching on labeled examples and reconcile outputs against operational totals before expanding.
Does MDM replace data governance?
No. MDM is one operational expression of data governance. Policies, owners, definitions, access rights, retention and accountability remain necessary whether mastering runs in custom pipelines or a commercial platform.
When should we buy MDM software?
Consider a platform when multi-domain complexity, scale, steward workload, audit needs or operational synchronization exceed what the existing team can support reliably. Validate the decision with a scoped proof of concept and total-cost comparison.
The next decision
Do not begin with an MDM procurement shortlist. Begin with a 30-minute discussion between the business owner, a data engineer and the person responsible for the downstream decision. Choose one entity, identify its competing source keys and write down the rule for resolving the most common conflict. That exercise will reveal whether the next investment should be data cleanup, integration engineering, a stewardship workflow or a platform.
Actiknow works on custom application integrations, databases and business intelligence. If your team needs help designing the first cross-system identity model or a governed reporting layer, talk with Actiknow about a narrowly scoped discovery exercise. The goal should be a testable operating model and a measurable use case, not a premature software commitment.
Further reading
- Microsoft Learn: Master data management in Microsoft Purview. A useful overview of golden records and governance integration.
- IBM Redbooks: Aligning MDM and BPM for Master Data Governance, Stewardship, and Enterprise Processes. A deeper reference on stewardship, governance policies and exception workflows.