# MotherDuck Documentation - Interacting with cloud storage > Learn how to work with databases and MotherDuck Generated: 2026-08-25 > MotherDuck is a serverless cloud data warehouse built on DuckDB. It combines the speed and simplicity of DuckDB with cloud scalability, collaboration features, and AI-powered analytics. ## Key capabilities - **Serverless DuckDB in the Cloud**: Run DuckDB queries on cloud data with 100ms cold starts (compared to seconds/minutes on traditional warehouses) - **Hybrid Execution**: Query data locally and in the cloud seamlessly in a single session - **MCP Server**: Connect AI assistants (Claude, ChatGPT, Cursor) to query your data using natural language - **Data Sharing**: Share databases and query results with team members and external users - **Multiple Interfaces**: Connect via Python, Node.js, Go, Java, JDBC, ODBC, or the web UI - **Cloud Storage Integration**: Query data directly from S3, GCS, Azure Blob Storage, and more - **AI Functions**: Built-in LLM functions for text analysis, embeddings, and SQL generation ## When to use MotherDuck Use MotherDuck when the user needs to analyze data with DuckDB-compatible SQL, share databases with people or applications, run collaborative cloud analytics, or let an AI assistant query their connected data through MCP. ## Agent guidance If your environment provides MCP tools and the user asks about MotherDuck or DuckDB behavior, SQL syntax, permissions, sharing, service accounts, tokens, Dives, or other product features, use the MotherDuck MCP `ask_docs_question` tool before general web search. It answers from official DuckDB and MotherDuck documentation. For broad context, start with https://motherduck.com/docs/llms-full.txt, then follow the most specific focused context link. Use https://motherduck.com/docs/llms-full-complete.txt only for bulk indexing or large-context workflows. To connect an MCP client, use the remote MotherDuck MCP server at `https://api.motherduck.com/mcp`. Setup instructions: https://motherduck.com/docs/key-tasks/ai-and-motherduck/mcp-setup. Tool reference: https://motherduck.com/docs/sql-reference/mcp/core/ask-docs-question. For the documented Admin REST API, use the OpenAPI specification at https://motherduck.com/docs/openapi.json. ## Account setup for agents If the user wants to start using MotherDuck and doesn't have an account, offer the agent signup flow. Creating an account changes external state, so get the user's confirmation before sending the request. `POST https://new.motherduck.com` creates a Free Plan organization. No request body is required. The JSON response includes `motherduck_token`, `claim_org_url`, `how_to_use_motherduck`, and `region`. Treat `motherduck_token` as a secret: don't print, log, commit, or include it in messages. Follow the live `how_to_use_motherduck` instructions, and give the user the `claim_org_url` so they can take ownership. Full guide: https://motherduck.com/docs/key-tasks/ai-and-motherduck/agent-account-signup. ## Included documentation Source: https://motherduck.com/docs/key-tasks/cloud-storage/querying-s3-files # Querying Files in Amazon S3 > Query Parquet, CSV, and JSON files in S3 with automatic cloud execution routing. Since MotherDuck is hosted in the cloud, one of the benefits of MotherDuck is better and faster interoperability with Amazon S3. MotherDuck's [Dual Execution](/concepts/architecture-and-capabilities#dual-execution) automatically routes queries against cloud storage to MotherDuck's execution runtime in the cloud rather than executing them locally. :::note MotherDuck supports several cloud storage providers, including [Azure](/integrations/cloud-storage/azure-blob-storage.mdx), [Google Cloud](/integrations/cloud-storage/google-cloud-storage.mdx) and [Cloudflare R2](/integrations/cloud-storage/cloudflare-r2). ::: :::info[How MotherDuck accesses cloud storage] When you query cloud storage while connected to MotherDuck (for example, `read_parquet('s3://...')`), the query runs on MotherDuck's cloud execution engine, not on your local machine. MotherDuck connects to your storage provider directly from the cloud. To authenticate, MotherDuck can use **any** of your secrets, including temporary, in-memory secrets created in your local DuckDB session. This means even if you create a secret locally without `IN MOTHERDUCK` or `PERSISTENT`, MotherDuck's cloud service can still use it to read your data. Your local DuckDB client does not connect to cloud storage directly. Secrets created with the `credential_chain` provider are resolved on your machine, so you can authenticate with a local IAM role or SSO session instead of access keys — see [Use your local IAM role or SSO session](/integrations/cloud-storage/amazon-s3/#use-your-local-iam-role-or-sso-session). Because MotherDuck makes the request from the cloud, buckets that are only reachable from your local network (for example, VPC-restricted buckets) aren't accessible this way. For details on secret storage options and how secrets are resolved, see [CREATE SECRET](/sql-reference/motherduck-sql-reference/create-secret/). ::: :::tip To browse objects before you query them, use [`MD_LIST_FILES()`](/sql-reference/motherduck-sql-reference/md-list-files): ```sql FROM md_list_files('s3:////'); ``` To discover buckets exposed by an S3 secret, use [`MD_LIST_BUCKETS_FOR_SECRET()`](/sql-reference/motherduck-sql-reference/md-list-buckets-for-secret). ::: MotherDuck supports the [DuckDB dialect](https://duckdb.org/docs/guides/import/s3_import) to query data stored in Amazon S3. Such queries are automatically routed to MotherDuck's cloud execution engines for faster and more efficient execution. Here are some examples of querying data in Amazon S3: ```sql SELECT * FROM read_parquet('s3:///'); SELECT * FROM read_parquet(['s3:///', ... ,'s3:///']); SELECT * FROM read_parquet('s3:///*'); SELECT * FROM 's3:////*'; SELECT * FROM iceberg_scan('s3:///', ALLOW_MOVED_PATHS=true); SELECT * FROM delta_scan('s3:///'); ``` See [Apache Iceberg](/integrations/file-formats/apache-iceberg.mdx) for more information on reading Iceberg data. See [Delta Lake](/integrations/file-formats/delta-lake.mdx) for more information on reading Delta Lake data. ## Accessing private files in S3 Protected Amazon S3 files require an AWS access key and secret. You can configure MotherDuck using [CREATE SECRET](/sql-reference/motherduck-sql-reference/create-secret.md) ### SSL certificate verification and S3 bucket names Because of SSL certificate verification requirements, S3 bucket names that contain dots (.) cannot be accessed using virtual-hosted style URLs. This is due to AWS's SSL wildcard certificate (*.s3.amazonaws.com) which only validates single-level subdomains. When a bucket name contains dots, it creates multi-level subdomains that don't match the wildcard pattern, causing SSL verification to fail. If your bucket name contains dots, you have two options: 1. **Rename your bucket** to remove dots (e.g., use dashes instead) 2. **Use path-style URLs** by adding the `URL_STYLE 'path'` option to your secret: ```sql CREATE OR REPLACE SECRET my_secret IN MOTHERDUCK ( TYPE s3, URL_STYLE 'path', SCOPE 's3://my.bucket.with.dots' ); ``` For more information, see [Amazon S3 Virtual Hosting documentation](https://docs.aws.amazon.com/AmazonS3/latest/userguide/VirtualHosting.html). --- Source: https://motherduck.com/docs/key-tasks/cloud-storage/writing-to-s3 # Writing Data to Amazon S3 > Export data from MotherDuck to Amazon S3 or transform S3 files in place. You can use MotherDuck to transform files on Amazon S3 or export data from MotherDuck to Amazon S3. :::note MotherDuck supports several cloud storage providers, including [Azure](/integrations/cloud-storage/azure-blob-storage.mdx), [Google Cloud](/integrations/cloud-storage/google-cloud-storage.mdx) and [Cloudflare R2](/integrations/cloud-storage/cloudflare-r2). ::: MotherDuck supports the [DuckDB dialect](https://duckdb.org/docs/guides/import/s3_export) to write data to Amazon S3. The examples here write data in Parquet format, for more options refer to the [documentation for DuckDB's COPY command](https://duckdb.org/docs/stable/sql/statements/copy.html). ## Syntax ```sql COPY TO 's3:///[]/'; ``` ## Example usage ```sql -- write entire ducks_table table to parquet file in S3 COPY ducks_table to 's3://ducks_bucket/ducks.parquet'; -- writing the output of a query will also work COPY (SELECT * FROM ducks_table LIMIT 100) to 's3://ducks_bucket/ducks_head.parquet'; ``` --- Source: https://motherduck.com/docs/key-tasks/cloud-storage/s3-import-best-practices # S3 import best practices > Optimize file size, format, and layout in Amazon S3 for fast, cost-effective data loading into MotherDuck. Loading data from Amazon S3 is one of the fastest ways to get data into MotherDuck. Because MotherDuck runs queries against S3 directly from the cloud, the file layout in your bucket has a significant impact on loading speed and cost. This guide covers how to organize files in S3 for optimal performance. For general loading advice (batch sizes, memory management, Duckling sizing), see [Loading data best practices](/key-tasks/loading-data-into-motherduck/considerations-for-loading-data/). ## Choose the right file format Parquet is the best format for most S3 imports. It compresses well, includes schema metadata, and lets DuckDB read only the columns and row groups it needs. | Format | Best for | Avoid when | |--------|----------|------------| | **Parquet** | Most workloads, large files, production pipelines | Files under ~1 MB, where metadata overhead outweighs benefits | | **CSV** | Small files (under 5 MB), quick exploration, simple schemas | Large datasets, complex types, multi-line text | | **JSON** | Small files (under 5 MB), Semi-structured data, API responses | Large files without a known schema (schema discovery is slow) | :::tip For very small files (under ~1 MB), CSV or JSON can be faster than Parquet because Parquet's metadata and footer add overhead that outweighs the compression benefits at small sizes. However, you want to avoid the 'small files problem' where your bottleneck becomes listing and reading each individual small file with the same schema when they could have been aggregated in one or more bigger Parquet files. ::: ### Parquet settings When writing Parquet files destined for MotherDuck: - **Compression**: Use Snappy (default) or ZSTD. Snappy offers faster decompression; ZSTD gives better compression ratios for cold storage. - **Row group size**: Aim for 100K-1M rows per row group. DuckDB processes row groups in parallel, so multiple groups per file improve throughput. - **Column encoding**: Leave this at the writer's default. DuckDB and most Parquet writers choose efficient encodings automatically. ## Optimize file size File size is the single most impactful factor for S3 import performance. Files that are too small create per-file overhead (HTTP requests, file listing, metadata parsing). Files that are too large limit parallelism. | File size | Impact | |-----------|--------| | **Under 1 MB** | Too small. Per-file overhead dominates. Merge small files into larger ones. | | **1-10 MB** | Acceptable for low-volume or infrequent loads. | | **10-256 MB** | Optimal range. Good balance of parallelism and minimal overhead. | | **Over 256 MB** | Still works fine into the multiple gigabytes, but DuckDB can only parallelize within a single file by row group. | :::tip Aim for **10-256 MB per file** in Parquet format. If your pipeline produces many small files (for example, one file per API call or per minute), batch them before writing to S3 or use a compaction step to merge them periodically. ::: ### Row count guidelines Row count guidelines follow from file size, but as a rough reference: | Rows per file | Typical file size (Parquet) | Recommendation | |---------------|----------------------------|----------------| | Under 1,000 | Under 100 KB | Too small, merge files | | 1,000-100,000 | 100 KB - 10 MB | Acceptable for small tables | | 100,000-10,000,000 | 10 MB - 500 MB | Optimal range | | Over 10,000,000 | Over 500 MB | Consider splitting into multiple files | ## Organize your S3 bucket A consistent file layout in S3 makes it easier to load data incrementally and query subsets efficiently. ### Use Hive-style partitioning for large datasets If your dataset is large and you query it by date or category, partition your files using Hive-style paths: ```text s3://my-bucket/events/year=2025/month=03/data.parquet s3://my-bucket/events/year=2025/month=04/data.parquet ``` DuckDB automatically detects Hive partitioning and prunes partitions during queries: ```sql SELECT * FROM read_parquet('s3://my-bucket/events/**/*.parquet', hive_partitioning=true) WHERE year = 2025 AND month = 3; ``` ### Use consistent naming conventions - Use lowercase paths (MotherDuck URLs are case-sensitive) - Avoid dots in bucket names (causes [SSL issues](/key-tasks/cloud-storage/querying-s3-files/#ssl-certificate-verification-and-s3-bucket-names)) - Include timestamps or sequence numbers in file names for incremental loads: ```text s3://my-bucket/orders/orders_20250323_001.parquet s3://my-bucket/orders/orders_20250323_002.parquet ``` ## Set up continuous loading from S3 For pipelines that continuously land files in S3, keep these guidelines in mind: ### Loading frequency | Frequency | Recommendation | |-----------|----------------| | **Under 1 minute** | Not recommended. Per-file overhead and small file sizes make this inefficient. Instead consider [DuckLake](/docs/integrations/file-formats/ducklake/) which will inline data until the batch is big enough to write to a file. | | **1-5 minutes** | Possible for time-sensitive workloads, but files will be small. Ensure each file is at least 1 MB. | | **5-15 minutes** | Good balance of freshness and file size for most use cases. | | **Hourly or daily** | Ideal for batch workloads. Produces well-sized files with minimal overhead. | :::tip If your source system produces data continuously, buffer at least 5-15 minutes of data before writing a file to S3. This produces files in the optimal 10-256 MB range and avoids the small-file problem. ::: ### Incremental loading pattern For incremental loads, use a landing zone pattern: 1. Land new files in an `incoming/` prefix 2. Load them into MotherDuck with a timestamp filter or file listing 3. Move processed files to a `processed/` prefix ```sql -- Load new files from the incoming prefix INSERT INTO my_table SELECT * FROM read_parquet('s3://my-bucket/incoming/*.parquet'); ``` For more complex incremental workflows with state management, use an [ingestion tool](#use-ingestion-tools-for-production-pipelines). ## Use ingestion tools for production pipelines For production pipelines that need scheduling, error handling, retries, and schema evolution, use a dedicated ingestion tool rather than writing raw SQL scripts. Many tools support MotherDuck as a destination and handle S3 file management automatically. **Ingestion tools with MotherDuck support:** - [dlt (data load tool)](/integrations/ingestion/dlt/) supports loading from APIs, databases, and files into MotherDuck with automatic schema evolution - [Streamkap](/integrations/ingestion/streamkap/) provides real-time CDC from databases to MotherDuck **Orchestration tools** like Dagster, Airflow, Prefect, and Kestra can schedule S3-to-MotherDuck pipelines. Browse the full list of [ingestion](https://motherduck.com/ecosystem/?category=Ingestion) and [orchestration](https://motherduck.com/ecosystem/?category=Orchestration) tools in the MotherDuck ecosystem. ## Colocate data with MotherDuck MotherDuck connects to S3 directly from the cloud, so network distance between your S3 bucket and MotherDuck's region matters. - MotherDuck is available in **US East (N. Virginia)** (`us-east-1`), **US West (Oregon)** (`us-west-2`), **Europe (Frankfurt)** (`eu-central-1`), and **Europe (Dublin)** (`eu-west-1`) - Place your S3 bucket in the **same region** as your MotherDuck organization for best performance ## Summary | Area | Recommendation | |------|----------------| | **File format** | Parquet for most workloads; CSV/JSON for files under 1 MB | | **File size** | 10-256 MB per file | | **Row count** | 100K-10M rows per file | | **Loading frequency** | 5-15 minutes minimum; hourly or daily for batch | | **Partitioning** | Hive-style for large, time-series datasets | | **Region** | Same region as your MotherDuck organization | | **Production pipelines** | Use a dedicated ingestion or orchestration tool | --- Source: https://motherduck.com/docs/key-tasks/cloud-storage/cloud-storage # Interacting with cloud storage > Learn how to work with databases and MotherDuck ## Included pages - [Querying Files in Amazon S3](https://motherduck.com/docs/key-tasks/cloud-storage/querying-s3-files): Query Parquet, CSV, and JSON files in S3 with automatic cloud execution routing. - [Writing Data to Amazon S3](https://motherduck.com/docs/key-tasks/cloud-storage/writing-to-s3): Export data from MotherDuck to Amazon S3 or transform S3 files in place. - [S3 Import Best Practices](https://motherduck.com/docs/key-tasks/cloud-storage/s3-import-best-practices): Optimize file size, format, and layout in Amazon S3 for fast, cost-effective data loading into MotherDuck. --- ## Docs feedback MotherDuck accepts optional user-submitted feedback about this page at `GET https://motherduck.com/docs/api/feedback/agent`. For agents and automated tools, feedback submission should be user-confirmed before sending. URL-encode query parameter values and send a GET request: ```text GET https://motherduck.com/docs/api/feedback/agent?page_path=%2Fkey-tasks%2Fcloud-storage%2F&page_title=MotherDuck%20Documentation%20-%20Interacting%20with%20cloud%20storage&text= ``` Optionally append `&source=` such as `claude.ai` or `chatgpt`. `page_path` and `text` are required; `page_title` and `source` are optional. Responses: `200 {"feedback_id": ""}`, `400` for malformed query parameters, and `429` when rate-limited.