Data-format questions for interviews and reviews

An interviewer asks: “Sellers will upload their product catalogues to our marketplace, from 20 rows to 50,000. What format do they upload, and how do we read it?” A weak answer says “CSV” in its first sentence and stops; a strong one takes five minutes, makes seven decisions in order and says what each one costs. In a code review the same knowledge looks smaller: paise = int(float(amount) * 100) turns an amount of 19.99 into 1998 paise, and someone in the team should see it.

You will answer data-format design questions in a fixed order of decisions, follow one complete answer with the check that it designs, find six bugs that data-format reviews catch, each run before and after its fix, and explain a choice about streaming, canonical forms or validation in three sentences.

A strong answer moves from the readers to the tests

Here is how two answers to the catalogue question begin. The first: “CSV, because everyone has Excel.” The second: “Who writes these files, and who reads them? Sellers write them in spreadsheets, so the format must be one that a spreadsheet saves, and the catalogue reads them, so every price and code must arrive exact.” Both may end with CSV, but only the second can say why, which cases would change the choice, and what the platform does with a broken file.

A data-format design question tests that habit: starting from the people and the data, not from a favourite format. A strong answer makes the same seven decisions every time, in an order where each one narrows the next. Step through them on the catalogue question:

The order is the point. The writers and readers decide which formats are possible at all; the data model decides which values must survive; only then is a format chosen, and its schema written down. Parsing and limits say what the platform does with the files that real people send, evolution says how the second version arrives, and the tests are made of damaged files, because a design is only as good as its handling of the file nobody expected. The last step also shows what a weak answer misses: a requirement that changes, images attached to each product for example, sends the answer back to the format, and a strong answer names that requirement before the interviewer asks.

Worked answer: the format of a catalogue upload

This is the catalogue question answered in full, the way it could be said in five minutes.

Writers and readers. Most sellers keep their catalogue in a spreadsheet and upload it from there; a few large ones upload from their own systems. The catalogue service reads every file, and the seller reads the report of what went wrong, so the report is part of the design. Files run from 20 rows to 50,000, about 5 MB at 100 bytes a row.

Data model. One row per product variant: a sku and a barcode (gtin) that are codes, so text; price and mrp in paise, as integers; stock, a whole number; and a title in any script. A GTIN can “be 8, 12, 13, or 14 digits long”, in the words of GS1 US, so it is checked as digits of those lengths, never as a number.

Format. CSV in UTF-8 for spreadsheet users, because every spreadsheet saves it and it is read one row at a time; JSON LinesJSON Lines: A text format with one JSON value on each line, in UTF-8, every line ended by a line feed; also called JSONL, and close to NDJSON. Each line is parsed on its own, so a file is read one record at a time. More in “JSON Lines, JSONC, JSON5 and other variants” for sellers whose systems upload, because it keeps strings as strings and integers as integers; spreadsheet files converted to rows at the edge, never read deep inside the platform. Answer two questions about your own sellers to see how the table decides:

Schema. The column list with one rule per column, published with a template that sellers download, and the same list in the check that reads the uploads, so the two cannot drift apart.

Parsing and limits. Read the bytes, decode them as UTF-8 with a byte-order markbyte-order mark: The character U+FEFF at the very start of a text: the bytes EF BB BF in UTF-8, FF FE or FE FF in UTF-16. It names the encoding, and many parsers refuse it unless it is removed first. More in “UTF-8, BOMs and line endings in data files” allowed, read every cell as text, and convert each by its column’s rule; a file above the size limit is refused before it is read. Here is an upload with the mistakes that sellers’ spreadsheets really make, and the check that the answer describes:

sku,title,price,mrp,stock,gtin
0042-RED,Cotton kurta (red),799.00,999.00,12,8901234567890
0042-BLU,Cotton kurta (blue),"1,299.00",1499.00,4,8901234567906
TEA-250,Assam tea 250 g,249.5,260,abc,8901234567913
TEA-250,Assam tea 500 g,449.00,480.00,8,8.90123E+12
LAMP-01,,1999.00,2499.00,3,8901234567937
"DIYA-12","मिट्टी के दीये, 12 का सेट",149.00,199.00,40,8901234567944
MUG-07,Steel mug 300 ml,449.00,399.00,25,8901234567951

