How to Use MotherDuck: The Beginner's Guide (First ATTACH, Hybrid Queries, the CLI, and Token Safety)

CodexFuse Team · 2026-11-02T10:12:29.888Z · 16 min read min read

Learn how to use MotherDuck: attach your first cloud database, run hybrid local-and-cloud queries, master the CLI, and handle tokens safely — a beginner’s guide.

If you already know DuckDB, learning how to use MotherDuck takes one command: ATTACH 'md:'. MotherDuck is a serverless cloud data warehouse built on DuckDB — you connect from the same DuckDB client you already use on your laptop, and from that moment your queries can run locally, in MotherDuck's cloud, or split between both at once. This MotherDuck tutorial walks you through the whole beginner arc: creating your account and token, your first ATTACH, the hybrid-query lesson, the MotherDuck CLI, reading S3 data, and what it actually costs.

Draw the one line that matters before we start: MotherDuck is not local DuckDB. Local DuckDB is the embedded, in-process engine that queries a file on your machine — if you are new to it, our DuckDB beginner guide is the right starting point. MotherDuck is the serverless-cloud layer on top of it: you ATTACH your cloud database inside local DuckDB, and the cloud side brings auto-scaling compute, access control, and object-storage integration you never had to provision.

What is MotherDuck, exactly?

MotherDuck is a serverless cloud data warehouse built on DuckDB. Instead of standing up a warehouse cluster, you attach to one from any DuckDB client with a single line — ATTACH 'md:'; — and your DuckDB instance becomes, in the vendor's own words, "supercharged by MotherDuck": queries are automatically routed to the best place to run them, local DuckDB, MotherDuck cloud, or both.

That routing is the whole product idea. A local query over a CSV on your laptop stays local and costs you nothing; a query over your cloud tables runs on MotherDuck's servers; and you can join the two in one query, which no local engine can do alone. And if you arrived here by searching "MotherDuck vs DuckDB" wondering which one you need: it is not a contest — MotherDuck is DuckDB, with a serverless cloud attached.

How do you connect DuckDB to MotherDuck?

You connect with one line — ATTACH 'md:' — but that line only works once MotherDuck knows who you are, which is what the token is for. Here is the full first-time flow, exactly as the vendor documents it.

First, create an account and a token. MotherDuck's UI signs you in with Google, GitHub, or a username and password. Then, in the UI, click your organization name in the top left, open Settings, and click + Create token. Give it a recognizable name (the docs suggest something like "DuckDB CLI on my laptop"), pick Read/Write or Read Scaling, choose an expiry, and copy the token to your clipboard.

Second, save the token as an environment variable — this is the token rule, and it is the single most important habit in this guide:

export motherduck_token="your-token-here"

The vendor's own guidance: add that line to your ~/.zprofile or ~/.bash_profile, or store it in a .env file in your project root. The token is sensitive — never paste it into a notebook cell, a shared script, or a chat message. You can also pass it inline in the connection string as md:my_db?motherduck_token=..., but the environment variable is the documented best practice.

Third, attach — the "MotherDuck attach" moment. From inside the DuckDB CLI:

ATTACH 'md:';
-- or a named database:
ATTACH 'md:my_db';

With the motherduck_token variable set, DuckDB downloads the signed MotherDuck extension and you are connected — no browser step. Without any authentication, the attach reaches for the network immediately and fails: I verified this on a clean machine, where ATTACH 'md:my_db' with no token died trying to reach MotherDuck's cloud. That is your proof that md: is not a local file — it is a cloud database being mounted into your local session. In notebooks like marimo the same idea holds: ATTACH IF NOT EXISTS 'md:my_db' opens a browser login the first time, and setting motherduck_token before marimo edit skips the prompt afterward. If you live in notebooks, our Jupyter beginner guide covers the notebook habits that transfer here.

One more thing the docs make clear: there are two command-line tools and they do different jobs. The DuckDB CLI (duckdb) runs SQL interactively over local files and MotherDuck databases in one session. The MotherDuck CLI (motherduck) signs you in or creates an account, scripts queries with JSON output, and builds and publishes Dives and Flights. Do not confuse them — beginners who install one and expect the other's job get stuck in the first ten minutes.

What is dual execution in MotherDuck?

