FormatsGENERALFORMAT-SPECIFICTOOL-SPECIFIC

CSV, JSON and Their Limits

No types, no schema, no statistics, ambiguous quoting and — for CSV — a splittability problem that has no clean fix. And still the right answer for interchange, small data and human inspection.

What actually happens How to build it Can I trust it?

Who needs this, what one row is, and why the obvious build breaks

Every lesson starts from the consumer, because designing from the source outward is this domain's characteristic mistake.

The question

What exactly does a text format fail to store, and when is that failure irrelevant?

Who needs this

A human opening the file, a spreadsheet, a partner company's import tool, a script written in twenty minutes. Every one of those is a legitimate consumer that a binary columnar format serves badly, which is why text formats have not gone anywhere (Who Actually Consumes This Data).

What one row is

One line — in CSV a record, in JSONL a record, in a single JSON document not a record at all but the whole file. That last distinction matters more than it sounds: a JSON array of a million objects cannot be streamed or split by any reader that must parse valid JSON.

The obvious build

Export to CSV. Everything reads CSV, it opens in a spreadsheet, and there is no library to add. For a one-off extract, a partner handoff or a file a person will look at, this is correct and any objection to it is overengineering.

Why it breaks

A product name contains a comma. Or a newline. Or a quote. Quoting handles all three and only if every producer and consumer implement the same quoting rules, which they do not (Data Engineering Anti-Patterns).

How it breaks with real data

A product name contains a comma. Or a newline. Or a quote. Quoting handles all three and only if every producer and consumer implement the same quoting rules, which they do not (Data Engineering Anti-Patterns).
A leading-zero identifier — 007 — is read as the number seven by the next tool in the chain, and the join to the dimension table silently drops those rows (Missing Rows).
A date is written as 03/04/2026 and the reader is in a different locale. The value parses successfully into the wrong month, which is the worst possible outcome (Semantic Changes).
A null and an empty string are both written as nothing between two commas, and the distinction is gone permanently (Nullability & Defaults).
A large gzipped CSV cannot be split, so one file is one task and the job's runtime is set by its largest file (Why Analytical Data Compresses).
A JSON export is one array in one document. The reader must hold the whole thing to parse it, and no worker can start halfway (What Happens After the Bytes Land).

Source→Ingestion→Raw→Transformation→Validation→Storage Model→Serving→Consumer→Observability

What is actually happening

