Exporting Partitioned Parquet Datasets from DuckDB with COPY TO

The Problem: Organizing Exported Data for Downstream Readers

When you export query results to Parquet, a single flat directory forces every downstream tool to scan all files. Partitioning the output by columns such as region or date lets query engines skip irrelevant files and reduces I/O. DuckDB's COPY TO command can write directly to a Hive-style partitioned layout, but the partition columns are removed from the individual Parquet files. This guide shows how to produce that dataset, verify it, and recover when a write goes wrong.

Desired Outcome

A directory tree containing Parquet files organized by key=value subdirectories. For example, partitioning sales data by region and year produces paths like output/region=west/year=2024/part_0.parquet. Downstream tools that understand Hive partitioning can reconstruct the partition columns from the directory names and prune files during queries.

Prerequisites

• A DuckDB CLI session or a client application connected to a DuckDB database.
• A source table or view containing the columns you intend to partition by.
• A writable output directory on a filesystem with enough free space for the full result set.
• Basic familiarity with SQL and filesystem paths.

Focused Procedure

Writing the Partitioned Dataset

Run the COPY TO command from a SQL prompt in DuckDB. The PARTITION_BY clause accepts one or more columns and controls the directory hierarchy.

COPY (SELECT * FROM sales) TO '/data/exports/sales_partitioned' (FORMAT PARQUET, PARTITION_BY (region, year));

In this example, /data/exports/sales_partitioned is a placeholder for your target path. DuckDB creates the directory structure automatically. The columns listed in PARTITION_BY are removed from the Parquet file schemas and encoded only in the directory names.

What Happens to the Schema

If your source query returns columns id, amount, region, and year, the Parquet files inside each partition directory will contain only id and amount. The region and year values live in the path. Any reader that does not support Hive-style partitioning will see those columns as missing.

Expected Checks

Verify the Directory Layout

After the COPY finishes, inspect the output directory from your operating system shell or file browser. You should see nested folders matching your partition columns, with .parquet files inside each leaf directory. A quick check from a Unix-like shell:

find /data/exports/sales_partitioned -type f -name '*.parquet' | head

Replace the path with your actual output location. The result should show files under paths containing region=... and year=.... If you see only loose files or an empty directory, the write did not complete as expected.

Read the Data Back

Use DuckDB's read_parquet function with hive_partitioning enabled to confirm the dataset is readable and the row count matches the source.

SELECT count(*) FROM read_parquet('/data/exports/sales_partitioned/**/*.parquet', hive_partitioning=true);

The glob pattern **/*.parquet matches files in all subdirectories. Setting hive_partitioning=true tells DuckDB to parse the key=value directory names and add those columns back to the result. Compare the count to SELECT count(*) FROM sales; or your original source query. They should match.

Inspect a Single File

To confirm the partition columns are absent from the physical files, read one Parquet file directly without the glob pattern.

DESCRIBE SELECT * FROM read_parquet('/data/exports/sales_partitioned/region=west/year=2024/part_0.parquet');

The schema should list only the non-partition columns. If region or year appear here, the PARTITION_BY clause did not apply as expected.

Recovery Options

Incomplete or Failed Writes

DuckDB's COPY TO is not an atomic cross-storage transaction. If the process is interrupted—by a crash, disk-full error, or network failure—partial files may remain in the target directory. Because overwriting a partitioned dataset can leave stale files from previous runs, the safest recovery is to remove the target directory entirely and rerun the COPY after verifying permissions and available disk space.

rm -rf /data/exports/sales_partitioned

Run this from your operating system shell, not inside DuckDB. Then execute the COPY statement again. If the failure was caused by insufficient disk space, free up space or write to a different volume before retrying.

Missing Partition Columns on Read

If downstream queries show fewer columns than expected, check that the reader is using Hive partitioning. In DuckDB, ensure hive_partitioning=true is passed to read_parquet. Other tools have their own settings; consult their documentation for enabling Hive-style partition discovery.

Limitations and Risks

• High-cardinality partition columns (for example, a unique user ID) can create thousands of tiny files and directories, which degrades read performance. Prefer low-to-moderate cardinality columns like date, region, or category.
• Because partition columns are not stored in the files, any tool that reads the Parquet files directly without Hive support will produce incomplete schemas.
• The glob pattern **/*.parquet requires DuckDB's multi-file reader support; paths must be quoted and wildcards interpreted by DuckDB, not the shell.
• Deleting the output directory is destructive. Confirm the path before running removal commands.

Practical Result Check

A successful export produces a nested directory tree where every leaf contains Parquet files, the total row count read back with hive_partitioning=true equals the source count, and a direct file read shows only the non-partition columns. If all three conditions hold, the dataset is ready for downstream Hive-style consumers.