Converting a CSV to Parquet and Why You Might Want To

CSV is a good format for sending a table to a person or another system. It is a bad format for asking the same table a hundred questions. Every query has to read all the text, split it at commas, find the quotes and convert the digits to numbers, and it does that again for each query. Parquet is the format most analytical tools use for the second job. This article converts one CSV into Parquet in one statement and then opens the file up to show what changed: the types, the layout, the compression and the statistics. Those four things are the entire reason for the speed-up, and understanding them tells you when the conversion is worth doing.

The conversion

With DuckDB, reading a CSV and writing Parquet is one COPY statement (the Parquet section of the DuckDB documentation lists the options):

head -n 3 work/orders-2m.csv
duckdb -box -c "DESCRIBE SELECT * FROM read_csv('work/orders-2m.csv')"

order_id,customer_id,zip,amount,created_at,status,country,note
1,22313,99154,103.1,2025-11-22 13:08:24,shipped,GB,rush order
2,193489,34494,31.59,2025-03-03 08:03:33,failed,BR,leave at door
┌─────────────┬─────────────┬──────┬──────┬─────────┬───────┐
│ column_name │ column_type │ null │ key  │ default │ extra │
├─────────────┼─────────────┼──────┼──────┼─────────┼───────┤
│ order_id    │ BIGINT      │ YES  │ NULL │ NULL    │ NULL  │
│ customer_id │ BIGINT      │ YES  │ NULL │ NULL    │ NULL  │
│ zip         │ VARCHAR     │ YES  │ NULL │ NULL    │ NULL  │
│ amount      │ DOUBLE      │ YES  │ NULL │ NULL    │ NULL  │
│ created_at  │ TIMESTAMP   │ YES  │ NULL │ NULL    │ NULL  │
│ status      │ VARCHAR     │ YES  │ NULL │ NULL    │ NULL  │
│ country     │ VARCHAR     │ YES  │ NULL │ NULL    │ NULL  │
│ note        │ VARCHAR     │ YES  │ NULL │ NULL    │ NULL  │
└─────────────┴─────────────┴──────┴──────┴─────────┴───────┘

The first part of the output is what DuckDB inferred from the CSV before writing anything. order_id and customer_id are BIGINT, created_at is TIMESTAMP, zip, status, country and note are VARCHAR, and **amount is DOUBLE**. Two of those are decisions you may not agree with. zip stayed text, rightly, because DuckDB saw leading zeros (00500). And amount became a floating-point number, though it holds money. A CSV carries no types, so the converter chooses them, and Parquet records the choice permanently. I come back to this below.

Writing with different compression codecs:

for c in snappy zstd gzip uncompressed; do
  printf '%-13s' $c
  s=$(date +%s.%N)
  duckdb -c "COPY (SELECT * FROM read_csv('work/orders-2m.csv')) TO 'work/pq/o_$c.parquet' (FORMAT parquet, COMPRESSION $c)"
  e=$(date +%s.%N)
  printf '%10d bytes %6.2f s\n' $(stat -c %s work/pq/o_$c.parquet) $(echo "$e - $s" | bc)
done
ls -l work/orders-2m.csv | awk '{printf "%-13s%10d bytes (the CSV)\n", "csv", $5}'

snappy         51477383 bytes   0.93 s
zstd           34206903 bytes   0.59 s
gzip           34908832 bytes   1.20 s
uncompressed   75512763 bytes   0.68 s
csv           129919987 bytes (the CSV)

Even without compression the Parquet file is 58% of the CSV, because numbers stored as binary integers and doubles take less space than their digits. Compression takes it to 26% with zstd. zstd was both the smallest and the fastest to write in this run, which is not a general law; compression ratios depend on the data. The same job through other libraries:

/usr/bin/time -f "pandas + pyarrow: %e s, peak %M KB" python -c "
import pandas as pd
pd.read_csv('work/orders-2m.csv').to_parquet('work/pq/o_pandas.parquet')
"
ls -l work/pq/o_pandas.parquet | awk '{print $5 " bytes"}'
/usr/bin/time -f "polars streaming: %e s, peak %M KB" python -c "
import polars as pl
pl.scan_csv('work/orders-2m.csv').sink_parquet('work/pq/o_polars.parquet')
"
ls -l work/pq/o_polars.parquet | awk '{print $5 " bytes"}'

pandas + pyarrow: 2.82 s, peak 500296 KB
49004545 bytes
polars streaming: 1.12 s, peak 531992 KB
35534323 bytes

pandas with pyarrow read the CSV into memory and wrote 49,004,545 bytes in 2.82 s with a 500,296 KB peak. polars' streaming sink did it in 1.12 s with 531,992 KB for 35,534,323 bytes. They are fine for this size and need the data in memory (pandas) or a streaming plan (polars); DuckDB's COPY is the shortest to write. For files larger than memory, see reading a large CSV in chunks and the DuckDB articles.

What is inside the file

A Parquet file is a sequence of row groups, and each row group stores each column as its own block. DuckDB can show the file's metadata:

