Amazon S3

Amazon S3 is a Data Sources/Sinks service for storing and retrieving data.

Configure S3 credentials

You can safely store your Amazon S3 credentials in MotherDuck for convenience by creating a SECRET object using the CREATE SECRET command. Secrets are scoped to your user account and are not shared with other users in your organization.

Create a SECRET object

SQL

-- to configure a secret manually:
CREATE SECRET IN MOTHERDUCK (
    TYPE S3,
    KEY_ID 'access_key',
    SECRET 'secret_key',
    REGION 'us-east-1',
    SCOPE 'my-bucket-path'
)

note

When creating a secret using the CONFIG (default) provider, be aware that the credential might be temporary. If so, a SESSION_TOKEN field also needs to be set for the secret to work correctly.

-- to store a secret using your local AWS credentials (from `aws configure` or SSO):
-- if you use AWS SSO, run `aws sso login --profile <your_sso_profile>` first
CREATE SECRET aws_secret IN MOTHERDUCK (
      TYPE S3,
      PROVIDER credential_chain,
      -- optional: add CHAIN and PROFILE for SSO credentials
      CHAIN 'sso',
      PROFILE '<your_sso_profile>'
  )

Secret validation

Starting with DuckDB v1.4.0, credentials are validated at secret creation time. If your credentials are not resolvable locally (for example, expired SSO tokens or missing ~/.aws/credentials), the CREATE SECRET command will fail with a Secret Validation Failure error. The recommended fix is to use the correct CHAIN and PROFILE for your credential type (see the SSO example above). If you need to bypass local validation, you can add VALIDATION 'none', but keep in mind that this skips the local check that confirms your credentials are valid before storing them in MotherDuck.

-- test the s3 credentials
SELECT count(*) FROM 's3://<bucket>/<file>'

-- browse objects in a bucket or prefix
FROM md_list_files('s3://<bucket>/')

Python

import duckdb

con = duckdb.connect('md:')
con.sql("CREATE SECRET IN MOTHERDUCK (TYPE S3, KEY_ID 'access_key', SECRET 'secret_key', REGION 'your_bucket_region')")

# testing that our s3 credentials work
con.sql("SELECT count(*) FROM 's3://<your_bucket>/<your_file>'").show()
# 42

UI

Click on your profile to access the Settings panel and click on Secrets menu.

Then click on Add secret in the secrets section.

You will then be prompted to enter your Amazon S3 credentials.

You can update your secret by executing CREATE OR REPLACE SECRET command to overwrite your secret.

Delete a SECRET object

SQL

You can use the same method above, using the DROP SECRET command.

DROP SECRET <secret_name>

UI

Click on your profile and access the Settings menu. Click on the bin icon to delete your current secrets.

Amazon S3 credentials as temporary secrets

MotherDuck supports DuckDB syntax for providing S3 credentials.

CREATE SECRET (
    TYPE S3,
    KEY_ID 's3_access_key',
    SECRET 's3_secret_key',
    REGION 'us-east-1'
)

note

Local/In-memory secrets are not persisted across sessions.

Use your local IAM role or SSO session

If you authenticate to AWS with an IAM role, SSO, or instance profile instead of long-lived access keys, use a local DuckDB session with the credential_chain provider. DuckDB uses your local AWS setup to get credentials, and MotherDuck's cloud execution engine uses those credentials to read from S3. Grant your AWS identity permission to list the bucket, get its location, and read its objects. Buckets encrypted with AWS KMS also require kms:Decrypt permission on the key. MotherDuck doesn't need standing access. For an example policy, see the AWS S3 secrets troubleshooting guide.

This pattern is a good fit for one-off or ad hoc loads when you already have a local AWS identity and don't want to store long-lived access keys in MotherDuck. The credentials usually expire with your AWS SSO or STS session.

If your credentials are in a named AWS profile, start DuckDB with that profile after you sign in to AWS:

AWS_PROFILE=<your_aws_profile> duckdb

-- Connect to MotherDuck
ATTACH 'md:'

-- Use your local AWS identity (IAM role, SSO, or instance profile)
CREATE SECRET my_s3 (
    TYPE S3,
    PROVIDER credential_chain,
    REGION 'us-east-1'
)

-- Read from S3 and write into a MotherDuck table
CREATE TABLE my_db.main.events AS
SELECT * FROM read_parquet('s3://<bucket>/<prefix>/*.parquet')

-- Verify the data loaded
SELECT count(*) FROM my_db.main.events

The my_s3 secret in this example lives only for the DuckDB session. Run the CREATE SECRET statement again after your AWS credentials expire or when you start a new DuckDB session. To check which secret a path uses, run SELECT * FROM which_secret('s3://<bucket>/<file>', 's3').

info

MotherDuck's cloud execution engine makes the request to S3, not your local machine. If your bucket is only reachable from your local network (for example, restricted to a VPC without a public endpoint), the read fails. In that case, set MD_RUN = LOCAL on the initial S3 read to force it to run in your local DuckDB session. Load the result into a local table, then insert it into MotherDuck:

CREATE TEMP TABLE local_events AS
SELECT *
FROM read_parquet(
    's3://<bucket>/<prefix>/*.parquet',
    MD_RUN = LOCAL
)

CREATE TABLE my_db.main.events AS
SELECT * FROM local_events

info

Even temporary, in-memory secrets are available to MotherDuck's cloud execution engine when you connect your local DuckDB instance to MotherDuck. When you query S3, the query runs on MotherDuck's servers, not your local machine, and MotherDuck uses the best-matching secret to authenticate, whether it is stored locally or in MotherDuck. For more details, see CREATE SECRET.

Troubleshooting

For detailed troubleshooting steps, see the AWS S3 secrets troubleshooting guide.

Browse buckets and files

To inspect storage from SQL before querying specific files:

FROM md_list_buckets_for_secret('__default_s3')

FROM md_list_files('s3://<bucket>/')
FROM md_list_files('s3://<bucket>/<prefix>/')

See MD_LIST_BUCKETS_FOR_SECRET() and MD_LIST_FILES() for details.