Case Study 2: The CSV Export That Broke Every Quarter

"It broke on a product called Hi-Vis Jacket, 3M™ Scotchlite™, 'Highway' Orange. Every character in that name is a landmine."

Executive Summary

Kestrel exports a daily product catalogue to three wholesale partners as CSV. Over two years it broke seven times. Every break was a different partner, a different symptom, and the same root cause: CSV has no types, no schema, and no standard, so every producer and consumer makes assumptions and the assumptions differ.

This case study catalogues all seven failures, because the catalogue is more useful than any single one. It then covers what Kestrel changed — which was not "stop using CSV," because two of the three partners genuinely cannot read anything else.

It is a small case study about an unglamorous problem, and it is here because interchange with systems you do not control is a permanent condition of the job, and CSV is what that looks like.

Skills applied: CSV's specific failures (§11.2); format choice for interchange (§11.9); schema location (§11.5); data contracts (Chapter 17, previewed).

Background

The export. products.csv — 47,000 rows, 14 columns — generated nightly and delivered to three partners:

Why CSV. Partners A and B genuinely cannot read anything else. This is the ordinary situation and it is not stupidity: A's ERP was configured in 2011 and changing its import module is a six-figure project; B is one person with a spreadsheet who is very good at their job.

The Problem — All Seven Failures

1. The comma in the product name

Hi-Vis Jacket, Class 3 — a comma inside a field.

Kestrel quoted it correctly per RFC 4180. Partner A's importer split on commas without honoring quotes, producing a row with 15 fields where 14 were expected, and shifted every subsequent column by one. Prices landed in the weight column.

Detected: by partner A, four days later, after they had repriced 1,200 products.

2. The quote in the product name

Highway "Orange" — a double quote inside a quoted field.

RFC 4180 says double it: "Highway ""Orange""". Partner C's parser handled it. Partner B's Excel handled it. Partner A's importer treated the doubled quote as a field terminator.

3. The newline in the description

A description containing a line break, quoted correctly, spanning two physical lines.

All three partners broke, in three different ways: A truncated the row, B split it into two rows, and C raised an exception — which was the good outcome, and the only one detected the same day.

4. The leading zero

SKU 0004821. Excel interpreted it as a number and displayed 4821. Partner B's re-export back to Kestrel — for an inventory reconciliation — contained 4821, which matched nothing.

This one was not a parsing bug. Every layer behaved correctly per its own rules. Excel's rule is "a string of digits is a number," and that rule is right most of the time.

5. The date

03/04/2025. Partner A read it as 3 April; Partner B's Excel, in a US locale, read 4 March.

Kestrel had intended ISO-8601 and a code change had introduced a locale-dependent formatter.

Detected: eleven weeks later, when a seasonal promotion's start date was wrong in a partner's system.

6. The encoding

A product name containing ™ and é. Kestrel wrote UTF-8. Partner A's importer assumed Windows-1252.

3M™ became 3Mâ„¢. Not an error, not a crash — just wrong text, in a catalogue, for three weeks.

7. The null

An optional_dimension_cm column, empty for most products.

Kestrel wrote an empty field. Partner C's parser produced an empty string, not a null, and their downstream validation rejected every row with an empty string in a numeric column — 41,000 of 47,000 rows.

⚠️ Failure Mode — Seven failures, one cause

None of these is a bug in Kestrel's exporter. It wrote valid RFC 4180 CSV in UTF-8 every time.

The cause is that CSV has no schema and no standard, so every property that matters is a convention that both sides must independently share:

Property	Convention	Failure
Field separator	comma	1
Quoting	RFC 4180 doubling	2
Embedded newlines	quoted, multi-line	3
Type of a digit string	text or number	4
Date format	ISO-8601 or locale	5
Character encoding	UTF-8 or CP1252	6
Null representation	empty, NULL, \N, NA	7

Seven properties, seven failures, one per property. That is not coincidence — it is what happens when a format leaves seven things unspecified and you have three consumers.

A typed format with a schema — Parquet, Avro — specifies all seven in the file. This is the concrete content of §11.1's second question, and the reason "CSV is an interchange format, never an internal one" is a rule rather than a preference.

The Analysis

The team's first instinct was to move everyone to Parquet. Partner C agreed immediately. Partners A and B could not, and no amount of argument changes an ERP import module.

So the question became: given that CSV is unavoidable for two of three consumers, how do you make it not break?

The answer they arrived at is the useful part, and it generalizes to any interchange with a system you do not control.

Specify every one of the seven properties, in writing, per partner. Not "we send CSV" but a document that pins each convention. The products.csv contract:

# platform/contracts/export_products_partner_a.yml
format: csv
encoding: UTF-8                    # with BOM -- partner A's importer requires it
line_ending: CRLF
delimiter: ","
quoting: minimal                   # RFC 4180; quote only when necessary
quote_char: '"'
escape: doubled                    # "" inside a quoted field
embedded_newlines: FORBIDDEN       # stripped and replaced with a space at export
null_representation: ""            # empty field
header: required
date_format: "%Y-%m-%d"            # ISO-8601, always
decimal_separator: "."
columns:
  - {name: sku,              type: string, notes: "leading zeros significant; quoted always"}
  - {name: product_name,     type: string, max_length: 200}
  - {name: list_price_cents, type: integer, notes: "CENTS, not dollars"}
  - {name: weight_grams,     type: integer}
  # ...

Three things in that file do real work:

