Skip to content
Tutorials Center
My learning

Topics

Lesson 16 of 38 ยท Reading Files and Formats

Parquet Files

Learn why Parquet is the ideal partner of DuckDB: columnar layout, compression, row groups, statistics, read_parquet, parquet_metadata, projection and filter pushdown, and writing Parquet.

If CSV is the format everybody can open, Apache Parquet is the format analytics engines love. It is a compressed, columnar file format with built-in types and statistics. DuckDB reads and writes Parquet natively, and the two fit together: the same ideas you saw in Columnar storage and vectorized execution apply to a Parquet file, so a query often reads only a small fraction of it.

Reading a Parquet file needs the real engine and a real file, so DuckDB code is read-only here. The runnable stand-ins illustrate the concepts (what a column chunk stores, why filtering on statistics works) with SQLite queries.

Why Parquet beats CSV

CSV Parquet
Layout Text rows Binary columns
Types Guessed Stored in the file
Size Large Often 5 to 10 times smaller
Reading one column Must parse everything Reads that column only
Statistics for skipping None Min, max, null count per row group
Nested data No Yes (lists, structs, maps)

A 10 GB CSV commonly becomes a 1 to 2 GB Parquet file, and a query that touches three columns reads a few percent of that. Many data tools write Parquet: Spark, pandas, Polars, cloud warehouse exports and data lakes.

Reading a Parquet file

The simplest form again uses the file name, and read_parquet accepts options:

SQL
-- DuckDB (read-only example)
SELECT * FROM 'sales.parquet' LIMIT 5;

SELECT country, sum(amount) AS revenue
FROM read_parquet('sales.parquet')
GROUP BY country
ORDER BY revenue DESC;

No sniffing is needed. The file contains its schema, so DESCRIBE SELECT * FROM 'sales.parquet' is exact. Compare the CSV case, where types are guessed.

Looking inside: schema and metadata

DuckDB has functions to inspect the file's structure without reading the data:

SQL
-- DuckDB (read-only example)
SELECT * FROM parquet_schema('sales.parquet');      -- columns and types
SELECT * FROM parquet_metadata('sales.parquet');    -- per row group, per column: min, max, size
SELECT * FROM parquet_file_metadata('sales.parquet');  -- row count, row groups, created by

parquet_metadata shows for each row group and column chunk: the number of values, the compressed size, the encoding and the min and max statistics. These are the numbers DuckDB uses to skip data.

How a Parquet file is organised

flowchart TD
  F["Parquet file"] --> RG1["Row group 1 (about 100k rows)"]
  F --> RG2["Row group 2"]
  F --> FT["Footer: schema + statistics"]
  RG1 --> C1["Column chunk: order_id"]
  RG1 --> C2["Column chunk: country"]
  RG1 --> C3["Column chunk: amount"]

A file is split into row groups. Inside each, every column has its own column chunk, compressed separately with codecs such as Snappy, Zstd or Gzip, plus encodings like dictionary and run-length. A footer at the end lists the schema and the statistics for every chunk, so readers can plan before touching the data.

Pushdown: why queries read so little

Two optimizations make Parquet queries fast:

  • Projection pushdown: only the column chunks named in the query are read. SELECT amount on a 50-column file reads one column.
  • Filter pushdown: DuckDB compares your WHERE conditions with the min/max statistics, skipping every row group that cannot match. With WHERE amount > 1000 and a group whose maximum is 400, that group is never read.

You can see pushdown in a plan: the PARQUET_SCAN (or READ_PARQUET) operator lists Projections and Filters, as you saw in Reading query plans.

The statistics idea can be shown with plain SQL. Imagine the table is split into blocks of ten rows, and each block records its min and max price. A filter price > 150 only needs blocks whose max exceeds 150:

SQL Stand-in: zone maps over blocks of rows ยท database: shop

Press Run (or Ctrl + Enter) to see the result here.

Blocks marked "skip" are exactly what DuckDB avoids reading. When the data is sorted by the filter column, blocks have narrow ranges, so more of them are skipped. A second stand-in shows how sorting tightens the ranges:

SQL Stand-in: sorting narrows the ranges ยท database: shop

Press Run (or Ctrl + Enter) to see the result here.

Reading options

SQL
-- DuckDB (read-only example)
SELECT * FROM read_parquet('sales.parquet', filename = true);          -- adds a file-name column
SELECT * FROM read_parquet(['jan.parquet', 'feb.parquet']);            -- several files
SELECT * FROM read_parquet('data/*.parquet', union_by_name = true);    -- schemas that differ
SELECT * FROM read_parquet('data/**/*.parquet', hive_partitioning = true);

Reading several files at once is covered in Many files, globs and Hive partitions.

Writing Parquet

COPY ... TO writes a query's result as Parquet:

SQL
-- DuckDB (read-only example)
COPY (SELECT * FROM 'orders.csv') TO 'orders.parquet' (FORMAT parquet);

COPY orders TO 'orders.parquet'
  (FORMAT parquet, COMPRESSION zstd, ROW_GROUP_SIZE 100000);

Useful settings:

  • COMPRESSION: snappy (default in older versions, fast), zstd (smaller, a great default), gzip, uncompressed.
  • ROW_GROUP_SIZE: rows per group. Larger groups compress better; smaller groups skip more precisely.
  • PER_THREAD_OUTPUT or FILE_SIZE_BYTES: write multiple files.
  • PARTITION_BY (year, month): write a folder tree split by column values.

Sort before you write. COPY (SELECT * FROM t ORDER BY event_date) TO 'out.parquet' gives narrow statistics on event_date and much better skipping later.

Parquet is immutable

You cannot update a row inside a Parquet file. To change data you write a new file. That is why Parquet is great for analytical snapshots and data lakes but not for application data that changes constantly. A DuckDB table or a transactional database holds the live data, and Parquet is the exchange and archive format.

Common mistakes

  • Reading with SELECT * on a very wide file: name your columns.
  • Tiny files: thousands of 100 KB files cost more in opening overhead than one 1 GB file. Aim for files of tens or hundreds of megabytes.
  • Writing without a sort, so statistics are wide and useless.
  • Assuming CSV-like type guessing: Parquet types are exact, so DECIMAL, TIMESTAMP and STRUCT columns arrive as such.

Check your understanding

Quick check ยท 5 questions

of 5 answered You scored / 5 ยท perfect!

  1. 1. Why does a query on a Parquet file often read only a small part of it?

    Projection pushdown reads only needed columns, and min/max statistics let filters skip row groups.
  2. 2. Where does a Parquet file keep its schema and statistics?

    The footer lists the schema and per-chunk statistics so readers can plan before reading data.
  3. 3. Which function shows min and max values per row group?

    parquet_metadata returns one row per column chunk with statistics, sizes and encodings.
  4. 4. What is a good habit before writing a Parquet file you will filter by date?

    Sorted data gives narrow min/max ranges per row group, which makes skipping more effective.
  5. 5. Can you UPDATE one row inside a Parquet file?

    Parquet files are immutable; changing data means writing new files.

Summary

  • Parquet is a typed, compressed, columnar format with statistics in its footer.
  • DuckDB uses projection and filter pushdown to read only what a query needs.
  • parquet_schema, parquet_metadata and parquet_file_metadata inspect a file.
  • Write with COPY ... (FORMAT parquet, COMPRESSION zstd) and sort first; avoid tiny files.
  • Parquet files are immutable snapshots, not live tables.

Next: Reading JSON