Data Cleansing - Yenra

Profile before transforming

Keep an untouched source snapshot, then inspect column names, types, missingness, distinct values, and representative outliers. Check the source's encoding, delimiter, date conventions, and identifiers. A postal code or account ID may contain leading zeros that numeric conversion would erase.

Write the rules as a versioned contract with an owner. Preserve the original value alongside any proposed correction when review is needed.

Worked example: classify a small customer extract

This in-memory Python example trims outer whitespace from two required fields and routes repeated IDs to review. The fictional input has four records. It deliberately makes no attempt to infer email validity or decide whether two rows belong to the same person.

import csv
import io

source = io.StringIO('''customer_id,email
001, alice@example.test
002,
001,alice@example.test
003,bob@example.test
''')

clean, review, seen = [], [], set()
for row_number, raw in enumerate(csv.DictReader(source), start=2):
    row = {key: value.strip() for key, value in raw.items()}
    reason = None
    if not row["customer_id"] or not row["email"]:
        reason = "missing_required_value"
    elif row["customer_id"] in seen:
        reason = "repeated_id"
    if reason:
        review.append({"row": row_number, "reason": reason,
                       "original": raw})
    else:
        clean.append(row)
        seen.add(row["customer_id"])

assert len(clean) == 2
assert len(review) == 2
assert clean[0]["customer_id"] == "001"
print(len(clean), len(review))  # 2 2

The accepted records are IDs 001 and 003. Row 3 lacks an email; row 4 repeats an already accepted ID. No source file is changed. The earlier occurrence is only provisionally accepted: a production conflict-resolution rule may need to hold every occurrence of a repeated ID for review.

This short example assumes exactly the stated columns and correctly shaped rows. For real files, validate headers and row lengths before calling strip(). Python's CSV documentation explains that DictReader can represent missing fields with None and extra fields separately. Open CSV files with an explicit encoding and newline=''.

Make the process reproducible

1. Identify the input. Record a source version or content hash and when it was obtained.
2. Apply explicit rules. Record the cleaning code and rule version with the run.
3. Separate outcomes. Produce accepted records, review records, and a summary with reasons.
4. Reconcile counts. Every input record should have a documented disposition; grouping or merging requires its own accounting.
5. Publish after validation. Keep the original and avoid overwriting the previous usable output until the new result passes checks.

Test an empty input, missing columns, extra fields, non-ASCII text, duplicate identifiers, and already-clean records. Check that applying a normalization twice does not keep changing values. A lower rejection count is not evidence of better quality if the process silently guesses missing facts.

Where AI assistance fits

Give the assistant the schema, synthetic examples, permitted transformations, and expected outputs. Ask it to explain each rule and identify assumptions. Keep actual personal or confidential records within the organization's approved tools and access arrangements.

Using this customer schema and synthetic sample, propose a profiling report and a deterministic cleaning function. Preserve identifiers as strings. Do not infer missing values or merge similar names. Return accepted and review records with reasons, and propose tests for malformed rows and conflicting IDs.

If a model helps standardize free-text categories, retain its proposed label and the original text for evaluation. Establish allowed labels and measure performance on a reviewed sample. Free-form source text is data to classify, not instructions that can change the cleaning rules.

Can fuzzy matching automatically merge customers?

Similarity can identify candidates. A merge decision needs additional evidence, such as an authoritative identifier, and a way to reverse mistakes. Households, shared inboxes, and spelling changes make name or email similarity insufficient on its own.

How does cleansing relate to AI systems?

A retrieval or analysis system needs reliable source records and traceable transformations. Cleaning should preserve provenance and meaningful distinctions, including uncertainty. Removing inconvenient examples can make an evaluation misleading.

Use job scheduling when this becomes recurring work, with an alert for missing inputs or unexpected rejection rates.