embedded_newlines: FORBIDDEN. Kestrel strips them at export rather than quoting them correctly. This is technically a loss of fidelity and it removes failure 3 entirely. When a consumer cannot handle a valid construct, the cheapest fix is not to emit it.

encoding: UTF-8 with BOM, per partner. Partner A's importer needs a byte-order mark to detect UTF-8; partner C's parser chokes on one. The same data is exported twice, differently. That feels wrong and it is much cheaper than the alternative.

sku quoted always. Not "when necessary." Quoting 0004821 unconditionally is what stops Excel converting it, and it is the fix for failure 4 that does not require Excel to behave differently.

📐 Design Decision — Fix the producer, or fix the consumer?

Six of the seven failures are, strictly, defects in a consumer's parser. Kestrel emitted valid CSV and something on the other end mishandled it.

The case for fixing the consumer: it is correct. Kestrel is not wrong. Partner A's importer should honor RFC 4180 quoting, and a world in which it does is better.

The case for accommodating the consumer, which won: Kestrel has no authority over partner A's ERP, the vendor has no incentive to change it, and the practical choice is between "emit data they can read" and "be right." A daily export that breaks quarterly is a worse outcome than a slightly lossy export that never does.

What accommodation costs: three export variants instead of one, a contract file per partner, and a permanent asymmetry where Kestrel absorbs complexity generated elsewhere. It also means that when partner A eventually upgrades, nobody will remember why the BOM is there.

The mitigation for that last cost is the contract file itself, with the reason recorded beside each setting. # partner A's importer requires it is nine words that will save someone an afternoon in 2029.

The Decision

Four changes.

1. A contract file per partner, as above, in version control, with reasons recorded.

2. The exporter reads the contract. One code path, parameterized by the contract, rather than three exporters that drift.

3. A round-trip test in CI. For each partner's contract, export a fixture containing every known landmine — comma, quote, newline, leading zero, non-ASCII, empty value, an extreme date — then read it back with a parser configured per that partner's stated behavior, and assert equality.

That fixture is the most valuable artifact of the whole exercise, and it is 30 rows:

sku,product_name,list_price_cents,weight_grams,released_on,optional_dimension_cm
"0004821","Hi-Vis Jacket, Class 3",8995,840,2025-03-04,
"0004822","Highway ""Orange"" Vest",4995,320,2025-03-04,42
"0004823","3M™ Scotchlite™ Trim",1295,80,2025-11-28,
"0004824","Café Apron — Blanc",2495,210,2024-12-31,15

4. Partner C moved to Parquet. They could, so they did. Their integration has not broken since.

What Happened

Zero export failures in the eighteen months since, against seven in the preceding two years.

Three observations from the review.

The round-trip test caught two problems before they shipped. A change to the product-name field length, and a locale-dependent date formatter reintroduced by a dependency upgrade — the same bug as failure 5, caught at build time.

Partner B's Excel is still Excel. The leading-zero fix works because the field is quoted, and if Partner B ever re-exports from Excel the problem returns, because Excel drops the quoting on save. Kestrel documented this as a known limitation rather than solving it, because it cannot be solved from Kestrel's side. Writing down a limitation you cannot fix is worth doing; it is what stops the next engineer spending a week on it.

The team's own summary is worth quoting: "We spent two years treating each break as a bug and eighteen months treating the format as the bug. The second approach worked. CSV is not a data format, it is a family of data formats that all have the same file extension."

Lessons

1. CSV leaves seven properties unspecified, and with three consumers you will get seven failures, one per property.

2. Valid CSV is not sufficient. Six of seven failures were consumer defects and all seven were Kestrel's problem.

3. Specify every convention in writing, per consumer — delimiter, quoting, escaping, newlines, encoding, nulls, dates, decimals. A contract file, in version control.

4. Record the reason beside each setting. # partner A's importer requires it is what stops someone removing it in 2029.

5. When a consumer cannot handle a valid construct, the cheapest fix is not to emit it. Stripping embedded newlines is lossy and eliminates a whole failure class.

6. A landmine fixture is the highest-value artifact here — comma, quote, newline, leading zero, non-ASCII, empty, extreme date. Thirty rows, round-tripped in CI.

7. Move the consumers who can move. Partner C went to Parquet and has not broken since. Do not let the constrained consumers set the format for everyone.

8. Write down limitations you cannot fix. It is what stops the next engineer spending a week rediscovering that Excel drops quoting on save.

Questions for Discussion

1. Six of seven failures were consumer defects. Construct the argument for refusing to accommodate them, and say what would have to be true about Kestrel's relationship with partner A for it to win.

2. Kestrel strips embedded newlines rather than quoting them — a deliberate loss of fidelity. When is that unacceptable? Give a field where you would refuse.

3. The landmine fixture is 30 rows. Write ten more rows for it, each targeting a failure the seven above do not cover.

4. Partner B re-exports from Excel and the leading-zero problem returns. Design a validation Kestrel could run on the returned file that catches it, without requiring anything from Partner B.

5. Failure 5, the locale-dependent date, was reintroduced by a dependency upgrade. What class of test catches this generally, and where in a pipeline does it belong?

6. Failure 6 — the encoding — produced wrong text for three weeks with no error anywhere. Design the check. What is its false-positive rate on a catalogue containing legitimate accented characters?

7. The team's summary calls CSV "a family of data formats that all have the same file extension." Is there any other format in this book with the same property? What makes the difference?