2 - Loading Your Data

In this section, you'll learn how to load your own data into MotherDuck and run powerful hybrid queries that combine local and cloud data.

👈 Go back to Part 1: Running Your First Query

Loading your data

Loading Data using CREATE TABLE AS SELECT

The CREATE TABLE AS SELECT (CTAS) pattern creates a new table and populates it with data in a single operation:

CREATE OR REPLACE TABLE docs_playground.my_table AS SELECT * FROM 'my_data.csv';

Loading Data using INSERT INTO

The INSERT INTO pattern allows you to append data to existing tables, update specific records, and manage data incrementally:

-- First, create the table structure
CREATE TABLE docs_playground.my_table AS SELECT * FROM 'my_data.csv' LIMIT 0;

-- Then load data incrementally
INSERT INTO docs_playground.my_table SELECT * FROM 'new_data.csv';
INSERT OR REPLACE INTO docs_playground.my_table SELECT * FROM 'updated_data.csv';

tip

While CREATE TABLE AS SELECT is convenient for one-time loads or small datasets, for larger datasets and production workflows, we recommend using INSERT INTO. This approach provides better control over data loading, allows for incremental updates, and is more efficient for ongoing data management.

There are several ways to get your data into MotherDuck, depending on where your data currently lives:

From Local File System

To load data files from your file system into MotherDuck, you'll need:

1. A valid MotherDuck token stored as the motherduck_token environment variable
2. A DuckDB client (DuckDB CLI, Python, etc.)

To create a MotherDuck token, navigate to the MotherDuck UI, click your organization name in the top left, then go to Settings > Integrations > Access Token. For detailed instructions, see our authentication guide.

DuckDB CLI

Install the DuckDB CLI for macOS/Linux. For other operating systems, see the DuckDB installation guide.

curl https://install.duckdb.org | sh

Launch the DuckDB CLI:

duckdb

-- Connect to MotherDuck
ATTACH 'md:';

-- Load CSV data from your local file into the playground database
CREATE TABLE docs_playground.popular_currency_rate_dollar AS 
SELECT * FROM './popular_currency_rate_dollar.csv';

Python

Install DuckDB using your preferred package manager, such as pip:

pip install duckdb

import duckdb

# Connect to MotherDuck
conn = duckdb.connect('md:')

# Load data into the playground database (automatically created)
conn.execute("""
    CREATE TABLE docs_playground.popular_currency_rate_dollar AS 
    SELECT * FROM './popular_currency_rate_dollar.csv'
""")

MotherDuck UI

Head over to the Create table from file button in the MotherDuck UI and upload your file directly. This works great for smaller files and provides a visual interface.

From Remote Storage (S3, GCS, etc.)

For data already stored in cloud storage, you have multiple options:

SQL

You can run queries directly against remote storage using our interactive SQL editor:

CREATE TABLE IF NOT EXISTS docs_playground.popular_currency_rate_dollar AS
SELECT
  *
FROM
  's3://us-prd-motherduck-open-datasets/misc/csv/popular_currency_rate_dollar.csv';

SQL Editor loading...

DuckDB CLI

ATTACH 'md:';
CREATE TABLE docs_playground.popular_currency_rate_dollar AS 
SELECT * FROM 's3://us-prd-motherduck-open-datasets/misc/csv/popular_currency_rate_dollar.csv';

Python

import duckdb
conn = duckdb.connect('md:')
conn.execute("""
    CREATE TABLE docs_playground.popular_currency_rate_dollar AS 
    SELECT * FROM 's3://your-bucket/your-file.csv'
""")

info

For private AWS s3 buckets, you'll need to configure AWS credentials. Check our AWS s3 authentication guide for details.

Querying Your Data

Once your data is loaded, you can query it from any interface:

SQL

FROM
  docs_playground.popular_currency_rate_dollar
LIMIT
  10;

SQL Editor loading...

DuckDB CLI

ATTACH 'md:';
FROM docs_playground.popular_currency_rate_dollar LIMIT 10;

Python

import duckdb

# Connect to MotherDuck
conn = duckdb.connect('md:')

# Query your data
result = conn.sql("FROM docs_playground.popular_currency_rate_dollar LIMIT 10").fetchall()
print(result)

👉 Continue to Part 3: Sharing Your Database →