Recipe: MotherDuck cloud + local snapshot¶
Use MotherDuck as the production warehouse, sync it locally for offline dev. The pattern that motivated tycoon data sync (issue #12).
Why this setup¶
Three problems running every dev query against prod:
- Slow — every query is a network round-trip
- Fragile — upstream pipeline staleness silently blocks downstream tools
- Risky — easy to accidentally mutate prod when the token's exported
A frozen local snapshot solves all three: dev queries are instant, the data is a known-good baseline immune to prod drift, and the snapshot is read-only no matter what the token has access to.
Setup¶
1. Authenticate with MotherDuck¶
Either token-based:
Or OAuth (browser-based, recognized by tycoon doctor since v0.1.2):
2. Initialize the project pointing at MotherDuck¶
mkdir cloud-demo && cd cloud-demo
tycoon init --template csv-import --name cloud-demo
tycoon register warehouse --type motherduck --catalog cloud_demo_prod --no-prompt
This sets tycoon.yml's database.warehouse: md:cloud_demo_prod and stack.warehouse: motherduck.
For a fresh MotherDuck catalog, you'd typically create it from the MotherDuck UI or via CREATE DATABASE cloud_demo_prod from a DuckDB shell — tycoon doesn't create empty MotherDuck catalogs for you.
3. Verify¶
You should see MotherDuck auth OK token (env) (or OAuth (cached session)).
Day-to-day: the sync pattern¶
Configure default sources in tycoon.yml¶
sync:
to: data/local_snapshot.duckdb
sources:
- from: md:cloud_demo_prod
schemas: ['mart'] # only mart schemas — staging is too churn-y
tables: ['dim_*', 'fct_*']
Now tycoon data sync (no flags) snapshots prod into data/local_snapshot.duckdb.
Pull a fresh dev baseline¶
Output:
> Syncing 1 source(s) → data/local_snapshot.duckdb (mode: replace)
mart.dim_users 12,453 rows from md:cloud_demo_prod
mart.fct_orders 1,205,331 rows from md:cloud_demo_prod
mart.dim_products 4,210 rows from md:cloud_demo_prod
OK Synced 1,221,994 rows across 3 table(s) to data/local_snapshot.duckdb
Use the snapshot for dev queries¶
For ad-hoc SQL, just point --db at the snapshot:
tycoon data query --db data/local_snapshot.duckdb \
"SELECT count(*) FROM mart.fct_orders WHERE order_date >= '2026-01-01'"
For dbt dev, point your dev target at the snapshot. In dbt_project/profiles.yml:
cloud_demo:
target: dev
outputs:
dev:
type: duckdb
path: data/local_snapshot.duckdb
prod:
type: duckdb
path: md:cloud_demo_prod
token: "{{ env_var('MOTHERDUCK_TOKEN') }}"
Then:
tycoon data transform run --target dev # against snapshot, fast
tycoon data transform run --target prod # against MotherDuck, when ready
You can also persist the dev/prod target choice in tycoon.yml:
This sets dbt_target: dev in tycoon.yml, so subsequent tycoon data transform run invocations default to dev (snapshot) without --target.
When to re-sync¶
Re-run tycoon data sync whenever you want a fresh baseline. Some patterns:
- Daily: cron
0 7 * * * tycoon data syncfor an "as-of-this-morning" snapshot - On demand: when the snapshot's gone stale enough to mislead you
- Per-feature: re-sync at the start of each new analytical question
The snapshot is intentionally allowed to go stale until you re-sync — that's the whole value prop.
Sync mode cheat-sheet¶
tycoon data sync # replace (default)
tycoon data sync --mode skip-existing # only fill in NEW tables
tycoon data sync --mode append # accumulate (no dedup!)
tycoon data sync --schema mart --tables 'dim_*' # narrow filter
tycoon data sync --from md:other_catalog --to ./snap2.duckdb # one-off sync to a different file
Limits to know about¶
- Pull-only: there's no
tycoon data sync --reversefor local → cloud. Deliberate, prevents accidental prod mutation. Use dlt or your existing prod pipeline for that direction. - Full replace per table: no incremental sync in v1. Re-sync re-copies every matched table.
md:and local DuckDB only for sources today. Snowflake / BigQuery / Postgres come later.- DuckLake catalogs: only the
READ_ONLYATTACH path is verified. Note that a DuckDB-backed catalog (ducklake:foo.ducklake, the default) is locked to one process at a time, so a concurrent reader such as Rill will conflict; a SQLite-backed catalog (ducklake:sqlite:foo.sqlite) is multi-process safe and does not. This corrects an earlier note here that attributed the lock to SQLite — see issue #71.
Related¶
tycoon data sync— full referencetycoon register warehouse— for the cloud setup step- Reference: tycoon.yml
syncblock - Issue #12 — original design doc