"""The upload check of the worked answer. A seller's catalogue arrives as CSV in UTF-8, with or without a byte-order
mark; each cell is read as text and converted by its column's rule, so no spreadsheet guess and no float reaches the
catalogue. Good rows go on as JSON Lines, and every problem of a bad row is reported with its line, for the seller.
A file above the size limit is refused before it is read; a column the check does not know is reported and ignored."""
import csv
import io
import json
import os
import re
from decimal import Decimal

COLUMNS = ["sku", "title", "price", "mrp", "stock", "gtin"]
MAX_BYTES = 10_000_000  # twice the largest catalogue: 50,000 rows of about 100 bytes


def paise(column, text):
    if not re.fullmatch(r"[0-9]+(?:\.[0-9]{1,2})?", text):
        separators = ", without separators" if "," in text else ""
        raise ValueError(f'{column} "{text}": write rupees and paise as in 1299.00{separators}')
    return int(Decimal(text) * 100)


def convert(row, line, first_line):
    """The record of one row, and the list of its problems."""
    if None in row or None in row.values():  # DictReader keeps extra cells under None and fills missing ones with None
        return {}, ["one cell for each column of the header, no more and no fewer"]
    problems, record = [], {"sku": row["sku"], "title": row["title"].strip()}
    if not re.fullmatch(r"[A-Z0-9][A-Z0-9-]{0,39}", row["sku"]):
        problems.append(f'sku "{row["sku"]}": capital letters, digits and hyphens only')
    elif first_line.setdefault(row["sku"], line) != line:
        problems.append(f"sku: also on line {first_line[row['sku']]}")
    if not record["title"]:
        problems.append("title: empty")
    for column in ("price", "mrp"):
        try:
            record[f"{column}_paise"] = paise(column, row[column])
        except ValueError as err:
            problems.append(str(err))
    if re.fullmatch(r"[0-9]+", row["stock"]):
        record["stock"] = int(row["stock"])
    else:
        problems.append(f'stock "{row["stock"]}": a whole number of 0 or more')
    if re.fullmatch(r"[0-9]{8}|[0-9]{12,14}", row["gtin"]):
        record["gtin"] = row["gtin"]  # a code, kept as text: never a number
    else:
        problems.append(f'gtin "{row["gtin"]}": 8, 12, 13 or 14 digits; format the column as text')
    if record.get("price_paise", 0) > record.get("mrp_paise", float("inf")):
        problems.append(f"price {row['price']} is above the mrp, {row['mrp']}")
    return record, problems


if os.path.getsize("catalogue.csv") > MAX_BYTES:
    raise SystemExit(f"catalogue.csv refused: more than {MAX_BYTES:,} bytes")
with open("catalogue.csv", "rb") as f:
    data = f.read()
text = data.decode("utf-8-sig")  # a byte-order mark is allowed and removed; bytes that are not UTF-8 refuse the file
reader = csv.DictReader(io.StringIO(text, newline=""))
if missing := [c for c in COLUMNS if c not in reader.fieldnames]:
    raise SystemExit(f"catalogue.csv refused: no column {', '.join(missing)}")
mark = "with" if data.startswith(b"\xef\xbb\xbf") else "without"
print(f"catalogue.csv: UTF-8 {mark} a byte-order mark; columns {', '.join(reader.fieldnames)}")
for column in reader.fieldnames:
    if column not in COLUMNS:
        print(f"column {column}: not one of {', '.join(COLUMNS)}, so it is ignored")

accepted, first_line, rows = [], {}, 0
for row in reader:
    rows += 1
    record, problems = convert(row, reader.line_num, first_line)
    for problem in problems:
        print(f"line {reader.line_num} ({row['sku']}): {problem}")
    if not problems:
        accepted.append(record)
print(f"{len(accepted)} of {rows} rows accepted, as JSON Lines:")
for record in accepted:
    print(json.dumps(record, ensure_ascii=False))

