Deep Dive into File Formats

In data engineering, choosing the right file format is one of the most critical decisions when designing data pipelines, data lakes, and storage repositories. The file format impacts storage costs, query speeds, memory usage during processing, and schema evolution support.

In this chapter, we compare the different storage formats and explore brief details with examples for the primary file formats: CSV, JSON, Parquet, Avro, ORC, and Apache Hudi.

Row-Oriented vs. Columnar Storage

The most fundamental division in data storage is how records are laid out on disk:

Row-Oriented: Stores data record-by-record. Ideal for transactional write-heavy databases (OLTP) where you need to retrieve or update an entire row. Column-Oriented (Columnar): Stores data column-by-column. Ideal for analytical query-heavy systems (OLAP) where you aggregate a few columns across millions of rows.

Row-Oriented Layout (CSV/JSON/Avro):
[Row 1: ID, Name, Salary] -> [Row 2: ID, Name, Salary] -> [Row 3: ID, Name, Salary]

Column-Oriented Layout (Parquet/ORC):
[Col 1: ID1, ID2, ID3] -> [Col 2: Name1, Name2, Name3] -> [Col 3: Salary1, Salary2, Salary3]

High-Level Comparison Matrix

1. CSV (Comma-Separated Values)

CSV is a plain-text file format representing tabular data, where each line corresponds to a data row, and each field is separated by a delimiter (typically a comma ,).

Best for: Small-scale sharing, simple configurations, manual review. Drawback: No native type metadata (everything is a string), poor querying speed (O(N) sequential scans).

Text Representation Example:

id,name,role,salary
1,Alice Smith,Data Engineer,125000.00
2,Bob Jones,Analytics Engineer,110000.00

2. JSON (JavaScript Object Notation)

JSON is a human-readable, semi-structured plain-text format storing records as key-value pairs or arrays. It supports nested schemas (objects within objects, lists).

Best for: SaaS APIs, application telemetry, logs, semi-structured data pipelines. Drawback: Highly verbose (keys are duplicated in every single row), resulting in massive storage overhead and high parsing cost.

Text Representation Example:

[
  {
    "id": 1,
    "name": "Alice Smith",
    "role": "Data Engineer",
    "salary": 125000.00
  },
  {
    "id": 2,
    "name": "Bob Jones",
    "role": "Analytics Engineer",
    "salary": 110000.00
  }
]

3. Apache Avro

Avro is a compact, binary, row-oriented format widely used in streaming pipelines. Avro files store data alongside its schema in JSON format, ensuring that files are always self-describing.

Best for: Streaming systems (Kafka payloads), high-write ETL jobs, message serialization. Drawback: Not human-readable without serialization libraries, slow for columnar analytical queries.

Schema Definition Example:

{
  "type": "record",
  "name": "Employee",
  "namespace": "com.example",
  "fields": [
    {"name": "id", "type": "int"},
    {"name": "name", "type": "string"},
    {"name": "role", "type": "string"},
    {"name": "salary", "type": "double"}
  ]
}

4. Apache Parquet

Parquet is a high-performance, binary, columnar storage format optimized for analytical queries. It supports advanced optimizations like projection pushdown (only reading queried columns) and predicate pushdown (filtering blocks before reading them).

Best for: Big Data analytics (Spark, Snowflake, Athena, Presto), cold storage. Drawback: Not human-readable, expensive write/serialization costs.

Columnar Representation Example:

// Disk Blocks (Stored Column-by-Column)
Column 'id':     [1, 2]
Column 'name':   ["Alice Smith", "Bob Jones"]
Column 'role':   ["Data Engineer", "Analytics Engineer"]
Column 'salary': [125000.00, 110000.00]

5. Apache ORC (Optimized Row Columnar)

ORC is a columnar storage format originally created for Apache Hive. Similar to Parquet, it splits data into row groups (stripes) and columns, storing statistics (min, max, sum) per block for aggressive query acceleration.

Best for: Hive-heavy environments, Hadoop architectures, optimized storage compression. Drawback: Poor ecosystem support outside Hadoop/Hive compared to Parquet.

Stripe Layout Example:

+-------------------------------------------------------------+
| Stripe 1                                                    |
|  - Index Data: Column 1 Min/Max, Column 2 Min/Max           |
|  - Row Data: [Stream Col 1 Values] [Stream Col 2 Values]    |
|  - Footer: Column offsets and stripe-level metrics          |
+-------------------------------------------------------------+

6. Apache Hudi (Lakehouse Open Table Format)

Apache Hudi is not a raw file format, but rather an Open Table Format. It manages datasets stored as Parquet files on top of a Data Lake, adding transaction logs to support ACID transactions, upsert/delete operations, and point-in-time time-travel queries.

Best for: Modern Data Lakehouses, streaming databases, incremental data updates. Drawback: Higher write amplification and metadata maintenance overhead.

Lakehouse Layout Example:

Dataset Root Directory
 ├── .hoodie/                       <-- Metadata commit history & transaction logs
 ├── year=2026/
 │   ├── partition_metadata
 │   ├── base_file_1.parquet        <-- Columns (Parquet base file)
 │   └── delta_log_1.log            <-- Delta log (records inserts/updates)