Converting a CSV to Parquet With DuckDB in One Line

If you query the same CSV more than a few times, convert it once. Parquet is a columnar binary format: it stores the type of every column, compresses each column on its own and lets a reader fetch only the columns and ranges a query asks for. DuckDB writes it with a single statement and reads it back the same way it reads a CSV, so the change to your queries is the file name. This article converts a 2,000,000 row orders file, looks at what schema DuckDB chose, fixes the one column I would not leave to a guess, compares the four compression codecs on the same data and checks that nothing changed on the way. The reasoning for querying files in place before converting is in the DuckDB CSV guide.

The one line

The source is a CSV with order_id, customer_id, zip, amount, created_at, status, country and note. The conversion is a COPY of a query to a file with a format:

duckdb -c "COPY (SELECT * FROM 'orders-2m.csv') TO 'orders-one-line.parquet' (FORMAT parquet)" && ls -l orders-one-line.parquet | awk '{printf "%s %.1f MB\n", $9, $5/1e6}'

orders-one-line.parquet 51.5 MB

That wrote orders-one-line.parquet at 51.5 MB, from a 129.9 MB source. The query inside the parentheses can be anything, so this is also where you filter rows, drop columns or rename them on the way. The statement itself is documented on the COPY page, and the format is described in the Parquet overview. I write it with the query inside because that is where a WHERE or a column list goes.

What schema it wrote

Because a CSV has no types, the types in the new file are whatever the sniffer decided. Parquet records them, so you can read them back with parquet_schema:

duckdb -box -c "SELECT name, type, converted_type FROM parquet_schema('orders-one-line.parquet') WHERE name <> 'duckdb_schema'"

┌─────────────┬────────────┬──────────────────┐
│    name     │    type    │  converted_type  │
├─────────────┼────────────┼──────────────────┤
│ order_id    │ INT64      │ INT_64           │
│ customer_id │ INT64      │ INT_64           │
│ zip         │ BYTE_ARRAY │ UTF8             │
│ amount      │ DOUBLE     │ NULL             │
│ created_at  │ INT64      │ TIMESTAMP_MICROS │
│ status      │ BYTE_ARRAY │ UTF8             │
│ country     │ BYTE_ARRAY │ UTF8             │
│ note        │ BYTE_ARRAY │ UTF8             │
└─────────────┴────────────┴──────────────────┘

order_id and customer_id are 64 bit integers. zip is a BYTE_ARRAY with the UTF8 annotation, that is, text, which is what I want for a ZIP code. created_at is INT64 with TIMESTAMP_MICROS, a real timestamp. status, country and note are text. And amount is DOUBLE, a binary floating point number.

That last one is the column to decide on purpose. A DOUBLE is fine for a measurement and the wrong type for money: it cannot represent most decimal fractions exactly, and the sums of many of them drift. The CSV did not say which it was; the sniffer saw 103.1 and 31.59 and chose the type that holds both. If the column is money, say so.

Fix the types before writing

Put a read_csv with types inside the query, as in the type fixing article, and add the codec while you are there:

duckdb -c "COPY (SELECT * FROM read_csv('orders-2m.csv', types={'zip': 'VARCHAR', 'amount': 'DECIMAL(10,2)'})) TO 'orders-typed.parquet' (FORMAT parquet, COMPRESSION zstd)" && duckdb -box -c "SELECT name, type, converted_type, logical_type FROM parquet_schema('orders-typed.parquet') WHERE name IN ('zip', 'amount', 'created_at')"

┌────────────┬────────────┬──────────────────┬─────────────────────────────────────────────────────────────────────────────────────────────────────┐
│    name    │    type    │  converted_type  │                                            logical_type                                             │
├────────────┼────────────┼──────────────────┼─────────────────────────────────────────────────────────────────────────────────────────────────────┤
│ zip        │ BYTE_ARRAY │ UTF8             │ NULL                                                                                                │
│ amount     │ INT64      │ DECIMAL          │ DecimalType(scale=2, precision=10)                                                                  │
│ created_at │ INT64      │ TIMESTAMP_MICROS │ TimestampType(isAdjustedToUTC=0, unit=TimeUnit(MILLIS=<null>, MICROS=MicroSeconds(), NANOS=<null>)) │
└────────────┴────────────┴──────────────────┴─────────────────────────────────────────────────────────────────────────────────────────────────────┘