catalogue.csv: UTF-8 with a byte-order mark; columns sku, title, price, mrp, stock, gtin
line 3 (0042-BLU): price "1,299.00": write rupees and paise as in 1299.00, without separators
line 4 (TEA-250): stock "abc": a whole number of 0 or more
line 5 (TEA-250): sku: also on line 4
line 5 (TEA-250): gtin "8.90123E+12": 8, 12, 13 or 14 digits; format the column as text
line 6 (LAMP-01): title: empty
line 8 (MUG-07): price 449.00 is above the mrp, 399.00
2 of 7 rows accepted, as JSON Lines:
{"sku": "0042-RED", "title": "Cotton kurta (red)", "price_paise": 79900, "mrp_paise": 99900, "stock": 12, "gtin": "8901234567890"}
{"sku": "DIYA-12", "title": "मिट्टी के दीये, 12 का सेट", "price_paise": 14900, "mrp_paise": 19900, "stock": 40, "gtin": "8901234567944"}

The check accepted 2 of the 7 rows and reported six problems, each with its line, in words the seller can act on. The byte-order mark was removed by decoding with utf-8-sig, so it did not become part of the first column’s name, the KeyError of the encodings lesson. The barcode in E notation is a code that a spreadsheet had already turned into a number, as the messy CSV lesson showed, and only a rule per column notices it. The Devanagari title passed unchanged.

Evolution. Columns are read by their header names, so a new optional column goes at the end without breaking anyone, and a column the check does not know is reported and ignored.

Tests. Fixture uploads like the one above, plus a file of 50,000 rows for the limit and the same file uploaded twice, which must change nothing: an idempotent importidempotent import: An import that gives the same result however many times the same file is loaded, because each record is matched by its key and replaced rather than added again. It makes a retry after a failure safe. matches each row by its sku and replaces the product rather than adding it again.

Three parser settings that a review should question

In a review of code that reads data, the few lines that set up the parser decide more than all the code after them. Each of these three passed its author’s tests, because the tests used files without the case that breaks them.

A YAML 1.1 parser in a new tool

A new deployment tool reads its settings with YAML.safe_load, which in Ruby is Psych, a parser that follows YAML 1.1:

# Settings of a new deployment tool, written by hand
target: staging
dry_run: no
shift_window: 22:30
file_mode: 0640
release: "2.10"

# Under review: a new deployment tool loads its settings with YAML.safe_load, that is with Psych, which follows YAML
# 1.1. Two settings come back as numbers, without an error. The fix checks each setting's type as soon as the file is
# loaded, so that a wrong type stops the tool with the key's name, whatever parser read the file.
require "yaml"

EXPECTED = { "target" => [String], "dry_run" => [TrueClass, FalseClass], "shift_window" => [String],
             "file_mode" => [String], "release" => [String] }.freeze

settings = YAML.safe_load(File.read("tool.yaml"))
puts "as reviewed: #{settings.inspect}"

problems = EXPECTED.filter_map do |key, types|
  value = settings[key]
  "#{key}: got the #{value.class} #{value.inspect}, needs a #{types.join(' or ')}" unless types.any? { |t| value.is_a?(t) }
end
puts "fixed, the check after loading:", problems.map { |p| "  #{p}" }

as reviewed: {"target" => "staging", "dry_run" => false, "shift_window" => 81000, "file_mode" => 416, "release" => "2.10"}
fixed, the check after loading:
  shift_window: got the Integer 81000, needs a String
  file_mode: got the Integer 416, needs a String

shift_window: 22:30 came back as the number 81000 and file_mode: 0640 as 416, without an error; a YAML 1.2 parser would have read a string and the number 640. The review comment: a new tool should use a parser that follows the YAML 1.2 core schema, and check each setting’s type right after loading whatever the parser, as the fixed half does; the values that must stay text, such as times and file modes, go in quotes, and dry_run: no becomes dry_run: false, which both versions read as a boolean.

DOCTYPE processing left on

An upload handler parses partners’ XML invoices with DocumentBuilderFactory.newInstance() and nothing else. The invoice below declares one harmless entity in its DOCTYPE, and the check fails on the parser as reviewed and passes on the fixed one:

<?xml version="1.0" encoding="UTF-8"?>
<!DOCTYPE invoice [
  <!ENTITY seller "MySmartCoPilot Books">
]>
<invoice><seller>&seller;</seller><amount_paise>124900</amount_paise></invoice>

