2.5.1. Columnar vs Row-Based Formats
š” First Principle: Row-based formats (CSV, JSON) store all fields of a record together ā efficient for writing and reading entire records. Columnar formats (Parquet, ORC) store all values of a single column together ā efficient for analytical queries that only need a few columns from many rows. The access pattern determines which format wins.
In a row-based file, reading "SELECT avg(price) FROM sales" requires scanning every byte of every row ā customer names, addresses, product descriptions ā to extract just the price column. In a columnar file, the price values are stored contiguously, so the query engine reads only the price column's data block. For wide tables with many columns, this reduces I/O by orders of magnitude.
| Characteristic | Row-Based (CSV, JSON) | Columnar (Parquet, ORC) |
|---|---|---|
| Write speed | Fast (append records) | Slower (organize by column) |
| Read entire row | Fast | Slower (reassemble from columns) |
| Read few columns | Slow (scan everything) | Fast (read only needed columns) |
| Compression | Moderate | Excellent (similar values compress well) |
| Schema | Implicit (CSV) or embedded (JSON) | Embedded with rich types |
| Splittable | CSV: yes; JSON: depends | Yes (block-level) |
| Best for | Ingestion, exchange, simple ETL | Analytics, data lake queries, long-term storage |
Parquet is the dominant columnar format in the AWS ecosystem. Athena, Redshift Spectrum, Glue, EMR, and Lake Formation all optimize for Parquet. It supports nested data structures, multiple compression codecs (Snappy for speed, GZIP/ZSTD for size), and predicate pushdown (the query engine skips row groups that can't match the WHERE clause based on column statistics).
ORC (Optimized Row Columnar) is similar to Parquet but historically associated with the Hive ecosystem. It supports ACID transactions on Hive and offers good compression. In the AWS context, both Parquet and ORC are supported, but Parquet appears more frequently on the exam.
Avro is a row-based format with a twist: it embeds the schema in the file and excels at schema evolution. It's commonly used for streaming data serialization (Kafka messages) and as an intermediate format in ETL pipelines. If a question mentions schema evolution in streaming data or Kafka integration, Avro may be the answer.
ā ļø Exam Trap: "Convert CSV to Parquet" is one of the most common optimizations on the exam ā but don't forget to also mention compression and partitioning. A question asking for "the most effective way to reduce Athena query costs" usually expects all three: columnar format + compression + partitioning by the common filter column.
Reflection Question: A data lake stores 10 TB of web clickstream data as gzipped JSON. Analytics queries typically filter by date and select 3 of 50 columns. What format and storage optimizations would you recommend?
