Data cleaning, deduplication & master data management

The same customer, supplier or product exists three times in three systems with different spellings, addresses and IDs. AI-based entity resolution scores record pairs probabilistically or with embeddings, LLMs normalise messy free text and explain borderline matches, and a master data management (MDM) process merges the result into one governed golden record. Typical benefits are fewer duplicate mailings and payments, reliable reporting and a sound base for every other AI use case.

Problem & value

• Today: master data is entered by many people and imported from acquisitions, webshops and legacy tools. Rules-based cleaning (exact keys, regex, fuzzy-string matching) misses variants such as "Müller GmbH & Co. KG" vs "Mueller Gmbh", transposed fields or addresses in different formats, and creates false merges when rules are loosened.
• AI value: learned matching handles typos, abbreviations, transliteration and partial records; LLMs standardise free text (job titles, product descriptions, units) and classify records; active learning concentrates scarce steward time on borderline pairs.
• Why it matters: duplicate suppliers enable duplicate or fraudulent payments, duplicate customers distort KPIs and breach GDPR accuracy and access-request duties (a person must be found everywhere). Data quality is also a legal duty for high-risk AI training data (AI Act Art. 10) [1][2].
• Sourced benefit figures are mostly vendor case studies; measure your own duplicate rate and steward hours before and after.

Variants

• Customer / contact MDM: B2C and B2B deduplication across CRM, ERP, newsletter and shop (see AGENT-011).
• Supplier / vendor MDM: legal-entity matching with VAT ID, LEI and registers; fraud and sanctions links.
• Product / material MDM: classification, attribute normalisation, units (see DATA-010).
• Public sector: population, business and address registers, record linkage across agencies, statistics.
• Healthcare / research: patient linkage with privacy-preserving linkage (hashed Bloom-filter encodings); sensitive data.
• Analyst / data-science one-off: cleaning a spreadsheet or dataset before analysis (OpenRefine, pandas, DuckDB).
• Private use: merging contacts or photo/file duplicates on one's own devices (built into phone OS and tools).

How it works

1. sources (ERP, CRM, PIM, files)
2. profiling (null rates, formats, outliers)
3. standardisation (case, Unicode, address parsing, phone/VAT/IBAN validation, unit conversion; LLM for free text)
4. blocking (candidate pairs via keys, phonetic codes or embedding nearest-neighbours)
5. pairwise scoring (Fellegi-Sunter / gradient boosting / fine-tuned encoder / LLM judge on hard cases)
6. clustering (connected components, transitive-closure checks)
7. survivorship (best value per field: source trust, recency, completeness)
8. steward review (borderline clusters, merges with high impact)
9. golden record + cross-reference IDs
10. publish to systems, keep lineage and an unmerge option

• Blocking avoids comparing every pair (quadratic cost); embedding search in a vector index finds semantic candidates that key-based blocking misses.
• Scoring: classic probabilistic linkage (Fellegi-Sunter, as in Splink) is fast, explainable and needs few labels, even unsupervised via EM. Supervised models (gradient boosting, DITTO-style fine-tuned transformers) win on messy text. LLMs are used as a judge for the hard 1-5% of pairs and to write a one-line justification for the steward, not for all-pairs comparison (cost, latency).
• Human-in-the-loop: thresholds define auto-merge / review / no-match bands; merges of regulated or high-value records (suppliers with bank data, patients) need approval; every merge is reversible.

Data preparation

This use case is data preparation itself, but it also needs preparation. Customer, supplier and product records live in the ERP, CRM, shop system, accounting, Excel lists on file shares and sometimes paper forms. Typical gaps are spelling variants, missing VAT IDs, old addresses, duplicates created by imports, inconsistent units, and no owner for the data.

Preparation steps: (1) inventory all systems and shadow lists that hold master data, see PREP-001, PREP-003 and PREP-005; (2) name a data steward per domain, see PREP-004; (3) define target fields, formats and survivorship rules (which source wins), see PREP-012; (4) export read-only copies and pseudonymise test sets, see PREP-022; (5) label 200-500 record pairs as match or no match to tune and evaluate the matcher, see PREP-018 and PREP-020; (6) check the purpose and legal basis for reusing the data, see PREP-025.

Tools: OpenRefine for first cleaning, Splink or Dedupe for matching, address and VAT validation services, DuckDB or PostgreSQL as working store. Rough share of effort: over 60 percent of the total, mostly steward review and agreeing rules, not model work. Start with GUIDE-003; related monitoring is DATA-008.

