Section One: The Manager's Hidden Pain — Cleaning Rules Always Live "in Someone's Head"

Anyone who has led a data team has encountered this scenario: a business system migration, merging data from legacy customers, or a one-off export of spreadsheets for analysis — the data arrives and it's full of pitfalls.

Phone numbers mixed with landline formats, names containing full-width spaces, address fields casually filled in by sales reps as "customer self-pickup," dates in eight different formats, and the same company appearing as three versions: "XX Co., Ltd.," "XX Limited Liability Company," and "XX Ltd." The typical way a data analyst cleans this data is to open Excel or write a one-off Python script, handle things by feel, dump the script in some folder when done, and rewrite it from scratch the next time a new batch of data arrives.

From a management perspective, there are three real risks here:

First, rules cannot be accumulated. Everyone's data cleaning logic lives in their own head or in local scripts. Once that person leaves, that logic disappears. When the next batch of dirty data arrives, the team starts from zero.

Second, rules cannot be audited. Why were "NULL," "null," "-", and "N/A" all treated as missing values? Should the underscore in "李_明" be removed or kept? Nobody documents these decisions. When a data incident happens, no one can explain why things were handled that way at the time.

Third, rules cannot be reused. Take address cleaning: marketing has one set of rules, customer service has another, and finance has yet another. The three definitions don't align, and everyone blames each other during month-end data reconciliation.

Our team isn't big, we have no dedicated data governance role, and we can't afford to hire someone just to write data dictionaries. This problem hung over us for a long time, until we tried bringing in AI — not to have AI directly clean the data, but to have AI batch-generate cleaning rules, with humans responsible for review and finalization.

This shift in division of labor is the key: what AI produces is not results, but rule assets that the team can review, archive, and reuse.

Section Two: The AI Workflow — Three Steps with Full Control Over Rule Output

Step One: Have AI "Diagnose" First, Don't Rush to Prescribe

We sampled 200 rows of dirty data (anonymized) and threw it at the AI along with field descriptions, asking only for a problem diagnosis checklist: what types of dirty data exist in each field, estimated frequency of occurrence, and possible root causes in the data source.

AI does this step much faster than humans. Manually sampling and checking a single field might take a dozen-plus minutes, while AI can surface the anomaly patterns for all fields within minutes — including ones the naked eye easily misses, like whether the last character of an ID number is a lowercase "x" or uppercase "X," or Chinese commas mixed into amount fields.

Step Two: Batch-Generate Cleaning Rules, Each with a "Rationale"

After confirming the diagnosis, we asked the AI to produce structured cleaning rules for each problem. Every rule must contain four elements: match condition, action, scope, and business rationale. The "business rationale" item is mandatory, because it directly determines whether a rule can pass human review — if the AI can't give a reasonable business rationale, it means a human needs to make that judgment call.

Step Three: Two-Person Review + Versioned Archiving

The AI-generated rules table goes into a review process: the data owner does the initial review, and business stakeholders confirm items involving business judgment (for example, "should customer short names be merged into full names" requires business sign-off). Approved rules are stored in a shared document, annotated with version numbers and applicable data batches.

From then on, any new batch of dirty data is first run against the rules library, and any new problems not covered by the library go through another round of "AI generation — human review — archiving." For the first time, data cleaning became an engineering practice with a process, with accumulated knowledge, and with version management.

Section Three: A Copy-Paste Prompt Template

This is the template our team actually uses. You can copy it directly into any LLM chat:

# 角色
你是一名资深数据治理顾问，擅长为脏数据设计清洗规则。

# 任务
我会提供一份脱敏后的数据样本（CSV格式）和字段说明。
请分两步完成：

## 第一步：问题诊断
逐字段列出存在的数据质量问题，输出表格，包含：
字段名 | 问题类型（缺失/格式混乱/逻辑矛盾/重复/编码异常）| 示例值 | 估计占比 | 可能的产生原因

## 第二步：清洗规则
针对每个问题输出一条规则，格式为：
- 规则编号
- 匹配条件（尽量用可执行的判断逻辑描述）
- 处理动作（删除/替换/标准化/标记待人工确认）
- 适用范围（哪些字段或数据批次）
- 业务理由（为什么这样处理是合理的）
- 风险提示（这条规则可能误伤什么数据）

# 约束
1. 不确定的业务判断不要擅自决定，标记为"需人工确认"并给出建议选项
2. 涉及个人信息字段，规则需兼顾脱敏合规要求
3. 优先给出保守规则：宁可标记待确认，不要自动删除
4. 输出末尾附上你认为还需要我补充的背景信息清单

# 数据样本
（在此粘贴数据）
# 字段说明
（在此粘贴字段含义）

Two pieces of advice: first, don't remove the "risk warnings" item — it often exposes pitfalls in rules ahead of time; second, having the AI ask you at the end what background information is missing can significantly reduce the odds of it making wild guesses.

Section Four: Before and After AI

Efficiency: In the past, processing a medium-sized batch of dirty data (dozens of fields, tens of thousands of rows) took someone familiar with the business two to three days to probe the problems, write scripts, and iterate. Now diagnosis plus rule generation is done within an hour, human review takes half a day, and the whole thing is compressed to under a day. More importantly, when a second batch of similar data comes in, the rules library is reused directly and only the execution step remains.

Collaboration: Previously, only the person doing the cleaning understood the cleaning logic, and reviewers could only guess at the process from the results. Now the rules are a table everyone can read. When business stakeholders participate in review, they can directly point out "this one is wrong — in our business we need to keep the original value." Disagreements get resolved at the rule stage, not after a data incident.

Risk control: Every rule has a version, a rationale, and a sign-off. When data problems arise, you can trace them back to a specific rule, not to "whoever handled it back then." For a small team without a dedicated data governance role, this is audit capability purchased at minimal cost.

A dose of cold water: About 10%–15% of AI-generated rules need correction or rejection. Common issues are over-standardization (flattening differences that actually carry business meaning) and careless assumptions about edge values. So the human review step cannot be skipped — AI accelerates the "drafting" of rules, but responsibility for rules always rests with people and process.

Section Five: Final Thoughts

Dirty data cleaning is something most small teams do, but few manage as an "engineering practice." The value of AI isn't in clicking a few buttons for you, but in converting the experience that used to live in individual heads into rule assets the team can review, accumulate, and reuse — at low cost. If you're looking for a stable, reliable model API to implement this kind of workflow, check out https://api.thistoken.ai/register — sign up and start building your own data processing pipeline.

---

Every example in this post runs with a single API key — get yours at https://api.thistoken.ai/register and start in minutes.