// Under review: an upload handler parses partners' XML invoices with DocumentBuilderFactory.newInstance() and no
// other setting. The check below fails on that parser, which processes the document's DOCTYPE, and passes on the fix,
// which refuses any DOCTYPE before a single entity is declared.
import java.io.File;
import javax.xml.parsers.DocumentBuilder;
import javax.xml.parsers.DocumentBuilderFactory;
import org.xml.sax.SAXParseException;
import org.xml.sax.helpers.DefaultHandler;

public class ReviewXml {
    static DocumentBuilderFactory asReviewed() {
        return DocumentBuilderFactory.newInstance();
    }

    static DocumentBuilderFactory fixed() throws Exception {
        DocumentBuilderFactory factory = DocumentBuilderFactory.newInstance();
        factory.setFeature("http://apache.org/xml/features/disallow-doctype-decl", true);
        factory.setXIncludeAware(false);
        return factory;
    }

    /** The check for the test suite: true when the factory refuses a document that has a DOCTYPE. */
    static boolean refusesDoctype(DocumentBuilderFactory factory, File file) throws Exception {
        DocumentBuilder builder = factory.newDocumentBuilder();
        builder.setErrorHandler(new DefaultHandler()); // throw on a fatal error, print nothing of its own
        try {
            String seller = builder.parse(file).getElementsByTagName("seller").item(0).getTextContent();
            System.out.println("  parsed, seller = " + seller);
            return false;
        } catch (SAXParseException e) {
            System.out.println("  refused at line " + e.getLineNumber() + ": " + e.getMessage());
            return true;
        }
    }

    public static void main(String[] args) throws Exception {
        File invoice = new File("invoice.xml");
        System.out.println("as reviewed:");
        System.out.println("  refuses a DOCTYPE: " + refusesDoctype(asReviewed(), invoice));
        System.out.println("fixed:");
        System.out.println("  refuses a DOCTYPE: " + refusesDoctype(fixed(), invoice));
    }
}

as reviewed:
  parsed, seller = MySmartCoPilot Books
  refuses a DOCTYPE: false
fixed:
  refused at line 2: DOCTYPE is disallowed when the feature "http://apache.org/xml/features/disallow-doctype-decl" set to true.
  refuses a DOCTYPE: true

The parser as reviewed processed the DOCTYPE and expanded the entity into the seller’s name; a parser that follows a DOCTYPE can be made to expand entities without end or, with external entities, to read files on the server, as the safe XML parsing lesson showed. The fix is the one the OWASP cheat sheet gives for DOM parsers, to reject DOCTYPE declarations: disallow-doctype-decl made the parser refuse the invoice at line 2. Python’s own XML documentation notes that its Expat parser “does not access local files or create network connections” by default, so the same review in Python looks first at entity expansion and the Expat version.

CSV opened without newline=“”

An export job writes CSV with open(name, "w"), and the import reads it with open(name):

"""Under review: an export job writes CSV with open(name, "w") and an import reads it with open(name). A note with a
line break inside quotes loses its \r on the way in, and on Windows each row gains an extra \r on the way out. Both
are fixed by newline="" when the file is opened, as the csv module's documentation asks."""
import csv

ROWS = [["id", "note"], ["BK-20417", "Leave at the gate\r\nCall on arrival"]]

# Writing: in text mode Windows turns every "\n" into "\r\n"; newline="\r\n" does the same on any computer.
with open("as_reviewed.csv", "w", newline="\r\n", encoding="utf-8") as f:
    csv.writer(f).writerows(ROWS)
with open("fixed.csv", "w", newline="", encoding="utf-8") as f:
    csv.writer(f).writerows(ROWS)
for name in ("as_reviewed.csv", "fixed.csv"):
    with open(name, "rb") as f:
        print(f"{name:15} {f.read()!r}")

# Reading: without newline="", the "\r\n" inside the quoted note comes back as "\n".
with open("fixed.csv", encoding="utf-8") as f:
    print("read as reviewed:", repr(list(csv.reader(f))[1][1]))
