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 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 (an invisible marker some tools write at the start of a file, which then glues itself to the first header). 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.

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

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("\ufeff"), row[1].strip()  # \ufeff is the byte-order mark, spelled out so it is visible
        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 handles the row-level failures shown here as rejects. It never touches a file or a database, and never logs (the caller logs the rejects). Run it twice on the same text and you get equal lists of records and rejects. The index i counts CSV records from zero; it is not a physical line number, because a quoted record can span several lines. The CSV reader documentation distinguishes record counts from line_num.

This is a small example, not a complete ingestion contract. The CSV reader can still raise, for example when a field exceeds its size limit. The caller must handle a file-level parse failure; do not assume that skipping one physical line can recover a broken quoted record. A real schema should also reject non-finite amounts and define a money representation, header handling and extra-column policy.

The caller decides policy: log every reject with its record index. For recoverable row errors, fail the batch if the reject rate crosses a threshold (say 5 percent). A handful of junk rows may be expected; half the file failing is a reason to investigate a schema change.

Run it on a deliberately ugly string and the two lists tell the story. Feed it a file with a byte-order mark before the first id, a blank line, an amount of "abc", a row with one column, a row with an empty id, a quoted id containing a comma, and a "Total:" row. Two orders come back clean, including the quoted one, because the CSV reader handled the embedded comma. Four rows land in rejects, each with its zero-based record index and a reason: bad amount, too few columns, missing order id, and the Total row rejected as a bad amount because its second column is empty. Nothing raised, and running it again gives the identical two lists.

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.