Querying Parquet Files Directly in DuckDB with Predicate Pushdown and Column Pruning

Problem: Slow analytics when you have to ETL Parquet into a temporary table

Many analysts copy Parquet files into a staging table or convert them to CSV before running SQL, which adds I/O and latency. The goal is to query the files directly, letting the engine read only the data it needs.

How DuckDB treats Parquet as a first‑class source

DuckDB can open a Parquet file just like a table: SELECT * FROM 'data.parquet'. The file is accessed through the built‑in Parquet reader; no extra loader is required. When the httpfs extension is loaded, the same syntax works for remote URLs such as s3://bucket/path/file.parquet.

Predicate pushdown

If the query contains a WHERE clause on columns that are part of the Parquet file’s partitioning or regular columns, DuckDB pushes the filter down to the reader. The reader evaluates the predicate while scanning row groups and skips those that do not match. Only simple equality (col = 5) or range (col BETWEEN 10 AND 20) predicates can be pushed; expressions that involve functions or UDFs stay in the query engine.

Column pruning

DuckDB inspects the SELECT list and the predicates to determine which columns are actually needed. The Parquet reader then decompresses only those column chunks, leaving the rest untouched in the file. This reduces both disk I/O and memory pressure, especially for wide files with many unused fields.

Worked example

1. Create a small Parquet file (outside DuckDB) with two columns, id INTEGER and value DOUBLE, and 10 million rows.
2. Start DuckDB and load the httpfs extension if you plan to query S3: INSTALL httpfs; LOAD httpfs;
3. Run a query that filters on id and returns only value:

   SELECT AVG(value) FROM 'file.parquet' WHERE id BETWEEN 1000 AND 2000;

4. Inspect the plan:

   EXPLAIN SELECT AVG(value) FROM 'file.parquet' WHERE id BETWEEN 1000 AND 2000;

   You should see a ParquetScan node that lists the filter (id BETWEEN 1000 AND 2000) and indicates that only the value column is scheduled for reading.
5. Check parallelism: DuckDB will print something like Parallelism: 4 threads in the console, showing that the scan is executed with multiple workers.

Trade‑offs and limitations

• Pushdown works only for simple predicates; complex expressions (WHERE UPPER(name) = 'ALICE') cannot be pushed down and will be evaluated after the scan.
• The first time a Parquet file is opened DuckDB reads the footer and builds column statistics, which adds a small fixed cost. Subsequent queries reuse the cached metadata, so the overhead is amortized.
• Remote storage performance depends on network bandwidth and the httpfs extension’s ability to perform range reads; very high latency connections may diminish the benefit of predicate pushdown.

Actionable closing

If you are doing ad‑hoc analytics on data already stored in Parquet, try querying the files directly with DuckDB. Keep your files partitioned on columns you frequently filter, install the httpfs extension for cloud storage, and use EXPLAIN to verify that pushdown and column pruning are active. When you notice a query scanning more data than expected, check whether the WHERE clause contains push‑down‑eligible predicates; otherwise consider materializing a smaller subset or adding appropriate partitions.