with open("fixed.csv", newline="", encoding="utf-8") as f:
    print("read fixed:      ", repr(list(csv.reader(f))[1][1]))

as_reviewed.csv b'id,note\r\r\nBK-20417,"Leave at the gate\r\r\nCall on arrival"\r\r\n'
fixed.csv       b'id,note\r\nBK-20417,"Leave at the gate\r\nCall on arrival"\r\n'
read as reviewed: 'Leave at the gate\nCall on arrival'
read fixed:       'Leave at the gate\r\nCall on arrival'

Written the way Windows opens text files, every line ended in \r\r\n, inside the quoted note too, and read back without newline="" the note’s \r\n came back as \n. The csv module’s documentation warns of both: without that argument, line breaks inside quoted fields “will not be interpreted correctly”, and writing on a system whose lines end in \r\n adds a second \r. The fix is one argument on both open calls.

Three contracts that break between a writer and a reader

The other three bugs appear only where one program’s output meets another program’s reading: a float between a text amount and a ledger, a schema between two versions of an object, a serialiser between a signer and a verifier.

Floats for money

The importer turns each amount into paise with int(float(amount) * 100):

"""Under review: an importer turns each row's rupee amount into paise with int(float(amount) * 100). The fix reads the
text as a Decimal, which holds 19.99 exactly, and refuses an amount with more than two decimal places."""
from decimal import Decimal


def to_paise_as_reviewed(amount):
    return int(float(amount) * 100)


def to_paise_fixed(amount):
    value = Decimal(amount)
    if value != value.quantize(Decimal("0.01")):
        raise ValueError(f"{amount} has more than two decimal places")
    return int(value * 100)


for amount in ["19.99", "0.29", "1.15", "100.00"]:
    print(f"{amount:>6}: as reviewed {to_paise_as_reviewed(amount):>5} paise, fixed {to_paise_fixed(amount):>5}")

texts = [f"{paise // 100}.{paise % 100:02d}" for paise in range(10_000)]
lost = [text for paise, text in enumerate(texts) if to_paise_as_reviewed(text) != paise]
print(f"from 0.00 to 99.99, {len(lost)} of 10,000 amounts lose a paisa as reviewed, {lost[0]} first")
print(f"fixed, every one is exact: {all(to_paise_fixed(text) == paise for paise, text in enumerate(texts))}")
try:
    to_paise_fixed("19.999")
except ValueError as err:
    print("fixed, on 19.999:", err)

 19.99: as reviewed  1998 paise, fixed  1999
  0.29: as reviewed    28 paise, fixed    29
  1.15: as reviewed   114 paise, fixed   115
100.00: as reviewed 10000 paise, fixed 10000
from 0.00 to 99.99, 573 of 10,000 amounts lose a paisa as reviewed, 0.29 first
fixed, every one is exact: True
fixed, on 19.999: 19.999 has more than two decimal places

19.99 became 1998 paise and 0.29 became 28: a binary float holds 19.99 as a value a little below it, and int() cuts the rest away. Across the 10,000 amounts from 0.00 to 99.99, 573 lose a paisa that way, and no error appears anywhere. Python’s decimal documentation puts the difference plainly: “Decimal numbers can be represented exactly”, and built from a string they keep the digits as written. The fixed version reads the text as a Decimal, and refuses an amount with a third decimal place rather than rounding it in silence.

A closed schema next to allOf

A delivery address reuses the base address through allOf and adds "additionalProperties": false to refuse misspelt members:

"""Under review: a delivery address reuses the base address schema through allOf and closes itself with
additionalProperties: false, to refuse misspelt members. A small evaluator of the five keywords involved shows a
correct address refused; unevaluatedProperties, which looks through allOf, accepts it and still refuses the typo."""

BASE = {"properties": {"city": {}, "pin": {}}, "required": ["city", "pin"]}
AS_REVIEWED = {"allOf": [BASE], "properties": {"landmark": {}}, "additionalProperties": False}
FIXED = {"allOf": [BASE], "properties": {"landmark": {}}, "unevaluatedProperties": False}


