Compression and Encoding: Why Columnar Shrinks So Much
Dictionary, run-length, delta, and bit-packing encodings plus a codec (Snappy, Zstd, Gzip) shrink a column far more than a row-interleaved CSV, and you can measure the ratio in SQL.
What columnar buys you (part 2: compression)
Storing a column together does more than let you skip columns: it makes the data compress far better, because a compressor works best on similar values sitting next to each other. A row store interleaves an integer id, a string country, and a float price on every row, so a general compressor sees noise. A column store hands the compressor a million country values in a row, and those are extremely repetitive.
Two layers of shrinking happen, in order.
| encoding | what it does | great for |
|---|---|---|
| dictionary | store each distinct value once, rows point to it by id | low-cardinality strings (country, event_type) |
| run-length (RLE) | store a repeated value once with a count | long runs of one value (sorted or sparse columns) |
| delta | store the difference between consecutive values | sorted numbers, timestamps, ids |
| bit-packing | use only as many bits as the values need | small integers and dictionary ids |
First, Parquet applies column-local encodings that exploit structure:
- Dictionary encoding stores each distinct value once and replaces every row with a small integer id. A
countrycolumn with 200 distinct values becomes a tiny dictionary plus a column of ids. This is why low-cardinality strings compress dramatically. - Run-length encoding (RLE) stores a repeated value once with a count, which crushes sorted or sparse columns.
- Delta encoding stores the difference between consecutive values, ideal for sorted ids and timestamps.
- Bit-packing uses only as many bits as the values actually need.
Then, on top of the encoded column, Parquet applies a general compression codec. The three to know:
- Snappy is fast with a modest ratio, and it is the default in Spark, pandas, and PyArrow. (State this precisely: the Parquet Java library itself defaults to uncompressed; it is those engines that default to Snappy.)
- Gzip compresses smaller but slower.
- Zstd is the modern balance, often near Gzip's size at near Snappy's speed, and is increasingly the default choice.
The result is that data that would be a large CSV becomes a much smaller Parquet file, and because both storage and bytes-scanned cost money, that shrink is a direct cost saving.
Measuring the shrink with SQL
Each column in parquet_column_stats records both its uncompressed and its compressed size, so the compression ratio is just one divided by the other. The exercises compute the ratio per column (watch the low-cardinality strings win and the high-cardinality URL lose) and the whole file's overall shrink.
Common mistake: expecting every column to shrink equally. Low-cardinality columns dictionary-encode to almost nothing, while a high-cardinality column like a URL or a raw id barely compresses, so a file's size is usually dominated by a few wide columns.
Interview nuance: the second half of "why is Parquet faster and cheaper than CSV" is compression: columnar data groups like values, so dictionary and run-length encoding plus a codec like Snappy or Zstd shrink it far more than a row-interleaved CSV could. Naming an encoding (dictionary encoding for low-cardinality strings) is the detail that shows you understand why.
On a real platform this differs. Real compression ratios depend on the data, the encodings the writer chose, and the codec. The
parquet_column_statsnumbers here are illustrative but shaped like reality: a low-cardinalitycountrystring compresses far harder than a high-cardinalityurl. The ratio query is the real one.
CREATE TABLE parquet_column_stats (
column_name TEXT,
data_type TEXT,
uncompressed_bytes INTEGER,
compressed_bytes INTEGER
);
INSERT INTO parquet_column_stats (column_name, data_type, uncompressed_bytes, compressed_bytes) VALUES
('event_id', 'INT64', 80000000, 20000000),
('user_id', 'INT64', 80000000, 24000000),
('event_type', 'STRING', 200000000, 8000000), -- low-cardinality, dictionary-encodes tiny
('country', 'STRING', 120000000, 3000000), -- very low cardinality
('url', 'STRING', 500000000, 180000000), -- high-cardinality, compresses poorly
('device', 'STRING', 100000000, 5000000),
('revenue', 'DOUBLE', 80000000, 40000000),
('ts', 'INT64', 80000000, 16000000);-- Average compression ratio by data type: strings win because they dictionary-encode.
SELECT data_type,
ROUND(SUM(uncompressed_bytes) * 1.0 / SUM(compressed_bytes), 2) AS ratio_by_type
FROM parquet_column_stats
GROUP BY data_type
ORDER BY ratio_by_type DESC;Apply
Your turn
The task this lesson builds to.
Write a query that returns each column's compression ratio (uncompressed divided by compressed), as (column_name, data_type, compression_ratio), best-compressing first, over parquet_column_stats(column_name, data_type, uncompressed_bytes, compressed_bytes).
Round compression_ratio to 2 decimals, ordered by it descending, so the columns that compress best are on top.
3 hints and 1 automated check are waiting in the workspace.
Practice
Make it stick
A second problem on the same idea, plus 4 bonus drills.
Write a query that returns the whole table's total uncompressed and compressed size in MB and its overall compression ratio, as (uncompressed_mb, compressed_mb, overall_ratio), over the same parquet_column_stats table.
Treat 1 MB as 1,000,000 bytes. The overall ratio is total uncompressed divided by total compressed. Round every column to 2 decimals.
3 hints and 1 automated check are waiting in the workspace.