Columnar storage, partitions and clustering

3 min read

Why it matters

Data warehousing appeared in 68% of the Data Engineer job descriptions we read. Cloud warehouses charge by bytes scanned (BigQuery on demand) or by compute time (Snowflake, Redshift, Databricks). Table design, not query cleverness, decides most of that cost. Interviewers ask how you would lay out a large table and why.

The concept

Row storage (CSV, most OLTP databases) keeps each row together: good for reading or updating one record.
Columnar storage (Parquet, ORC, every analytical warehouse) keeps each column together: a query that needs 3 of 20 columns reads only those 3, and similar values next to each other compress very well.
Partitioning splits a table into separate chunks by a column, usually a date. Queries filtering on it skip whole partitions.
Clustering or sorting orders data within a table so that each block (a Parquet row group, a Snowflake micro-partition) covers a narrow range of values. The engine reads block statistics (min and max) and skips blocks that cannot match a filter.

Choose partition and cluster keys from the filters your queries actually use.

Worked example: CSV against Parquet, sorted against unsorted

layouts.py

import os
import time
import duckdb

src = "yellow_tripdata_2025-01.parquet"          # NYC TLC, 3,475,226 trips
con = duckdb.connect()
con.sql(f"copy (select * from '{src}') to 'trips.csv' (header)")
con.sql(f"copy (select * from '{src}' order by tpep_pickup_datetime) "
        "to 'trips_sorted.parquet' (format parquet, row_group_size 100000)")

print("CSV MB", round(os.path.getsize("trips.csv") / 1e6, 1),
      "Parquet MB", round(os.path.getsize(src) / 1e6, 1))

def timed(label, sql):
    start = time.perf_counter()
    result = con.sql(sql).fetchall()
    print(f"{label:28} {time.perf_counter() - start:.3f}s {result}")

timed("CSV, one column", "select avg(fare_amount) from 'trips.csv'")
timed("Parquet, one column", f"select avg(fare_amount) from '{src}'")
day = "tpep_pickup_datetime >= '2025-01-20' and tpep_pickup_datetime < '2025-01-21'"
timed("unsorted Parquet, one day", f"select count(*) from '{src}' where {day}")
timed("sorted Parquet, one day", f"select count(*) from 'trips_sorted.parquet' where {day}")

On a laptop:

layout	size	query	time
CSV	365.0 MB	average fare	0.435 s
Parquet	59.2 MB	average fare	0.008 s
Parquet, unsorted	59.2 MB	trips on 20 January	0.016 s
Parquet, sorted by pickup time (35 row groups)		trips on 20 January	0.004 s

Both day queries return 90,094 trips. The columnar file is about six times smaller and about fifty times faster for a one-column aggregate, because the engine reads one compressed column instead of parsing every character of every row. Sorting by pickup time lets the engine skip row groups whose time range misses 20 January.

In cloud warehouses the same levers are named differently: partitioned tables and clustering in BigQuery, clustering keys over micro-partitions in Snowflake, sort keys in Redshift, partitioning and Z-ordering or liquid clustering in Databricks.

Common mistakes

select * in a columnar warehouse. You pay for every column.
Partitioning by a high-cardinality column such as customer id: millions of tiny partitions make everything slower.
Filtering on a function of the partition column (where date(ts) = ... when partitioned by ts) in engines that cannot prune through the function. Filter on the column directly.
Loading CSV into the lake and leaving it there. Convert to Parquet at the first step.

Interview angle

"How would you design a table for 5 billion events a year that analysts query by date and customer?" Answer: columnar format, partition by event date (daily or monthly depending on volume), cluster by customer id, and explain how both reduce scanned data. Mention monitoring bytes scanned per query.

Practice task

Write the trips partitioned by day (partition_by (pickup_date)) and compare the one-day query again.
Sort by PULocationID instead and time a query for one pickup zone.
Estimate the cost of scanning the CSV and the Parquet file daily on an on-demand warehouse, using the vendor's current price page.

#Why it matters

#The concept

#Worked example: CSV against Parquet, sorted against unsorted

#Common mistakes

#Interview angle

#Practice task

#Further reading