zip is explicitly VARCHAR and stays BYTE_ARRAY/UTF8. amount is now INT64 with converted_type DECIMAL and a logical type of DecimalType(scale=2, precision=10), which stores the value as an integer count of cents and shows it with two decimals. created_at stayed TIMESTAMP_MICROS, with isAdjustedToUTC=0, meaning the timestamp carries no time zone, as in the CSV. Reading a few rows back shows the effect:

duckdb -box -c "SELECT order_id, zip, amount, created_at FROM 'orders-typed.parquet' LIMIT 3"

┌──────────┬───────┬────────┬─────────────────────┐
│ order_id │  zip  │ amount │     created_at      │
├──────────┼───────┼────────┼─────────────────────┤
│ 1        │ 99154 │ 103.10 │ 2025-11-22 13:08:24 │
│ 2        │ 34494 │ 31.59  │ 2025-03-03 08:03:33 │
│ 3        │ 93451 │ 89.98  │ 2025-02-15 11:13:00 │
└──────────┴───────┴────────┴─────────────────────┘

103.10, 31.59 and 89.98, with the two decimal places the type guarantees, where the DOUBLE version prints 103.1. If you are going to compute with these values later in another tool, an exact decimal type is one less thing to explain.

Choosing a compression codec

Parquet compresses each column chunk with a codec, and DuckDB lets you pick one with COMPRESSION. I wrote the same data four times:

for c in uncompressed snappy gzip zstd; do
  f=/tmp/orders-$c.parquet
  /usr/bin/time -f "%e" -o /tmp/t.txt duckdb -c "COPY (SELECT * FROM 'orders-2m.csv') TO '$f' (FORMAT parquet, COMPRESSION $c)"
  printf '%-13s %6.1f MB  %5.2f s\n' $c "$(echo "$(stat -c %s $f) / 1000000" | bc -l)" "$(cat /tmp/t.txt)"
done

uncompressed    75.5 MB   0.50 s
snappy          51.5 MB   0.45 s
gzip            34.9 MB   1.02 s
zstd            34.2 MB   0.50 s

The no-option file in the previous section was 51.5 MB, the same as snappy, so snappy is what you get by default. zstd is smaller than gzip and took half the time on this data, and smaller than snappy by a third at about the same time. These are single runs of one command, which I would not stretch beyond "zstd was not slower here"; the sizes are exact, the times are not. My default is zstd. If the files will be read by an older tool, check what it supports first, since snappy is the most widely supported of the four.

How the file is laid out

A Parquet file is split into row groups, each holding a chunk of every column. The size of a group sets how well a reader can skip data it does not need:

duckdb -box -c "SELECT count(*) AS row_groups, sum(row_group_num_rows) / count(DISTINCT row_group_id) AS rows_per_group FROM parquet_metadata('orders-typed.parquet') WHERE path_in_schema = 'order_id'" 2>&1 | head -6

┌────────────┬────────────────────┐
│ row_groups │   rows_per_group   │
├────────────┼────────────────────┤
│ 17         │ 117647.05882352941 │
└────────────┴────────────────────┘

The 2,000,000 rows were written as 17 row groups. The format also records a minimum and maximum for each column in each group, which is how a query like WHERE order_id < 1000 can skip every group but the first. You rarely need to tune this; I mention it because it is the reason a Parquet file answers range queries faster than its size suggests.

Check that nothing changed

A conversion that has not been checked is a guess. Compare the row count and a numeric total between the CSV and the new file:

duckdb -box -c "SELECT (SELECT count(*) FROM 'orders-2m.csv') AS csv_rows, (SELECT count(*) FROM 'orders-typed.parquet') AS parquet_rows, (SELECT round(sum(amount), 2) FROM 'orders-2m.csv') AS csv_total, (SELECT sum(amount) FROM 'orders-typed.parquet') AS parquet_total"

┌──────────┬──────────────┬──────────────┬───────────────┐
│ csv_rows │ parquet_rows │  csv_total   │ parquet_total │
├──────────┼──────────────┼──────────────┼───────────────┤
│ 2000000  │ 2000000      │ 160174646.36 │ 160174646.36  │
└──────────┴──────────────┴──────────────┴───────────────┘

2,000,000 rows in both, and a total of 160,174,646.36 from the CSV against 160,174,646.36 from the Parquet. Then the check for the column that is easiest to damage:

duckdb -box -c "SELECT count(*) FILTER (WHERE zip LIKE '0%') AS zips_with_leading_zero_parquet FROM 'orders-typed.parquet'" -c "SELECT count(*) FILTER (WHERE zip LIKE '0%') AS zips_with_leading_zero_default FROM 'orders-one-line.parquet'"

┌────────────────────────────────┐
│ zips_with_leading_zero_parquet │
├────────────────────────────────┤
│ 190451                         │
└────────────────────────────────┘
┌────────────────────────────────┐
│ zips_with_leading_zero_default │
├────────────────────────────────┤
│ 190451                         │
└────────────────────────────────┘

190,451 ZIP codes starting with 0 in the typed file, and the same number in the one made with no overrides. Both kept the leading zeros. If one of these numbers were lower, the sniffer would have made zip an integer somewhere and the zeros would be gone for good.

What it buys

On the same query, grouping 2,000,000 orders by country and taking the top three by revenue, I ran each file five times and kept the fastest:

for src in "orders-2m.csv" "orders-typed.parquet"; do
  best=999
  for i in 1 2 3 4 5; do
    t=$( { /usr/bin/time -f "%e" duckdb -c "SELECT country, count(*), round(sum(amount),2) FROM '$src' GROUP BY country ORDER BY 3 DESC LIMIT 3" >/dev/null; } 2>&1 )
    best=$(echo "if ($t < $best) $t else $best" | bc -l)
  done
  printf '%-22s best of 5: %s s\n' "$src" "$best"
done

orders-2m.csv          best of 5: .20 s
orders-typed.parquet   best of 5: .04 s

0.20 s for the CSV and 0.04 s for the Parquet file, and the sizes were 129.9 MB against 33.9 MB:

ls -l orders-2m.csv orders-typed.parquet | awk '{printf "%-22s %.1f MB\n", $9, $5/1e6}'

orders-2m.csv          129.9 MB
orders-typed.parquet   33.9 MB

A fifth of the time and a quarter of the space. The CSV is parsed from text on every query and the Parquet file is not, and the query reads only the country and amount columns from the Parquet file. For one query, the conversion costs more than it saves, since writing took about half a second. From the second or third it pays for itself, and for a file that is queried daily, it is not close. How the format compares for repeated analysis is covered in the Parquet versus CSV comparison.

How to tell it worked

Compare the row count, a total and the minimum and maximum of each numeric and date column between the source and the new file. Read the schema with parquet_schema and check each type against what the column means. Open the file in another tool, such as pandas or a different DuckDB version, to confirm that the types survive. And keep the conversion statement, with its types, so the next file is converted the same way.

When Parquet is the wrong target

If the file is read once and thrown away, converting it costs more than it saves. If people need to open the data in a text editor, diff it in version control or paste it into a spreadsheet, a binary format is in the way. If something downstream only reads CSV, such as a vendor upload form, keep the CSV. And Parquet files are written once: you cannot append a row to one, so a table that grows by the minute is a folder of files, or a database. For the large, read-mostly, repeatedly queried case, it is the right choice, and the conversion is one line.