duckdb -box -c "SELECT path_in_schema AS col, type, compression, encodings, total_compressed_size AS bytes, stats_min, stats_max FROM parquet_metadata('work/pq/o_snappy.parquet') WHERE row_group_id = 0"
duckdb -box -c "SELECT count(DISTINCT row_group_id) AS row_groups, max(row_group_num_rows) AS rows_per_group FROM parquet_metadata('work/pq/o_snappy.parquet')"

┌─────────────┬────────────┬─────────────┬──────────────────┬────────┬─────────────────────┬─────────────────────┐
│     col     │    type    │ compression │    encodings     │ bytes  │      stats_min      │      stats_max      │
├─────────────┼────────────┼─────────────┼──────────────────┼────────┼─────────────────────┼─────────────────────┤
│ order_id    │ INT64      │ SNAPPY      │ PLAIN            │ 492545 │ 1                   │ 122880              │
│ customer_id │ INT64      │ SNAPPY      │ PLAIN            │ 583235 │ 1                   │ 249998              │
│ zip         │ BYTE_ARRAY │ SNAPPY      │ PLAIN            │ 669825 │ 00500               │ 99997               │
│ amount      │ DOUBLE     │ SNAPPY      │ PLAIN_DICTIONARY │ 339061 │ 0.08                │ 607.8               │
│ created_at  │ INT64      │ SNAPPY      │ PLAIN            │ 911679 │ 2025-01-01 00:03:30 │ 2025-12-31 23:55:54 │
│ status      │ BYTE_ARRAY │ SNAPPY      │ PLAIN_DICTIONARY │ 46672  │ failed              │ shipped             │
│ country     │ BYTE_ARRAY │ SNAPPY      │ PLAIN_DICTIONARY │ 46655  │ BR                  │ US                  │
│ note        │ BYTE_ARRAY │ SNAPPY      │ PLAIN_DICTIONARY │ 41200  │ call first          │ rush order          │
└─────────────┴────────────┴─────────────┴──────────────────┴────────┴─────────────────────┴─────────────────────┘
┌────────────┬────────────────┐
│ row_groups │ rows_per_group │
├────────────┼────────────────┤
│ 17         │ 124226         │
└────────────┴────────────────┘

The 2,000,000 rows are in 17 row groups of up to about 122,880 rows. For the first group, each column has its own type (INT64, BYTE_ARRAY for strings, DOUBLE), a compression codec (SNAPPY), an encoding and a size in bytes. Four things to read in it.

Column layout. Because amount is stored apart from note, a query that needs amount reads only the amount blocks. A CSV cannot do that; its columns are interleaved in every line, and a reader has to scan them all to find the fourth field in each.

