Parquet and Arrow

Rows or columns?

A CSV file or a database row store keeps each record together: all the fields of trade 1, then trade 2, and so on. Analysis rarely wants that. Computing the average price of ten million trades needs only the price column, but a row layout makes the reader step over every other field. A columnar layout keeps each column contiguous, so a query reads only the columns it names, similar values sit side by side and compress very well, and arithmetic over a column runs over one tight block of memory. This is why quant data lakes use Parquet on disk and Arrow in memory.

# The same table as rows and as columns
rows = [{"symbol": "AAPL", "price": 184.25, "qty": 100},
        {"symbol": "MSFT", "price": 410.5, "qty": 50},
        {"symbol": "AAPL", "price": 184.5, "qty": 25}]

columns = {name: [row[name] for row in rows] for name in rows[0]}
print(columns)                                       # {'symbol': ['AAPL', 'MSFT', 'AAPL'], 'price': [184.25, 410.5, 184.5], 'qty': [100, 50, 25]}
print(sum(columns["price"]) / len(rows))             # 259.75   the average only touched one column

back = [{name: columns[name][i] for name in columns} for i in range(len(rows))]
print(back == rows)                                  # True  nothing is lost either way

Parquet: row groups, statistics and encodings

A Parquet file splits the table into row groups (say a million rows each); inside a group every column is stored as its own chunk, and the file records the minimum and maximum of each chunk. A query for price between 180 and 190 reads the statistics first and skips every row group whose range cannot contain such a price (predicate pushdown), and it reads only the requested columns (projection pushdown). Columns are also encoded: a column with few distinct values, such as the symbol, is stored as a small dictionary plus one tiny integer code per row, and the result is then compressed. A file of repetitive market data is often ten times smaller than the CSV.

# Dictionary encoding turns a few distinct values into a small dictionary plus integer codes
def dictionary_encode(values):
    positions, dictionary, codes = {}, [], []
    for value in values:
        if value not in positions:
            positions[value] = len(dictionary)
            dictionary.append(value)
        codes.append(positions[value])
    return dictionary, codes

symbols = ["AAPL", "MSFT", "AAPL", "AAPL", "MSFT", "NVDA"] * 1000
dictionary, codes = dictionary_encode(symbols)
print(dictionary, codes[:6])                         # ['AAPL', 'MSFT', 'NVDA'] [0, 1, 0, 0, 1, 2]
print([dictionary[c] for c in codes] == symbols)     # True  decoding restores the column
print(len(dictionary), "distinct values for", len(symbols), "rows")      # 3 distinct values for 6000 rows

# Statistics let a reader skip whole row groups without opening them
def groups_to_read(stats, low, high):
    return [i for i, (minimum, maximum) in enumerate(stats) if maximum >= low and minimum <= high]

stats = [(180.0, 183.5), (183.0, 186.0), (185.5, 190.0), (189.0, 195.0)]        # (min, max) of price per row group
print(groups_to_read(stats, 184.0, 185.0))           # [1]        only the second group can hold a price in this range
print(groups_to_read(stats, 189.5, 192.0))           # [2, 3]
print(groups_to_read(stats, 200.0, 210.0))           # []         no group needs to be read at all

Arrow, pandas and reading less

Apache Arrow defines the same columnar layout for memory, so programs and languages can hand a table to each other without converting it: pandas, Polars, DuckDB and Spark can all view one Arrow table with no copy. In practice you use pandas.read_parquet(path, columns=["symbol", "price"], filters=[("price", ">", 180)]), which pushes the column choice and the filter into the reader, or pyarrow.parquet.read_table. Write data partitioned by a column such as date (df.to_parquet(path, partition_cols=["date"])) so a query for one day opens one directory. Keep row groups large enough to compress well but small enough that the statistics can skip many of them, and sort the data by the column you filter on so each group covers a narrow range.

# Sorting by the filter column makes the row groups cover narrow, non-overlapping ranges
import random

rng = random.Random(7)
prices = [round(rng.uniform(100, 200), 2) for _ in range(10_000)]

def group_stats(values, size):
    groups = [values[i : i + size] for i in range(0, len(values), size)]
    return [(min(g), max(g)) for g in groups]

def groups_to_read(stats, low, high):
    return [i for i, (a, b) in enumerate(stats) if b >= low and a <= high]

unsorted_groups = len(groups_to_read(group_stats(prices, 1000), 150.0, 151.0))
sorted_groups = len(groups_to_read(group_stats(sorted(prices), 1000), 150.0, 151.0))
print(unsorted_groups, sorted_groups)                # 10 1   without sorting every group's range overlaps the query

• Columnar storage keeps each column together: queries read only the columns they need, similar values compress well, and column arithmetic is fast.
• Parquet splits data into row groups with min/max statistics and dictionary-encodes repeated values; readers skip groups and columns they do not need.
• Arrow is the in-memory twin shared by pandas, Polars and DuckDB; partition by date, sort by the filter column, append instead of editing.

Next up: time-series databases, stores built for timestamped data such as ticks and metrics.

Lab · what's graded

• Turn rows into columns with to_columns25 pts
• Rebuild the rows with to_rows25 pts
• Shrink repeats with dictionary_encode25 pts
• Skip what you can with groups_to_read25 pts