Dual execution is MotherDuck's automatic query routing: the planner decides whether each part of your query runs locally on your DuckDB client, in the MotherDuck cloud, or both. Query a local database and processing stays local at zero cost; query a cloud database and it runs on MotherDuck's servers; join local and cloud data and the planner splits the work.

This is the hybrid lesson, and it is worth seeing mechanically. The attach mechanism works for any database, not just the cloud one — I proved it locally by attaching a second DuckDB file as if it were the cloud side and running a join across both in a single query:

-- two attached databases, one query (verified on this machine)
ATTACH '/tmp/warehouse.db' AS cloud;   -- stand-in for ATTACH 'md:my_db'

SELECT p.product, p.price, o.qty, ROUND(p.price * o.qty, 2) AS line_total
FROM local_prices p
JOIN cloud.cloud_orders o USING (product)
ORDER BY line_total DESC;

-- result:
-- widget  9.99   3   29.97
-- gadget  24.50  1   24.50
-- widget  9.99   2   19.98

That is exactly what a hybrid MotherDuck query looks like, except the cloud database lives in MotherDuck's cloud instead of a local file. The vendor's own example makes the point vividly: you can join a CSV on your laptop with a petabyte table in the cloud in one statement. And when you want to see where each piece actually ran, put EXPLAIN in front of any query — it shows which operations execute remotely versus locally.

How do you install and use the MotherDuck CLI?

The MotherDuck CLI is the terminal route into the product: install it, sign in or create an account, run queries, and publish pipelines and dashboards — all without opening a browser. I installed it from the official installer on a clean Linux machine this week, and here is the real arc.

Install with the vendor's one-liner:

curl -s https://install.motherduck.com | sh    # install the CLI
motherduck login                               # existing user: browser login
motherduck new                                 # or: free org, no signup needed

The installer downloads the latest DuckDB version MotherDuck supports, installs the motherduck extension, and puts the CLI under ~/.motherduck/ — in my test it pulled in DuckDB 1.5.6 automatically. MotherDuck started as a DuckDB extension, which meant installing DuckDB first; the CLI now ships with DuckDB inside, so motherduck query works locally and against the cloud from one install.

The fastest path for a beginner is motherduck new: it creates a temporary organization for free with no signup and stores the token. Either route lands you on the Lite plan with a default database named my_db and the sample_data database attached, so you can run motherduck query "SELECT count(*) FROM sample_data.nyc.taxi" in the first minute. Two honest caveats from the docs: the no-signup organization has no owner and lapses 72 hours after creation along with everything in it — open the claim URL to take ownership and keep your work — and if a MOTHERDUCK_TOKEN environment variable is present (the CI case), the CLI picks it up automatically.

Here is the real command inventory, captured from the installed CLI (version 1.5.6-2026-10-707, verified this week):

Usage: motherduck [options] [command]

MotherDuck CLI (v1.5.6-2026-10-707)

Commands:
  login [options]        Authenticate via OAuth device flow; use --headless for
                         browserless login or set MOTHERDUCK_TOKEN
  logout                 Remove saved token
  status [options]       Show authentication state, account, and duckling size
  new [options]          Create a new MotherDuck account and organization, and
                         save the new token
  query [options] [sql]  Execute a SQL query against MotherDuck and write
                         results to stdout
  upgrade                Upgrade the motherduck CLI
  dive                   Create and manage Dives, interactive visualizations
                         over your data
  flight                 Create and manage Flights, Python data pipelines that
                         run on a schedule or on demand
  guide                  Create and manage Guides, Markdown documents that
                         capture context and guidance about your data
  help [command]         display help for command

The query subcommand is the workhorse — "Execute a SQL query against MotherDuck and write results to stdout" — with --file to read SQL from a file, --timeout (default 120 seconds), and -o table|json|csv for output format. The vendor's quickstart shows the shape of real usage:

motherduck query "FROM 's3://bucket/data.parquet' LIMIT 10"
motherduck flight push my_pipeline --run       # publish + run a Python pipeline
motherduck dive push my_dashboard              # publish a hosted dashboard

What are Dives and Flights?

Dives and Flights are the two things the MotherDuck CLI builds and publishes, and beginners constantly mix them up. A Dive is an interactive visualization over your data; a Flight is a Python data pipeline that runs on a schedule or on demand. One is for looking at data, the other is for moving and transforming it.

