How to Use MotherDuck with DuckDB from Python
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?
- Get an access token from the MotherDuck UI under Settings → Access Tokens.
pip install duckdb— the standard DuckDB package includes the MotherDuck extension.- Set the token in the environment:
MOTHERDUCK_TOKEN, or pass it in the connection string. - Connect with
duckdb.connect('md:')for the default cloud catalogue, orduckdb.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.