Implementation paths

Which path for whom

• smb: Path C, because customer and supplier records are sensitive and open-source entity resolution (e.g. Splink, Dedupe, OpenRefine) runs on HW1 and the same tools serve several data-prep use cases.
• enterprise: Path B, because master data is core and system-specific, so matching logic and stewardship workflows are best built on own data platform with API-based LLM normalisation; commodity MDM suites cost high seat and licence fees.
• developer: Path C, because open libraries give full control of matching rules, explainability and lineage.
• public: Path C on own or sovereign EU/German infrastructure, because citizen and patient records must not leave controlled environments.

Path A — Buy (off-the-shelf)

• Enterprise MDM platforms: Informatica MDM / CLAIRE, Reltio, Profisee, Tamr, Semarchy xDM (EU, France), Stibo Systems (EU, Denmark; strong in product data), Syniti, SAP Master Data Governance, IBM, Ataccama ONE (EU, Czechia), CluedIn (EU, Denmark). Most now ship AI-assisted matching, LLM-based data-quality rule suggestions and steward copilots.
• SMB tooling: CRM-native duplicate management (Salesforce, HubSpot, Microsoft Dynamics), Dataladder, WinPure, Openprise, plus address/company reference services (Deutsche Post Direkt, Dun & Bradstreet, Creditreform).
• Choose A when: several systems of record, ongoing governance needs, no data-engineering team. Check EU hosting, survivorship configurability, lineage/unmerge, and DPA terms.

Path B — Build on cloud APIs

• Warehouse-native: dbt/SQL models on Snowflake, Databricks, BigQuery or Microsoft Fabric with embedding functions and LLM functions (e.g. Cortex, AI Functions, Vertex AI) for normalisation and match adjudication.
• LLM calls: structured-output extraction to a schema (Claude, Gemini, GPT, Mistral — EU regions or EU provider preferred) for address/name parsing, classification and pair judging; embeddings via Mistral, Cohere or Azure OpenAI.
• Orchestration: Airflow/Dagster/n8n; reference data via VIES (VAT check), GLEIF (LEI) and national company registers.
• Choose B when: you already run a data platform and need custom domains or tight cost control.

Path C — Self-host (open source)

• Entity resolution: Splink (UK Ministry of Justice; DuckDB/Spark/Athena backends), Zingg (active learning), dedupe (Python), recordlinkage, Ditto/Magellan-style research code; OpenRefine for interactive cleaning.
• Quality rules: Great Expectations, Soda Core, pandera (see DATA-008). Address parsing: libpostal, Pelias.
• MDM hub: Pimcore (EU, Austria) or Apache-licensed alternatives; small open LLMs (Qwen3, Llama, Mistral, Phi class) via Ollama or vLLM for normalisation, bge-m3 / multilingual-e5 embeddings with pgvector, Qdrant or OpenSearch.
• Hardware: most work runs on CPU/DuckDB (HW1); a 7-14B LLM for adjudication or free-text cleaning is comfortable on HW2.

Path D — Custom training

Worth it for large domain-specific volumes (e.g. millions of product titles or patient records) where generic matchers plateau. Needs a few thousand labelled match / non-match pairs, usually harvested from steward decisions; fine-tune a small encoder or cross-encoder as pair classifier. Keep a held-out set to track precision and recall.

State of the art (2026)

• Entity matching with LLMs performs well zero- and few-shot on benchmark datasets and generalises to new domains with little labelled data, but costs more per pair than fine-tuned encoders; the winning pattern is cascade: cheap blocking and models first, LLM only for ambiguous pairs [3].
• Splink v4 and DuckDB-based pipelines make probabilistic linkage on tens of millions of records feasible on a single machine [4].
• Agentic data cleaning: vendors and open projects let an agent profile a table, propose cleaning rules, run them in a sandbox and ask a steward for approval; MDM vendors market "agentic" stewardship, with uneven maturity.
• Limits and failure modes: LLMs can silently "fix" valid-but-unusual values (hallucinated normalisation), merge distinct people with common names or family members at one address, and drift when source formats change. False merges are costlier than missed duplicates, so tune for precision, require lineage, and test fairness across name origins (transliteration errors hit non-Western names more often).

Shared resources & combinations