def evaluate(schema, value):
    """(errors, members evaluated) for properties, required, allOf, additionalProperties, unevaluatedProperties."""
    errors = [f"{name}: required" for name in schema.get("required", []) if name not in value]
    evaluated = set(schema.get("properties", {})) & set(value)
    for subschema in schema.get("allOf", []):
        sub_errors, sub_evaluated = evaluate(subschema, value)
        errors += sub_errors
        if not sub_errors:  # only a subschema that passed counts as having evaluated its members
            evaluated |= sub_evaluated
    if schema.get("additionalProperties") is False:  # sees the properties beside it, never those inside allOf
        errors += [f"{name}: not allowed" for name in value if name not in schema.get("properties", {})]
    if schema.get("unevaluatedProperties") is False:  # runs last, with everything evaluated at this place
        errors += [f"{name}: not allowed" for name in value if name not in evaluated]
    return errors, evaluated


addresses = {
    "a correct address": {"city": "Pune", "pin": "411001", "landmark": "near the bus stand"},
    "a misspelt member": {"city": "Pune", "pin": "411001", "landmrk": "near the bus stand"},
}
for label, schema in [("as reviewed", AS_REVIEWED), ("fixed", FIXED)]:
    for what, address in addresses.items():
        errors, _ = evaluate(schema, address)
        print(f"{label:11} {what}: {'; '.join(errors) or 'valid'}")

as reviewed a correct address: city: not allowed; pin: not allowed
as reviewed a misspelt member: city: not allowed; pin: not allowed; landmrk: not allowed
fixed       a correct address: valid
fixed       a misspelt member: landmrk: not allowed

The correct address was refused: additionalProperties judges members only against the properties beside it, so city and pin, named inside allOf, are extra to it. unevaluatedProperties, from JSON Schema 2019-09 on, counts every member that any keyword at the same place evaluated, so it accepted the address and still caught landmrk, as the objects and arrays lesson works through in detail.

sort_keys used as canonical JSON

A webhook receiver checks a partner’s signature against json.dumps of the parsed body with sorted keys, believing that this rebuilds the text the partner signed. The partner’s service is written in JavaScript:

{"receipt": "R-77", "note": "Chai café, Pune", "tax_rate": 5.0, "amount_paise": 10500}

"""Under review: a webhook receiver checks a partner's signature against json.dumps(..., sort_keys=True) of the
parsed body, as if that were the text the partner signed. The partner's JavaScript sorts the same members, and the
two texts still differ. The fix checks the signature over the bytes that arrived, before anything is parsed."""
import hashlib
import json

with open("receipt.json", "rb") as f:
    body = f.read()  # the bytes as they arrived

text = json.dumps(json.loads(body), sort_keys=True, separators=(",", ":"))
print("Python's sorted text:", text)
print("its SHA-256 begins       ", hashlib.sha256(text.encode()).hexdigest()[:16])
print("the body's SHA-256 begins", hashlib.sha256(body).hexdigest()[:16])

Python's sorted text: {"amount_paise":10500,"note":"Chai caf\u00e9, Pune","receipt":"R-77","tax_rate":5.0}
its SHA-256 begins        da590fca9092c3f7
the body's SHA-256 begins 206ed9acbd5db7de

// The partner's side: the same receipt, parsed and written again with its members sorted by name.
const body = '{"receipt": "R-77", "note": "Chai café, Pune", "tax_rate": 5.0, "amount_paise": 10500}';

const receipt = JSON.parse(body);
console.log("JavaScript's sorted text:", JSON.stringify(receipt, Object.keys(receipt).sort()));

JavaScript's sorted text: {"amount_paise":10500,"note":"Chai café, Pune","receipt":"R-77","tax_rate":5}

Both sorted the same members, and the texts still differ: Python wrote caf\u00e9 and 5.0, JavaScript café and 5, so the two hashes would never match, and neither matches the bytes that arrived. Sorting keys is not a canonical formcanonical form: The one spelling that a rule picks out of all the texts holding the same data, so that equal data always gives equal bytes, which is what a hash or a signature needs. More in “Canonical JSON (RFC 8785) and signatures”; RFC 8785 also fixes how strings and numbers are written. The fix is to check the signature over the exact bytes received, before parsing, and to use RFC 8785 on both sides only where the text must be rebuilt, as the canonical JSON lesson does.

