48-Hour Field Notes: Every Record Parsed Cleanly and the Reconciliation Was Off by a Cent

Parse success is a terrible health signal, and I keep relearning that lesson the expensive way. My extraction step reported a clean parse for every record in the batch, yet the reconciliation job refused to close the day. The totals were off by exactly one cent, which is the least dramatic and most irritating failure a pipeline can hand you. What do you actually do when your schema gate says yes and your ledger says no? The 48-hour log, in order Hours 0-4 - I blamed the model response and diffed raw output against parsed rows, which showed nothing because both looked identical when printed with print(). Hours 4-12 - The bug vanished locally: my fixture file stored amounts as strings, while the live payload used JSON numbers that Python happily turned into floats. Hours 12-20 - I finally looked at the shapes I had been ignoring: thousands separators, accounting negatives in parentheses, a comma used as a decimal point, and a value with three decimals. Hours 20-36 - I stopped hunting individual rows and wrote a fixture table that states the expected outcome for every shape I had seen in the wild. Hours 36-48 - I replaced "did it parse" with a gate that refuses any value it cannot represent exactly in cents. Three ways a number stops being a number 1. The float that arrived before you looked If you call json.loads() on a payload with "amount": 19.99, you get a float, and the precision damage happens before your validation code runs. My old gate accepted floats because isinstance(x, (int, float)) felt generous. Generous is exactly the wrong instinct for money. 2. Separator chaos dressed as a string Models and spreadsheets both love 1,234.50, 1.234,50, 1,234, and (12.34) for negative. Every one of those is parseable, and at least one of them is ambiguous without a declared locale. A parser that guesses silently will guess wrong on some input you never wrote a test for. 3. Rounding that nobody signed off on A value like 1.005 is not representable in cents, so somebody has to decide what happens. If the rounding decision lives inside a library default, you have made a business rule without writing it down. My gate now rejects more than two decimals unless a caller explicitly opts in. The gate I run now import re from decimal import Decimal, InvalidOperation, ROUND_HALF_UP CENTS = Decimal("0.01") PLAIN = re.compile(r"^[+-]?(\d+(\.\d*)?|\.\d+)$") CURRENCY = re.compile(r"^(?:USD|EUR|GBP|\$|€|£)\s*|\s*(?:USD|EUR|GBP)$") class AmountError(ValueError): """Raised when a value is not a trustworthy 2-decimal amount.""" def parse_amount(raw, field="amount", decimal_comma=False): if raw is None: raise AmountError(f"{field}: missing value") if isinstance(raw, bool): raise AmountError(f"{field}: bool is not an amount") if isinstance(raw, float): raise AmountError(f"{field}: float already lost precision upstream") if isinstance(raw, int): text = str(raw) elif isinstance(raw, str): text = raw.replace("\u00a0", " ").replace("\u2212", "-").strip() if not text: raise AmountError(f"{field}: empty string") if text.startswith("(") and text.endswith(")"): text = "-" + text[1:-1].strip() text = CURRENCY.sub("", text).strip() text = _fold_separators(text, field, decimal_comma) else: raise AmountError(f"{field}: unsupported type {type(raw).__name__}") if not PLAIN.match(text): raise AmountError(f"{field}: not a plain decimal: {raw!r}") try: value = Decimal(text) except InvalidOperation as exc: raise AmountError(f"{field}: unparsable: {raw!r}") from exc if value.normalize().as_tuple().exponent < -2: raise AmountError(f"{field}: more than two decimal places: {raw!r}") return value.quantize(CENTS, rounding=ROUND_HALF_UP) def _fold_separators(text, field, decimal_comma): if "," in text and "." in text: if text.rfind(",") > text.rfind("."): return text.replace(".", "").replace(",", ".") return text.replace(",", "") if "," not in text: return text parts = text.split(",") if decimal_comma and len(parts) == 2 and len(parts[1]) == 2: return text.replace(",", ".") if all(len(p) == 3 for p in parts[1:]) and 1