One Column Was Always Empty, and the Exit Code Was Still 0

The daily ETL job finished in forty seconds, wrote a clean summary table, and returned exit code 0. The row counts matched the source CSV exactly, the timestamps looked right, and the health check reported everything healthy. One problem: the customer_id column in the output was empty on every single row, and nobody noticed for three days.

The setup was deliberately boring. I ran a small parser on a free server that pulled a daily CSV export from a vendor, normalized a few fields, and wrote the result into a SQLite table for reporting. I had generated the parser with a free model on MonkeyCode (Disclosure: This article was prepared as part of MonkeyCode's product outreach.) because the transformation was repetitive and I did not want to hand-write another csv.DictReader loop. The free server was enough for the job, the model's code looked reasonable, my local test passed, and I deployed it without much ceremony.

The first two hypotheses were both wrong

My first instinct was to blame the source data. I downloaded the exact export the server had processed and opened it in a spreadsheet editor, and the customer_id column was full of values. The data was fine, so I moved to the mapping logic. I printed the column names from the parsed rows, and they looked identical to the CSV headers. That is the moment I should have stopped trusting my eyes, because "looked identical" and "were identical" are very different statements.

The breakthrough was a two-byte check

The actual root cause took about thirty seconds to find once I stopped looking at rendered text. I ran xxd on the first line of the production CSV and saw three extra bytes before the first header name: ef bb bf. That is the UTF-8 byte order mark, and spreadsheet editors hide it completely. csv.DictReader does not hide it, though — it keeps the BOM as part of the first dictionary key, so the parsed rows had a key that looked like customer_id but was actually \ufeffcustomer_id.

head -c 16 export.csv | xxd
# 00000000: efbb bf63 7573 746f 6d65 725f 6964 2c70  ...customer_id,p

The generated code used defensive .get() access, so every lookup with the clean string returned None instead of raising a KeyError. Here is the minimal reproduction:

import csv
from io import StringIO

raw = b"\xef\xbb\xbfcustomer_id,plan\n123,free\n"
rows = list(csv.DictReader(StringIO(raw.decode("utf-8"))))

print(rows[0].get("customer_id"))   # None, no exception
print(repr(list(rows[0].keys())[0]))
# '\ufeffcustomer_id'

Notice what did not happen: no exception, no warning, no failed row. The writer serialized None as an empty cell, the pipeline stayed green, and the data quietly rotted.

Why it passed locally and failed on the server

The local test passed because my fixture was a file I had created myself, and my editor saved it without a BOM. The vendor's export service added the BOM, the free server processed the real file, and the difference was invisible to every layer of the pipeline. The row count matched because every row was present. The exit code was 0 because nothing threw. The monitoring was silent because I had only checked counts and status codes, never content. The uncomfortable part is that every layer of the stack did its job correctly, and the result was still wrong.

The fix was one line, plus one assertion

with open("export.csv", encoding="utf-8-sig") as f:
    rows = list(csv.DictReader(f))

assert all(r.get("customer_id") for r in rows), "missing customer_id"

utf-8-sig strips the BOM when it is present and is harmless when it is not. The assertion turns a silent empty column into a loud failure, which is exactly what a free server needs when nobody is watching the logs in real time.

I also added a schema check, because a renamed or reordered header is the same class of bug:

expected = {"customer_id", "plan", "created_at"}
actual = set(rows[0].keys())
assert expected <= actual, f"schema changed: {actual}"

This assertion would have caught the BOM on day one, because the parsed key would not match the expected header.

The reusable debugging workflow

This failure was not really about CSV, and it was not really about BOMs. It was about a class of bug where every observable signal says success while the actual output is wrong. The workflow I use now looks like this:

1. Reproduce with the exact production file, not a hand-made fixture. If the bug does not reproduce, the fixture is lying to you.
2. Compare bytes, not appearances. repr() the keys, xxd the first line, and check what the parser actually sees.
3. Assert on content, not just counts. Row counts measure presence, and exit codes measure crashes; neither measures correctness.
4. Treat silent None and empty strings as first-class failure modes. In Python, a missing key often produces a value, not an error.

Limitations and who should skip this

This approach is not universal. If you control the export service, fix the BOM upstream and delete the workaround. If you generate the files yourself, you probably do not need the extra assertions. And if you have a real monitoring stack with data-quality checks, the content assertion belongs there, not in the parser. The technique matters most for small jobs on free infrastructure where the monitoring budget is close to zero and a three-day silent failure is a realistic outcome.

The cheapest debugging tool I reached for this week was a hex dump, not a fancier model or a bigger server. The free model wrote the parser quickly, but the real lesson was that production data will not match my local assumptions. Next time a field is mysteriously empty, check the bytes before you check the logic.