How to Use MotherDuck with DuckDB from Python

October 27, 2026

MotherDuck is a managed cloud service that runs DuckDB databases in the cloud while your Python process uses the same DuckDB client you already know. You connect with a connection string like md:my_database and an access token, and queries run either locally or in the cloud depending on what tables they touch.

The local-first split is the whole product. Your small files stay on your laptop; shared tables live in the cloud. Queries that join both get executed with the right parts shipped to the right side.

How do you connect from Python?

  1. Get an access token from the MotherDuck UI under Settings → Access Tokens.
  2. pip install duckdb — the standard DuckDB package includes the MotherDuck extension.
  3. Set the token in the environment: MOTHERDUCK_TOKEN, or pass it in the connection string.
  4. Connect with duckdb.connect('md:') for the default cloud catalogue, or duckdb.connect('md:analytics') for a specific database.

A local-only connection stays duckdb.connect('local.duckdb'). Nothing about your existing local code changes; you just point at a different string.

How does hybrid querying work?

This is the feature worth understanding. When a query references md:analytics.events and a local DataFrame or parquet file, DuckDB decides where to run it.

  • All-local query → runs locally, no network.
  • All-cloud query → runs in MotherDuck’s compute.
  • Mixed → DuckDB picks a side and transfers the smaller side over the wire.

The rule of thumb: put the large shared table in the cloud, keep the small lookup or today’s export locally. If you flip that, you will ship a 4GB file over the network on every query and wonder why it is slow.

You can force cloud execution with SET motherduck_force_cloud_execution = true; — useful for benchmarking, dangerous for large local tables.

How do you create and upload tables?

Creating a table in the cloud: CREATE TABLE md:analytics.daily_metrics AS SELECT ... FROM 'local_file.parquet'; — that runs the query and writes results to the cloud database.

Uploading an existing local DuckDB file is different and people get it wrong. ATTACH 'md:' then COPY FROM DATABASE local_db TO cloud_db; copies schema and data. There is also a UI upload path with a size limit; the exact cap depends on your plan tier, and large uploads are slow either way.

The safer pattern for a big local file: leave it local, create a cloud view over a subset, and only materialise what you need. MotherDuck’s storage pricing is per GB-month, so uploading everything is a billing decision, not just a convenience.

What are the practical limits

Constraint Detail
Single-user compute DuckDB is single-node; not a warehouse for concurrent BI traffic
Free tier Limited storage and query volume; fine for evaluation
Concurrency Multiple readers work; heavy concurrent writes will conflict
Token scope Tokens are per-user by default; service tokens for automation

That first one is not a small detail. If twenty analysts hit a MotherDuck database through a BI tool simultaneously, the experience degrades in ways a Postgres or Snowflake setup would not. MotherDuck is built for the analyst who wants a shared warehouse without a platform team, not for a 200-person BI deployment.

How do you use it in a real workflow

A pattern that works for small data teams:

  • Raw landing zone in the cloud, loaded by a nightly job.
  • Transformation as SQL in a repo, run against md:.
  • Local notebooks connect to the same cloud database for exploration — no data dump needed.
  • Small extracts stay local as parquet for fast iteration.

The killer feature is that a local notebook and a scheduled job hit identical table names. No staging environment drift, because there is one database and one set of table definitions.

Where it falls short

No Spark-scale processing. No row-level security comparable to a real warehouse. No mature orchestration — dbt works, but you are wiring the scheduler yourself. And the local-first model means query performance depends on where you run it, which is a new kind of debugging for people used to everything being server-side.

Key Takeaways

  • MotherDuck runs DuckDB in the cloud; connect from Python with duckdb.connect('md:database') plus a token.
  • Hybrid queries run locally, in the cloud, or split — put large shared tables in the cloud.
  • Loading a local DuckDB file requires COPY FROM DATABASE, not a simple attach.
  • It is single-node: great for one to a handful of analysts, not for high-concurrency BI.
  • Storage is billed per GB-month, so uploading everything local is a real cost.