Duplicate master data: use AI to suggest corrections without merging records by mistake

Let's discuss your project →

What this guide helps you do

Use AI as a proposal layer for duplicate master data, not as an automatic merge engine. The workflow in this guide helps you:

  • spot records that deserve human review;
  • keep source data unchanged;
  • separate AI suggestions from the final decision;
  • record why a case was approved, rejected, or deferred.

The technical background is the W3C Tabular Data Model, which treats tabular data as metadata-rich data that can be validated and checked through unique row identification. Duplicate primary keys are validation errors, but that is still different from deciding whether two business entities should be merged.

At the end you will have:

  • a complete synthetic CSV with six records, R01 to R06;
  • a reusable prompt that asks AI to classify candidates without merging them;
  • a JSON output template with the three required outcomes: POSSIBLE_DUPLICATE, DISTINCT, and INSUFFICIENT_DATA;
  • a separate human decision register;
  • a final acceptance check you can repeat.

Who this is for

This guide is for operations managers, admin and finance teams, data quality owners, and IT staff who manage customer, supplier, partner, or company master data.

It is also useful for teams introducing AI into controlled business processes and needing a clear boundary between suggestion and decision. If the workflow later becomes part of a broader software process, the same governance mindset applies to Responsible AI for companies, processes and automation, Custom software and integrations, and Strategic IT advisory and governance.

Tools, prerequisites, and materials included

Tools

  • a text editor or spreadsheet;
  • an AI tool that accepts a prompt and can return structured text or JSON;
  • a separate working copy, not the production master data;
  • a decision register, even a simple table, that is not the source of truth for the master data.

Prerequisites

  • know which fields are stable in your environment;
  • know which field is the row identifier in your working copy;
  • agree in advance that AI may propose candidates, but may not merge records;
  • use only synthetic or anonymized data in this exercise.

What this guide includes

  1. A complete CSV dataset with six synthetic records.
  2. A prompt you can copy as-is.
  3. A JSON example of a valid AI proposal.
  4. A human decision register.
  5. A final troubleshooting and acceptance checklist.

1. Prepare the working copy and keep the original data unchanged

Where to work: a scratch spreadsheet, a local CSV file, or a test worksheet.

Input to copy: the synthetic dataset below.

record_id,company_name,fictional_identifier,location,contact
R01,Alpha Services Ltd,ACME-001,London,info@example.invalid
R02,ALPHA SERVICES LIMITED,ACME-001,London,accounts@example.invalid
R03,Alpha Services North Ltd,ACME-019,London,north@example.invalid
R04,Beta Logistics PLC,BETA-120,Manchester,contact@example.invalid
R05,Beta Logistics UK PLC,BETA-121,Manchester,office@example.invalid
R06,Delta Studio Cooperative,DELTA-550,Bristol,team@example.invalid

What to do: import or paste the dataset exactly as shown. Do not rename the columns yet. Do not delete rows. Do not combine candidates.

Expected result: the working copy contains six rows and five fields, and every record keeps its original record_id.

If it diverges: if the file import shifts columns, check the separator and encoding before continuing. If any record_id changes, stop and restart the working copy.

Why this matters: R01 and R02 are the strong-match pair: same fictional identifier, same location, and only form-level differences in the company name. R04 and R05 are the “similar name, different identifier” pair that should not be merged automatically.

2. Normalize only in derivative columns, never in the source fields

Where to work: a copy of the working sheet or a separate tab.

Input to use: the original columns from the CSV.

What to do: create helper columns such as company_name_norm, fictional_identifier_norm, and location_norm. Apply only lightweight normalization: lowercase, trim repeated spaces, and remove purely typographic differences in the working copy.

Expected result: Alpha Services Ltd and ALPHA SERVICES LIMITED become easier to compare, while the original values remain untouched.

If it diverges: if the normalized value replaces the original, undo the change. Normalization is a comparison aid, not a proof of identity.

Important limit: a normalized string can look cleaner, but it still does not prove that two companies are the same legal entity. That decision remains a business judgment, not a formatting result.

3. Use a prompt that forces proposal mode, not merge mode

Where to work: the AI tool you use for the exercise.

Input to copy: the prompt below plus the CSV dataset.

You are an assistant for reviewing company master data.
You receive a CSV with these fields: record_id, company_name, fictional_identifier, location, contact.

Goal:
- propose only the records or pairs that should be reviewed by a human;
- do not merge records;
- do not overwrite source data;
- do not invent missing information;
- do not treat normalization as proof of identity.

Classify each record or pair into one of these outcomes:
- POSSIBLE_DUPLICATE
- DISTINCT
- INSUFFICIENT_DATA

Rules:
1. If fictional_identifier is identical and company_name changes only by capitalization, spacing, abbreviations, or punctuation, mark POSSIBLE_DUPLICATE.
2. If the name is similar but fictional_identifier is different, mark DISTINCT or INSUFFICIENT_DATA only if there is no reliable criterion to decide.
3. If the data is not enough, explain what is missing.
4. Return JSON only.
5. Always include a short reason.
6. State which records must be sent to the human reviewer.

Required JSON format:
{
  "proposals": [
    {
      "pair": ["R01", "R02"],
      "outcome": "POSSIBLE_DUPLICATE",
      "reason": "...",
      "send_to_human": true
    }
  ],
  "single_records": [
    {
      "record_id": "R06",
      "outcome": "DISTINCT",
      "reason": "..."
    }
  ]
}

Expected result: the model returns structured JSON, not a narrative answer and not a merge proposal.

If it diverges: if the model writes prose, repeat the prompt and keep the final line “Return JSON only.” If it proposes a merged record, reject the output: that is a process failure, not a useful answer.

