DuckDB + Amazon S3

This guide covers reading and writing files in Amazon S3 from DuckDB: the extensions, the secret, the SQL, and the errors you are most likely to hit, each under its exact message. Everything in the first part is plain open-source DuckDB (checked on 1.5). The last section shows the same thing on Thunderduck, where the setup is done for you.

1. Load the extensions

INSTALL httpfs;  LOAD httpfs;
INSTALL aws;     LOAD aws;

httpfs reads and writes s3:// paths. aws provides PROVIDER credential_chain in the next step. DuckDB autoloads both on first use, but that is a download in the middle of your first query; install them up front, or bake them into your image.

2. Create a secret

A secret tells DuckDB how to sign requests to S3. The one you want almost always is credential_chain: it finds credentials the same way the AWS CLI and SDKs do (environment variables, ~/.aws/config, SSO, an instance or container role), so no keys appear in your SQL.

CREATE SECRET s3_creds (
    TYPE s3,
    PROVIDER credential_chain,
    REGION 'us-east-1'
);

Useful options:

For a public bucket, a secret with only a region reads anonymously:

CREATE SECRET public_data (TYPE s3, PROVIDER config, REGION 'us-west-2');

Static keys work too (PROVIDER config, KEY_ID '…', SECRET '…'), but then they live in your SQL or your scripts. Prefer the chain.

3. Read files

read_parquet, read_csv and read_json take s3:// paths directly, with wildcards:

-- one file
SELECT * FROM read_parquet('s3://my-lake/events/2026-10-01.parquet');
 
-- every file under a prefix
SELECT event_type, count(*) AS events
FROM   read_parquet('s3://my-lake/events/**/*.parquet')
GROUP  BY 1
ORDER  BY 2 DESC;
 
-- key=value folders become columns, and filters on them skip whole folders
SELECT count(*)
FROM   read_parquet('s3://my-lake/events/*/*.parquet', hive_partitioning = true)
WHERE  day = '2026-10-01';

A real public example you can run as is (Overture Maps, us-west-2, with the anonymous secret above):

SELECT country, count(*) AS areas
FROM   read_parquet('s3://overturemaps-us-west-2/release/2026-10-21.0/theme=divisions/type=division_area/*.parquet')
GROUP  BY 1
ORDER  BY 2 DESC
LIMIT  3;

To see which files a pattern matches before reading them:

SELECT file FROM glob('s3://my-lake/events/**/*.parquet');

4. Write files with COPY TO

-- one Parquet file
COPY (SELECT * FROM orders WHERE order_date >= DATE '2026-10-01')
TO 's3://my-lake/exports/orders_october.parquet' (FORMAT parquet);
 
-- one folder per day: s3://my-lake/orders/day=2026-10-01/data_0.parquet
COPY (SELECT *, order_date AS day FROM orders)
TO 's3://my-lake/orders' (FORMAT parquet, PARTITION_BY (day), OVERWRITE_OR_IGNORE);

The partition column is not written inside the files (it lives in the folder name), so PARTITION_BY on every column of a query fails with "No column to write as all columns are specified as partition columns". Writing needs s3:PutObject on the target prefix; reading needs s3:GetObject, and wildcards also need s3:ListBucket.

Errors and fixes

HTTP 403 Forbidden: No credentials are provided

HTTP Error: HTTP GET error reading 'https://my-bucket.s3.us-east-1.amazonaws.com/x.parquet'
in region 'us-east-1' (HTTP 403 Forbidden)
AccessDenied: Access Denied
Authentication Failure - this is usually caused by invalid or missing credentials.
* No credentials are provided.

DuckDB sent the request unsigned. Either there is no S3 secret at all, or the only matching secret is a region-only one meant for public data. Create a credential_chain secret (step 2), and check which secret a path will use:

FROM which_secret('s3://my-bucket/x.parquet', 's3');

HTTP 403 Forbidden: Credentials are provided, but they did not work

... (HTTP 403 Forbidden)
AccessDenied: Access Denied
* Credentials are provided, but they did not work.

DuckDB signed the request, and AWS refused it. In order of likelihood:

1. The role or user lacks s3:GetObject on that object (or s3:ListBucket on the bucket, for a wildcard).
2. The bucket policy denies you, for example it only allows a VPC endpoint.
3. The objects are encrypted with a KMS key you can't use (kms:Decrypt).
4. The chain picked up different credentials than you expected. Pin it with CHAIN and PROFILE, then check aws sts get-caller-identity with the same profile.

Secret Validation Failure

Invalid Configuration Error: Secret Validation Failure: during `generate` using the following:
Profile: 'analytics'
Credential Chain: 'sso'

CREATE SECRET … PROVIDER credential_chain checks that the chain finds credentials when you create it, and fails here if it can't. Usually the SSO session expired (aws sso login --profile analytics), the profile name is wrong, or the environment variables aren't set in the process running DuckDB (a notebook kernel or a cron job sees a different environment than your shell).

Expired temporary credentials in a long session

Credentials from SSO, an assumed role or an instance profile expire, often after an hour. A secret created earlier keeps the old ones, so a long-running notebook or service starts failing with 403s. Add REFRESH auto to the secret so DuckDB fetches fresh credentials when they expire, or recreate the secret.

Several buckets, several secrets

With more than one S3 secret, DuckDB picks the one whose SCOPE is the longest match for the path. Give each bucket its own:

CREATE SECRET lake (TYPE s3, PROVIDER credential_chain, REGION 'us-east-1',
                    SCOPE 's3://my-lake');
CREATE SECRET partner (TYPE s3, PROVIDER credential_chain, PROFILE 'partner',
                       REGION 'eu-west-1', SCOPE 's3://partner-drop');
 
FROM which_secret('s3://partner-drop/2026/10/file.csv', 's3');  -- partner

The same thing on Thunderduck

Thunderduck runs DuckDB with this setup already done. You add the bucket once as an object-storage catalog (S3, GCS, Azure or Cloudflare R2), and every query after that:

• has no extensions, secrets or keys to manage. Credentials are tested when you save the catalog and kept in a secrets manager, never in SQL.
• uses catalog paths instead of bucket URLs. The catalog name stands in for the bucket, so access is checked per catalog and per role.
• runs in its own DuckDB worker, sized from the query plan, that can only reach your own catalogs.

-- catalog "raw_files" points at s3://my-lake
SELECT event_type, count(*) AS events
FROM   read_parquet('raw_files/events/**/*.parquet')
GROUP  BY 1;
 
COPY (SELECT * FROM sales_pg.public.orders WHERE amount > 100)
TO 'raw_files/exports/orders_{today}.parquet' (FORMAT parquet);

The files can also be joined with your databases and Iceberg tables in the same query. See Reading files and Exporting files for the full syntax.