Works on the Sample, Fails on Real Data

Scenario

Example deployment: an FDE builds an invoice-reconciliation pipeline for a logistics company. During discovery the customer shares a 200-row CSV export; the pipeline handles it perfectly and the demo goes well. The first nightly run uses the real ERP export: 1.4 million rows from 11 subsidiaries.

What you see in production

The job crashes at row 48,211 with UnicodeDecodeError. After a quick fix it finishes, but finance reports totals that are off by 9%: some subsidiaries use 1.250,00 for twelve hundred and fifty, dates are DD/MM/YYYY in Europe and MM/DD/YYYY in the US entity, and 3,000 invoices appear twice with different spacing in the supplier name. Rows that failed parsing were skipped without a trace.

The naive code

import csv

with open("erp_export.csv") as f:                     # platform default encoding
    for row in csv.DictReader(f):
        amount = float(row["amount"].replace(",", ""))  # assumes one number format
        date = datetime.strptime(row["date"], "%m/%d/%Y")
        try:
            save(row["invoice_id"], amount, date)
        except Exception:
            continue                                     # silently drop anything odd

Reproduce it

A small sample of what real exports contain, and what a careful parser does with it:

from datetime import datetime
from decimal import Decimal

SUBSIDIARY_FORMATS = {"DE": {"decimal": ",", "date": "%d.%m.%Y"}, "US": {"decimal": ".", "date": "%m/%d/%Y"}}
rows = [
    {"sub": "US", "id": "U-1", "amount": "1,250.00", "date": "03/04/2026", "supplier": "Acme Corp"},
    {"sub": "DE", "id": "D-1", "amount": "1.250,00", "date": "04.03.2026", "supplier": "Müller GmbH"},
    {"sub": "DE", "id": "D-2", "amount": "980,5", "date": "31.02.2026", "supplier": "Weber AG"},
    {"sub": "US", "id": "U-2", "amount": "1,250.00", "date": "03/04/2026", "supplier": "Acme  Corp "},
]

def parse_amount(text, decimal_sep):
    thousands = "." if decimal_sep == "," else ","
    return Decimal(text.replace(thousands, "").replace(decimal_sep, "."))

clean, quarantine, seen = [], [], set()
for r in rows:
    fmt = SUBSIDIARY_FORMATS[r["sub"]]
    key = (r["sub"], " ".join(r["supplier"].split()).lower(), r["amount"], r["date"])
    try:
        amount = parse_amount(r["amount"], fmt["decimal"])
        date = datetime.strptime(r["date"], fmt["date"]).date()
    except Exception as e:
        quarantine.append((r["id"], f"{type(e).__name__}: {e}"))
        continue
    if key in seen:
        quarantine.append((r["id"], "probable duplicate of an earlier row"))
        continue
    seen.add(key)
    clean.append((r["id"], amount, date.isoformat()))

print("clean:", clean)
for rid, reason in quarantine:
    print(f"quarantined {rid}: {reason}")

clean: [('U-1', Decimal('1250.00'), '2026-03-04'), ('D-1', Decimal('1250.00'), '2026-03-04')]
quarantined D-2: ValueError: day is out of range for month
quarantined U-2: probable duplicate of an earlier row

Root cause

The pipeline was designed from a sample that didn't represent the population: one subsidiary, one locale, one encoding. Parsing assumptions were implicit, errors were swallowed, and there were no reconciliation checks (row counts and totals compared with the source), so wrong results looked like success.

The fix

1. Profile real data early: ask for a full (or large, anonymised) export in discovery and profile every column: formats, nulls, distinct values, encodings.
2. Make assumptions explicit: per-source configuration for encoding, decimal separator, date format and time zone.
3. Validate and quarantine: every rejected row is stored with a reason; nothing is dropped silently.
4. Reconcile: compare row counts and totals with the source system after each run, and alert on differences.
5. Report to the customer: a daily data-quality report that their data owners can act on.

import codecs

def open_export(path, encoding):
    # explicit encoding per source; replace undecodable bytes and count them instead of crashing
    return open(path, encoding=encoding, errors="replace", newline="")

Trade-offs

• Quarantining means partial results; agree with the customer whether partial loads are acceptable or the run must fail.
• Per-source configuration adds setup work, but it's documentation of reality that someone will need anyway.
• Fuzzy duplicate detection can merge legitimate records; flag for review rather than auto-merging financial data.

Detect it in production

• Reconciliation checks (counts, sums) per run, alerting on mismatch.
• Quarantine rate per source over time; a jump usually means the source changed.
• Data-quality tests (Great Expectations, dbt tests) on the output tables.

Key points

• Samples lie; profile real, full-size data during discovery.
• Make parsing assumptions explicit per source, and never swallow errors silently.
• Quarantine with reasons, reconcile totals, and report quality to the customer.