A text format stores characters, not values. Every type is inferred by the reader, and inference is a guess made per file, per column, sometimes per row — which is why the same file read by two tools produces two schemas (Hashing vs Encryption vs Encoding).
There is no schema in the file. A header row is a list of names, not a contract: it declares no types, no nullability, no units, no ordering guarantee and no version. Anything a consumer knows about the file, it knows from somewhere else (Data Contracts).
There are no statistics and no structure to skip. A reader cannot know whether a range of rows can be ignored without reading them, so every query is a full read regardless of its predicate (The Parquet Read Path).
Splittability is where CSV is structurally worse than JSONL. A reader that seeks to an arbitrary byte offset in a CSV cannot tell whether it landed inside a quoted field containing a newline, so it cannot reliably find the next record boundary. JSONL is line-delimited with no multi-line records, which makes it splittable in a way CSV is not — one of the few genuine technical advantages of JSONL over CSV.
Quoting and escaping are the ambiguity. The rules are conventions with widely-deployed variants: which character quotes, whether quotes are doubled or backslash-escaped, whether embedded newlines are permitted, what the delimiter is. A file that round-trips through two tools may not survive it (Unsafe Deserialization).
JSON adds types — string, number, boolean, null, object, array — and that is a real improvement over CSV. It does not add a schema, and its number has no defined precision, so a monetary amount or a 64-bit identifier can lose fidelity in a reader that maps it to a float (Why 0.1 + 0.2 Is Not 0.2 + 0.1's Problem).

What is not in the file

The productive way to learn this is by subtraction: take a Parquet file, list what it stores beyond the values, and remove each item. Schema goes. Types go. Nullability goes. Per-chunk statistics go. Column byte offsets go. What remains is characters and separators, and every consumer must supply the rest from outside the file.

The grain table below is the version of that subtraction that catches people out, because the failures are not "the file will not parse" — they are "the file parses and the values are wrong". A shifted column, a coerced identifier and a locale-flipped date all produce a clean load and a wrong table.

The middle two rows are the expensive ones. Both are cases where information present in the source is destroyed by the text representation and cannot be recovered from the file afterwards, no matter what the downstream pipeline does.

What one CSV line represents, and how each assumption fails

Stage	One row is	Breaks if
The file	A sequence of characters in an encoding nobody declared.	The encoding is assumed. Non-ASCII values arrive as replacement characters and the corruption is only visible to someone who knows what the name should look like.
A line	One record — unless a quoted field contains a newline, in which case one record spans several lines.	A reader splits on newlines, or seeks to a byte offset. This is the reason CSV is not reliably splittable and JSONL is.
A field	Characters between delimiters, with quoting rules applied.	Producer and consumer disagree on the quoting convention. Every subsequent column shifts and each row remains individually parseable.
A value	A string that a reader will guess a type for.	The guess differs between tools or between files. `007` becomes `7`, a long identifier loses precision, `03/04` picks a month by locale (Nullability & Defaults).
An empty field	Either a null or an empty string — the format cannot distinguish them.	The distinction mattered. A null customer name and a blank customer name are different facts, and after a CSV round trip they are the same fact.
The header	A list of names, and nothing else.	It is treated as a contract. It carries no type, no unit, no nullability, no version — and it may be absent, repeated mid-file, or in a different order than last week (Data Contracts).

None of these produces an error by default. Every one produces a table that loads successfully and reports the wrong number, which is the shape of failure this whole domain is organised around (The Pipeline Succeeded. The Data Is Wrong.).

The ambiguities, concretely

It is worth seeing the failures as bytes rather than as warnings, because each one looks harmless in isolation and each one has taken down a production feed somewhere.

The block below is a valid CSV file by at least one convention and a corrupt one by several others. Work through what a positional reader does with line four, and what a locale-sensitive date parser does with line three.

The checks after it are the boundary controls that catch these. Note the misses column on each: a strict parse catches structure and types and is blind to meaning, and no amount of parsing rigour will tell you that an amount arrived in the wrong currency (The Dimensions of Data Quality).

Boundary controls for a text feed, and what each one still misses

Check	Expresses	Catches	Still misses
Strict parse against a declared schema, failures quarantined not dropped	Every row conforms to the types and nullability we agreed with the producer.	Type drift, unexpected nulls, malformed numbers, a column that changed from integer to string upstream.	Anything that parses cleanly and means something different — a date in the wrong locale, an amount in the wrong currency, an identifier that lost a leading zero at the producer (Semantic Changes).
Column count per row equals the header count	Quoting and delimiting behaved as expected for every record.	An unquoted delimiter inside a value, a producer that changed dialect, a shifted-column corruption that per-value type checks pass.	A shift where the displaced values happen to be type-compatible — two adjacent string columns swapping content parses perfectly and is undetectable structurally.
Header fingerprint compared against the agreed contract	The producer has not added, removed, renamed or reordered a column.	Silent schema drift in a feed with no schema — the only place drift is detectable in a text format (CDC and Schema Drift).	Files with no header at all, and any change in meaning under an unchanged header, which is the most common way a text feed goes wrong.
Row count and byte size against the feed's own history	This delivery looks like a normal delivery.	A truncated export, a partial upload, a source that produced nothing, a duplicate file delivered twice (Volume Anomalies).	A file of the right size with wrong contents; and it fires falsely on any genuine business change in volume.

Run all four at the boundary, before conversion. After conversion the structural evidence is gone, and a typed columnar file records the corruption as if it were data (Contract Enforcement).

One CSV file, five ambiguities

1order_id,customer,ordered_on,amount,note
20071,"Müller, GmbH",03/04/2026,1234.50,
30072,Smith Ltd,04/03/2026,1.234,50,"delivered"
40073,"O'Brien ""Bob""",2026-04-03,,ok
50074,Acme
6Industries,2026-04-03,99.00,"line
7break inside a quoted field"
8 
9WHAT A READER HAS TO GUESS
10 1. order_id  0071 -> is it a string, or the number 71?
11                       A numeric guess drops the leading zero and every
12                       join to the dimension table silently loses the row.
13 2. ordered_on 03/04/2026 -> March 4th or April 3rd? Line 3 uses a third
14                       format entirely. All three parse. Two are wrong.
15 3. amount    "1.234,50" -> European decimal comma, and it is UNQUOTED,
16                       so a comma-splitting reader sees SIX columns on
17                       that line and shifts everything right.
18 4. quoting   "O'Brien ""Bob""" -> doubled quotes here; other producers
19                       backslash-escape; some do neither and hope.
20 5. record 0074 spans two physical lines because of a newline inside a
21                       quoted field. A reader that splits on \n sees two
22                       broken records. A reader seeking to a byte offset
23                       cannot tell whether it is inside a quoted field.
24 
25  amount on line 3 is empty. Null, or zero, or "not yet known"?
26  The file cannot say, and after loading, neither can anyone else.

What to notice: only ambiguity 5 is likely to raise an error. The other four produce a successful load with wrong values, which is why a parse that "worked" is not evidence of anything (Trusting Data).

Where text is still the right answer

Everything above is an argument against text as a *storage layer*. It is not an argument against text, and the domain is full of teams who over-corrected and built a Parquet pipeline for a file a partner sends once a week with four hundred rows in it.

The distinguishing question is who or what reads it. A human, a spreadsheet, a partner's import tool, a script someone will write in twenty minutes, a config-shaped dataset small enough to read in full — these are text's consumers and no binary format serves them better.

The decision below is the one worth having explicitly, because the two failure directions are symmetric and both are common: text where a typed format was needed, and a typed format where a human needed to open the file.

Text, or a typed format?

Who reads this file, and how much of it do they read?

CSV

when A person will open it, a spreadsheet will import it, or a partner's tooling requires it. Small, flat, tabular data.

cost No types, no schema, quoting ambiguity, not reliably splittable. Pin the dialect in writing and validate strictly on the way back in.

JSONL

when Machine-to-machine text where nesting or types matter and volume is moderate — webhook payloads, API exports, log-shaped records.

cost Larger per record than a binary encoding because keys repeat; still no schema; still a full read for any query. Splittable, which CSV is not (Payload Size: 20KB, 200KB, 5MB).

A schema-carrying binary row format

when High-volume records on a transport, many producers, schemas evolving independently, replay expected.

cost A registry to operate, binary payloads needing tooling, and no column pruning for anyone who queries it (Avro).

A columnar format

when The consumer is an analytical query projecting a few columns across long ranges of history.

cost Batch-shaped writes, a buffering delay, poor record-at-a-time access, and nothing a human can open (Parquet).

Text-format incidents that a schema would have prevented

Trigger	Symptom	Cause	Response
A partner adds a column in the middle of their weekly CSV.	Every column after it holds the previous column's values; loads succeed; a downstream metric moves.	The reader was positional, and a header-row change carries no version, no notification and no compatibility check.	Assert the header fingerprint at the boundary and quarantine the file on mismatch rather than loading it (Contract Enforcement).
An export tool is upgraded and starts writing dates in a locale format.	Roughly a third of dates are silently wrong — the ones where day and month are both valid.	No type or format is declared in the file, so the reader's inference changed with the data rather than being fixed by a contract.	Declare the date format explicitly in the parse configuration and reject values that do not match, rather than accepting whatever parses (Data Tests).
A CSV of customer records is gzipped and loaded nightly by a Spark job.	The job takes as long with twenty workers as with two.	Whole-file gzip is not splittable; one file became one task regardless of cluster size (Why Analytical Data Compresses).	Convert at the boundary to a splittable columnar format, and size the output files as units of work.
A JSON export grows past what a reader can hold in memory.	The pipeline that worked for two years fails outright one night.	The export is a single JSON document — one array — so it must be parsed whole. There is no incremental read available for valid JSON of that shape.	Ask the producer for JSONL, or stream-parse the array. The first is a contract change and the second is a workaround (What Happens After the Bytes Land).
An identifier column with leading zeros round-trips through a spreadsheet.	A subset of joins to the dimension table stop matching, and the fact table quietly loses those rows.	Type inference turned a string into a number, and the leading zeros are gone from the only copy that was kept.	Retain the original file as received, and treat identifiers as strings everywhere by declaration rather than by inference (The Raw Landing Zone).

How to build it

Most important first.

Use text formats at the edges: interchange with a partner, a manual export, a small config-shaped dataset, anything a human will open. Do not use them as the storage layer of an analytical platform (The Data Lake).
Prefer JSONL over CSV for machine-to-machine text: it is splittable, it has types, it handles nesting, and it does not have the quoting ambiguity. The only thing CSV does better is open in a spreadsheet.
When you must produce CSV, pin the dialect explicitly — delimiter, quote character, escape convention, encoding, line ending, null representation, date format — and publish it with the file. Every one of those is a real source of production incidents (Data Contracts).
Convert to a typed format at the earliest boundary, and make that conversion the place where the contract is enforced rather than a place where types are guessed (Contract Enforcement).
Never let a reader infer types on a production path. Declare the schema explicitly, and fail loudly on a value that does not conform instead of accepting a null (Data Tests).
Keep the original text file when it is what actually arrived. It is the evidence of what the partner sent, and in a dispute it is the only artefact that settles anything (The Raw Landing Zone).

What this actually promises

Naming the guarantee you do not have is worth more than naming the one you do — everything downstream inherits the weakest promise in the chain.

A text file guarantees that it is text, in some encoding you may or may not have been told. That is close to the entire list.
Nothing is guaranteed about types, nullability, column order, header presence, delimiter, quoting convention or date format. Every one of these is a convention between the producer and whoever happens to read it.
JSON guarantees a parseable structure and a small set of value kinds. It does not guarantee numeric precision, key ordering, or that two documents in the same file share a shape.
JSONL guarantees one record per line, which is the guarantee that makes it splittable. CSV guarantees no such thing, because a quoted field may contain a newline.
No text format guarantees anything a reader can use to skip data. Full read, every time, by construction.

Can I trust it?

A green pipeline is evidence that code ran. These four fields are the evidence that the data is right.

The check that would catch this

The check that belongs at a text boundary is a strict-parse-with-declared-schema step: parse every row against explicit types, route failures to a quarantine location, and alert on quarantine volume rather than discarding.
Add a column-count assertion per row, because a mis-quoted field shifts every subsequent column left and produces rows that are individually parseable and completely wrong (The Dimensions of Data Quality).
These miss values that parse cleanly and mean the wrong thing — a date in the wrong locale, an amount in the wrong currency, an identifier that lost a leading zero before you ever saw the file (Semantic Changes).

Freshness

Text formats are the cheapest thing to write incrementally: append a line. That makes them a natural landing format for low-volume feeds and for anything that must be visible immediately (Batch Ingestion).
A file being appended to is readable up to its last complete line, and a consumer that reads a partially-written line gets a parse error rather than a wrong value — which is a better failure than most.
The freshness cost arrives at the analytical layer, where a full read on every query means query latency scales with total data rather than with the data the query needed.

When the schema or meaning changes

A column added in the middle of a CSV shifts every subsequent column for any reader working positionally, and readers work positionally more often than anyone admits (Breaking Schema Changes).
A column renamed in the header breaks name-based readers and is invisible to positional ones — so the same change breaks half your consumers and silently succeeds for the other half.
JSON is more forgiving: added keys are ignored by most readers and removed keys become missing rather than shifted. That is genuinely better and still not a contract (Forward Compatibility).
Because there is no schema in the file, there is nowhere for evolution to be recorded. The version of a text feed lives in a wiki page or in nobody's head (Dataset Documentation).

How to re-run this safely

Text files are the easiest thing in this domain to inspect and repair by hand, and that is a real operational property — during an incident you can look at the bytes (Debugging a Data Incident).
Reprocessing from retained text originals is straightforward and idempotent if the parse is deterministic and the output range is derived from the input (Reprocessing vs Retrying).
A partially-written text file is recoverable up to the last complete line, which is a better partial-failure story than a Parquet file missing its footer (Partial Failure).

What can go wrong

Failure modes

Quoting mismatch between producer and consumer, shifting columns and producing well-formed wrong rows.
Type inference differing between two readers of the same file, so two pipelines disagree about a column's type.
Leading zeros, large integers and decimals losing fidelity through a numeric inference step (Integer Overflow: The Hardware Wraps, the Language Decides).
A gzipped CSV removing splittability and pinning a job to one worker per file.
A single-document JSON array too large to parse in memory, which fails at exactly the moment volume grows past a threshold nobody was watching.
Character encoding assumed rather than declared, so non-ASCII values arrive as replacement characters and nobody notices until a customer name is wrong.

Misreads

"CSV is fine, we compress it." Compression addresses size and nothing else — not types, not schema, not statistics, not skipping — and whole-file compression additionally removes splittability (Why Analytical Data Compresses).
"The header row is a schema." It is a list of names. It declares no type, no nullability, no unit, no ordering guarantee and no version, and it is frequently absent or duplicated inside the file.
"JSON has types, so it is safe." It has a small set of value kinds with no defined numeric precision and no schema. A 64-bit identifier and a monetary decimal are both at risk in a reader that maps number to a double (Why 0.1 + 0.2 Is Not 0.2 + 0.1's Problem).
"We will convert it later." Later is after a year of history has accumulated in a format with no schema, and the conversion then has to guess at what every historical value meant (Keeping Raw History: The Recovery Position and the Liability).
"Text formats are obsolete." They are the correct answer for interchange, for small data, for anything a human opens and for any integration that must work with a tool you do not control. The mistake is using them as a storage layer, not using them at all.

Privacy, retention and access

A text export is the easiest data in an organisation to copy, email and leave on a laptop, which makes it the highest-risk artefact per byte in most platforms (Data Classification).
Because there is no schema, there is also no place to attach a classification label to a column. A CSV of customers carries no marking that it contains personal data, which is precisely why extracts escape governance controls that the source table was under (PII in Pipelines).

Operating it

How you see it in production

Quarantine volume and quarantine reasons at the parse boundary — the single most informative signal a text feed produces (The Data Quality Dashboard).
Column count distribution per file, which catches quoting failures that a per-value type check does not.
Inferred versus declared type mismatches per column per run, so a source changing a format is caught at arrival (CDC and Schema Drift).
File size and line count against their own history, which is the cheapest broad detector for a truncated or partial export (Volume Anomalies).

What changes at 10x and 100x

At 10x, parse CPU and full-read cost become the dominant terms and the conversion to a typed format stops being optional.
At 100x, a text-based analytical layer is simply not viable — there is no tuning available, because the format offers no structure to exploit (Physical Data Layout).
At 100x producers rather than volume, dialect variation becomes the problem: every partner's CSV is subtly different and each one needs its own parse configuration (Ingestion Sources).

What drives cost here

Scan cost is the worst of any format discussed here, because there is no skipping mechanism at all — every query reads everything (Scan Cost).
Parse CPU is substantial and is per character: quoting rules, delimiter handling and type inference on every field (What Serialization Costs).
Storage is the largest per record before compression and comparable after it for simple data, since text compresses well — which is exactly why the compressed-CSV trap is tempting.
The hidden cost is engineering time spent on dialect incidents, which for a long-running partner feed reliably exceeds the cost of having converted it on day one.

What this approach costs

Text buys universal readability and human inspection, and costs types, schema, statistics and skipping. For an interchange file that is a good trade and for a storage layer it is a bad one.
JSONL buys splittability, types and nesting over CSV, and costs the ability to open the file in a spreadsheet — which is a real loss for the humans who are frequently the actual consumer.
Keeping the text original costs storage and buys the only artefact that can settle a dispute about what a partner actually sent.

Where this applies

Almost nothing here is universal. These labels say what each claim is specific to, and where a different engine, format, warehouse or scale would differ.

GENERALThe absence of types, schema and statistics is inherent to storing values as characters and holds for every text format and every tool that reads one. What varies is how badly each specific ambiguity bites, not whether it exists.
FORMAT-SPECIFICJSONL is line-delimited and therefore splittable and streamable; CSV permits newlines inside quoted fields and is not reliably splittable; a single JSON document is neither. Treating "text format" as one thing hides the biggest practical difference between them.
TOOL-SPECIFICCSV dialects differ across spreadsheet applications, database bulk loaders and language libraries in delimiter, quote handling, escape convention and null representation. A file that round-trips correctly through one toolchain can be corrupted by another with no error raised.

Where the depth lives

This domain teaches how data moves and how you know it arrived intact. It hands the rest off by name.

Backendserialization-basics deserialization file-processing file-uploads serving-files

API Designpayload-size compression request-contracts

Securitydata-classification hashing-encryption-encoding

Observabilitycpu-bound-vs-io-bound

Computer Architecturefloating-point-precision integer-overflow binary-representation endianness

DSAstring naive-string-matching

Domains that do not exist yet

— DevOps / Production Engineering owns the delivery mechanics of a partner file feed — the transfer, the retry, the alert when a weekly file does not arrive — which is where most text-format incidents are actually detected.