Forward Deployed Engineering Tutorial · Integration Engineering · Lesson 11 of 24

Mapping and Cleaning Customer Data

Handle real customer data: field mapping, dates and time zones, encodings, duplicates, and validation reports customers can act on.

Create a free account to save this page, take notes and track your progress. Sign in

Real data is messy

Expect different date formats in one column, numbers with thousands separators, trailing spaces, Windows-1252 encodings, duplicate records with slightly different spellings, missing values written as N/A, - or 0, and time zones nobody wrote down.

Map, normalise, validate

Mapping: a documented table from source fields to your schema, agreed with the data owner.
Normalise: trim, case, parse dates with an explicit time zone, parse amounts as decimals (never floats for money).
Validate: required fields, ranges, referential checks; quarantine bad rows with a reason instead of dropping them silently.
Report: give the customer a clear list of rejected rows so their team can fix the source.

A validation report instead of silent dropsTry it yourself

from datetime import datetime
from decimal import Decimal, InvalidOperation

rows = [
    {"id": "A-1", "amount": "1,250.00", "date": "2026-03-04", "email": " [email protected] "},
    {"id": "A-2", "amount": "N/A", "date": "04/03/2026", "email": "[email protected]"},
    {"id": "A-3", "amount": "99.9", "date": "2026-13-01", "email": "cho@example"},
    {"id": "A-1", "amount": "1250", "date": "2026-03-04", "email": "[email protected]"},
]

clean, rejected, seen = [], [], set()
for r in rows:
    errors = []
    try:
        amount = Decimal(r["amount"].replace(",", ""))
    except InvalidOperation:
        errors.append(f"amount '{r['amount']}' is not a number")
    try:
        date = datetime.strptime(r["date"], "%Y-%m-%d").date()
    except ValueError:
        errors.append(f"date '{r['date']}' is not YYYY-MM-DD")
    email = r["email"].strip().lower()
    if "." not in email.split("@")[-1]:
        errors.append(f"email '{email}' has no domain")
    if r["id"] in seen:
        errors.append("duplicate id")
    if errors:
        rejected.append((r["id"], errors))
        continue
    seen.add(r["id"])
    clean.append({"id": r["id"], "amount": amount, "date": date.isoformat(), "email": email})

print("clean:", clean)
for rid, errors in rejected:
    print(f"rejected {rid}: {'; '.join(errors)}")

from datetime import datetime
from decimal import Decimal, InvalidOperation

rows = [
    {"id": "A-1", "amount": "1,250.00", "date": "2026-03-04", "email": " Ana@Example.com "},
    {"id": "A-2", "amount": "N/A", "date": "04/03/2026", "email": "ben@example.com"},
    {"id": "A-3", "amount": "99.9", "date": "2026-13-01", "email": "cho@example"},
    {"id": "A-1", "amount": "1250", "date": "2026-03-04", "email": "ana@example.com"},
]

clean, rejected, seen = [], [], set()
for r in rows:
    errors = []
    try:
        amount = Decimal(r["amount"].replace(",", ""))
    except InvalidOperation:
        errors.append(f"amount '{r['amount']}' is not a number")
    try:
        date = datetime.strptime(r["date"], "%Y-%m-%d").date()
    except ValueError:
        errors.append(f"date '{r['date']}' is not YYYY-MM-DD")
    email = r["email"].strip().lower()
    if "." not in email.split("@")[-1]:
        errors.append(f"email '{email}' has no domain")
    if r["id"] in seen:
        errors.append("duplicate id")
    if errors:
        rejected.append((r["id"], errors))
        continue
    seen.add(r["id"])
    clean.append({"id": r["id"], "amount": amount, "date": date.isoformat(), "email": email})

print("clean:", clean)
for rid, errors in rejected:
    print(f"rejected {rid}: {'; '.join(errors)}")

Output

clean: [{'id': 'A-1', 'amount': Decimal('1250.00'), 'date': '2026-03-04', 'email': '[email protected]'}]
rejected A-2: amount 'N/A' is not a number; date '04/03/2026' is not YYYY-MM-DD
rejected A-3: date '2026-13-01' is not YYYY-MM-DD; email 'cho@example' has no domain
rejected A-1: duplicate id

Test yourself

Why use Decimal rather than float for invoice amounts?

Floats can't represent many decimal amounts exactly, causing rounding errors
Decimal is faster
JSON requires Decimal
Floats can't be negative

Show answer

Answer: Floats can't represent many decimal amounts exactly, causing rounding errors

0.1 + 0.2 isn't exactly 0.3 in binary floating point; money needs exact decimal arithmetic.

Was this page helpful?