# MotherDuck Documentation - Loading data into MotherDuck > Learn how to load data into MotherDuck from various sources 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/loading-data-into-motherduck/considerations-for-loading-data # Loading data best practices > Understanding trade-offs and performance implications when loading data into MotherDuck When loading data into MotherDuck, understanding the trade-offs between different approaches helps you make informed decisions that optimize for your specific use case. This guide explains the key considerations that impact performance, cost, and reliability. ## File format considerations The choice of file format significantly impacts loading performance: | | Parquet (recommended) | CSV | JSON | |---|---|---|---| | **Compression** | 5-10x better than CSV | Minimal | Moderate | | **Performance** | 5-10x more throughput | Slower, especially for large files | Slower than Parquet due to parsing overhead | | **Schema** | Self-describing with embedded metadata | Requires type inference or specification | Flexible but requires careful type handling. DuckDB scans data to discover the schema before running the query, which can add significant time for large or deeply nested files (see [tips for loading JSON](/key-tasks/data-warehousing/replication/flat-files/#json)) | | **Best for** | Production data loading, large datasets | Simple data exploration, small datasets | Semi-structured data, API responses | ## Avoid single-row INSERTs A common mistake is inserting data one row at a time using repeated `INSERT INTO ... VALUES (...)` statements. This pattern is significantly slower than bulk loading because each individual INSERT statement incurs network round-trip overhead to MotherDuck and prevents DuckDB from parallelizing the work. :::tip Do not use single-row `INSERT INTO ... VALUES` statements to load data into MotherDuck. Instead, use bulk approaches like `INSERT INTO ... SELECT` from files, `COPY`, or load data from DataFrames. See [Loading data into MotherDuck](/key-tasks/loading-data-into-motherduck/loading-data-into-motherduck.mdx) for recommended methods. ::: If you're working with a client library (Python, Node.js, Java), avoid looping over rows and calling `execute("INSERT INTO ...")` for each one. Methods like `executemany` also send individual INSERT statements under the hood and are equally slow. Instead, write your data to a file (Parquet or CSV) and load it with `COPY` or `INSERT INTO ... SELECT`, or use a DataFrame-based approach where available. ## Performance optimization strategies ### Batch size DuckDB internally processes data in row groups of ~122,000 rows and parallelizes work across multiple row groups. This means batch size affects both memory usage and throughput: | Batch size | What happens | |---|---| | **1-100 rows** (single-row INSERTs) | Each statement has network and transaction overhead. Very slow — avoid this pattern entirely. | | **100K rows** | Fits in roughly one row group. Already a bulk operation and orders of magnitude faster than row-by-row. Good default chunk size when streaming from Python to manage memory. | | **1M+ rows** | Spans multiple row groups, so DuckDB parallelizes across threads. Best throughput for large loads. | :::tip When streaming data from a client library, load in chunks of at least **100K rows** to keep memory manageable while staying well above row-by-row overhead. For maximum throughput on large datasets, aim for **1M+ rows** per load operation to fully leverage DuckDB's parallelization. ::: Keep individual transactions under roughly one minute. If you have tens of millions of rows, break them into multiple loads rather than one very large transaction. ### Memory management Effective memory management is crucial for large data loads: **Data Type Optimization** - Use explicit schemas to avoid type inference overhead — this is especially important for JSON, where schema discovery can add minutes for large or deeply nested files - Choose appropriate data types (for example, TIMESTAMP for dates) - Avoid unnecessary type conversions **Sorting Strategy** - Sort data by frequently queried columns during loading - To re-sort existing tables, use `CREATE OR REPLACE` with the preferred sorting method - Improves query performance through better data locality - Consider the trade-off between loading speed and query performance ### Network and location considerations **Data Location** - MotherDuck is available on AWS in six regions across the US, Europe, and Asia Pacific (see [Cloud regions](/about-motherduck/cloud-regions/)) - For optimal performance, consider locating source data in the same region as your MotherDuck Organization - Consider network latency when loading from remote sources **Cloud Storage Integration** - Direct integration with S3, R2, GCS, Azure Blob Storage - Use [cloud storage](/integrations/cloud-storage/) to leverage network speeds for better performance - Reduces local storage requirements - Consider setting [force_download=true](https://duckdb.org/docs/stable/configuration/overview) when querying files stored in remote storage to accelerate response times. This could be useful in scenarios where it makes sense to download the full file upfront instead of making many small requests. ## Duckling sizing **Duckling Selection** For data sets under 100 GB in size, use Jumbo Ducklings to load the data. For larger data sizes, use [Mega or Giga](/about-motherduck/billing/duckling-sizes/). ## Summary The key to successful data loading in MotherDuck is understanding the trade-offs between different approaches and optimizing for your specific use case. Focus on: 1. **Bulk loading** with at least 100K rows per chunk, and 1M+ for maximum throughput. 2. If you can control how they are written from sources, use **Parquet** for compression and speed 3. Write data into **S3** for speedy reads. 4. Use **larger Duckling sizes (Jumbo or bigger)** for loading bigger data sets. By following these guidelines and understanding the underlying principles, you can build efficient, reliable data loading pipelines that scale with your needs while managing costs effectively. --- Source: https://motherduck.com/docs/key-tasks/loading-data-into-motherduck/loading-patterns # Data loading patterns > Common data loading patterns for production pipelines, including incremental loads, upserts, deduplication, and idempotent operations in MotherDuck. Beyond basic `COPY` and `INSERT` statements, production data pipelines often need incremental loads, upserts, and idempotent operations. This guide covers common patterns you can use with MotherDuck. ## Incremental loading Incremental loading adds only new or changed data to a target table, rather than reloading everything. This reduces processing time and resource usage for large datasets that receive frequent updates. The core idea is to track a **watermark**: a column value that marks the boundary between already-loaded data and new data. ### Using a timestamp watermark If your source data has an `updated_at` or `created_at` column, use it to filter for new records: ```sql INSERT INTO analytics.events SELECT * FROM read_parquet('s3://bucket/events/*.parquet') WHERE updated_at > (SELECT MAX(updated_at) FROM analytics.events); ``` ### Using a monotonic ID watermark If your data has an auto-incrementing ID and records are never updated after creation, an ID-based watermark avoids timestamp precision issues: ```sql INSERT INTO analytics.events SELECT * FROM read_parquet('s3://bucket/events/*.parquet') WHERE event_id > (SELECT COALESCE(MAX(event_id), 0) FROM analytics.events); ``` :::tip Timestamp watermarks handle both new and updated records. ID-based watermarks only catch new records but avoid issues with clock skew and timestamp precision. Choose based on whether your source data gets updated in place. ::: ### Handling late-arriving data Data doesn't always arrive in order. Sensors go offline, mobile apps sync late, and distributed systems have clock skew. To account for this, subtract a safety buffer from your watermark: ```sql INSERT INTO analytics.events SELECT * FROM read_parquet('s3://bucket/events/*.parquet') WHERE updated_at > ( SELECT MAX(updated_at) - INTERVAL 2 HOURS FROM analytics.events ); ``` Combine this with deduplication (see [below](#deduplication-on-load)) to prevent duplicate rows from the overlapping window. ## Upserts An upsert inserts new rows and updates existing ones in a single operation. DuckDB supports three syntaxes for this. ### INSERT OR REPLACE The simplest approach: replaces the entire row when a conflict occurs on the primary key. ```sql CREATE TABLE customers ( id INTEGER PRIMARY KEY, name VARCHAR, email VARCHAR, updated_at TIMESTAMP ); INSERT OR REPLACE INTO customers SELECT * FROM read_csv('new_customers.csv'); ``` ### INSERT ON CONFLICT For more control, use `ON CONFLICT` to update only specific columns: ```sql INSERT INTO customers (id, name, email, updated_at) SELECT * FROM read_csv('updates.csv') ON CONFLICT (id) DO UPDATE SET name = EXCLUDED.name, email = EXCLUDED.email, updated_at = EXCLUDED.updated_at; ``` You can also use `ON CONFLICT ... DO NOTHING` to silently skip duplicates: ```sql INSERT INTO customers (id, name, email, updated_at) SELECT * FROM read_csv('updates.csv') ON CONFLICT (id) DO NOTHING; ``` :::warning `ON CONFLICT` requires a `PRIMARY KEY` or `UNIQUE` constraint on the conflict column(s). Without one, DuckDB raises an error. `INSERT OR REPLACE` also requires a `PRIMARY KEY` or `UNIQUE` constraint. ::: ### MERGE INTO `MERGE INTO` (DuckDB 1.4 and later) performs standard SQL upserts **without requiring a primary key or unique constraint**, which makes it the best fit for analytical tables that don't define keys: ```sql MERGE INTO customers AS t USING (SELECT * FROM read_csv('updates.csv')) AS s ON t.id = s.id WHEN MATCHED THEN UPDATE SET name = s.name, email = s.email, updated_at = s.updated_at WHEN NOT MATCHED THEN INSERT (id, name, email, updated_at) VALUES (s.id, s.name, s.email, s.updated_at); ``` `MERGE INTO` also supports `WHEN NOT MATCHED BY SOURCE` clauses for handling rows that exist in the target but not in the source, such as deleting records that disappeared upstream. See the [DuckDB MERGE INTO documentation](https://duckdb.org/docs/stable/sql/statements/merge_into) for the full syntax. ## Full refresh with swap When your dataset is small enough to reload entirely, or when incremental logic would be too complex, a full refresh with a table swap is the simplest reliable pattern: ```sql -- Load into a staging table CREATE OR REPLACE TABLE staging_products AS SELECT * FROM read_parquet('s3://bucket/products/*.parquet'); -- Swap the tables DROP TABLE IF EXISTS products; ALTER TABLE staging_products RENAME TO products; ``` :::tip This pattern is naturally idempotent: running it twice produces the same result. It also avoids issues with partial updates since the old table stays intact until the swap. ::: ## Deduplication on load Source data often contains duplicates, especially when replaying events or combining overlapping file batches. Use `ROW_NUMBER()` to keep only the most recent version of each record: ```sql INSERT INTO events SELECT * EXCLUDE (rn) FROM ( SELECT *, ROW_NUMBER() OVER ( PARTITION BY event_id ORDER BY received_at DESC ) AS rn FROM read_parquet('s3://bucket/events/*.parquet') ) WHERE rn = 1; ``` The `EXCLUDE (rn)` clause drops the helper row-number column so the inserted rows match the target schema. For an incremental load with deduplication, combine the watermark filter with the dedup logic: ```sql INSERT OR REPLACE INTO events SELECT * EXCLUDE (rn) FROM ( SELECT *, ROW_NUMBER() OVER ( PARTITION BY event_id ORDER BY received_at DESC ) AS rn FROM read_parquet('s3://bucket/events/*.parquet') WHERE received_at > ( SELECT MAX(received_at) - INTERVAL 2 HOURS FROM events ) ) WHERE rn = 1; ``` ## Idempotent loads with transactions Wrap multi-step loads in a transaction so that either all steps succeed or none do. This prevents partial loads from leaving your data in an inconsistent state: ```sql BEGIN TRANSACTION; -- Step 1: Load new data into staging CREATE OR REPLACE TABLE staging_orders AS SELECT * FROM read_parquet('s3://bucket/daily/2026-03-10/*.parquet'); -- Step 2: Delete existing records for the same date range (idempotent reload) DELETE FROM orders WHERE order_date IN (SELECT DISTINCT order_date FROM staging_orders); -- Step 3: Insert deduplicated staging data INSERT INTO orders SELECT * EXCLUDE (rn) FROM ( SELECT *, ROW_NUMBER() OVER (PARTITION BY order_id ORDER BY updated_at DESC) AS rn FROM staging_orders ) WHERE rn = 1; -- Step 4: Clean up DROP TABLE staging_orders; COMMIT; ``` :::tip Using `CREATE OR REPLACE` for staging tables makes each step idempotent. If a load fails partway through and you re-run it, the staging table is recreated from scratch. ::: ## Scheduling and automating loads For production pipelines that run on a schedule, consider these options: - **Flights**: [Run scheduled Python workflows directly in MotherDuck](/key-tasks/flights/), without external infrastructure. For a working example of the patterns on this page, see the [scheduled S3 Parquet ingestion recipe](/cookbook/flight-scheduled-s3-ingest/) in the cookbook. - **Service accounts**: Use a [MotherDuck service token](/key-tasks/service-accounts-guide/) to authenticate automated pipelines without interactive login. - **Ingestion tools**: [dlt](/integrations/ingestion/dlt/) and [Streamkap](/integrations/ingestion/streamkap/) handle incremental loading, schema management, and change data capture out of the box. - **Transformation pipelines**: Use [dbt](/integrations/transformation/dbt/) to define incremental models declaratively, with built-in support for merge strategies and deduplication. ## See also - [Loading data best practices](/key-tasks/loading-data-into-motherduck/considerations-for-loading-data/): Batch sizes, file formats, and performance optimization - [From cloud storage or over HTTPS](/key-tasks/loading-data-into-motherduck/loading-data-from-cloud-or-https/): Setting up cloud storage access - [DuckDB INSERT statement](https://duckdb.org/docs/stable/sql/statements/insert): Full syntax reference for INSERT, ON CONFLICT, and RETURNING --- Source: https://motherduck.com/docs/key-tasks/loading-data-into-motherduck/loading-data-from-local-machine # From Your Local Machine > Moving data from local to MotherDuck through the UI or programmatically. ## Single file ### CLI Using the CLI, you can connect to MotherDuck, create a database, and load a single local file (JSON, Parquet, CSV, etc.) to a MotherDuck table. First, connect to MotherDuck using the `ATTACH` command. ```sql ATTACH 'md:'; ``` Create a cloud database (or point to any existing one) and load a local file into a table. ```sql CREATE DATABASE test01; USE test01; CREATE OR REPLACE TABLE orders as SELECT * from 'orders.csv'; ``` ### UI In the MotherDuck UI, you can add JSON, CSV or Parquet file directly using the **Add data** button in the top left of the UI. See the [Getting Started Tutorial](../../../getting-started/e2e-tutorial/part-2#loading-your-data) for details. ## Multiple files or database To upload multiple files at once, or data in other formats supported by DuckDB, you can use the DuckDB CLI or any other supported [DuckDB client](https://duckdb.org/docs/data/multiple_files/overview.html). ### CLI If your all your files reside from a single table, you can use the [glob syntax to load all files into a single table](https://duckdb.org/docs/data/multiple_files/overview.html). For example, to load all CSV files from a directory into a single table, you can use the following SQL command: ```sql ATTACH 'md:'; CREATE DATABASE test01; USE test01; CREATE OR REPLACE TABLE orders as SELECT * from 'dir/*.csv'; ``` If your files are in different formats or you want to load them into different tables, you can first load the files into different tables in a local DuckDB database and then copy the entire database into MotherDuck. To copy the entire local DuckDB database into MotherDuck, you can use the following SQL commands: ```sql ATTACH 'md:'; ``` ```sql ATTACH 'local.ddb'; CREATE DATABASE cloud_db from 'local.ddb'; ``` --- Source: https://motherduck.com/docs/key-tasks/loading-data-into-motherduck/loading-data-md-python # Loading data to MotherDuck with Python > Efficient methods for loading data from Python using DataFrames, temporary files, or bulk inserts. When you ingest data with Python, typically from an API or other source, you have three options to load it into MotherDuck: 1. **FAST:** Use a Pandas, Polars, or PyArrow dataframe as an in-memory buffer before bulk loading. This is the easiest approach because dataframe libraries are optimized for bulk insert. 2. **FAST:** Write to a temporary file and load it with a `COPY` command. This involves writing to disk, but the `COPY` command is faster than `INSERT` statements. 3. **SLOW:** Use `executemany` to perform several `INSERT` statements in a single transaction. This should be discouraged unless data is very small (fewer than 500 rows). :::tip No matter which options you are picking, we recommend loading data in chunks (typically `120K` rows to match row group size) to avoid memory issues and making sure your transaction is not too large, typically finishing around a minute maximum. You can further optimize the data loading by reading our guidelines on [connections](/key-tasks/authenticating-and-connecting-to-motherduck/connecting-to-motherduck.md). ::: ## 1. load data to MotherDuck with Pandas, Polars, or PyArrow When using a dataframe library you can load data to MotherDuck in a single transaction. DuckDB uses Apache Arrow as its internal data interchange format. This means **PyArrow and Polars** (which are Arrow-native) benefit from zero-copy data transfer, making them the most memory-efficient choice. **Pandas** with the default NumPy backend copies data during transfer, which doubles memory usage. If you use Pandas, consider using [Arrow-backed dtypes](https://pandas.pydata.org/docs/user_guide/pyarrow.html) (`dtype_backend="pyarrow"`) to avoid the extra copy. ```python # Creating your table with PyArrow import duckdb import pyarrow as pa data = { 'id': [1, 2, 3, 4, 5], 'name': ['Alice', 'Bob', 'Charlie', 'David', 'Eva'] } arrow_table = pa.table(data) con = duckdb.connect('md:') con.sql('CREATE TABLE my_table AS SELECT * FROM arrow_table') ``` ### Batching data When you have a large dataset, it's recommended you chunk your data and load it in batches. This will help you to avoid memory issues and make sure your transaction is not too large. This example uses PyArrow and DuckDB in a class to: 1. Initialize a connection 2. Create a database and table if they do not already exist 3. Accept a PyArrow table to insert 4. Insert the data in chunks ```python import duckdb import os import pyarrow as pa import logging # Setup basic configuration for logging logging.basicConfig(level=logging.INFO, format='%(asctime)s - %(levelname)s - %(message)s') class ArrowTableLoadingBuffer: def __init__( self, duckdb_schema: str, pyarrow_schema: pa.Schema, database_name: str, table_name: str, destination="local", chunk_size: int = 100_000, # Default chunk size ): self.duckdb_schema = duckdb_schema self.pyarrow_schema = pyarrow_schema self.database_name = database_name self.table_name = table_name self.total_inserted = 0 self.conn = self.initialize_connection(destination, duckdb_schema) self.chunk_size = chunk_size def initialize_connection(self, destination, sql): if destination == "md": logging.info("Connecting to MotherDuck...") if not os.environ.get("motherduck_token"): raise ValueError( "MotherDuck token is required. Set the environment variable 'MOTHERDUCK_TOKEN'." ) conn = duckdb.connect("md:") logging.info( f"Creating database {self.database_name} if it doesn't exist" ) conn.execute(f"CREATE DATABASE IF NOT EXISTS {self.database_name}") conn.execute(f"USE {self.database_name}") else: conn = duckdb.connect(database=f"{self.database_name}.db") conn.execute(sql) # Execute schema setup on initialization return conn def insert(self, table: pa.Table): total_rows = table.num_rows for batch_start in range(0, total_rows, self.chunk_size): batch_end = min(batch_start + self.chunk_size, total_rows) chunk = table.slice(batch_start, batch_end - batch_start) self.insert_chunk(chunk) logging.info(f"Inserted chunk {batch_start} to {batch_end}") self.total_inserted += total_rows logging.info(f"Total inserted: {self.total_inserted} rows") def insert_chunk(self, chunk: pa.Table): self.conn.register("buffer_table", chunk) insert_query = f"INSERT INTO {self.table_name} SELECT * FROM buffer_table" self.conn.execute(insert_query) self.conn.unregister("buffer_table") ``` Using the above class, you can load your data in chunks. ```python import pyarrow as pa # Define the explicit PyArrow schema pyarrow_schema = pa.schema([ ('id', pa.int32()), ('name', pa.string()) ]) # Sample data to create a PyArrow table based on the schema data = { 'id': [1, 2, 3, 4, 5], 'name': ['Alice', 'Bob', 'Charlie', 'David', 'Eva'] } arrow_table = pa.table(data, schema=pyarrow_schema) # Define the DuckDB schema as a DDL statement duckdb_schema = "CREATE TABLE IF NOT EXISTS my_table (id INTEGER, name VARCHAR)" # Initialize the loading buffer loader = ArrowTableLoadingBuffer( duckdb_schema=duckdb_schema, pyarrow_schema=pyarrow_schema, database_name="my_db", # The DuckDB database filename or MotherDuck database name table_name="my_table", # The name of the table in DuckDB or MotherDuck destination="md", # Set "md" for MotherDuck or "local" for a local DuckDB database chunk_size=2 # Example chunk size for illustration ) # Load the data loader.insert(arrow_table) ``` ### Typing your dataset When working with production pipeline, it's recommended to type your dataset to avoid any issues with inference. Pyarrow is our recommendation to type your dataset as it's the easiest way to type your dataset, especially for complex data types. In the above example, the schema is defined explicitly on both the PyArrow table and the DuckDB schema. ```python # Initialize the loading buffer loader = ArrowTableLoadingBuffer( duckdb_schema=duckdb_schema, # prepare a DuckDB DDL statement pyarrow_schema=pyarrow_schema, # define explictely your PyArrow schema database_name="my_db", table_name="my_table", destination="md", chunk_size=2 ) ``` ## 2. write to a temporary file and load with `COPY` When you have a large dataset, another method is to write your data to temporary files and load it to MotherDuck using a `COPY` command. This also works great if you have existing data on a blob storage like AWS S3, Google Cloud Storage or Azure Blob Storage as you will benefit from cloud network speed. ```python import pyarrow as pa import pyarrow.parquet as pq import duckdb import os # Step 1: Define the schema and create a large PyArrow table schema = pa.schema([ ('id', pa.int32()), ('name', pa.string()) ]) # Example data - multiply the data to simulate a large dataset data = { 'id': list(range(1, 1000001)), # Simulating 1 million rows 'name': ['Name_' + str(i) for i in range(1, 1000001)] } # Create the PyArrow table with the schema large_table = pa.table(data, schema=schema) # Step 2: Write the large PyArrow table to a Parquet file parquet_file = "large_data.parquet" pq.write_table(large_table, parquet_file) # Step 3: Load the Parquet file into MotherDuck using the COPY command conn = duckdb.connect("md:") # Connect to MotherDuck conn.execute("CREATE TABLE IF NOT EXISTS my_table (id INTEGER, name VARCHAR)") # Use the COPY command to load the Parquet file into MotherDuck conn.execute(f"COPY my_table FROM '{os.path.abspath(parquet_file)}' (FORMAT 'parquet')") print("Data successfully loaded into MotherDuck") ``` ## 3. use `executemany` for small datasets For small datasets (fewer than 500 rows), you can use the `executemany` method to insert data row by row in a single transaction. This approach is the slowest of the three options and should only be used when working with very small amounts of data. ```python import duckdb # Sample data as a list of tuples data = [ (1, 'Alice'), (2, 'Bob'), (3, 'Charlie'), (4, 'David'), (5, 'Eva') ] con = duckdb.connect('md:') con.execute('CREATE TABLE IF NOT EXISTS my_table (id INTEGER, name VARCHAR)') con.executemany('INSERT INTO my_table VALUES (?, ?)', data) print("Data successfully loaded into MotherDuck") ``` :::warning The `executemany` method sends individual `INSERT` statements, which is significantly slower than the dataframe or `COPY` approaches. Use Option 1 or Option 2 for datasets larger than a few hundred rows. ::: --- Source: https://motherduck.com/docs/key-tasks/loading-data-into-motherduck/loading-data-from-cloud-or-https # From cloud storage or over HTTPS > Load data into MotherDuck from S3, Azure, GCS, or public HTTPS URLs. # From public cloud storage MotherDuck supports several cloud storage providers, including [Amazon S3](/integrations/cloud-storage/amazon-s3.mdx), [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). :::note MotherDuck is available on AWS in six regions across the US, Europe, and Asia Pacific (see [Cloud regions](/about-motherduck/cloud-regions/)). For an optimal experience, we strongly encourage you locate your data in the same region as your MotherDuck Organization. ::: :::tip If you want to inspect storage paths from SQL before loading data, see [`MD_LIST_FILES()`](/sql-reference/motherduck-sql-reference/md-list-files). It supports S3 and Azure paths. For S3 bucket discovery by secret, see [`MD_LIST_BUCKETS_FOR_SECRET()`](/sql-reference/motherduck-sql-reference/md-list-buckets-for-secret). ::: The following example features Amazon S3. ### UI 1. In the left panel of the UI, click **Add data** 2. Select **From cloud storage** ![Image](useBaseUrl('/img/key-tasks/loading-data-into-motherduck/from-cloud-storage.png')) 3. For a publicly accessible bucket, skip creating a secret ![Image](useBaseUrl('/img/key-tasks/loading-data-into-motherduck/skip-create-secret.png')) 4. Enter the S3 bucket path (e.g., `s3://motherduck-demo`) and select the files you want, or use Wildcard mode to choose files with a matching pattern 5. Preview the files and select the table names and destination database 6. Click **Create tables** ![Image](useBaseUrl('/img/key-tasks/loading-data-into-motherduck/create-multiple-tables-browse.png')) ### SQL Connect to MotherDuck if you haven't already by doing the following: ```sql -- assuming the db my_db exists ATTACH 'md:my_db'; ``` ```sql -- CTAS a table from a publicly available demo dataset stored in s3 CREATE OR REPLACE TABLE pypi_small AS SELECT * FROM 's3://motherduck-demo/pypi.small.parquet'; -- JOIN the demo dataset against a larger table to find the most common duplicate urls -- Note you can directly refer to the url as a table! SELECT pypi_small.url, COUNT(*) FROM pypi_small JOIN 's3://motherduck-demo/pypi_downloads.parquet' AS s3_pypi ON pypi_small.url = s3_pypi.url GROUP BY pypi_small.url ORDER BY COUNT(*) DESC LIMIT 10; ``` ## From a secure cloud storage provider MotherDuck supports several cloud storage providers, including [Amazon S3](/integrations/cloud-storage/amazon-s3.mdx), [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). To access them securely, you first must [create a secret](/sql-reference/motherduck-sql-reference/create-secret/). :::info When you load data from cloud storage while connected to MotherDuck, the query runs on MotherDuck's cloud execution engine, not your local machine. MotherDuck connects to your storage provider directly and can use any matching secret, including temporary secrets from your local DuckDB session. For more details, see [CREATE SECRET](/sql-reference/motherduck-sql-reference/create-secret/#querying-with-secrets). ::: :::note For SQL-based object discovery, [`MD_LIST_FILES()`](/sql-reference/motherduck-sql-reference/md-list-files) supports only `s3://`, `azure://`, and `az://` paths. It does not accept `gcs://`, `gs://`, or `r2://` paths. ::: ### UI You can set cloud storage secrets directly from the UI under Settings —> Integrations —> Secrets, or with the "Add data" button in the left panel. First, create a secret for your cloud storage credentials: 1. Go to **Settings** → **Integrations** → **Secrets** ![The MotherDuck UI for adding a new secret](./img/loading_data__secrets_overview.png) 2. Click **Add secret** and select your cloud storage provider (S3, R2, GCS, Azure) ![Image](useBaseUrl('/img/key-tasks/loading-data-into-motherduck/loading_data__secrets_add_new.png')) 3. Enter your access key and secret for your service account in your cloud storage provider. 4. For S3 credentials, you can test and verify your connection before saving Once your secret is configured, load data from your secure bucket: 1. In the left panel of the notebook UI, click **Add data** 2. Select **From cloud storage** 3. Enter the bucket path and select the files you want, or use Wildcard mode to choose files with a matching pattern 4. Preview the files and select the table names and destination database 5. Click **Create tables** :::note When loading data from [Azure](/integrations/cloud-storage/azure-blob-storage) or [Hugging Face](https://duckdb.org/docs/extensions/httpfs/hugging_face), you must use Wildcard mode to select files. Browse mode is not supported for these providers. ::: ### SQL To create a secret in MotherDuck from the CLI or SQL notebooks, add `IN MOTHERDUCK` explicitly. ```sql CREATE SECRET IN MOTHERDUCK ( TYPE S3, KEY_ID 'access_key', SECRET 'secret_key', REGION 'us-east-1', SCOPE 'my-bucket-path' ); -- Now you can query from a secure S3 bucket CREATE OR REPLACE TABLE mytable AS SELECT * FROM 's3://...'; ``` If you authenticate to AWS with an IAM role or SSO session instead of access keys, use the `credential_chain` provider from a local DuckDB session, such as the CLI. SQL notebooks can't read your local AWS credential cache; for notebooks, create the access-key secret shown above. For details, see [Use your local IAM role or SSO session](/integrations/cloud-storage/amazon-s3/#use-your-local-iam-role-or-sso-session). ## Over HTTPS MotherDuck supports loading data over HTTPS, including CSV exports from public Google Sheets. ### SQL ```sql SELECT * FROM read_csv( 'https://docs.google.com/spreadsheets/d//export?format=csv&gid=', MD_RUN = REMOTE ); ``` For a full Google Sheets walkthrough, including private sheets with HTTP authentication, see the [Google Sheets integration](/integrations/file-formats/google-sheets/). ## Related content - [Troubleshooting AWS S3 Secrets](/docs/troubleshooting/aws-s3-secrets/) --- Source: https://motherduck.com/docs/key-tasks/loading-data-into-motherduck/loading-duckdb-database # Load a DuckDB database into MotherDuck > Upload a local DuckDB database file to MotherDuck cloud storage. MotherDuck supports uploading local DuckDB databases in the cloud as referenced by the [CREATE DATABASE](/sql-reference/motherduck-sql-reference/create-database.md) statement. ### CLI To create a remote database from the current active local database, execute the following command: ```sql CREATE OR REPLACE DATABASE remote_database_name FROM CURRENT_DATABASE(); ``` To upload an attached local duckdb database, execute the following commands: ```sql ATTACH '/path/to/local/database.ddb' AS local_db_name; ATTACH 'md:'; CREATE OR REPLACE DATABASE remote_database_name FROM local_db_name; ``` To upload an duckdb file on disk: ```sql ATTACH 'md:'; CREATE OR REPLACE DATABASE remote_database_name FROM '/path/to/local/database.ddb'; ``` Here's a full end-to-end example: ```sql -- Let's generate some data based on the tpch extension (will be automatically autoloaded). -- This will create a couple of tables in the current database. CALL dbgen(sf=0.1); -- Connect to MotherDuck ATTACH 'md:'; CREATE OR REPLACE DATABASE remote_tpch from CURRENT_DATABASE(); ``` :::note Uploading database does not alter context, meaning you are still in the local context after the upload and the query will run locally. ::: --- Source: https://motherduck.com/docs/key-tasks/loading-data-into-motherduck/loading-data-from-postgres # From a PostgreSQL or MySQL Database > Learn to load a table from your PostgreSQL or MySQL database into MotherDuck. ## Using PostgreSQL or MySQL DuckDB extensions DuckDB's [PostgreSQL extension](https://duckdb.org/docs/extensions/postgres.html) and [MySQL extension](https://duckdb.org/docs/extensions/mysql.html) make it easy to connect to OLTP databases and copy data into MotherDuck from a DuckDB client running on your own machine or compute resource. In this guide we demonstrate the workflow with PostgreSQL. Consult the [DuckDB MySQL extension documentation](https://duckdb.org/docs/extensions/mysql) to adapt the same pattern for MySQL. :::info MotherDuck does not yet support the PostgreSQL and MySQL extensions, so you need to perform the following steps on your own computer or cloud computing resource. We are working on supporting the PostgreSQL extension on the server side so that this can happen within the MotherDuck app in the future with improved performance. ::: ### Prerequisites - **PostgreSQL Database Credentials**: Ensure you have access details to the PostgreSQL database, including host address, port, and user credentials. You can put the user credentials in the [PostgreSQL Password File](https://www.postgresql.org/docs/current/libpq-pgpass.html), [store them in environment variables](https://duckdb.org/docs/extensions/postgres.html#configuring-via-environment-variables), or pass them inline in the script below. - **Network Connectivity**: Your machine must be able to connect to the target PostgreSQL database. - **MotherDuck Credentials**: MotherDuck credentials should be set up. If not, follow the steps in [Authenticating to MotherDuck](/key-tasks/authenticating-and-connecting-to-motherduck/authenticating-to-motherduck/authenticating-to-motherduck.md). - **DuckDB**: Either the DuckDB command-line interface or Python + the DuckDB package should be installed and operational. See the [Getting Started tutorials](../../getting-started/getting-started.mdx) for instructions to install DuckDB. ### Steps The following SQL script installs and loads DuckDB's PostgreSQL extension, tunes a few settings that matter for larger bulk loads and copies one PostgreSQL table into the MotherDuck table `my_db.pg_data_schema.first_pg_table`. Fill in the placeholders ``, ``, ``, ``, ``, and `` with the appropriate values and save the script to a file, for example `ingest_data_from_postgres.sql`. ```sql INSTALL postgres; LOAD postgres; -- Tune the local DuckDB client for a larger initial load. SET threads = 8; SET memory_limit = '8GB'; SET pg_connection_limit = 8; SET pg_pages_per_task = 250; -- Connect to MotherDuck. ATTACH 'md:'; USE my_db; -- Optionally create a schema. By default MotherDuck uses the main schema. CREATE SCHEMA IF NOT EXISTS pg_data_schema; -- Ingest data from PostgreSQL to a MotherDuck table. CREATE OR REPLACE TABLE pg_data_schema.first_pg_table AS SELECT * FROM postgres_scan( 'dbname= host= port=5432 user= password= connect_timeout=10', '', '
' ); -- Optional: verify the number of rows in the MotherDuck table. SELECT count(1) FROM pg_data_schema.first_pg_table; ``` If you only want to smoke-test the connection first, add `LIMIT 1000` to the `SELECT` before running the full load. ### Best practices Here are a few tips to keep larger PostgreSQL loads predictable. #### Run DuckDB close to both systems This workflow is client-side, so the DuckDB client becomes the data mover. Run DuckDB on a machine with a good network path to both PostgreSQL and MotherDuck, and use separate client compute when possible instead of competing with the production PostgreSQL instance for the same RAM. #### Tune scan parallelism explicitly Start with `SET threads = ` and `SET memory_limit = ''`, then tune `pg_connection_limit` and `pg_pages_per_task` for your source table. For larger tables, start with `pg_connection_limit` in the `4-8` range and `pg_pages_per_task` in the `250-1000` range rather than relying on defaults. ::::warning[Watch Out] Increasing `pg_connection_limit` can increase pressure on the source PostgreSQL instance. If PostgreSQL memory or connection pressure climbs, reduce `pg_connection_limit` before reducing DuckDB `threads`. :::: #### Reduce each statement's working set The DuckDB side of this workflow is typically streaming rather than loading the full source table into RAM. Out-of-memory risk is usually driven more by the source PostgreSQL instance and the host's overall headroom than by DuckDB itself. Select only the schema and columns you need, and attach PostgreSQL with `READ_ONLY` if you use `ATTACH` instead of `postgres_scan`. #### Keep credentials out of long-lived scripts Use PostgreSQL environment variables, the PostgreSQL password file, or DuckDB secrets instead of embedding credentials directly in production scripts. #### Load in chunks For very large tables, break the initial load into ranges and insert them one chunk at a time. ```sql INSTALL postgres; LOAD postgres; SET threads = 8; SET memory_limit = '8GB'; SET pg_connection_limit = 8; SET pg_pages_per_task = 250; ATTACH 'md:'; USE my_db; CREATE SCHEMA IF NOT EXISTS pg_data_schema; CREATE TABLE IF NOT EXISTS pg_data_schema.first_pg_table AS SELECT * FROM postgres_scan( 'dbname= host= port=5432 user= password= connect_timeout=10', '', '
' ) WHERE 1 = 0; INSERT INTO pg_data_schema.first_pg_table SELECT * FROM postgres_scan( 'dbname= host= port=5432 user= password= connect_timeout=10', '', '
' ) WHERE updated_at >= TIMESTAMP '2026-01-01' AND updated_at < TIMESTAMP '2026-02-01'; ``` Repeat the `INSERT` statement for each key range or time window until the backfill is complete. If you need recurring replication, change data capture (CDC), or production orchestration, prefer a dedicated ingestion partner over a one-off client-side script. ### Run with DuckDB CLI After filling out the placeholders, you can either execute the statements line by line in the DuckDB CLI, or save the commands in a file, for example `ingest_data_from_postgres.sql`, and run: ```sh > duckdb < ingest_data_from_postgres.sql ``` ### Run with Python You can also execute it using Python with the DuckDB package. ```python import duckdb with open("ingest_data_from_postgres.sql", 'r') as f: s = f.read() duckdb.sql(s) ``` After completing these steps, you should see the new table show up in the MotherDuck Web UI. ## Using MotherDuck ingestion partners MotherDuck collaborates with various integration partners to facilitate data transfer in diverse ways—including change data capture (CDC)—from your PostgreSQL or MySQL database to MotherDuck. For example, you can refer to our [Estuary guide](https://motherduck.com/blog/streaming-data-to-motherduck/) that demonstrates how to stream data from Neon, a PostgreSQL-based database, to MotherDuck. To explore the full range of solutions tailored to your needs, visit our [MotherDuck ecosystem partners page](https://motherduck.com/ecosystem/). --- Source: https://motherduck.com/docs/key-tasks/loading-data-into-motherduck/loading-data-via-postgres-endpoint # Loading data via the Postgres endpoint > Best practices for loading data into MotherDuck efficiently when you are connected through the Postgres endpoint. MotherDuck's Postgres endpoint is a good thin-client loading path when your application, BI tool, or serverless runtime already speaks PostgreSQL and you want to run SQL in MotherDuck without installing a DuckDB client. It is best suited to server-side loading from remote data sources. :::tip[Best practice] If your files already live in object storage or are available over HTTPS, use the Postgres endpoint to run `CREATE TABLE AS SELECT` or `INSERT INTO ... SELECT` and let MotherDuck read the files remotely. ::: If your data is on your laptop, application server disk, or in a local DuckDB file, a DuckDB client path is usually a better fit. In that case, either: - Upload the files to object storage first, then load them remotely through the Postgres endpoint. - Use a DuckDB client path instead, such as `duckdb`, Python DuckDB, or another DuckDB client connected to `md:`. ## Recommended patterns ### Load directly from cloud storage or HTTPS This is the preferred pattern for the Postgres endpoint. The examples below use public sample files so you can run them directly. ```sql CREATE OR REPLACE TABLE my_db.main.orders_raw AS SELECT * FROM read_parquet( 'https://shell.duckdb.org/data/tpch/0_01/parquet/orders.parquet', MD_RUN = REMOTE ); ``` You can use the same approach with CSV or JSON: ```sql CREATE OR REPLACE TABLE my_db.main.weather_events AS SELECT * FROM read_csv( 'https://raw.githubusercontent.com/duckdb/duckdb-web/main/data/weather.csv', HEADER = true, AUTO_DETECT = true, MD_RUN = REMOTE ); ``` This keeps the work inside MotherDuck and avoids sending rows one statement at a time over the Postgres wire. ### Load into a staging table, then transform For repeatable pipelines, stage the raw data first and then publish into the final table. ```sql CREATE SCHEMA IF NOT EXISTS my_db.ingest; CREATE OR REPLACE TABLE my_db.ingest.orders_stage AS SELECT * FROM read_parquet( 'https://shell.duckdb.org/data/tpch/0_01/parquet/orders.parquet', MD_RUN = REMOTE ); CREATE OR REPLACE TABLE my_db.main.orders_curated AS SELECT o_orderkey AS order_id, o_custkey AS customer_id, o_orderdate::TIMESTAMP AS order_ts, o_totalprice::DOUBLE AS total_amount FROM my_db.ingest.orders_stage; ``` This keeps ingestion and transformation separate, which makes validation, retries, and backfills easier. ### Batch rows if the data exists only in application memory If your source data exists only in application memory, use multi-row `INSERT` statements instead of row-by-row inserts. Recommended: ```sql CREATE OR REPLACE TABLE my_db.main.orders_batch ( id INTEGER, note VARCHAR, amount DOUBLE ); INSERT INTO my_db.main.orders_batch VALUES (1, 'a', 10.0), (2, 'b', 20.0), (3, 'c', 30.0); ``` Less efficient: ```sql INSERT INTO my_db.main.orders_batch VALUES (1, 'a', 10.0); INSERT INTO my_db.main.orders_batch VALUES (2, 'b', 20.0); INSERT INTO my_db.main.orders_batch VALUES (3, 'c', 30.0); ``` Single-row inserts create unnecessary round trips and are much slower for loading. When loading rows from an application: - fewer, larger batches - append-only staging tables - transactions that stay comfortably below a minute ## Use a DuckDB client path instead when The Postgres endpoint is not intended for workflows that depend on local DuckDB-client capabilities. Use a DuckDB client path instead when you need: - local-file `COPY` - `EXPORT DATABASE` - `IMPORT DATABASE` - `ATTACH ':memory:'` - `ATTACH '/path/to/file.duckdb'` - `CREATE DATABASE ... FROM '/path/to/file.duckdb'` - `MD_RUN = LOCAL` - `INSTALL` and `LOAD` In practice, that means the Postgres endpoint is not the primary interface for: - loading directly from local files - attaching local or in-memory DuckDB databases - extension-based workflows - local execution paths such as `MD_RUN = LOCAL` ## Protected cloud storage If you are loading from protected S3, GCS, R2, or Azure storage, make sure the required MotherDuck secret already exists. Cloud-storage secret creation requires DuckDB extension support and is not supported through the Postgres endpoint. The recommended workflow is: 1. Create the secret using a DuckDB client path or another supported MotherDuck workflow. 2. Then use the Postgres endpoint to run the load query. ## Decision guide | Situation | Best approach | |---|---| | Files already in S3, GCS, R2, Azure, or public HTTPS | Use `read_parquet`, `read_csv`, or `read_json` with `MD_RUN = REMOTE` over the Postgres endpoint | | Data is local on your machine | Prefer a DuckDB client path, or upload the files to object storage first | | Data exists only in app memory and volume is modest | Use explicit large multi-row `INSERT` batches over the Postgres endpoint | | Very large local bulk load | Use a DuckDB client path instead | ## Summary For the best mix of throughput and simplicity: 1. Write source files as Parquet when you can. 2. Put them in object storage close to your MotherDuck region. 3. Use the Postgres endpoint to run `CREATE TABLE AS SELECT` or `INSERT INTO ... SELECT` with `MD_RUN = REMOTE`. 4. Stage first, validate row counts and schemas, then publish into the final table. ## Related pages - [Postgres Endpoint reference](/sql-reference/postgres-endpoint) - [Loading data best practices](./considerations-for-loading-data.mdx) - [From cloud storage or HTTPS](./loading-data-from-cloud-or-https.md) - [From your local machine](./loading-data-from-local-machine.md) - [Loading a DuckDB database](./loading-duckdb-database.md) - [Connect from Python using Postgres endpoint](/key-tasks/authenticating-and-connecting-to-motherduck/postgres-endpoint/python) --- Source: https://motherduck.com/docs/key-tasks/loading-data-into-motherduck/loading-data-into-motherduck # Loading Data into MotherDuck > Learn how to load data into MotherDuck from various sources You can leverage MotherDuck's managed storage to persist your data. MotherDuck storage provides a high level of manageability and abstraction, optimizing your data for secure, durable, performant, and efficient use. There are several ways to load data into MotherDuck storage. ## Before You Start: Understanding Trade-offs Before choosing a loading method, it's important to understand the performance implications and trade-offs involved. Our [Considerations for Loading Data](./considerations-for-loading-data.mdx) guide explains: - **Batch vs. streaming approaches** and when to use each - **File format choices** and their impact on performance - **Optimal batch sizes** for different scenarios - **Cost implications** of different loading strategies - **Common performance pitfalls** and how to avoid them This understanding will help you make informed decisions that optimize for your specific use case. ## Included pages - [Loading Data Best Practices](https://motherduck.com/docs/key-tasks/loading-data-into-motherduck/considerations-for-loading-data): Understanding trade-offs and performance implications when loading data into MotherDuck - [Data loading patterns](https://motherduck.com/docs/key-tasks/loading-data-into-motherduck/loading-patterns): Common data loading patterns for production pipelines, including incremental loads, upserts, deduplication, and idempotent operations in MotherDuck. - [From Your Local Machine](https://motherduck.com/docs/key-tasks/loading-data-into-motherduck/loading-data-from-local-machine): Moving data from local to MotherDuck through the UI or programmatically. - [Loading data to MotherDuck with Python](https://motherduck.com/docs/key-tasks/loading-data-into-motherduck/loading-data-md-python): Efficient methods for loading data from Python using DataFrames, temporary files, or bulk inserts. - [From Cloud Storage or over HTTPS](https://motherduck.com/docs/key-tasks/loading-data-into-motherduck/loading-data-from-cloud-or-https): Load data into MotherDuck from S3, Azure, GCS, or public HTTPS URLs. - [Load a DuckDB database into MotherDuck](https://motherduck.com/docs/key-tasks/loading-data-into-motherduck/loading-duckdb-database): Upload a local DuckDB database file to MotherDuck cloud storage. - [From a PostgreSQL or MySQL Database](https://motherduck.com/docs/key-tasks/loading-data-into-motherduck/loading-data-from-postgres): Learn to load a table from your PostgreSQL or MySQL database into MotherDuck. - [Via the Postgres Endpoint](https://motherduck.com/docs/key-tasks/loading-data-into-motherduck/loading-data-via-postgres-endpoint): Best practices for loading data into MotherDuck efficiently when you are connected through the Postgres endpoint. --- ## 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%2Floading-data-into-motherduck%2F&page_title=MotherDuck%20Documentation%20-%20Loading%20data%20into%20MotherDuck&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.