CSV, JSON, and Schema Inference

CSV and JSON describe how values are serialized, not a complete analytical schema. A CSV cell is text until a reader interprets it. JSON distinguishes strings, numbers, booleans, arrays, objects, and null, but it still does not say whether an identifier is an integer, whether a timestamp is UTC, or whether a decimal must retain two places.

Prerequisites

Complete the SQL and relational-thinking batch, especially Relational Data Models. Run examples from the repository root so the fixture paths resolve.

Learning goals

• Distinguish a text format from the schema imposed by a reader.
• Explain why schema inference is a sample-dependent guess.
• Parse missing values without confusing them with malformed values.
• Validate a small input before scaling the same contract to many files.

Worked example

The repository fixture contains five order rows. amount is missing for order 104; that empty field represents a known schema field with an unknown value. We supply Arrow types rather than letting one sample decide them.

from pathlib import Path
import pyarrow as pa
import pyarrow.csv as pacsv

orders_path = Path("fixtures/data-engineering/files-tools/orders.csv")
order_types = {
    "order_id": pa.int64(),
    "ordered_at": pa.date32(),
    "customer_id": pa.int64(),
    "amount": pa.decimal128(10, 2),
    "status": pa.string(),
}
orders = pacsv.read_csv(
    orders_path,
    convert_options=pacsv.ConvertOptions(column_types=order_types),
)
print(orders.schema)
print(orders.column("amount").null_count)

The schema is int64, date32[day], int64, decimal128(10, 2), and string; the amount column has one null. Decimal is intentional: binary floating point is often unsuitable when exact scale is part of the contract.

Malformed data is different from missing data. The second row below contains both not-a-date and unknown. A strict conversion fails instead of silently turning a bad value into null.

from pathlib import Path
import pyarrow as pa
import pyarrow.csv as pacsv

bad_path = Path("fixtures/data-engineering/files-tools/orders-malformed.csv")
try:
    pacsv.read_csv(
        bad_path,
        convert_options=pacsv.ConvertOptions(
            column_types={"ordered_at": pa.date32(), "amount": pa.decimal128(10, 2)}
        ),
    )
except pa.ArrowInvalid as error:
    print(type(error).__name__)

The result is ArrowInvalid. In a real ingestion job, preserve the source file and row location, quarantine or reject the record according to policy, and report a count. Do not erase the evidence by coercing every failure to null.

JSON Lines stores one JSON object per line, which supports streaming record by record. Ordinary JSON may instead contain one large array or nested object. Neither layout guarantees that every object has the same fields; readers must still define missing-field and extra-field behavior.

Limitations and common mistakes

• Inference can change when a later file contains a wider integer, a timestamp, or a malformed token not present in the sample.
• An empty CSV field, the text NULL, JSON null, and an absent JSON key are not automatically equivalent.
• CSV dialect details—delimiter, quoting, escaping, header, encoding, and newline conventions—belong in the input contract.
• JSON numbers do not encode a fixed-width integer or decimal scale. Map them to domain types explicitly.
• Permissive parsing can be useful for exploration, but production ingestion should expose rejected rows and reasons.

Practice

Add an order whose identifier has a leading zero. Decide whether that field is a number or a code, declare the corresponding type, and explain what information a numeric parse would lose.

Next steps

Continue to Columnar Storage and Parquet to separate logical types from an on-disk analytical layout. Revisit NULLs and Three-Valued Logic before filtering imported missing values.

Primary sources

Checked 2026-10-15: Apache Arrow CSV reading, Apache Arrow JSON reading, and RFC 8259: The JavaScript Object Notation (JSON) Data Interchange Format.