4. Check the normal case before you test missing data

Where to work: the AI output and your review sheet.

Input to inspect: the JSON returned by the model for the full six-row dataset.

What to do: verify that each proposal uses only the original record_id values and that no synthetic record has been created.

Expected result: one strong candidate for review, R01 and R02, plus clear DISTINCT outcomes for the other pairs or single records.

Simulated expected JSON output

{
  "proposals": [
    {
      "pair": ["R01", "R02"],
      "outcome": "POSSIBLE_DUPLICATE",
      "reason": "Same fictional identifier ACME-001, same location, and only formatting differences in the company name.",
      "send_to_human": true
    },
    {
      "pair": ["R04", "R05"],
      "outcome": "DISTINCT",
      "reason": "Similar name but different fictional identifiers: BETA-120 and BETA-121. Similar wording is not enough to merge them.",
      "send_to_human": false
    },
    {
      "pair": ["R03", "R04"],
      "outcome": "DISTINCT",
      "reason": "Different location and different identifiers; the shared word 'Services' is not a sufficient match.",
      "send_to_human": false
    }
  ],
  "single_records": [
    {
      "record_id": "R06",
      "outcome": "DISTINCT",
      "reason": "No strong match in the supplied sample."
    }
  ]
}

If it diverges: if you see R01+R02, a new combined record, or rewritten fields, stop. The model is not only classifying; it is altering the data structure, which this workflow forbids.

Acceptance check: the output must preserve the original record identifiers, use only the three allowed outcomes, and clearly identify which cases go to a human reviewer.

5. Test the missing-information case on purpose

Where to work: a fresh copy of the same working file.

Input to change: remove fictional_identifier from R01 and R02 only, then rerun the same prompt.

What to do: ask the model to classify again, but do not give it the strongest matching field.

Expected result: the model should become more cautious. It may return INSUFFICIENT_DATA for the R01 / R02 pair, or it may explain that it cannot decide safely without an identifier.

Simulated acceptable reason text

  • “The name is strongly similar, but the unique identifier is missing, so the match is not reliable.”
  • “A similar company name is not enough to merge records without a stronger key.”

If it diverges: if the model still says to merge the pair, the prompt is too permissive. Add a stricter rule: “When the key field is missing, do not infer identity; use INSUFFICIENT_DATA.”

Why this matters: many duplicate workflows fail not because the model is weak, but because the prompt pushes it to conclude too early.

6. Record the human decision in a separate register

Where to work: a separate decision table, not the master data source.

Input to copy: the AI output plus the human reviewer’s final call.

What to do: log the decision in a table with date, reviewer, outcome, and note. Keep the source rows unchanged.

Sample decision register

Case Records AI outcome Human decision Final note Send to human?
1 R01 ↔ R02 POSSIBLE_DUPLICATE Approved as duplicate Same identifier, same location, form-level name differences only Yes
2 R04 ↔ R05 DISTINCT Confirmed distinct Similar wording, but different identifiers No
3 R03 ↔ R04 DISTINCT Confirmed distinct No strong evidence of identity No
4 R06 DISTINCT Confirmed distinct No close match in the sample No
5 R01 ↔ R02 without identifier INSUFFICIENT_DATA Deferred Matching evidence is too weak without the identifier Yes

Expected result: the register shows who decided, what they saw, and why the decision was made.

If it diverges: if the human decision is written directly into the source master data, the process loses auditability. Move the decision into a separate table before proceeding.

7. Run the final acceptance test and keep the process repeatable

Where to work: the working copy, the AI output, and the decision register.

Input to check: the original CSV, the AI JSON, and the human register.

What to do: verify the same sequence every time:

  1. load the CSV;
  2. create the derivative normalization columns;
  3. run the prompt;
  4. inspect the JSON;
  5. validate that no merge or overwrite happened;
  6. record the human decision separately.

Expected result: only R01 and R02 are strong enough to be reviewed as a possible duplicate in the normal case, R04 and R05 stay distinct, and missing data never triggers an automatic merge.

If it diverges: if the output is inconsistent between runs on the same data, check whether the prompt changed, whether the model received the same fields, or whether a hidden instruction is pushing it toward consolidation.

Final acceptance criteria

  • the original dataset remains unchanged;
  • the AI uses only POSSIBLE_DUPLICATE, DISTINCT, and INSUFFICIENT_DATA;
  • any merge decision is human-made;
  • the register is separate from the source data;
  • every proposal can be traced back to original record IDs.

Troubleshooting

The model is too aggressive

If the model flags too many duplicates, tighten the prompt. Put the identifier first, then make it explicit that normalization alone is not proof.

The model returns a paragraph instead of JSON

Add the line “Return JSON only” and reject any answer that does not match the schema.

The team wants to auto-merge after the AI response

Do not do that in this workflow. The safe design is: AI proposes, human decides, source data stays unchanged until the decision is logged.

The real dataset contains sensitive data

Use a minimized or anonymized sample first. If the real workflow requires personal or sensitive information, that is a governance decision, not just a prompt-writing problem.

Final note and next step

This workflow is useful when the business problem is not “find duplicates at any cost,” but “reduce review effort without creating irreversible mistakes.” It is especially useful when master data quality affects orders, invoices, permissions, or reporting.

If you want to move from a spreadsheet exercise to a controlled operational process, the next step is to describe your data flow, your source systems, and the validation point where AI should stop and a human should take over. The most relevant service context for that discussion is Responsible AI for companies, processes and automation, especially when the proposal layer needs to sit inside a broader software process. For implementation planning, Custom software and integrations and Strategic IT advisory and governance are the natural follow-ups.

Sources