Integrating Customer Data: Mapping, Cleaning and Quarantine

Every customer stores data differently. Their “priority” might be High, Medium, Low and Urgent, while your product expects severity 1 to 3. Their dates might mix formats. Some records will be broken. Data integration is the work of turning their data into the shape your product expects, without losing or inventing information. Unlike a general ETL pipeline, the rules here must be agreed with a specific customer, because only they know what their values mean.

Agree the mapping in writing

For each field, agree with the customer:

• which of their fields maps to which product field,
• how each value maps, including blanks and unknown values,
• which formats to expect, such as dates as day/month or month/day.

Keep the mapping as data, in a file or table, not buried in code. Then it can be reviewed by the customer and changed without a code release.

Transform, and quarantine what fails

This example applies an agreed mapping to support tickets. Rows that cannot be converted are quarantined with a reason, not dropped silently or guessed:

from datetime import datetime

# Customer rows as received (after removing exact duplicates)
customer_rows = [
    {"ticket_id": "T-1", "created_at": "2026-10-01 09:12", "category": "Billing",  "priority": "High"},
    {"ticket_id": "T-3", "created_at": "2026-10-02 14:30", "category": "Delivery", "priority": "Low"},
    {"ticket_id": "T-4", "created_at": "02/10/2026 15:00", "category": "Delivery", "priority": "Medium"},
    {"ticket_id": "T-5", "created_at": "2026-10-03 08:45", "category": "Returns",  "priority": "Urgent"},
    {"ticket_id": "T-6", "created_at": "yesterday",        "category": "billing",  "priority": ""},
]

# The mapping agreed with the customer, kept as data so it can be reviewed and changed without code
MAPPING = {
    "category": {"billing": "billing", "delivery": "shipping", "returns": "returns"},
    "priority": {"low": 1, "medium": 2, "high": 3, "urgent": 3, "": 2},   # urgent = high; blank = medium (agreed)
    "date_formats": ["%Y-%m-%d %H:%M", "%d/%m/%Y %H:%M"],                  # customer confirmed day/month order
}

def parse_date(text):
    for fmt in MAPPING["date_formats"]:
        try:
            return datetime.strptime(text, fmt).isoformat()
        except ValueError:
            pass
    raise ValueError(f"unknown date format: {text!r}")

def to_product(row):
    """Convert one customer row to the product's schema, or raise with a clear reason."""
    return {
        "external_id": row["ticket_id"],
        "opened_at": parse_date(row["created_at"]),
        "queue": MAPPING["category"][row["category"].strip().lower()],
        "severity": MAPPING["priority"][row["priority"].strip().lower()],
    }

loaded, quarantined = [], []
for row in customer_rows:
    try:
        loaded.append(to_product(row))
    except (ValueError, KeyError) as e:
        quarantined.append((row["ticket_id"], str(e)))

for r in loaded:
    print("loaded     ", r)
for tid, reason in quarantined:
    print("quarantined", tid, "-", reason)

loaded      {'external_id': 'T-1', 'opened_at': '2026-10-01T09:12:00', 'queue': 'billing', 'severity': 3}
loaded      {'external_id': 'T-3', 'opened_at': '2026-10-02T14:30:00', 'queue': 'shipping', 'severity': 1}
loaded      {'external_id': 'T-4', 'opened_at': '2026-10-02T15:00:00', 'queue': 'shipping', 'severity': 2}
loaded      {'external_id': 'T-5', 'opened_at': '2026-10-03T08:45:00', 'queue': 'returns', 'severity': 3}
quarantined T-6 - unknown date format: 'yesterday'

Note how decisions from discovery became rules: “billing” and “Billing” are the same, “Urgent” means high, a blank priority means medium, and “02/10/2026” is day/month. The record with “yesterday” as a date is set aside with a clear reason, so someone can fix it at the source instead of it becoming a wrong date in your product.

Reconcile with the source

After every load, check that numbers add up: records received, loaded and quarantined should equal the source count, and key totals (such as number of open tickets per queue) should match the customer’s own reports. Share the reconciliation with the customer; it builds trust quickly. See data quality checks and contracts.

Keep it running

• Monitor the quarantine: a sudden rise usually means the customer changed something.
• Review unknown values regularly and extend the mapping with the customer.
• Make loads idempotent, upserting on the external ID, so reruns and backfills are safe.
• Version the mapping, so you can explain what rule applied to any record.

Common mistakes

1. Guessing the meaning of values instead of asking the customer.
2. Dropping bad records silently, so totals never match and nobody knows why.
3. Mapping hidden in code that only the engineer can read.
4. No reconciliation, so errors are found by users months later.

Key takeaway

Integrating customer data means agreeing a mapping with the customer, keeping it as reviewable data, normalising values and formats, quarantining what cannot be converted, and reconciling every load against the source. Next: running the solution where the customer needs it, in deploying in customer environments.