Write the data contract before converting
Start with a small synthetic fixture that represents every shape in the real exchange: required and optional fields, null, empty text, booleans, numbers, dates, identifiers, arrays and nested objects. Record which fields are keys, which may be absent and which must remain text. A tool cannot infer business meaning from a value that happens to look like a number or date.
Define the destination at the same time. CSV is not one universal database format; the receiving spreadsheet, bulk loader or partner API determines delimiter, quoting, line ending, character encoding, header and formula-handling rules. JSON has a clearer grammar but still leaves number range, duplicate-name handling and schema validation to the implementation.
Protect JSON meaning during parsing
Validate raw JSON before formatting or conversion. Formatting reserializes a parsed value, so insignificant whitespace disappears and large numbers can already be rounded by a JavaScript numeric model. If a long account number is an identifier, represent it as a string in the contract. Do not depend on object member order unless a separate protocol explicitly defines an ordered sequence.
Nested data needs a declared flattening strategy. Serializing an object into one CSV cell preserves more structure than silently dropping it, but downstream users must know the cell contains JSON text and may require a second parse. For analytical tables, an explicit normalized table or repeated child file is often easier to validate than ad hoc dotted column names.
Treat CSV as a dialect and a security boundary
Quoted CSV fields may contain delimiters, quotes and record breaks. Test all three together, plus an empty final field and a file whose last record has no trailing newline. Confirm whether the importer accepts doubled quotes and whether blank, missing and quoted-empty values remain distinguishable after loading.
Spreadsheet applications can interpret cells beginning with formula markers. If untrusted data will be opened interactively, define a formula-injection policy rather than assuming CSV quoting disables evaluation. Also verify text encoding and byte-order-mark expectations with the actual importer; readable English test data will not expose a broken non-ASCII path.
Make the handoff observable
Record source row count, destination row count, column set and rejected records. Calculate checksums for source artifacts when custody matters, but do not use a non-cryptographic text fingerprint as a security proof. Keep the original export immutable so a later transformation defect can be reproduced without asking the source system to regenerate changing data.
Schema failures should identify the record and field without logging sensitive values. Separate parsing errors, schema errors and business validation errors because each has a different owner and recovery action. A successful parse says that syntax was accepted; it does not say the record is complete, authorized or meaningful.
Use round-trip and golden-file tests
A useful regression set includes one simple record, every quoting edge, Unicode, a long identifier, null and blank values, heterogeneous object keys and a nested structure. Compare semantic values after JSON-to-CSV-to-JSON rather than expecting byte-for-byte equality, then compare the produced CSV with an approved golden file for stable column and quoting behavior.
- Test with the exact production importer and exporter.
- Keep identifiers as text when arithmetic is not intended.
- Reconcile row counts and rejected records on every handoff.