Explain each choice in three sentences: when, how, the trap

Interviews and design reviews often ask for one decision at a time, briefly: “Would you stream this?” A good short answer has three sentences: when the choice is right, how it is done, and the trap, with a number where the track has measured one.

For example, on streaming: “I would stream it when the file can outgrow memory, which a whole-document parser needs several times over: the streaming lesson measured 76 MB of memory for a 13 MB JSON array in CPython. I would read it as JSON Lines, one record at a time, and keep only running totals. The trap is a bad record in the middle, so I would report it with its line number and go on, rather than lose the rest of the night’s records.”

Practise: review and fix an importer

The exercise is the review itself: a short importer with three of this lesson’s bugs, an identifier read as a number, an amount through a float and a CSV text cut into lines before the csv module sees the quotes. The sample tests use the cases of this lesson, from 19.99 to a note with a line break:

import csv


def load_rows(csv_text):
    """The rows of a bank statement as records. Under review: three data-format bugs; the tests show them."""
    rows = list(csv.reader(csv_text.splitlines()))
    records = []
    for ref, amount, note in rows[1:]:
        records.append({"ref": int(ref), "amount_paise": int(float(amount) * 100), "note": note})
    return records

from importer import load_rows

HEADER = "ref,amount,note\r\n"


def test_reference_stays_text():
    """a reference is a code: its leading zeros stay"""
    assert load_rows(HEADER + "007,10.00,ok\r\n") == [{"ref": "007", "amount_paise": 1000, "note": "ok"}]


def test_amounts_are_exact():
    """19.99 is 1999 paise, not 1998"""
    rows = load_rows(HEADER + "101,19.99,x\r\n102,0.29,y\r\n103,100,z\r\n")
    assert [row["amount_paise"] for row in rows] == [1999, 29, 10000]


def test_more_than_two_decimals_is_refused():
    """an amount with a third decimal place is an error, not a rounding"""
    try:
        load_rows(HEADER + "104,19.999,x\r\n")
    except ValueError:
        return
    assert False, "19.999 should raise ValueError"


def test_quoted_line_break_survives():
    """a line break inside quotes is part of the note, exactly as written"""
    rows = load_rows(HEADER + '105,1.00,"Leave at the gate\r\nCall first"\r\n')
    assert rows[0]["note"] == "Leave at the gate\r\nCall first"


def test_no_rows():
    """a statement with only its header has no records"""
    assert load_rows(HEADER) == []

Finally, a self-check across the five parts of this lesson, the seven decisions, the worked upload, the six bugs and the short explanations, which begins with the part where your answers have been weakest:

Interview questions

Warm-up (fresher to 2–5 years): what shape should an answer to a data-format design question take?

Seven decisions in order, each with its reason. Start with the writers and readers: who produces the data, who consumes it, how much and how often. Then the data model: the records, their keys and the values that must stay exact, such as amounts in minor units and times with offsets. Then the format, chosen against those needs with the trade-off said out loud, and the schema that states the contract. Then parsing and limits: the encoding, the parser’s settings, the sizes, and what happens to a bad record. Then evolution, how a second version arrives without breaking readers, and last the tests, built from damaged files. A weak answer names a format in its first sentence and stops; a strong one also says which requirement would change the choice.

Three harder questions follow: a proposal to accept YAML in a public API, an audit log that must be verifiable for years, and a review that must fit into fifteen minutes. Make your seven decisions first, then compare them with the first steps given:

Key takeaways

• A data-format design answer is seven decisions in order, from the writers and readers to the tests, each with its trade-off, and it names the requirement that would change the format.
• For sellers with spreadsheets the worked answer chose CSV in UTF-8, read every cell as text with a rule per column, accepted 2 of the 7 sample rows and reported each problem of the other five with its line.
• Three parser settings deserve a question in every review: a YAML 1.1 loader in new code, a DOCTYPE left on, and csv files opened without newline="".
• Three contracts break between writer and reader: amounts through floats, which lost a paisa on 573 of 10,000 amounts, a closed schema next to allOf, and sorted keys used as a canonical form.
• A short explanation of streaming, canonicalisation or validation says when, how and the trap, with a measured number where there is one.