Querying Parquet Files Directly in DuckDB with Predicate and Projection Pushdown

Quick answer

Point DuckDB at your Parquet files and let the WHERE clause filter rows before they are read. DuckDB pushes the filter into the Parquet scan, skipping entire row groups whose min/max statistics cannot satisfy the predicate, and it reads only the columns referenced in the SELECT list.

How pushdown works – a worked example

Assume you have a directory of yearly sales files named sales/2023.parquet, sales/2024.parquet, etc., each containing columns order_date, region, amount and many others.

SELECT region,
       SUM(amount) AS total_sales
FROM   'sales/*.parquet'
WHERE  order_date >= DATE '2024-01-01'
GROUP BY region;

What happens internally:

1. DuckDB expands the glob 'sales/*.parquet' into a logical table that references all matching files.
2. During planning, the WHERE order_date >= DATE '2024-01-01' predicate is pushed down into the Parquet scan node.
3. For each Parquet file, DuckDB examines the zone‑map statistics (min/max) stored per row group for order_date. If a row group’s maximum date is earlier than 2024‑01‑01, the whole group is omitted from I/O.
4. Projection pushdown tells the scanner to decode only the columns order_date, region, and amount. All other columns stay on disk.
5. The remaining row groups are read in parallel, filtered, and aggregated by the vectorized execution engine.

You can see the pushdown in the query plan:

EXPLAIN SELECT region,
               SUM(amount) AS total_sales
FROM   'sales/*.parquet'
WHERE  order_date >= DATE '2024-01-01'
GROUP BY region;

The output will show a PARQUET_SCAN node with a filter entry and a projected_columns list limited to the three columns.

Limits and common pitfalls

1. Statistics dependence

Pushdown only skips row groups when useful min/max statistics exist. Files written without statistics (e.g., older Parquet writers) or with very small row groups will see little I/O reduction.

2. Schema conflicts across files

read_parquet (or the shortcut FROM 'file.parquet') infers a single schema from all matched files. If one file has amount as DOUBLE and another as DECIMAL(18,2), DuckDB may either fail or coerce values unexpectedly.

3. Version‑dependent syntax

The convenience of writing FROM 'sales/*.parquet' and automatic Hive‑style partitioning (hive_partitioning=true) appeared in recent releases. Older versions require the explicit read_parquet function and may need hive_partitioning=true set manually. Check your version with SELECT version();.

4. Single‑writer limitation

DuckDB’s database file is not safe for concurrent writers, but scanning Parquet files is read‑only and therefore unaffected by this constraint.

Verification steps

• Run EXPLAIN ANALYZE on the filtered query and compare the bytes_read metric with a SELECT * query on the same files. The filtered, projected query should show substantially fewer bytes read.
• Confirm the installed version: SELECT version(); and consult the matching documentation for read_parquet options.
• If you suspect missing statistics, inspect a file with PRAGMA show_parquet_metadata('sales/2024.parquet'); and look for statistics entries per column.