Parquet vs. CSV: Columnar Advantages for Analytical Queries

Row-Based vs. Column-Based Storage: A Fundamental Difference

At the heart of the CSV vs. Parquet comparison is the way they organize data on disk. CSV files are row-oriented. Think of an Excel spreadsheet or a traditional ledger: all the information for a single record—Transaction ID, Date, Amount, Cost Center, etc.—is written together as one contiguous line or row.

Most databases you've encountered, like PostgreSQL or MySQL, are also row-oriented by default. This structure is excellent for transactional work, where you often need to retrieve or update an entire record at once (e.g., fetching all details for a single customer order).

However, this is not how analytical work usually operates. To understand the two approaches and their performance implications, the following video provides an excellent deep dive into the mechanics of how data is physically read from a disk in both row-stores and column-stores.

Parquet files, in contrast, are column-oriented. Instead of storing data row by row, Parquet stores all the values for a single column together. All Transaction-IDs are grouped, all Dates are grouped, all Revenue amounts are grouped, and so on.

This simple illustration provides a great mental model for the difference:

How Parquet Optimizes for Analytics

This architectural difference gives Parquet three major advantages for the kind of aggregation, filtering, and analysis common in FP&A. The video below is an excellent and concise explanation of how Parquet leverages its columnar structure.

As you saw in the video, the key benefits stem from this design:

1. Column Pruning (Only Reading What You Need)

Financial analysis rarely requires all columns in a dataset. Imagine a typical FP&A query: calculating the total revenue by business unit for the last quarter from a table with 20 columns.

SELECT
    business_unit,
    SUM(revenue)
FROM actuals
WHERE transaction_date >= '2024-04-01'
GROUP BY business_unit;

• With CSV (Row-based): To execute this, the query engine must read every single byte of every row. It reads the transaction ID, notes, salesperson ID, and 17 other columns it doesn't even need, just to get to the business_unit, revenue, and transaction_date values. It then discards the unneeded data in memory. This is incredibly wasteful, consuming disk I/O and memory bandwidth.
• With Parquet (Columnar): The engine can be surgical. It identifies that it only needs three columns (business_unit, revenue, transaction_date). It goes directly to the sections of the file where these three columns are stored and reads only that data, completely ignoring the other 17 columns on disk. This dramatically reduces the amount of data that needs to be processed.

2. More Effective Compression

Data within a single column is highly uniform. A business_unit column contains a limited set of repeating text strings. A revenue column contains only numbers. This homogeneity allows Parquet to use highly efficient, column-specific compression techniques.

• Dictionary Encoding: Instead of storing the string "Global Technology Services" millions of times, Parquet can build a dictionary where 0 = "Global Technology Services", 1 = "Consulting", etc., and then just store the tiny integers 0 and 1.
• Run-Length Encoding (RLE): If a column has sorted data, like a date column, it might have the same value for many consecutive rows. RLE can store this as ('2024-06-15', 10000) instead of writing the date ten thousand times.

These techniques result in significantly smaller file sizes compared to a gzipped CSV, which uses a more generic compression method. Smaller files mean less storage cost and, more importantly, less data to read from disk, leading to faster queries.

3. Data Skipping (Predicate Pushdown)

Parquet files are internally organized into "row groups" (chunks of rows, e.g., 100,000 rows at a time). For each row group, Parquet stores metadata, including the minimum and maximum values for each column within that chunk.

When you run a query with a WHERE clause (a "predicate"), the query engine can use this metadata to skip huge portions of the file. In our query WHERE transaction_date >= '2024-04-01', the engine first reads the file's metadata. If it finds a row group where the maximum transaction_date is '2024-03-31', it knows that no rows in that entire chunk can possibly match the filter. It skips reading that group entirely, avoiding potentially millions of rows of I/O. CSV files have no such internal structure, forcing the engine to scan every single row.

The Proof Is in the Performance

These theoretical benefits translate into very real performance gains. The DuckDB team published a blog post that compares the two formats. While they have done incredible work to make CSV reading fast, the fundamental advantages of Parquet's format still make it the clear winner for analytics.

The performance charts below visually summarize these findings, showing that Parquet is not only faster for queries but also results in dramatically smaller files.

Conclusion

You now understand the "why" behind Parquet's popularity. While CSV is simple and human-readable, its row-based structure is fundamentally inefficient for the types of analytical queries that are central to finance. Parquet's columnar format is purpose-built for this work.

Key Takeaways:

• CSV is row-based: Data for an entire record is stored together. This is inefficient for queries that only need a subset of columns.
• Parquet is column-based: Data for each column is stored together. This enables several key optimizations.
• Column Pruning: Query engines only read the specific columns needed for a query, drastically reducing I/O.
• Efficient Compression: The uniformity of data within a column allows for specialized encoding (like dictionary and RLE) that significantly reduces file size.
• Data Skipping: Metadata stored within Parquet files allows the engine to skip large chunks of data that don't match the query's WHERE clause.
• The Result: For analytical workloads, Parquet delivers smaller files and much faster query performance.

In our next lesson, we will move from theory to practice. You'll learn how to take the data you've processed with DuckDB and save it into the efficient Parquet format, creating a high-performance foundation for your automated financial analysis pipelines.