DuckDB's Native Parquet Support: What Engineers Actually Need to Know

The problem: moving columnar data without the ceremony

You have a 50 GB Parquet dataset in S3. You need to filter three columns, aggregate by a fourth, and write the result back to Parquet—ideally without spinning up a Spark cluster or wrestling with PyArrow schemas. DuckDB's built-in Parquet extension does exactly this from a single SQL process, but the documentation scatters the relevant knobs across several pages. Here's the practical map.

Read path: column pruning and predicate push-down happen automatically

When you run SELECT col_a, col_c FROM read_parquet('s3://bucket/data/*.parquet') WHERE col_b > 100, DuckDB's PARQUET_SCAN operator reads only the row groups and columns required. The planner pushes the col_b > 100 filter into the scan, so the engine never materializes for rows that fail the predicate. You can verify this with EXPLAIN:

EXPLAIN SELECT col_a, col_c FROM read_parquet('s3://bucket/data/*.parquet') WHERE col_b > 100;

Look for column_pruning=true and predicate_push_down=true in the PARQUET_SCAN node. If they're missing, you're likely on a pre-0.6 build or the predicate references a column with a type the statistics can't represent (e.g., a nested struct field).

Write path: one statement, configurable compression

Writing is symmetric. The COPY command lets you control compression, row-group size, and partitioning without leaving SQL:

COPY (
  SELECT user_id, event_ts, payload
  FROM events
  WHERE event_ts >= '2024-01-01'
) TO 's3://bucket/processed/events_2024.parquet'
  (FORMAT PARQUET, COMPRESSION ZSTD, ROW_GROUP_SIZE 1000000,
   PARTITION_BY (year(event_ts), month(event_ts)));

ZSTD (default) gives a good ratio/speed trade-off; SNAPPY is faster but larger. ROW_GROUP_SIZE tunes the unit of parallel reads downstream—larger groups favor scan throughput, smaller groups favor predicate selectivity. The PARTITION_BY clause emits a Hive-style directory layout (year=2024/month=01/) that Athena, Spark, and DuckDB itself all understand.

Worked example: cleaning a messy log dataset

Suppose raw_logs.parquet has a message column stored as VARCHAR but you know it's actually JSON. You want to extract request_id and latency_ms, drop the rest, and write a lean Parquet file for a dashboard.

1. Inspect schema: DESCRIBE SELECT * FROM read_parquet('raw_logs.parquet') LIMIT 0;
2. Extract with JSON functions (DuckDB 1.0+):

   CREATE TABLE clean_logs AS
   SELECT
     json_extract_string(message, '$.request_id') AS request_id,
     json_extract(message, '$.latency_ms')::INTEGER AS latency_ms,
     timestamp
   FROM read_parquet('raw_logs.parquet');

3. Write partitioned output:

   COPY clean_logs TO 's3://bucket/clean_logs/'
     (FORMAT PARQUET, COMPRESSION ZSTD,
      PARTITION_BY (date_trunc('day', timestamp)));

4. Verify pruning works:

   EXPLAIN SELECT count(*) FROM read_parquet('s3://bucket/clean_logs/**')
   WHERE request_id = 'req-abc-123';

The final EXPLAIN should show PARQUET_SCAN with partition filters applied before any row-group statistics are consulted.

Trade-offs and limitations

• Remote storage latency: Column pruning only saves I/O if the HTTP server honors Range requests. S3 and GCS do; many on-prem object stores or misconfigured CDNs don't. If you see full-file downloads in network traces, check curl -I -H "Range: bytes=0-100" .
• Nested type evolution: Adding a field to a STRUCT inside a LIST works on read (missing fields become NULL), but writing the evolved schema back requires an explicit CAST or a full rewrite. Test schema changes on a sample before committing pipeline code.
• Version floor: Predicate push-down for IN lists and LIKE patterns landed in 0.9. If you're pinned to 0.8.x for compatibility, expect full scans on those predicates.

Quick verification checklist

• Run SELECT * FROM duckdb_extensions(); and confirm parquet shows loaded.
• Execute SELECT * FROM read_parquet('sample.parquet') LIMIT 5; on a known-good file—no error means the reader works.
• Run an EXPLAIN on a filtered query and grep for column_pruning and predicate_push_down flags.

If all three pass, you're getting the columnar benefits DuckDB advertises. If not, upgrade the binary or check the storage layer's Range support before blaming the engine.