JSON, JSONL, CSV, columnar formats and bounded ingestion

Ingestion is where outside bytes become inside facts. Unbounded reads, lenient parsing and lossy conversions turn one bad row into silent data loss. Explicit formats with quarantine keep the bad row visible and the good rows intact.

implement

You can now

Implement bounded-memory ingestion for JSONL and CSV with malformed-row quarantine and deterministic normalized output.
Convert normalized records to Parquet with pyarrow and test types, nulls, ordering and column projection.
Defend the format choice for one interchange case against a credible alternative, including null and numeric semantics.

Covers

JSON, JSONL and CSV encoding, null and numeric semantics
Bounded-memory ingestion and malformed-row quarantine
Columnar layout, Arrow and Parquet basics

Study, in this order

5 steps

Serialization: JSON, JSON Lines and `gzip`
Stas' Python Cookbook - data and interfaces

JSON: dumps and loads options, the JSON Lines write and read pattern and the DataclassEncoder; gzip: the text-mode open pattern

Depth · The cookbook reads JSON Lines into a list with a comprehension. For bounded memory, iterate the file and parse one line at a time. Skip XML and Archives here.

Done
CSV and the `pickle` warning
Stas' Python Cookbook - data and interfaces

CSV: always the csv module, files opened with newline set to the empty string, csv.reader, DictReader and csv.writer. Pickle: Never unpickle untrusted data, it can execute arbitrary code.

Done
Working with JSON: JSON Lines and error handling
AlgoMaster Python - interfaces, generics and data files

JSON Lines: One Record Per Line; Handling Errors: JSONDecodeError; JSON Types vs Python Types including the null to None row

Done
Working with CSV: `newline`, readers and dialects
AlgoMaster Python - interfaces, generics and data files

Why Not Just Split on Commas; Why You Always Need newline set to the empty string; csv.DictReader and csv.DictWriter; Quoting Constants

Done
Reading and Writing Single Files
PyArrow Parquet read and write docs

Reading and Writing Single Files (write_table, read_table, the columns argument for subset reads); Finer-grained Reading and Writing (ParquetFile); Inspecting the Parquet File Metadata; Data Type Handling

Done

Focus on

Row formats (JSON, JSONL, CSV) stream one record at a time. Parquet is columnar: a column-subset read skips the rest of the file.
Open CSV files with newline set to the empty string and let the csv module own line endings. Otherwise newline translation rewrites line breaks inside quoted fields, and Windows writes gain blank rows.
Parse one JSONL line at a time and quarantine JSONDecodeError with its line number, or one truncated line kills the whole file.
NaN and Infinity are Python extensions, not valid JSON. In Arrow, NaN is a float value and None is null: a table built with from_pylist or from_pydict keeps both through Parquet, while pandas folds them together (from_pandas writes NaN as null, to_pandas reads null as NaN).

Reproduce closed-book

Closed-book, stream a mixed input with good rows, a truncated JSON line, and a quoted CSV field holding a comma and a newline, into quarantined normalized output in bounded memory. Convert it to Parquet with Table.from_pylist and assert schema types, None nulls preserved, NaN distinct from null, and a column-subset read.

Traps

Splitting CSV on commas. Quoted commas, doubled quotes and embedded newlines need the csv module; the cookbook states this as a rule.
Round-tripping through pandas. from_pandas writes NaN as null and to_pandas reads null back as NaN, so a missing value and a failed computation become the same thing. Build the Arrow table with from_pylist or from_pydict and assert null_count.
list(reader) or json.load on a large file. Materializing the whole input removes the memory bound; iterate and write incrementally.

Practice

PyArrow Parquet docs: single-file read and write round trip

Reading and Writing Single Files: the write_table and read_table round trip with a columns subset read

The docs build their table with from_pandas, which turns NaN into null. Build yours from the quarantined records with from_pylist, then assert schema, null_count, a NaN that survives, ordering and the projected columns.

Self-test

What does json.loads('NaN') return, and is it valid JSON?
Why open CSV files with newline set to the empty string?
How do you bound memory on a large JSONL file with bad lines?
After a Parquet round trip, what happens to None and NaN?

Completion criterion

Stop when

A mixed good and malformed JSONL and CSV input streams to quarantined normalized output without materializing the file, and the Parquet round trip asserts types, nulls and ordering.

Mark passed, then open 4.3 Only mark this when you can do it without notes.

Leads to

4.3 Schema versions, compatibility and migrations Version the normalized record schema before it spreads.
4.4 Relational modeling, joins, aggregation and window functions Load the normalized records into constrained tables.

Comes after

4.1 Typed interfaces, runtime validation and configurationCarry the validated record into bounded JSONL and CSV ingestion with quarantine.

Resources for this unit

Stas' Python Cookbook - data and interfaces
Stas Bekmansupportreference
Use Serialization and Deserialization plus Environment and Arguments for focused implementation help. Compare JSON, JSONL, CSV and compressed data, and never use pickle for untrusted interchange.
AlgoMaster Python - interfaces, generics and data files
AlgoMastersupportcourse
Use Interfaces and Protocols plus Generic Types for structural typing, and Working with JSON plus Working with CSV for JSONL, error handling, newline and DictReader behavior. Lesson quizzes check recall; implementation is verified with your own scripts.
PyArrow Parquet read and write docs
Apache Arrowsupportreference
Use Reading and Writing Single Files plus Table from_pydict and from_pylist to convert normalized records to Parquet and test types, nulls, column projection and schema.

Also in this section

Official docs typed-service build path
Python typing, Pydantic, FastAPI and PostgreSQLprimarycourse
Build one record service in this order: structural protocols and generics; Pydantic models, validators, strict mode, serialization and settings; FastAPI request bodies, dependencies, errors and tests; then PostgreSQL transactions and EXPLAIN. Replay valid, malformed, duplicate and previous-schema requests through CLI and HTTP.
AlgoMaster SQL - selected route
AlgoMasterrequiredcourse
Study SQL Foundations, Querying Data, Joins, Subqueries and CTEs, Window Functions, DDL, DML and Views, Performance Optimization, Transactions and Concurrency, and Data Modeling. Stop when the service queries and transaction tests pass.
PostgreSQL transactions and EXPLAIN
PostgreSQLrequiredreference
Use the transaction tutorial and EXPLAIN guide to demonstrate commit, rollback, one isolation anomaly or prevented anomaly, and a measured plan improvement on the database actually used.
FastAPI tutorial
FastAPIsupportimplementation
Use Request Body, Dependencies, Handling Errors and Testing to expose the same service layer through HTTP. Keep framework models outside the domain core and preserve the same error semantics as the CLI.
AlgoMaster Low-Level Design - one rehearsal
AlgoMastersupportpractice
Use one fitting design prompt to rehearse interface contracts, dependency direction and failure ownership. Treat the review as feedback, then verify behavior with your own tests.
SQLAlchemy 2.1 documentation - select, Session and eager loading
SQLAlchemyreferencereference
Use the select() tutorial for 2.x query style, Session basics for the transaction boundary and identity map, and relationship loading for lazy versus selectinload versus joinedload plus raiseload. Use select() style and Session.execute or Session.scalars only, never the legacy Query API.
Alembic tutorial - migration scripts and downgrading
SQLAlchemysupportreference
Use Create a Migration Script, Running our First and Second Migration, Getting Information and Downgrading to run a paired upgrade and downgrade round trip on a scratch database.
Stripe idempotent requests
Stripesupportreference
Use the Idempotency-Key header contract, stored-response replay, parameter-mismatch errors and POST-only scope to implement keyed replay for one endpoint.