Parsing Messy, Real-World Data

TL;DR: Treat ingestion as parse-validate-normalize, where one bad row is logged and skipped rather than allowed to kill the batch. Keep the parser a pure function from bytes to (clean records, rejects), with no I/O or business logic inside, so it is deterministic and trivially testable against the ugly files the customer will actually send.

The shape of the problem

The spine below is parse, validate, normalize with the reject channel drawn, because that branch is what keeps one bad row from taking the batch with it.

The sample file the customer emailed is clean. The file their system exports at 2 a.m. is not. Real exports show up with mixed delimiters, fields quoted in some rows and not others, a header that is missing or duplicated, blank lines, a "Total:" summary row glued to the bottom, dates in three formats, and a stray byte-order mark on the first column name. A parser that assumes the happy path throws on row 40,000 and loses the 39,999 good rows with it.

The discipline that survives contact with these files has three moves. Parse defensively: every row can fail, so wrap per-row parsing and route failures to a reject channel instead of an exception that unwinds the loop. Validate then normalize: check required fields and types, then coerce to one canonical form (trim whitespace, parse dates to ISO, empty string to null) so downstream code sees uniform data. Separate parsing from business logic: the parser turns bytes into typed records and rejects; what you do with a record (insert, dedupe, enrich) lives elsewhere. That separation is what lets you unit-test the parser by feeding it a string and asserting on the output, no database required.

A worked example

import csv, io
from dataclasses import dataclass

@dataclass
class Order:
    order_id: str
    amount: float

def parse_orders(text):
    good, rejects = [], []
    reader = csv.reader(io.StringIO(text))  # csv handles quoting/escaping
    for i, row in enumerate(reader):
        if not row or all(c.strip() == "" for c in row):
            continue                         # skip blank lines silently
        if len(row) < 2:
            rejects.append((i, "too few columns", row)); continue
        oid, raw_amt = row[0].strip().lstrip(""), row[1].strip()
        try:
            amount = float(raw_amt.replace(",", "").replace("$", ""))
        except ValueError:
            rejects.append((i, f"bad amount: {raw_amt!r}", row)); continue
        if not oid:
            rejects.append((i, "missing order_id", row)); continue
        good.append(Order(oid, amount))
    return good, rejects

parse_orders takes a string and returns two lists. It never raises on malformed input, never touches a file or a database, and never logs (the caller logs the rejects). Run it twice on the same bytes and you get byte-identical output, which matters when a re-run must not produce different rows. The caller decides policy: log every reject with its line number, and fail the batch only if the reject rate crosses a threshold (say 5 percent), because a handful of junk rows is normal but half the file failing means the schema changed.

Why interviewers probe this

It is the realest test in the loop because it mirrors week one on a deployment. The screen is whether you reach for csv.reader (which handles quoting and embedded commas) instead of line.split(","), and whether your instinct is to skip-and-log or to throw. The follow-up they hold in reserve: "the batch is half-failing, what do you do?" The answer they want is that you do not silently drop 50 percent; you surface the reject rate, sample the rejects, and treat a spike as a schema-change signal, not a row problem. A second probe is testability: "how do you test this?" If your parser reads a file path internally, you have to write fixtures to disk; if it takes a string, you paste the ugly row inline and assert. That is the case for keeping it pure.

Common misconceptions

• "split(',') is fine for CSV." It breaks on the first quoted field containing a comma. Use a real CSV reader; for other formats use the format's parser, not regex guessing.
• "One bad row should fail the load so we catch problems." It also discards every good row in the batch and turns a data issue into an outage. Skip, log with line numbers, and alert on the rate.
• "Validate and normalize are the same step." Validation rejects what is wrong; normalization makes what is valid uniform. Conflating them hides which rows you dropped and why.
• "Encoding is the customer's problem." A BOM or a Latin-1 file decoded as UTF-8 corrupts the first column or throws mid-stream. Decode explicitly and strip the BOM.

Key takeaways

• Parse as parse-validate-normalize; route bad rows to a reject list with line numbers instead of raising.
• Keep the parser pure (bytes in, clean records plus rejects out): no I/O, no business logic, fully deterministic and unit-testable on strings.
• Use real format parsers (csv module), not split, and decode encodings explicitly.
• Skip and log individual bad rows; fail the batch only when the reject rate signals a schema change, not on the first bad line.