AI-Assisted CSV Cleaning: Email Validation, Deduplication, and Verification Practices

1. A Real Scenario: The Efficiency Problem in Data Cleaning

An operations team member receives a CSV file in poor shape: malformed email addresses, duplicate rows, and empty values mixed together. In the past, work like this meant comparing rows by eye, deleting entries by hand, and checking again and again — hours per file. Now she describes what she needs in plain language to an AI coding tool: “Keep only valid, non-duplicate emails and output the total count.” The tool returns a runnable Python script. Dozens of seconds later, the task is done.

The story reflects a real trend: natural language is becoming a new interface for human–machine collaboration. But what matters to technical practitioners is not the surface efficiency of “hours of work in minutes.” It is the data cleaning logic itself — and a question that is easy to overlook: when the code is generated by AI and we cannot read it, how do we know it is actually reliable?

This article starts from the handling of that “dirty data.” It breaks the task down completely, then discusses implementation and verification one by one.

2. Breaking Down the Task: What Cleaning a “Dirty” CSV Involves

A single-sentence request, translated into engineering language, contains four clear subtasks:

1. Reading and parsing: load the CSV correctly, identify the header, handle encoding (such as UTF-8) and line endings, and avoid field misalignment.
2. Format validation: decide whether each email matches a valid format, and drop empty and obviously invalid entries.
3. Deduplication: remove duplicates from the records that survive.
4. Output and counting: write the cleaned data out and return the final count of valid records.

Turning a vague natural language request into verifiable steps is the starting point for any data engineering work. Whether the code is written by a person or generated by AI, this decomposition has to be led by the requester — because the tool cannot define the business judgments for you, such as what counts as “dirty” and what counts as a “duplicate.”

3. Email Format Validation: From Regex to Edge Cases

The most common approach to email validation is format matching with a regular expression. A practical rule looks like this:

import re
EMAIL_PATTERN = re.compile(r'^[A-Za-z0-9._%+-]+@[A-Za-z0-9.-]+\.[A-Za-z]{2,}$')
def is_valid_email(addr: str) -> bool:
    if not addr:
        return False
    return bool(EMAIL_PATTERN.match(addr.strip()))

One thing has to be stated clearly: a regex validates “format,” not “authenticity.” It cannot tell whether an address actually exists or has been deactivated. Confirming authenticity normally requires an additional MX record lookup (checking whether the domain has mail servers configured) or even a verification email. Writing it explicitly in the script comments — “this step only performs format validation; it does not mean the email is usable” — is an important engineering habit. It prevents downstream colleagues from placing mistaken trust in the results.

The regex itself also needs care against being “too strict” or “too loose.” Too strict and legitimate addresses get rejected (for example, aliases containing +); too loose and obvious invalid entries pass through. Choosing a widely field-tested general-purpose rule is safer than inventing a complex expression of your own.

4. Deduplication Strategy: Simple in Appearance, Detailed in Practice

Deduplication is not simply “delete identical items.” Several details must be handled:

• Case normalization: User@Mail.com and user@mail.com are usually the same email address in business terms, so normalize to lowercase before comparing.
• Whitespace cleanup: data entry often brings in extra spaces, so run strip() first.
• Choice of deduplication data structure: a hash set (set) completes deduplication in a single pass with O(n) time complexity, far better than the O(n²) cost of pairwise comparison.

def clean_csv(input_path: str, output_path: str) -> int:
    seen = set()
    kept = []
    with open(input_path, newline='', encoding='utf-8') as fin:
        reader = csv.DictReader(fin)
        for row in reader:
            email = (row.get('email') or '').strip().lower()
            if not is_valid_email(email):
                continue          # drop invalid format
            if email in seen:
                continue          # drop duplicates
            seen.add(email)
            row['email'] = email
            kept.append(row)
    with open(output_path, 'w', newline='', encoding='utf-8') as fout:
        if kept:
            writer = csv.DictWriter(fout, fieldnames=kept[0].keys())
            writer.writeheader()
            writer.writerows(kept)
    return len(kept)              # return the total number of valid records

This code chains “validate + deduplicate + write + count” into a clear data pipeline in which every step can be understood and verified independently.

5. The Most Important Step: Where the Risk Lies in Code You Cannot Read

Back to how the operations person felt at the beginning: “It ran, but I have no idea what the code is doing.” This is exactly the part of the article that deserves the most warning. A data processing script you put into production without having read it is, in essence, a black box. The risks it may carry include:

• Silent data loss: if the regex is too strict, a large number of legitimate emails may be misjudged as invalid and dropped, and the script will not raise an error.
• Wrong deduplication semantics: without case normalization, the output looks deduplicated but a large amount of duplication was actually missed.
• Encoding traps: if the source file is not UTF-8, reading it may produce garbled text or throw an exception outright, corrupting the whole batch.

The quality of AI-generated code depends heavily on how clearly the input is specified, and it may carry logic that “looks correct but does not cover the edge cases.” A successful run only means the script did not crash; it does not mean the result is correct.

6. Verification and Testing: Making AI-Generated Code Trustworthy

Turning “I cannot read it” into “I trust it” is a solved problem in engineering:

1. Trial run on a small sample: take 10–20 rows and check them by hand first, confirm the output matches expectations, then process the full file.
2. Prepare boundary cases: construct special data such as empty values, very large fields, mixed case, and leading or trailing whitespace, and observe how the script behaves.
3. Write assertion-style tests: pin down the expected results so that any change can be regression-verified.

def test_email_cleaning():
    assert is_valid_email("user@example.com") is True
    assert is_valid_email("bad-email@") is False
    assert is_valid_email("") is False
    assert is_valid_email("user@domain") is False   # missing top-level domain

Once this validation layer exists, there is a safety net in place whether the code was written by hand or generated by AI.

7. From Personal Efficiency to Enterprise Practice: Making the Capability Durable

When data cleaning evolves from a “one-off script” into a “shared team capability,” more engineering questions come up. Scripts should be modular and reusable; they should log how many records were processed and how many were dropped; batch jobs should support a “dry run” mode that outputs statistics without actually writing files. For large-scale, continuous processing across multiple sources, you can rely on general cloud capabilities such as object storage, message queues, container platforms, and monitoring and alerting to build a stable data pipeline, making cleaning tasks schedulable, observable, and reversible. Baidu AI Cloud also offers this category of general cloud services for data processing and analytics scenarios, which can serve as a reference for enterprise-level implementation.

8. Conclusion: AI Writes, You Judge

AI coding tools have dramatically lowered the barrier to writing code, letting non-specialist developers handle data tasks that used to require professional scripting. But what they change is “how code gets generated,” not “who is accountable for the result.” Requirement decomposition, business rule definition, edge case judgment, and result verification remain core steps that cannot be outsourced to a machine.

The right posture toward AI is this: let it take on repetitive code writing, while you hold firm control over validation and verification. Reading the code you run — even just the key parts — is how you stay accountable to the data and to the business.