Skip to content
ON CHAIN
Menu

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

Most data cleaning does not need an LLM. This guide uses rules for the easy 90%, a model for the ambiguous cases, and a change log so every edit can be reviewed and reverted.

By OnChain Team8 min read

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

Rules do most of the work; the model handles the grey zone
ProfileMeasure what is wrongNormaliseRules fix formatsCandidatesBlocking + fuzzyscoreAuto-mergeScore 95 and aboveAI MODELLLM judgeScores 80 to 95Keep separateBelow 80DATAChange logProposals, not editsUSERReviewerApprove and apply≥ 9580–95< 80decisionrevert

Main flowSecondarySwipe to see the whole diagram →

Step 1: Set up

TerminalTerminal
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.

profile.pyPython
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.

normalise.pyPython
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 then scores each pair quickly.

candidates.pyPython
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 guarantee the answer fits a schema your code can act on.

judge.pyPython
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, 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.

fill.pyPython
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.

changes.pyPython
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 work.

Contact

Need this built for your product?

Tell us what you are building and where you are stuck. Email is the fastest way to reach the team.

contact@onchain.ro