Using AI to Batch-Generate Data Cleaning Rules: A Team Lead's Practice

As the lead of a small team of five, the words I used to dread most weren't "this requirement can't be done," but "this batch of data is a bit dirty—can we clean it before loading it into the warehouse."

Dirty data isn't technically demanding, but it's extremely draining. Addresses come in every imaginable format, phone numbers are littered with spaces and dashes, date fields mix timestamps with Chinese-style dates, company names alternate between "有限公司" and "Ltd"—every cleaning effort meant someone writing regex at midnight while cursing under their breath. What gave me an even bigger headache: cleaning rules were scattered across ad-hoc scripts, and whenever someone left, the rules left with them.

The Pain Point: Cleaning Rules Are the Team's Most Hidden Technical Debt

We did an audit once and found the problems fell into three areas:

First, rules depended on individual experience. Whoever handled the data, their judgment became the standard. The same "Beijing Chaoyang District" address might be written by one person as 北京/朝阳 and kept verbatim by another—downstream reconciliation descended into chaos.

Second, rules never accumulated. Cleaning logic lived in throwaway Python scripts, so the next time similar dirty data appeared, we started from scratch again. Pitfalls we hit three months ago, we hit again three months later.

Third, rules couldn't be reviewed. Regex might as well be hieroglyphics to a manager. I wanted to sign off before anything went live, but in practice all I could do was "trust it." The risk was a complete black box.

Later, we brought AI into this workflow—not to have AI "directly clean the data" (keeping data off external networks is a hard line for us), but to have AI batch-generate reviewable, versionable cleaning rules. That shift was the key.

The AI Workflow: Four Steps to Turn Cleaning Rules into an Asset

Step 1: Sample without transmitting data—only send "patterns." We manually selected representative samples of each problem type from the dirty data, anonymized them, summarized them into "problem description + example snippets," and fed those to the AI. At no point did the AI touch the full raw dataset—this rule is written into our data security policy.

Step 2: Have AI batch-generate rule drafts. With a single prompt, the AI outputs rules by problem type: regex expressions, normalization mapping tables, anomaly detection logic, and handling suggestions for edge cases. One run can produce dozens of rules, covering what would take us two days of manual work.

Step 3: Human review + test set validation. This is the step I value most as a manager. Rules output by the AI are first run against our prepared test samples (with expected results); those that pass go into the rule library, and those that fail are annotated with reasons and sent back for regeneration. AI handles the volume; humans handle approval.

Step 4: Rules go into the library—versioned, with annotations. Every rule carries its generation time, applicable scenarios, owner, and test pass rate. When a new hire takes over, they can understand the entire cleaning logic just by reading the rule library—no need to dig through a departed colleague's computer.

A Reusable Prompt Template

Here's our internally refined rule-generation template, ready to use:

你是一名数据质量工程师，请为以下脏数据问题批量生成清洗规则。

【数据背景】
数据类型：{如：用户注册信息表}
字段列表：{如：name, phone, address, created_at, company}
数据量级：{如：约50万行}
下游用途：{如：BI报表统计、客户去重}

【已知脏数据问题与示例（已脱敏）】
1. 手机号格式混乱，如：{示例1}、{示例2}
2. 地址缺失行政区，如：{示例}
3. 日期格式混用，如：{时间戳示例}、{中文日期示例}
4. 公司名后缀不统一，如：{示例}

【输出要求】
对每个问题输出：
- 规则编号与问题分类
- 清洗逻辑的中文描述（一句话，便于非技术人员评审）
- 具体实现（正则表达式 / SQL / Python代码，任选标注）
- 无法自动清洗需人工介入的判定条件
- 3个边界case及建议处理方式

【约束】
- 规则必须幂等：重复执行结果一致
- 不得删除原始字段，清洗结果写入新字段
- 每条规则单独输出，便于逐条评审与测试

The beauty of this template is that the output is naturally organized as "one problem, one set of rules," which maps directly onto the review process—no re-organization needed.

Before and After: From Black Box to Pipeline

On cost: we invoke the model through an API gateway for rule generation. Token usage is far lower than having AI process the full dataset directly (since we only send patterns, not data). Fees are per the official pricing page, but overall it accounts for only a tiny fraction of our AI budget.

Three Risk Control Points Managers Must Hold

1. Data never leaves the domain. The AI only touches anonymized samples and pattern descriptions; full-data cleaning runs in local scripts.
2. AI only produces drafts; humans approve. All rules must pass the test set before taking effect, and the test set is maintained by humans.
3. Rule changes go through review. Even rules regenerated by AI must have the replacement reason documented, to prevent downstream definitions from quietly drifting.

Final Thoughts

Cleaning rules used to be an unclaimed hidden debt on the team; now they're the first stop in our data pipeline. AI's role here isn't to clean data in place of humans—it's to max out the output of the most tedious step, "generating rules," so people can focus their energy on review and definition control—which happens to be exactly the part a manager can truly control.

If your team is being drained by dirty data, start by building this "AI drafts the rules, humans approve them" pipeline. If you want to try it hands-on, you can register an API account first: https://api.thistoken.ai/register, run the prompt template above, and you'll have your first batch of rule drafts the same day.

---

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