File formats for data engineering: a step-by-step guide

Why file format matters more than you think

The file format you choose shapes everything downstream: query performance, storage costs, schema flexibility, and team velocity. We've all inherited pipelines where CSV is streamed into a data lake and watched queries crawl, or seen Parquet files bloat because compression was disabled. In this guide, we'll walk through file formats for data engineering: a step-by-step guide, comparing Parquet, Avro, ORC, and the classics so you can make the right call for your workload.

Picking the right format by workload

There's no universal winner—each format trades off storage, speed, schema flexibility, and ecosystem support. The key is matching the format to how your data flows and who consumes it.

In practice, we use multiple formats in a single pipeline. Raw data from APIs lands as JSON, gets normalized into Avro for Kafka, and then migrates to Parquet in the data lake for analytics. Each stage has a reason.

Why columnar wins for analytics

Columnar storage—where a column's values are stored together instead of row-by-row—transforms query performance. When you want the average of a single column across a billion rows, a columnar format reads only that column's bytes from disk. Row-based formats must scan every row, even for a single field.

Parquet is the columnar standard in data engineering. Here's what happens under the hood:

from pyspark.sql import SparkSession

spark = SparkSession.builder.appName("ColumnComparison").getOrCreate()

# Read a Parquet file—only age column touched for this query
df = spark.read.parquet("s3://my-bucket/events.parquet")
result = df.select("age").where("age > 25").agg({"age": "avg"})

# Parquet's row group metadata tells Spark: 
# "Column 'age' spans row group 1, bytes 50000-75000. Others? Skip them."
result.show()

For a 100 GB dataset with 50 columns, querying just two columns via Parquet might touch 5 GB of disk; the same query on CSV touches all 100 GB. The difference compounds with network I/O costs in cloud environments—every GB read is billed.

Parquet achieves this through column-level metadata (min/max values, null counts, column chunks) that allow Spark and Presto to prune entire row groups without scanning them. This is predicate pushdown, and it's why Parquet dominates analytics.

Compression: storage and query speed

Compression reduces file size and network I/O, but CPU cost matters. Snappy is fast and gives ~40% compression. ZSTD is slower but squeezes to ~60% compression. Gzip is classic but often too slow for big data workloads.

Most teams pick Snappy for streaming pipelines and ZSTD for archival or cost-sensitive analytics:

from pyspark.sql import SparkSession

spark = SparkSession.builder.appName("Compression").getOrCreate()

df = spark.range(1000000).select("id")

# Write with Snappy—fast, ~40% compression
df.write.mode("overwrite") \
    .option("compression", "snappy") \
    .parquet("s3://my-bucket/events_snappy.parquet")

# Write with ZSTD—slower but ~60% compression
df.write.mode("overwrite") \
    .option("compression", "zstd") \
    .parquet("s3://my-bucket/events_zstd.parquet")

# Default is Snappy; be explicit if you want something else

A real-world rule of thumb: if your cluster is I/O-bound (queries wait on disk reads), use ZSTD. If your cluster is CPU-bound (CPU maxes out decompressing), use Snappy or no compression. In cloud, I/O often wins because bandwidth is metered; storing less data costs less.

Schema evolution: add fields without breaking pipelines

Avro and Parquet both support schema evolution—adding or reordering fields—without rewriting old files. This is critical when your data contracts evolve.

Avro's approach: schema lives separately, often in a registry (Confluent Schema Registry, AWS Glue). New fields must have defaults.

# Old Avro schema
old_schema = """
{
  "type": "record",
  "name": "User",
  "fields": [
    {"name": "id", "type": "string"},
    {"name": "name", "type": "string"}
  ]
}
"""

# New schema: add email with default
new_schema = """
{
  "type": "record",
  "name": "User",
  "fields": [
    {"name": "id", "type": "string"},
    {"name": "name", "type": "string"},
    {"name": "email", "type": ["null", "string"], "default": null}
  ]
}
"""

# Consumers using new schema can read old records—email will be null
# Producers writing new schema can append to old data

Parquet's approach: schema is embedded in each file, but newer readers ignore unknown columns and old readers skip new ones. No registry needed, though Hive metastore or AWS Glue tracks schema versions.

from pyspark.sql import SparkSession
from pyspark.sql.types import StructType, StructField, StringType, IntegerType

spark = SparkSession.builder.appName("SchemaEvolution").getOrCreate()

# Write version 1
df_v1 = spark.createDataFrame([("user1", "Alice")], ["id", "name"])
df_v1.write.mode("overwrite").parquet("s3://my-bucket/users_v1.parquet")

# Read v1, add a column, write v2
schema_v2 = StructType([
    StructField("id", StringType(), True),
    StructField("name", StringType(), True),
    StructField("email", StringType(), True)
])

df_v2 = spark.read.parquet("s3://my-bucket/users_v1.parquet")
df_v2 = df_v2.withColumn("email", lit(None).cast(StringType()))
df_v2.write.mode("overwrite").parquet("s3://my-bucket/users_v2.parquet")

# Old readers (v1) skip email column; new readers (v2) see it as null for old records

In streaming systems (Kafka → Avro), schema evolution is non-negotiable. In batch analytics (S3 → Parquet), it's nice-to-have but less critical if you version your dataset paths (e.g., events/date=2025-01-13/).

Common mistakes

• Using CSV for everything. It scales terribly. If you're landing data in a lake, Parquet or Avro from day one saves 10x effort later.
• Disabling compression to save CPU. In cloud, I/O cost dominates. Spend CPU on compression; save network dollars.
• Mixing formats in the same pipeline layer. If your "events" table is part Parquet and part Avro, your readers break. Pick one per logical table.
• Ignoring schema registry for streaming. When producers and consumers evolve independently (Kafka, event systems), a schema registry (Confluent, AWS Glue) is worth the operational cost. Schema drift becomes visible, not invisible.
• Not partitioning alongside format choice. Columnar formats shine when combined with partitioning. Store events/date=2025-01-13/events.parquet, not all events in one file.

Key takeaways

• Choose format by workload: Parquet for analytics, Avro for streaming, JSON for raw ingestion, CSV for export.
• Columnar storage (Parquet, ORC) reads only needed columns, slashing I/O and compute for analytics—predicate pushdown is your friend.
• Compression reduces storage and network cost; Snappy for speed, ZSTD for space. Cloud environments favor compression.
• Avro and Parquet support schema evolution with defaults and versioning; this is essential for long-lived pipelines.
• Format choice compounds over time. Getting it right early saves rewrites, costs, and team frustration.