Types. order_id is a 64-bit integer, 8 bytes for every value (the first group's block is 492,545 bytes, about 4 bytes a value after compression). The reader does not parse digits; it reads numbers.

Encoding. status, country and note use PLAIN_DICTIONARY: the file stores each distinct value once and the column as a list of small indexes. status has four distinct values, and its whole block for 122,880 rows is 46,672 bytes, about 3 bits a row. The text of those words costs 6 to 9 bytes a row in the CSV.

Statistics. Each row group records the minimum and maximum of each column. For order_id in the first group they are 1 and 122,880; for status they are failed and shipped. A query with WHERE order_id BETWEEN 1000 AND 1100 can read the statistics and skip every row group whose range does not include the values. A CSV has no statistics; to know where order 1000 is, you read the file.

Does it make queries faster?

Three queries, run against the CSV (read with read_csv, which re-parses on every run) and against the Parquet file:

Q1="SELECT status, count(*) AS n, round(sum(amount), 2) AS total FROM %s GROUP BY 1 ORDER BY 1"
for src in "read_csv('work/orders-2m.csv')" "'work/pq/o_snappy.parquet'"; do
  echo "== $src"
  q=$(printf "$Q1" "$src")
  time duckdb -c "$q" | head -n 12 | tail -n 4
done

== read_csv('work/orders-2m.csv')
│ pending  │ 400329 │  32077864.5 │
│ refunded │ 400135 │ 32069815.84 │
│ shipped  │ 399890 │ 32018038.17 │
└──────────┴────────┴─────────────┘

real	0m0.202s
user	0m0.395s
sys	0m0.131s
== 'work/pq/o_snappy.parquet'
│ pending  │ 400329 │  32077864.5 │
│ refunded │ 400135 │ 32069815.84 │
│ shipped  │ 399890 │ 32018038.17 │
└──────────┴────────┴─────────────┘

real	0m0.070s
user	0m0.084s
sys	0m0.047s

for src in "read_csv('work/orders-2m.csv')" "'work/pq/o_snappy.parquet'"; do
  echo "== $src: one column, max(amount)"
  time duckdb -noheader -list -c "SELECT max(amount) FROM $src"
done
echo; echo "== a selective filter"
for src in "read_csv('work/orders-2m.csv')" "'work/pq/o_snappy.parquet'"; do
  echo "$src"
  time duckdb -noheader -list -c "SELECT count(*) FROM $src WHERE order_id BETWEEN 1000 AND 1100"
done

== read_csv('work/orders-2m.csv'): one column, max(amount)
654.24

real	0m0.203s
user	0m0.323s
sys	0m0.137s
== 'work/pq/o_snappy.parquet': one column, max(amount)
654.24

real	0m0.036s
user	0m0.026s
sys	0m0.013s

== a selective filter
read_csv('work/orders-2m.csv')
101

real	0m0.191s
user	0m0.334s
sys	0m0.127s
'work/pq/o_snappy.parquet'
101

real	0m0.041s
user	0m0.031s
sys	0m0.021s

A factor of three to six, with a caveat: these queries are fast on the CSV too. DuckDB's CSV reader is parallel and the file was in the operating system's cache, so 130 MB is parsed in 0.2 s. On a wider table, a larger file, a cold cache or a slower reader the gap grows, because the CSV cost scales with all the bytes and the Parquet cost with only the columns and row groups the query touches. A longer comparison measures that on a bigger file. For a table you query once, converting costs more than it saves; for one you query repeatedly, you pay once.

The type decision

DuckDB made amount a DOUBLE, and a double cannot hold 103.10 exactly. For money, a decimal type is the right one, and the conversion can say so:

duckdb -c "COPY (SELECT order_id, customer_id, zip, CAST(amount AS DECIMAL(10,2)) AS amount, created_at, status, country, note FROM read_csv('work/orders-2m.csv')) TO 'work/pq/o_decimal.parquet' (FORMAT parquet, COMPRESSION zstd)"
ls -l work/pq/o_decimal.parquet work/pq/o_zstd.parquet | awk '{printf "%-22s %d bytes\n", $9, $5}' | sed 's#work/pq/##'
duckdb -box -c "SELECT column_name, column_type FROM (DESCRIBE SELECT * FROM 'work/pq/o_decimal.parquet') WHERE column_name = 'amount'"
duckdb -box -c "SELECT amount FROM 'work/pq/o_decimal.parquet' WHERE order_id = 1"

o_decimal.parquet 33922831 bytes
o_zstd.parquet 34206903 bytes
┌─────────────┬───────────────┐
│ column_name │  column_type  │
├─────────────┼───────────────┤
│ amount      │ DECIMAL(10,2) │
└─────────────┴───────────────┘
┌────────┐
│ amount │
├────────┤
│ 103.10 │
└────────┘

With CAST(amount AS DECIMAL(10,2)) the Parquet column is DECIMAL(10,2), the value for order 1 reads back as 103.10, and the zstd file is 33,922,831 bytes against 34,206,903 for the DOUBLE version. It is smaller, because a fixed-point decimal compresses better than 8-byte floats with irregular bits. Give identifiers, money and codes their types explicitly, pass the column types to the reader (types= in DuckDB, dtype= in pandas) instead of letting it guess, and keep the choice in the conversion script. The CSV types article explains why the guess is a guess.

Checking the conversion

Round-trip the data and compare it as sets of rows:

duckdb -noheader -list -c "SELECT count(*) FROM (SELECT * FROM read_csv('work/orders-2m.csv') EXCEPT SELECT * FROM 'work/pq/o_snappy.parquet')"
duckdb -noheader -list -c "SELECT count(*) FROM (SELECT * FROM 'work/pq/o_snappy.parquet' EXCEPT SELECT * FROM read_csv('work/orders-2m.csv'))"

0
0

EXCEPT in both directions returned 0, so the CSV and the Parquet file hold the same 2,000,000 rows under the inferred types. It is a strong check for types and values at once, and it would fail if a zip had lost its leading zero or a timestamp had shifted. Also compare the row counts and keep the CSV's checksum with the Parquet file.

What you give up

Parquet is not readable in a text editor; you need a tool, and it is awkward to inspect on a machine without one. Appending a row means writing a new file, since the layout is immutable, so a stream of small writes produces a pile of small files that reads slowly; the usual answer is to batch and compact. Changing a column's type means rewriting. Not every tool reads it: older ETL software, some spreadsheets and plenty of scripts want CSV. And the format has its own complexity: row group size, codecs and dictionary limits all matter once the data is big.

How to tell it worked

Compare row counts and run the EXCEPT check. Look at the schema and confirm each identifier is text and each amount is a decimal. Query a few values and compare them with the CSV. Time one query you care about against both files, with a cold cache if you can, and decide whether the saving is worth the extra file. Note the conversion script and the source checksum, so the Parquet can be rebuilt.

When a CSV is still the better file

For a file that goes to a person, to a partner who does not use analytical tools, to a system that only speaks CSV, or into a repository where a diff has to be readable, keep CSV. For a table that is read once, or is small enough that the parse time does not register, the conversion buys nothing. For a data set that will be queried many times by columns and filters, convert it, put the types in the script and keep the CSV as the source. The large-CSV hub puts this in the context of the other options.