Day 4 - Data Formats and Modeling

The same data, stored in different file formats, can differ 100x in query speed and 10x in cost. When a data analyst asks "why does this query take five whole minutes?", the answer is often "because the data is stored as CSV." A one-line transformation converting CSV to Parquet is frequently the single most effective optimization.

Today we compare the file formats a data engineer faces daily — CSV, JSON, Parquet, ORC, Avro — understand the essence of their differences, columnar vs row-based storage, and look at schema evolution, which deals with the reality that data structures change over time.

The Five Formats at a Glance

There are broadly three groups. Text-based (CSV/JSON) formats are easy for humans to read and highly compatible, but inefficient. Columnar binary (Parquet/ORC) formats are optimized for analytical queries. Row-based binary (Avro) is strong at writes and schema evolution, so it's used for streaming and events.

💡 Related theory: Format choice follows the workload. "Frequent writes, often handling entire records (write-heavy)" → row-based (Avro). "Read-heavy, aggregating only specific columns (read-heavy, analytical)" → columnar (Parquet/ORC). This one line is the criterion for picking a format on the exam.

Columnar vs Row-Based: The Essence of the Difference

This is today's core. It's the difference in how the same table is laid out on disk.

Original table
  id | name  | amount
  1  | Kim   | 100
  2  | Lee   | 200
  3  | Park  | 300

Row-based storage — CSV, Avro
  [1,Kim,100][2,Lee,200][3,Park,300]
  Each row is kept together as a whole

Columnar storage — Parquet, ORC
  [1,2,3][Kim,Lee,Park][100,200,300]
  Values of the same column are kept together

Consider an analytical query like SELECT SUM(amount). With row-based storage, reading amount requires scanning id and name too — everything. With columnar storage, reading just the [100,200,300] block is enough. Because only the needed columns are read, I/O drops dramatically. This is called column pruning.

Furthermore, values in the same column share similar data types and values, so compression works far better. The amount column is all numbers and compresses efficiently, whereas mixed types interleaved row by row do not.

-- Athena cost is proportional to "the amount of data scanned"
-- CSV: even if you only need amount, every column gets scanned → expensive and slow
-- Parquet: only the amount column is scanned → cheap and fast
SELECT region, SUM(amount)
FROM orders          -- with Parquet, only the region and amount columns are read
GROUP BY region;

Conversely, workloads that "read or write an entire row," like OLTP, favor row-based storage — because columnar storage must gather multiple column blocks to reconstruct a single row.

💡 Related theory: Beyond column pruning, columnar formats also gain speed from predicate pushdown and partition pruning. Parquet stores statistics such as min/max per data block, so blocks that can't match a condition like WHERE amount > 500 are skipped entirely without being read. The combination of column pruning (reading less horizontally) + predicate pushdown (reading less vertically) is the heart of OLAP performance.

Why Parquet Is the De Facto Standard for Analytics

Parquet is the default recommended format across the AWS analytics stack (Athena, Redshift Spectrum, Glue, EMR). To summarize why:

• Column pruning: reads only the needed columns, cutting I/O and cost (Athena bills by scan volume)
• Strong compression: stores the same data far smaller than CSV → lower storage cost
• Built-in statistics: per-block min/max enables predicate pushdown
• Embedded schema: the file itself contains the schema, making it self-describing

# A common optimization: convert raw CSV/JSON to Parquet (Glue/Spark)
df = spark.read.json("s3://raw/events/")
df.write.partitionBy("year","month","day") \
        .parquet("s3://processed/events/")
# Athena queries then get faster and scan costs plummet

ORC has nearly the same columnar advantages as Parquet and is especially strong in the Hive/Hadoop ecosystem. Both are well supported on AWS, but Parquet is more widely used.

Avro and Schema Evolution

Avro is row-based binary, yet it is treated as important — because its core strength is schema evolution. Avro handles the schema explicitly alongside the data, and it is designed so that the reader's schema and the writer's schema remain compatible even when they differ. That's why it is used almost as a standard in streaming and event pipelines (Kafka, etc.) where schemas change frequently.

Schema evolution means safely handling the reality that data structures change over time. For example, a coupon_code field might later be added to order events. Both the old data and the new data must remain readable.

Compatibility types in schema evolution
  Backward compatible : new schema can read old data
                        → removing fields, adding fields with defaults
  Forward compatible  : old schema can read new data
                        → adding fields
  Full compatible     : both directions work

The principle of safe evolution: when adding a field, give it a default, and never casually delete or rename fields. With a default, reading old data that lacks the field simply fills in the default — nothing breaks.

💡 Related theory: The mechanism that enforces schema evolution at the organizational level is the Schema Registry. When a producer registers a new schema, it validates compatibility with the existing one, blocking compatibility-breaking changes before deployment. AWS Glue Schema Registry plays this role and integrates with Kinesis and MSK. It is a governance device that enforces data contracts in code.

Format Selection Decisions

Here is the flow for choosing a format on the exam and in practice.

Read directly by humans or exchanged externally?    → CSV / JSON
Loaded into S3 for analytics (aggregation)?         → Parquet (AWS default recommendation)
Hive/Hadoop-ecosystem-centric analytics?            → ORC
Streaming/events with frequent schema changes?      → Avro
Nested, semi-structured data (API responses, logs)? → JSON (→ convert to Parquet later)

The typical real-world pattern is "raw data arrives as JSON/CSV, but the processing stage converts it to Parquet and loads it into the analytics zone." This captures both the convenience of ingestion (text) and the efficiency of analysis (columnar).

Wrapping Up

Today's three key points. First, the essential difference between formats is columnar (Parquet/ORC) vs row-based (CSV/Avro), and for analytical queries, columnar — which reads only the columns — is overwhelmingly faster and cheaper. Second, Parquet is the de facto standard for AWS analytics, slashing Athena costs through column pruning, compression, and predicate pushdown. Third, schema evolution is about safely handling data structure changes; adding fields with defaults is the safe principle, and a schema registry enforces it.

In the next article, we weave together the fundamentals covered in Week 1 — roles and pipelines, batch/streaming, the service map, and data formats — for review.