# How to Build an AI Agent That Cleans and Deduplicates Business Data

> A step-by-step guide to an AI data-cleaning agent: profile a dataset, normalise it, find fuzzy duplicates, let an LLM judge only the hard cases, and keep every change reviewable. With Python.

- Author: OnChain Team
- Published: 2026-11-12
- Page: https://www.onchain.ro/blog/ai-agent-data-cleaning

A good AI data-cleaning agent uses the model sparingly. Deterministic code profiles the data, normalises formats and finds candidate duplicates; a language model is asked only about the ambiguous cases, such as whether "ACME corp." and "Acme Corporation Ltd" at the same address are one customer; and every change is written to a log that a person can review, approve or revert. That keeps the agent cheap, fast and auditable. This guide builds it in Python with pandas, RapidFuzz and the Claude API, using a customer table as the example.

## Why not just ask a model to clean the file?

You could paste rows into a chat and ask for a cleaned version. It breaks down quickly:

- **Scale.** Real tables have tens of thousands of rows, far more than you want to send through a model one by one.
- **Silent changes.** A model rewriting rows can alter values you did not ask it to touch, and you cannot easily tell which.
- **No audit trail.** For customer, financial or compliance data, you need to know why each change was made.

The design below fixes all three: the model never edits the table directly, it only answers narrow questions, and your code applies the answers and logs them.

## The pipeline

```diagram
title: Rules do most of the work; the model handles the grey zone
node prof at 0,1 default "Profile" "Measure what is wrong"
node norm at 1,1 default "Normalise" "Rules fix formats"
node cand at 2,1 default "Candidates" "Blocking + fuzzy score"
node auto at 3,0 default "Auto-merge" "Score 95 and above"
node judge at 3,1 ai "LLM judge" "Scores 80 to 95"
node keep at 3,2 default "Keep separate" "Below 80"
node log at 4,1 store "Change log" "Proposals, not edits"
node review at 5,1 user "Reviewer" "Approve and apply"
edge prof -> norm
edge norm -> cand
edge cand -> auto "≥ 95"
edge cand -> judge "80–95"
edge cand -> keep "< 80" dashed
edge auto -> log
edge judge -> log "decision"
edge log -> review
edge review -> log "revert" dashed up
```

## Step 1: Set up

```bash
pip install pandas rapidfuzz anthropic pydantic
export ANTHROPIC_API_KEY=...
```

The example table has `id`, `name`, `email`, `phone`, `country` and `billing_address` columns. Swap in your own.

## Step 2: Profile before you change anything

Profiling tells you what is actually wrong, so you fix that rather than what you assume is wrong. It is also the "before" number you will compare against.

```python
# profile.py
import pandas as pd


def profile(df: pd.DataFrame) -> pd.DataFrame:
    rows = []
    for col in df.columns:
        s = df[col]
        rows.append(
            {
                "column": col,
                "missing": int(
                    s.isna().sum() + (s.astype(str).str.strip() == "").sum()
                ),
                "distinct": int(s.nunique(dropna=True)),
                "example": s.dropna().astype(str).head(3).tolist(),
            }
        )
    return pd.DataFrame(rows)


df = pd.read_csv("customers.csv", dtype=str)
print(f"{len(df)} rows, {df.duplicated().sum()} exact duplicate rows")
print(profile(df).to_string(index=False))
```

## Step 3: Normalise with plain rules

Most "duplicates" are formatting differences. Fix those deterministically and many problems disappear before any model is involved. Keep the original columns and add normalised ones, so nothing is lost.

```python
# normalise.py
import re
import pandas as pd

LEGAL_SUFFIXES = (
    r"\b(inc|incorporated|ltd|limited|llc|gmbh|srl|sa|bv|plc|corp|corporation|co)\b\.?"
)


def norm_name(value: str | None) -> str:
    if not isinstance(value, str):
        return ""
    v = value.casefold()
    v = re.sub(LEGAL_SUFFIXES, " ", v)
    v = re.sub(r"[^\w\s]", " ", v)
    return re.sub(r"\s+", " ", v).strip()


def norm_email(value: str | None) -> str:
    return value.strip().casefold() if isinstance(value, str) else ""


def norm_phone(value: str | None) -> str:
    return re.sub(r"\D", "", value) if isinstance(value, str) else ""


def normalise(df: pd.DataFrame) -> pd.DataFrame:
    out = df.copy()
    out["name_n"] = out["name"].map(norm_name)
    out["email_n"] = out["email"].map(norm_email)
    out["phone_n"] = out["phone"].map(norm_phone)
    out["domain_n"] = out["email_n"].str.split("@").str[-1].fillna("")
    return out
```