A Dive is, in the CLI's own words, "an interactive visualization authored as a React component that queries your live data. You develop and preview it locally, then push your changes when ready." A Dive directory holds an index.tsx component (exporting the component and the REQUIRED_DATABASES it queries) plus a dive.metadata.json file with the title, description, and Dive ID that push and pull resolve. Start one with motherduck dive init my_dive --title "My Dive" — the CLI even tells you to run motherduck dive guide first, which prints the authoring guide with the supported APIs and importable libraries.

A Flight is "a Python script MotherDuck runs for you, on a schedule or on demand. You build it locally, push it to MotherDuck, then trigger runs and read their logs." A Flight directory holds main.py (run as python main.py), an optional requirements.txt, and a flight.metadata.json with the name, schedule, config, and Flight ID. Start one with motherduck flight init --name my_flight, again after motherduck flight guide. Flights come in two compute sizes: F4 (0.5 vCPU, 4 GB memory, for lightweight automations and frequent schedules) and F16 (2 vCPU, 16 GB, the default).

How do you query S3 data with MotherDuck?

The "MotherDuck S3" story is refreshingly simple: store your credentials once as a secret, then read buckets with plain SQL — no ETL job, no data copy. It works the same for Azure Blob Storage and Google Cloud Storage, and the files can be Parquet, JSON, or CSV. The beginner mistake is pasting access keys into SQL strings. The right way is a secret object:

-- store S3 credentials once, scoped to your user account
CREATE SECRET IN MOTHERDUCK (
  TYPE S3,
  KEY_ID 'access_key',
  SECRET 'secret_key',
  REGION 'us-east-1',
  SCOPE 'my-bucket-path'
);

Secrets are scoped to your user account and are not shared with other users in your organization — that is the documented reason to use them instead of inline keys. With the secret in place, you read and write in plain SQL:

-- 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;

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

The reading itself is powered by httpfs, which the vendor glossary describes as "DuckDB's built-in module for reading and writing files directly over HTTP(S) and from S3-compatible object storage, Azure, and Google Cloud Storage, without downloading them first." Once the data lands in MotherDuck tables, the usual analytics workflow applies — and if that workflow means scheduled transformations, our dbt beginner guide is the natural next read.

What does MotherDuck cost?

MotherDuck pricing is usage-based with a genuinely usable free tier — but the free tier has edges, so here are the numbers exactly as the pricing page reads at the time of writing (November 2026; treat these as a snapshot, because they move).

The Lite plan starts at $0 and is aimed at solo practitioners and hobbyists. It includes up to 3 internal active users and 2 service accounts, 10 GB of free storage, 10 hours of Pulse compute per month, Pulse instances only, role-based access control with preset roles, Guides (shared context for AI agents), and community support. Snapshot retention on Lite is up to 1 day. Business is $250 per organization per month plus usage, with up to 10 users, five instance types, read-scaling replicas, Flights, and a 99.9% availability SLA; Enterprise is custom.

The mental model for the bill: storage is cheap and predictable; compute is where the money goes, and dual execution is your lever — keep local work local and it costs nothing. When your workload genuinely outgrows serverless and you need a dedicated always-on warehouse engine, that is the moment to evaluate one: our ClickHouse beginner guide covers the heavyweight columnar alternative.

How does the MotherDuck MCP server work?

Since November 2025, MotherDuck has shipped an MCP server that lets AI assistants query and explore your MotherDuck databases over the Model Context Protocol. There are two flavors: a remote MCP server (fully managed, read-write) and a local MCP server (fully customizable, self-hosted).

The remote server is hosted at https://api.motherduck.com/mcp. Connect an assistant — Claude, ChatGPT, Cursor, or others — and you can analyze your data in natural language and generate interactive visualizations without writing SQL. The local server is the self-hosted counterpart for teams that want to customize behavior. One section is all this needs in a beginner guide: it is a connection method, not the product — the product is still ATTACH, dual execution, and the CLI.

7 mistakes beginners make with MotherDuck

Every one of these comes from the docs, the CLI help, or a verified behavior — not from folklore.

Sources