• Shared stack: embeddings + vector index serve DOC-014 and KNOW-013; the LLM normaliser can also do DATA-010 product enrichment and DATA-022 partner schema mapping.
• Upstream/downstream: DATA-008 (monitoring catches new quality regressions), DATA-007 (catalogue and metadata), DATA-023 (labelling of match pairs), AGENT-011 (CRM hygiene), KNOW-026 (entity resolution in investigations), DATA-005 (synthetic data to test pipelines without real personal data).
• Golden records are the foundation for analytics (DATA-001) and fraud checks on duplicate suppliers.

Effort, cost & KPIs

• Path A: 3-12 months for a multi-domain MDM programme; licences are the main cost, process and ownership work often exceeds technology work.
• Path B/C: a first domain (e.g. customers) in 4-10 weeks with one data engineer and a steward; LLM cost stays small when used only on ambiguous pairs.
• KPIs: duplicate rate, precision / recall of matching on a labelled sample, share auto-merged vs reviewed, steward hours per 1,000 records, completeness / validity of key fields, returned mail and bounce rates, duplicate-payment incidents, time to answer a data-subject request.

People & change

Master data touches almost every department, so involve the department leads of sales, purchasing and finance together with management from the start; they must agree on which record survives. Data owners and a named steward make the final decisions on borderline merges. IT handles exports and writing back to source systems, and suppliers or customers may need to be informed about corrected data.

Works council: depends. Customer and supplier data alone does not trigger co-determination, but employee master data or logging of stewards' review work can, so §87(1) no. 6 BetrVG applies in those cases, and §90(1) no. 3 requires timely information about changed work processes. Public bodies involve the Personalrat. Data protection: personal data is processed, so involve the DPO, conclude an AVV with any processor, document the legal basis, and run a DPIA if large citizen, patient or employee datasets are matched.

AI literacy: support the stewards with training on how match scores work and where LLM normalisation can be wrong, see GUIDE-006. Pilot with one domain (for example customers of one region), keep a human review queue for uncertain matches and never auto-merge below a threshold, and make every merge reversible. Address the fear of data loss with backups and lineage. Guides: GUIDE-005, GUIDE-008, GUIDE-009.

Risks, regulation & compliance

• EU AI Act: typically minimal risk (internal data preparation). It becomes relevant if resulting data feeds high-risk systems (credit scoring, HR, benefits eligibility): then Art. 10 data-governance duties apply, and linkage errors affecting individuals must be controlled. AI literacy (Art. 4) applies to staff. Not an Art. 5 practice, but do not use linkage to build covert profiles or scoring of individuals [2].
• GDPR: customer and supplier master data is personal data. Needs a legal basis, purpose limitation (do not merge datasets collected for incompatible purposes), data minimisation, accuracy (Art. 5(1)(d)), data-subject rights across merged copies, DPA with vendors, no personal data to non-EU LLM endpoints without safeguards. Enrichment with third-party data needs its own legal basis [5].
• Business risks: wrongful merges (shared mailbox, family, same-name persons) leaking data between accounts; deleting the "losing" record breaks audit trails (GoBD, retention). Mitigation: soft-merge with cross-references, unmerge function, audit log, thresholds by risk class, sampling QA.

Getting started

1. Pick one domain (customers or suppliers), profile it and label ~500 record pairs as match / non-match for a baseline.
2. Standardise first (addresses, VAT IDs, legal forms): this alone removes many duplicates.
3. Run Splink or Zingg as a prototype; compare with your CRM's built-in matching on the same labelled set.
4. Add LLM adjudication for the ambiguous band only; log explanations for stewards.
5. Define survivorship rules and a data owner, then write golden records back via a reviewed, reversible process.

Sources

1. European Commission / EUR-Lex — Regulation (EU) 2024/1689 (AI Act), Art. 10 data and data governance — https://eur-lex.europa.eu/eli/reg/2024/1689/oj
2. AI Act Service Desk (European Commission) — https://ai-act-service-desk.ec.europa.eu/en
3. Narayan et al. — Can Foundation Models Wrangle Your Data? (VLDB 2022) — https://arxiv.org/abs/2205.09911
4. Splink documentation (UK Ministry of Justice) — https://moj-analytical-services.github.io/splink/
5. Regulation (EU) 2016/679 (GDPR) — https://eur-lex.europa.eu/eli/reg/2016/679/oj
6. Zingg entity resolution — https://github.com/zinggAI/zingg
7. OpenRefine — https://openrefine.org/
8. Li et al. — Deep Entity Matching with Pre-Trained Language Models (Ditto) — https://arxiv.org/abs/2004.00584
9. Semarchy xDM — https://semarchy.com/