DuckDB Parquet Partitioned vs Non-Partitioned Writes Guide

The decision

You need to export a DuckDB result to Parquet for downstream use. The practical choice is whether to use DuckDB’s built‑in partitioned write or a single file write. Partitioned writes create a directory with one Parquet file per partition value. Non‑partitioned writes create one Parquet file.

The useful takeaway is: partition when downstream reads are selective on a low‑cardinality column and you control the reader. Keep a single file when the dataset is small, read end‑to‑end, or consumed by tools that expect a single path.

Constraints to check first

• DuckDB version. The partition_by argument to write_parquet is not available in older releases. Check your version before relying on it.
• Downstream reader expectations. Spark, Hive and DuckDB’s read_parquet('dir/') understand Hive‑style partitions. A tool that expects a single file path will fail on a partitioned directory.
• Partition column cardinality. A column with thousands or millions of distinct values creates many small files and directory overhead.
• Write location permissions. The Python client needs write permission to the output directory and, for partitioned writes, permission to create subdirectories.

Options compared

Aspect	Partitioned write	Non‑partitioned write
Output shape	Directory with subfolders like country=US/ and multiple Parquet files	Single Parquet file
Read selectivity	Readers can skip whole partitions when filtering on partition column	Full scan required unless Parquet row groups are pruned
File management	More files, per‑file footers and directory metadata increase storage slightly	Simpler metadata, fewer files
Write behavior	Files written per partition, can improve parallel read throughput	One sequential write, lower overhead for small data
Best fit	Large tables, frequent filtered reads, Hive/Spark consumers	Small tables, full scans, single‑file consumers

Trade‑offs

Read performance

Partitioned writes improve selective reads. A query like WHERE country = 'US' can be satisfied by reading only the country=US directory. Non‑partitioned writes still benefit from Parquet column projection and predicate pushdown, but the engine must open the single file and evaluate row groups.

Write and storage cost

Partitioned writes can reduce per‑file write latency because each partition can be flushed independently, but total write time may increase with many partitions due to file open/close overhead. Storage is typically slightly higher for partitioned datasets because of repeated footers and directory entries.

Compression interaction

Compression choice interacts with file size. Smaller partitioned files benefit more from aggressive codecs like zstd. Very large single files may see diminishing returns and higher memory pressure during decompression.

Risks

Choosing a high‑cardinality column, e.g., timestamp with millisecond precision or a unique id, leads to file explosion and degrades performance. Partitioned output is a directory layout; passing a directory to a reader that expects a file will error.

Concrete implementation and validation

The following Python example shows both writes and how to validate row counts and file layout. Run it in a Python environment with duckdb and pandas installed, with write permission to the target directory.

import duckdb
import pandas as pd
import os

con = duckdb.connect(database=':memory:')

# Sample data

df = pd.DataFrame({
    'id': range(10000),
    'country': ['US','CA','GB'] * 3333 + ['US'],
    'value': range(10000)
})

# Create a temporary table in DuckDB
con.execute("CREATE TABLE src AS SELECT * FROM df")

# Non‑partitioned write
non_part_path = '/tmp/duckdb_parquet_demo/non_partitioned.parquet'
con.execute(f"COPY src TO '{non_part_path}' (FORMAT 'parquet')")

# Partitioned write
part_root = '/tmp/duckdb_parquet_demo/partitioned'
con.execute(f"COPY src TO '{part_root}' (FORMAT 'parquet', PARTITION_BY ['country'])")

Validation steps:

1. Check file layout. Use os.scandir to list files under non_part_path and part_root. Expect one file for non‑partitioned and multiple files under country=... subdirectories for partitioned.
2. Verify row counts match. Load both back with DuckDB and compare counts.

cnt_non = con.execute(f"SELECT COUNT(*) FROM read_parquet('{non_part_path}')").fetchone()[0]
cnt_part = con.execute(f"SELECT COUNT(*) FROM read_parquet('{part_root}')").fetchone()[0]
assert cnt_non == cnt_part

3. Check selective read behavior. Run a filtered query on the partitioned dataset and observe that only relevant partition directories are accessed. Compare against a full scan on the non‑partitioned file.

Limitations: The PARTITION_BY option in COPY ... TO and the partition_by parameter in the Python write_parquet API are version dependent. If unavailable, DuckDB will raise an error. Partition columns must be present in the source and are removed from the file schema, being encoded in the path.

Cleanup. Both writes create files on disk. Remove the output directories if you need to revert: rm -rf /tmp/duckdb_parquet_demo.