## Step 4: Find candidate duplicates with blocking and fuzzy matching

Comparing every row with every other row is slow: 50,000 rows means over a billion pairs. **Blocking** compares only rows that share something cheap to check, such as the email domain or the first letters of the name. [RapidFuzz](https://rapidfuzz.github.io/RapidFuzz/) then scores each pair quickly.

```python
# candidates.py
from itertools import combinations
import pandas as pd
from rapidfuzz import fuzz

FREE_MAIL = {"gmail.com", "yahoo.com", "outlook.com", "hotmail.com", "icloud.com"}


def blocks(df: pd.DataFrame):
    keys = df.apply(
        lambda r: (
            r["domain_n"]
            if r["domain_n"] and r["domain_n"] not in FREE_MAIL
            else r["name_n"][:4]
        ),
        axis=1,
    )
    for _, idx in df.groupby(keys).groups.items():
        if (
            1 < len(idx) <= 500
        ):  # skip giant blocks; split them with a second key instead
            yield list(idx)


def score(a: pd.Series, b: pd.Series) -> float:
    name = fuzz.token_sort_ratio(a["name_n"], b["name_n"])
    same_email = a["email_n"] and a["email_n"] == b["email_n"]
    same_phone = a["phone_n"] and a["phone_n"] == b["phone_n"]
    return min(100.0, name + (10 if same_email else 0) + (5 if same_phone else 0))


def candidate_pairs(df: pd.DataFrame) -> pd.DataFrame:
    pairs = []
    for idx in blocks(df):
        for i, j in combinations(idx, 2):
            s = score(df.loc[i], df.loc[j])
            if s >= 80:
                pairs.append({"left": i, "right": j, "score": s})
    return pd.DataFrame(pairs, columns=["left", "right", "score"])
```

Tune the weights and thresholds on a sample you have labelled by hand. Fifty labelled pairs are enough to tell whether 80 and 95 are sensible cut-offs for your data.

## Step 5: Ask the model only about the grey zone

Pairs above 95 are merged by rule. Pairs between 80 and 95 go to the model with the full records and a narrow question. [Structured outputs](https://platform.claude.com/docs/en/build-with-claude/structured-outputs) guarantee the answer fits a schema your code can act on.

```python
# judge.py
import json
from typing import Literal
import anthropic
from pydantic import BaseModel

client = anthropic.Anthropic()


class MatchDecision(BaseModel):
    same_entity: bool
    confidence: Literal["high", "medium", "low"]
    reason: str  # one sentence, shown to the reviewer


SYSTEM = (
    "You decide whether two customer records refer to the same company. "
    "Use only the fields provided. "
    "Different legal entities in the same group are NOT the same entity. "
    "If the evidence is weak or conflicting, answer same_entity=false with low confidence."
)


def judge(left: dict, right: dict) -> MatchDecision | None:
    response = client.messages.parse(
        model="claude-opus-5-5",
        max_tokens=1000,
        system=SYSTEM,
        messages=[
            {
                "role": "user",
                "content": f"Record A: {json.dumps(left)}\nRecord B: {json.dumps(right)}",
            }
        ],
        output_format=MatchDecision,
    )
    if response.stop_reason == "refusal":
        return None  # treat as "needs human review"
    return response.parsed_output
```

Send only the columns the decision needs. If the table holds personal data, check your data processing agreements before sending any of it to an external API, and consider masking fields the model does not need, such as full phone numbers.

For large volumes, submit these questions through the [Message Batches API](https://platform.claude.com/docs/en/build-with-claude/batch-processing), which is cheaper and does not need answers in real time.

## Step 6: Fill missing values only from evidence

Filling gaps is where an unsupervised agent does the most damage, by inventing plausible values. Use rules first, and accept a model's answer only if it comes from another field in the same row and passes validation.

```python
# fill.py
import pandas as pd
from pydantic import BaseModel

COUNTRIES = {"RO", "DE", "NL", "FR", "US", "GB"}  # your allowed values
PHONE_PREFIX = {"40": "RO", "49": "DE", "31": "NL", "33": "FR", "1": "US", "44": "GB"}


def country_by_rule(row: pd.Series) -> tuple[str | None, str]:
    phone = row.get("phone_n", "")
    for prefix, code in sorted(PHONE_PREFIX.items(), key=lambda kv: -len(kv[0])):
        if phone.startswith(prefix):
            return code, f"phone prefix +{prefix}"
    return None, ""


class CountryGuess(BaseModel):
    country: str | None  # ISO code, or null if the row does not say
    evidence_field: str | None  # which field the answer comes from


def country_by_model(row: pd.Series, client) -> tuple[str | None, str]:
    response = client.messages.parse(
        model="claude-opus-5-5",
        max_tokens=500,
        system=(
            "Return the ISO 3166-1 alpha-2 country code only if a field "
            "in the record states it. "
            "Otherwise return null. Never guess from a company name."
        ),
        messages=[
            {
                "role": "user",
                "content": str(row[["billing_address", "email"]].to_dict()),
            }
        ],
        output_format=CountryGuess,
    )
    guess = response.parsed_output
    if response.stop_reason == "refusal" or not guess or guess.country not in COUNTRIES:
        return None, ""
    return guess.country, f"model, from {guess.evidence_field}"
```

If neither rule nor model produces a validated answer, leave the value empty and flag the row. An honest gap is better than a confident wrong value.

## Step 7: Log every change and apply only what is approved

The agent produces proposals, not edits. Each proposal records the row, the column, the old and new value, who or what proposed it, and why.

```python
# changes.py
from dataclasses import dataclass, asdict
import pandas as pd


@dataclass
class Change:
    row_id: str
    column: str
    old: str | None
    new: str | None
    reason: str
    source: str  # "rule" or "model"
    approved: bool = False


def propose_merge(
    df: pd.DataFrame, keep: int, drop: int, reason: str, source: str
) -> list[Change]:
    # Keep the older record; mark the other as merged into it, so nothing is deleted
    return [
        Change(
            df.loc[drop, "id"], "merged_into", None, df.loc[keep, "id"], reason, source
        )
    ]


def apply(df: pd.DataFrame, changes: list[Change]) -> pd.DataFrame:
    out = df.set_index("id").copy()
    for c in changes:
        if c.approved:
            out.loc[c.row_id, c.column] = c.new
    return out.reset_index()


def revert(df: pd.DataFrame, changes: list[Change]) -> pd.DataFrame:
    out = df.set_index("id").copy()
    for c in reversed(changes):
        if c.approved:
            out.loc[c.row_id, c.column] = c.old
    return out.reset_index()


# Save proposals for review:
# pd.DataFrame([asdict(c) for c in changes]).to_csv("proposed_changes.csv")
```

A reviewer can approve rule-based changes in bulk and look at model-based ones individually. Merging by marking rows (`merged_into`) instead of deleting them means any merge can be undone later.

## Step 8: Measure the result

Run the profile again after applying changes and report the difference: duplicates removed, values filled, rows flagged for a human. Keep the labelled sample from step 4 and re-check precision whenever you change thresholds, prompts or the model. If merges start going wrong, you want to find out from that sample, not from a customer.

## What we would do

Put most of the effort into normalisation and blocking. They are cheap, fast and fully predictable, and they shrink the number of questions the model has to answer. Use the model for judgement on genuinely ambiguous cases, require it to point to evidence, and never let it write to the table directly. The change log is not an extra; it is what makes the agent safe to use on data that matters.

We build AI agents and data platforms like this in our [AI development](/services/ai-development) work.

---

OnChain · 15 Bld. Iuliu Maniu, 061075 Bucharest, Romania · contact@onchain.ro · https://www.onchain.ro
