Core data concepts · Lesson 5

Read file formats before choosing a store

7 min read

Lesson objectives

  • Compare CSV, JSON and column-oriented files by structure, validation and read pattern
Reading tools

A filename does not establish the structure of its contents. Start with the unit of record and the fields a consumer can reliably extract. CSV suggests rows, but column names, types, character encoding and delimiter rules are an agreement, not an automatic guarantee. The value 00123 may be a textual identifier rather than the number 123, and a date column may contain two incompatible formats. JSON can hold named fields and nested arrays, so it is often semi-structured, yet two documents need not carry identical fields. A receipt image remains unstructured for a question about its amount until the relevant fields are extracted into a validated record. Distinguish extension, internal structure and the contract required by the question.

Before moving a file into a store, write a small contract: record identifier, field type, allowed missing values, time zone and provenance. If amount is a decimal in a stated currency, do not turn it into text and add it to another currency. A comma inside a CSV organization name must follow quoting rules; splitting blindly at every comma destroys the columns. If a new JSON document uses a renamed field, absence of the old field does not mean zero. Test ordinary, boundary and malformed records. State whether each failure is rejected, repaired or quarantined. Silent coercion can produce an apparently complete dashboard whose denominator or amount has changed meaning.

The useful storage shape depends on the read. If a clerk must retrieve and update one record by identifier immediately, a database with a suitable key or index is closer to that need than scanning every file. If an analyst repeatedly aggregates one measure over millions of records, a column-oriented format may avoid reading unrelated fields. This is a workload distinction, not a promise that one format wins every benchmark: tiny files, compression, storage locality and value distribution affect performance. Keeping raw files supports traceability, while a validated analytical copy can use a different layout. Label these copies clearly so a transformed extract is never mistaken for the untouched source.

Treat conversion between formats as a possible change in meaning rather than a new suffix. Flattening a nested JSON order into item rows repeats order-level attributes. If the order total is repeated and then summed across those rows, revenue is overstated. Converting CSV into an efficient analytical layout does not fix duplicated identifiers or absent event dates. Specify expected input and output counts, the deduplication key and a path back to the source document. On schema change, ask whether the old consumer reads, ignores or misinterprets a new field. In an exam case, explain the business question, the recurring read pattern and the validation that must follow conversion.

Set a boundary on what a small sample can prove. Ten valid rows do not establish that a million rows satisfy the contract. Inspect null rates, key uniqueness, date ranges and rejected-record counts for each batch. Compare counts before and after conversion at the right grain: there may be more item rows than order documents without any loss or duplication, but the increase should agree with the number of items. When a new field appears, document the version in which it arrived and its effect on consumers. If the source contains personal data, avoid spreading it unnecessarily into analytical copies or error logs. These quality and governance checks inform the storage decision; the file format does not enforce privacy or correct bad facts by itself. State what you will measure after implementation to verify the chosen layout serves the original question.

An account identifier loses its leading zeros when you open a file. It looks numeric but serves as a label. Store it as text when zeros belong to its identity, then test an export-and-import round trip. Check date notation and decimal separators before converting values. The same date string can mean different days in two systems. Agree explicit formats and units, retaining source values alongside converted values during testing. Select a small amount, large amount, ambiguous date and identifier beginning with zero. These cases are more useful than a single easy row. When converting column-oriented data to another format, compare meaning, type and count. A file opening without errors does not establish a faithful conversion.

Lesson summary

Compare CSV, JSON and column-oriented files by structure, validation and read pattern.