Querying Parquet on S3 with DuckDB: secrets, speed and the settings that matter

DuckDB reads Parquet on S3 in one line, and that part is not the problem. The problem shows up a month later, when a query that took two seconds on your laptop takes forty against the bucket and nobody can say why.

The answer is almost never bandwidth. It is the number of requests. Object storage charges you a round trip for every file you open and every column chunk you fetch. A layout that costs nothing on local disk can mean tens of thousands of round trips on S3. This post covers credentials first, because they are what people search for, and then the layout and settings that decide speed.

One honest note. My own production pipeline reads Parquet from an NFS share on a NAS, not from S3. The credential and request-level detail here comes from DuckDB's documentation and from my book. The many-files failure near the end is mine, from that NFS pipeline, and it would have hit the same way on S3.

Can DuckDB read from S3?

Yes. The httpfs extension handles S3, and DuckDB autoloads it the first time you touch an s3:// path. You don't need to install or load anything by hand, though doing so in production scripts makes the dependency explicit.

SELECT count(*)
FROM read_parquet('s3://analytics/events/dt=2026-09-*/*.parquet');

For Parquet, DuckDB does not download whole files. Its docs describe partial reading: it reads the file's metadata, then uses HTTP range requests to fetch only the parts the query needs. A count(*) can be answered from metadata alone. That is why Parquet on S3 works at all, and also why the number of files matters so much: every file needs its own metadata read before anything else happens.

Setting S3 credentials: CREATE SECRET

DuckDB manages credentials as secrets. The S3 docs show two providers.

credential_chain asks the AWS SDK for credentials, the same way the AWS CLI would. That covers environment variables, profiles, SSO, assumed roles and instance or container roles. Use it on servers, so no key ever sits in your code:

CREATE OR REPLACE SECRET aws (
    TYPE s3,
    PROVIDER credential_chain
);

config takes the values directly. Use it when the keys come from somewhere else, such as a secrets manager your app already reads. Pass them in from code rather than typing them into SQL:

import os
import duckdb

con = duckdb.connect()
con.execute(
    "CREATE OR REPLACE SECRET aws "
    "(TYPE s3, PROVIDER config, KEY_ID ?, SECRET ?, REGION 'eu-west-2')",
    [os.environ["AWS_ACCESS_KEY_ID"], os.environ["AWS_SECRET_ACCESS_KEY"]],
)

Three details that save time:

• Scope secrets to buckets. Add SCOPE 's3://raw-bucket' and the secret only applies there. With several matching secrets, the secrets manager picks the longest matching prefix. FROM which_secret('s3://raw-bucket/x.parquet', 's3') tells you which one a path will use, and FROM duckdb_secrets() lists them with sensitive values redacted.
• Persistent secrets are plain files. Secrets live in memory for the session by default. CREATE PERSISTENT SECRET writes them to ~/.duckdb/stored_secrets, and the docs say they are "stored in unencrypted binary format on the disk." On a shared server, use credential_chain instead.
• S3-compatible stores need three more fields. For MinIO and similar, set ENDPOINT, URL_STYLE 'path' and, for a local HTTP endpoint, USE_SSL false.

CREATE OR REPLACE SECRET minio (
    TYPE s3,
    PROVIDER config,
    KEY_ID '<from your secret store>',
    SECRET '<from your secret store>',
    ENDPOINT 'minio:9000',
    URL_STYLE 'path',
    USE_SSL false,
    SCOPE 's3://analytics'
);

If you have older code that uses SET s3_access_key_id = ..., it still runs. Chapter 9 of Local-First Analytics uses that style for its MinIO example. DuckDB's docs now call it a legacy method and say to avoid it, because settings are easy to print by accident. Move to secrets when you next touch that code.

DuckDB Parquet S3 performance: why it gets slow

Count the requests a query makes. Before DuckDB reads a single row, it has to:

1. List the files. A glob such as dt=2026-09-*/*.parquet becomes ListObjectsV2 calls, per the S3 docs.
2. Read each file's footer. One or more range requests per file.
3. Fetch the column chunks it needs. At least one range request per column per row group it can't skip.

On a local disk each of those is microseconds. On S3 each is a network round trip. So the layout decides speed far more than any setting.

Fewer, larger files. Ten thousand 1 MB files cost ten thousand footer reads for 10 GB of data. A hundred 100 MB files cost a hundred. The book's Chapter 4 targets 100–500 MB per file and marks files under 10 MB as too small. My notes on running DuckDB on your own infrastructure say 100 MB–1 GB, and both ranges agree on the point: compact small files. They usually come from a streaming writer flushing every few seconds.

Hive-style folders, so filters skip files. DuckDB detects key=value folder names by default. A filter on a partition column skips whole files, so DuckDB never opens them. A folder named 2026-09-14/ with no key gives DuckDB nothing to prune on. The book makes the same point: the extra dt= characters are the cheapest speed-up you will get.

Sorted data, so statistics skip row groups. Inside a file, DuckDB skips any row group whose min and max for the filtered column can't match. That only helps if the data was sorted on that column when written. Unsorted, every row group spans the whole range and nothing is skipped. The book's Chapter 5 argues that a well-sorted file can match a partitioned layout on common filters.

Select columns, not *. Each extra column is more range requests. On S3 that costs time, not just memory.

Then the settings:

-- Most threads wait on the network, so use more of them than cores.
SET threads = 32;   -- on an 8-core machine

DuckDB's tuning guide suggests 2–5 times the CPU cores for remote reads, so that more HTTP requests are in flight at once. Since 1.3, an external file cache is on by default. It keeps fetched remote data in memory within memory_limit, so repeat queries in the same process are faster. In the release post's example, a remote query went from 1.456 s to 0.360 s on the second run. It helps a long-running service. It does nothing for a batch job that reads each file once and exits.

What row group size to use

DuckDB writes 122,880 rows per row group by default. Its Parquet tips give the rule that matters for reading: have at least as many row groups per file as threads reading it, because DuckDB reads row groups in parallel, even within one file.

Larger row groups compress better, since compression runs per row group, but they need more memory to write. The Parquet project recommends 512 MB–1 GB row groups. That figure was written with HDFS blocks in mind, and the book's references note that 128–512 MB is the practical range outside HDFS. For files on S3, a sound default is files of a few hundred MB, each holding several row groups, sorted by the main filter column:

COPY (
    SELECT * FROM read_parquet('s3://raw/events/**/*.parquet')
    ORDER BY event_date, customer_id
)
TO 's3://analytics/events'
(FORMAT PARQUET, PARTITION_BY (event_date), ROW_GROUP_SIZE 500000, COMPRESSION zstd);

Check what you actually wrote, rather than trusting the writer:

SELECT file_name,
       count(DISTINCT row_group_id)     AS row_groups,
       max(row_group_num_rows)          AS rows_per_group,
       sum(total_compressed_size) / 1e6 AS mb
FROM parquet_metadata('s3://analytics/events/**/*.parquet')
GROUP BY file_name
ORDER BY mb;

A long tail of tiny files at the top of that result is your speed problem.

Reading many files: the union_by_name trap

read_parquet([...], union_by_name = true) lines up columns by name across files whose schemas differ. It is useful, and its failure mode is quiet. This one is from my own pipeline.

Four of my Dagster assets consolidate raw Parquet files with DuckDB and union_by_name. One reads market-index files, one per trading day. On market holidays, an upstream job wrote a small "marker" file instead: no rows, and every column typed as VARCHAR. After I upgraded to DuckDB 1.5.1, a query with window functions over that read began throwing an InternalException. It was reading 952 files, 148 of them empty markers. The asset loaded 0 rows for all 153 indexes, and that went unnoticed for about three weeks. The same marker files had broken a DuckDB read once before.

The fix was not clever. I read the files one by one with Polars, skip anything under 5,000 bytes (the markers are about 135 bytes to 1.7 KB), and concatenate the rest. Three lessons apply to any bucket:

• Schema drift across files is data, not noise. union_by_name merges the types it finds, so one all-VARCHAR file can change how every file is read.
• Never write empty marker files into a data prefix. Put "no data today" in a manifest or a separate prefix.
• Check row counts after a run, not just success. My run succeeded and loaded nothing. A row-count check would have caught it on the first night.

Does DuckDB support Iceberg?

Yes, through the iceberg extension, with two levels. Reading a table directly from storage with iceberg_scan needs no catalog and is read-only. Attaching an Iceberg REST catalog adds writes: CREATE TABLE, INSERT, UPDATE, DELETE and MERGE INTO.

-- Read-only, no catalog
SELECT count(*) FROM iceberg_scan('s3://lake/warehouse/orders');

Know the limits before you build on it. UPDATE and DELETE write merge-on-read positional deletes only; copy-on-write is not supported. Direct reads need a version hint or an explicit version unless you enable version guessing. I haven't run Iceberg tables in production. This feature set has changed quickly, so check the docs for the DuckDB version you pin.

A short checklist for Parquet on S3

1. Credentials come from CREATE SECRET, preferably credential_chain, scoped to the bucket. No keys in SQL, no persistent secrets on shared machines.
2. Files are roughly 100 MB–1 GB, not thousands of small ones. parquet_metadata() proves it.
3. Folders are key=value on the column you filter by most.
4. Data is sorted on that column when written, so row-group statistics can skip.
5. Queries name their columns.
6. threads is set above the core count for remote-heavy work, and memory_limit is set explicitly.
7. Every consolidation job checks row counts, not just exit status.

So: is S3 fast enough for DuckDB?

For batch jobs and analysis over a well-laid-out bucket, yes. The engine is rarely the bottleneck. Round trips are. Fix the file count, the folder names and the sort order, and most "DuckDB is slow on S3" problems go away without a single setting change.

If your queries still wait on the network after that, the next step is a local copy of the hot partitions, not a bigger engine.

Local-First Analytics covers Parquet layout, partitioning and the MinIO setup for S3-style storage, with runnable code. Chapter 1 is free to read, the rest is on Amazon. Related: local-first analytics in practice, running DuckDB on your own infrastructure, Parquet vs CSV: what you actually save, and is DuckDB safe for production?

If your bucket is slow and you want someone to find out why, a Data Platform Audit is a week and a written roadmap you keep. The scoping call below is free.