1. MotherDuck pricing page (free tier, compute and storage prices, Lite/Business/Enterprise) — https://motherduck.com/pricing — fetched 2026-11-02.
2. "Authenticating to MotherDuck" (token creation, motherduck_token environment variable, connection strings) — https://motherduck.com/docs/key-tasks/authenticating-and-connecting-to-motherduck/authenticating-to-motherduck/ — fetched 2026-11-02.
3. "Connect & query from the DuckDB CLI" (the two CLIs, ATTACH 'md:', hybrid sessions) — https://motherduck.com/docs/getting-started/interfaces/connect-query-from-duckdb-cli/ — fetched 2026-11-02.
4. "MotherDuck CLI: query, pipelines, and dashboards from your terminal" (install, login/new, MCP since November 2025, DuckDB bundled) — https://motherduck.com/blog/motherduck-cli-announcement/ — fetched 2026-11-02.
5. MotherDuck MCP server docs (remote vs local server, natural-language analysis) — https://motherduck.com/docs/sql-reference/mcp/ and https://motherduck.com/docs/getting-started/mcp-getting-started/ — fetched 2026-11-02.
6. "Connect MotherDuck to Amazon S3" (CREATE SECRET, read_parquet, md_list_files) — https://motherduck.com/docs/integrations/cloud-storage/amazon-s3/ — fetched 2026-11-02.
7. httpfs glossary entry — https://motherduck.com/glossary/httpfs/ — fetched 2026-11-02.
8. MotherDuck llms-full.txt (dual execution, "serverless cloud data warehouse built on DuckDB") — https://motherduck.com/docs/llms-full.txt — fetched 2026-11-02.
9. "DuckDB + MotherDuck for Beginners" video FAQ (ATTACH one-liner, dual execution explained) — https://motherduck.com/videos/duckdb-motherduck-for-beginners-your-ultimate-guide/ — fetched 2026-11-02.
10. marimo MotherDuck integration (ATTACH IF NOT EXISTS 'md:my_db', token env var, Datasources Panel) — https://docs.marimo.io/integrations/motherduck/ — fetched 2026-11-02.
11. Hex MotherDuck integration (two connection methods, md: connection string) — https://motherduck.com/docs/integrations/bi-tools/hex/ — fetched 2026-11-02.
12. "What is MotherDuck and how does it extend DuckDB" (secondary explainer) — https://www.getorchestra.io/guides/what-is-motherduck-and-how-does-it-extend-duckdb — fetched 2026-11-02.
13. "MotherDuck Review: DuckDB With A Cloud Attached" (secondary review, not a walkthrough) — https://noizz.io/reviews/motherduck-review — fetched 2026-11-02.
14. Author's own verification, 2026-11-02: MotherDuck CLI v1.5.6-2026-10-707 installed from https://install.motherduck.com on a clean Linux machine; ATTACH 'md:my_db' with no token fails reaching for the network; cross-database hybrid JOIN demonstrated locally with DuckDB 2.0.0.

Frequently Asked Questions

Is MotherDuck free?

Yes, to start. The Lite plan starts at $0 and includes 10 GB of storage, 10 hours of Pulse compute per month, up to 3 users, and community support — enough for real learning and small projects. The fastest free route is motherduck new, which spins up a temporary organization with no signup, but it lapses after 72 hours unless you claim it.

What is the difference between MotherDuck and DuckDB?

DuckDB is the free, embedded analytical engine that runs inside your process and queries local files. MotherDuck is the serverless-cloud layer on top of it: you attach your cloud database into the same DuckDB client with ATTACH 'md:my_db', and MotherDuck adds auto-scaling compute, access control, sharing, and object-storage integration you never provision yourself.

Do I need to install DuckDB before installing the MotherDuck CLI?

No. The CLI ships with DuckDB inside — in my own test the official installer downloaded and installed DuckDB 1.5.6 automatically alongside the motherduck binary. One install gives you motherduck query against both local files and the cloud.

Is it safe to put my MotherDuck token in an environment variable?

That is the documented best practice. Save it as motherduck_token in your shell profile or a project .env file, and treat it like a password: never paste it into shared notebooks, scripts, or chat. When creating the token you can also choose Read Scaling instead of Read/Write and set an expiry, which limits what a leaked token can do.

Can AI assistants like Claude or ChatGPT query my MotherDuck data?

Yes, through the MotherDuck MCP server, launched in November 2025. The remote server (fully managed, read-write, at https://api.motherduck.com/mcp) lets assistants analyze your databases in natural language and generate visualizations without SQL; a local, self-hosted server exists for teams that want full customization.