## I

I. The Manager's Pain Point: Why Dirty Data Cleansing Always Runs Over Schedule

As the leader of a small team that has run several data projects, I have a visceral aversion to the phrase "data cleansing." In the project schedule it always takes up just two or three days, but in reality it swallows two or three weeks. Worse still, the cleansing rules are scattered across everyone's script comments, chat logs, and someone's head. The moment a person resigns, the rules become a black box.

The typical scenario goes like this: a customer table exported by operations, an order table stitched together from multiple channels, a product table scraped back by crawlers—inconsistent field names, nulls everywhere, seven or eight different date formats, landline numbers and garbled characters mixed in with mobile numbers. The team's usual approach is to write scripts individually, patching each dirty record as it comes up, with no rules being consolidated and no acceptance criteria. Then, one day before launch, someone discovers the upstream changed the format again, and everything starts over.

What a manager really needs to manage isn't any single line of code, but three things: whether the rules form a coherent system, whether the process is collaborative, and whether the results are auditable. This is exactly where AI is best positioned to step in—it shouldn't just cleanse one record for you; it should help you draft the entire "cleansing rule system" layer from scratch.

II. What AI Can Do for You: Not an Executor, but a Rule Drafting Officer

We adjusted our workflow: instead of having team members write rules from a blank document, we have AI first draft a complete set of cleansing rules based on data samples, with humans only reviewing, revising, and making final calls. AI plays the role of "first-draft producer + rule consistency checker."

The specific workflow has five steps:

Step 1: Sample and de-identify. Pull 100–200 rows of samples from each table, and use a script to replace sensitive fields like names, phone numbers, and ID numbers with placeholders. This step must be controlled by humans—don't throw raw samples directly at an external model.

Step 2: Have AI draft the rule list. Feed the de-identified samples and business context to the model, and require it to output field-level cleansing rules: each rule includes the field name, problem description, detection logic, handling method, and risk level. Use a unified prompt template (see below) for this step to ensure that results generated by multiple people across multiple runs have a consistent structure.

Step 3: Human review meeting. The team spends an hour going through AI's rule list, marking each rule as "adopt / modify / reject." The rejection rate itself is valuable information—if rejections cluster around a certain type of field, that's where business definitions are most ambiguous.

Step 4: Turn rules into code. The adopted rules are then translated by AI into Pandas or SQL scripts, with the rule list preserved as script comments, achieving "documentation as code."

Step 5: Staged execution and regression. Run the rules on 10% of the data first; AI compares statistical distributions before and after cleansing and outputs an anomaly report. The rule library is version-controlled, so when the upstream format changes, you only modify the rules and regenerate the scripts.

III. Prompt Template (Ready to Copy and Use)

你是一位资深数据治理工程师。请基于我提供的脱敏数据样本，
为以下数据表起草一份完整的清洗规则清单。

【业务背景】：{一句话描述数据来源和用途}
【数据样本】：{粘贴脱敏后的样本，含表头}
【特殊要求】：{如：手机号需保留前3后4、日期统一为YYYY-MM-DD}

请按以下格式输出，每个字段一组规则：

字段名：
- 问题描述：（观察到的脏数据现象，附样本中的实例）
- 判定逻辑：（如何识别，正则/条件表达式）
- 处理方式：（删除/填充/标准化/标记人工复核，写明具体动作）
- 风险等级：高/中/低（高=误清洗可能导致业务损失）
- 依赖确认：（需要业务方确认的问题）

最后请单独列出：
1. 你无法判断、需要人工决策的规则清单
2. 建议的字段间交叉校验规则（如金额与数量的一致性）

The last two items are the key—having AI proactively expose its own uncertainty is far cheaper than having the team step on landmines after the fact.

IV. Before and After: Three Ledgers from a Manager's Perspective

The time ledger: In the past, two people spent a week drafting rules and reworking as they went; now AI produces the first draft in ten minutes, and people spend half a day reviewing. A two-week cleansing cycle is compressed to three or four days, and the time saved goes into real business logic.

The collaboration ledger: Previously, rules lived inside individual scripts, so reviewing code meant reviewing rules—an extremely high cost. Now rules exist before code and follow a unified format; product and operations can both participate in reviews, and non-technical roles can weigh in on cleansing standards for the first time.

The risk ledger: This is the biggest gain. Every rule comes with a risk level and dependency confirmation items; high-risk operations (like direct deletion) require a human sign-off. With rules version-controlled, any data incident can be traced back to "which rule, which version, who approved it." AI drafts, humans approve, scripts are replayable—for the first time, the chain of accountability becomes clear.

The limitations should also be stated clearly: AI can still make mistakes in judging business semantics (for example, "duplicate orders" is ambiguous in refund scenarios), so the human review step cannot be skipped; de-identification of sensitive data must come first; when the rule volume is large, process tables in batches to avoid context overflow degrading quality.

V. Final Thoughts

Data cleansing is a chore no team can escape, but the root of the pain is often not that the data is dirty—it's that the rules have no owner and the process has no order. Handing the most time-consuming from-scratch work of "drafting rules" to AI, while people focus on review and decision-making, is a transformation managers can implement immediately.

If you're looking for a stable API with multi-model switching to run this workflow, give https://api.thistoken.ai/register a try—register and start building your rule-generation pipeline.

---

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