MotherDuck & DuckDB Cloud: Hybrid Execution, WASM in the Browser, Team Attach

motherduck is the commercial cloud data platform built by the original creators of DuckDB — and it is the pick-one architectural decision that decides whether your under-1-TB team analytics stack costs $50 a month with sub-second interactivity from a laptop or $50,000 a month on a warehouse that spins up a compute cluster for every ad-hoc query. Every senior data engineer who has spent the past decade watching Snowflake, BigQuery, and Databricks fight for the petabyte crown has quietly noticed the same thing: the majority of real analytics workloads at real companies sit between 10 GB and 1 TB, they are queried interactively by three-to-thirty analysts, and they have been over-served by cluster-oriented warehouses that charge for cluster time whether the analyst is querying or reading Slack. The engineering trade-off does not live in "should we adopt a warehouse" — every team needs one — but in which duckdb cloud shape you pick and how it composes with your existing notebooks, dashboards, and event streams.

This guide is the senior-DE walkthrough you wished existed the first time an interviewer asked "walk me through motherduck and its hybrid execution model", or "when does motherduck vs snowflake genuinely favour MotherDuck?", or "explain how duckdb wasm runs a full analytical engine inside a browser tab and what its performance envelope actually is." It walks through the four MotherDuck primitives — the hybrid execution model where the SQL planner splits a single query between local DuckDB and the cloud, DuckDB-WASM compiled to WebAssembly for zero-install in-browser analytics, the Team Attach primitive that shares databases across teammates with per-role RBAC and time-travel snapshots, and the decision matrix that names when MotherDuck wins the small-data wedge versus when Snowflake, BigQuery, and Databricks keep the petabyte / enterprise / ML crown. Each section pairs a teaching block with a Solution-Tail interview answer — code, a step-by-step trace, an output table, then a concept-by-concept breakdown of why it works.

When you want hands-on reps immediately after reading, drill the SQL practice library →, sharpen aggregation muscle with the aggregation practice library →, and cover system-design axes with the design practice library →.

---

On this page

• Why MotherDuck matters in 2026
• Hybrid execution model
• DuckDB WASM and browser execution
• Team Attach and collaboration
• When MotherDuck wins and interview signals
• Cheat sheet — MotherDuck recipes
• Frequently asked questions
• Practice on PipeCode

---

1. Why MotherDuck matters in 2026

DuckDB grew up in-process; MotherDuck is the cloud that finally lets a team of ten share it

The one-sentence invariant: MotherDuck is the commercial cloud data platform built on DuckDB by DuckDB's original creators, sitting in the market gap between "an analyst running DuckDB on a laptop" and "a hundred-person analytics org on Snowflake / BigQuery / Databricks", and its unique value proposition is hybrid execution — a single SQL query whose planner splits work between the analyst's laptop-resident DuckDB and MotherDuck's cloud compute, so the local disk and CPU are used for local files while cloud tables push their scans and joins down into the cloud without the analyst ever leaving their notebook. The pattern you pick in month one becomes the pattern you fight to migrate away from in year three, because every downstream dashboard, notebook, and shared model hard-codes assumptions about where compute lives, what the connection string looks like, who the RBAC principal is, and whether the query engine you built against speaks Iceberg REST or a proprietary SDK.

The four axes interviewers actually probe.

• Scale envelope. MotherDuck's design centre is < 1 TB per database, though it will happily hold and query multi-TB tables via hybrid execution. The wedge is single-machine analytics: the fastest query on a modest dataset is the one that never leaves the laptop. Snowflake / BigQuery / Databricks scale to petabytes but pay a cluster-startup tax on every ad-hoc query. Interviewers open with this because it separates architects who understand the small-data reality from those who default to "just use Snowflake."
• Hybrid boundary. MotherDuck's planner decides per relation whether a scan runs locally (against read_parquet('s3://...'), a local CSV, or an in-memory DataFrame) or in the cloud (against md:acme_prod.public.orders). This decision is invisible to the analyst — one query, two engines, one result. Explaining it out loud is a senior signal.
• WASM performance envelope. DuckDB compiles to WebAssembly, and MotherDuck integrates duckdb-wasm into notebooks and dashboards that run entirely in the browser tab. Interviewers probe the practical ceiling: 10-100 M rows is comfortable, 1 GB Parquet over HTTP range reads is realistic, browser memory becomes the bottleneck around ~2 GB heap.
• Team sharing and RBAC. Team Attach — the ATTACH 'md:acme_prod' primitive — is MotherDuck's answer to Snowflake Data Sharing / BigQuery datasets / Databricks Delta Sharing. Per-role read-only / read-write / admin grants, time-travel snapshots, and a governance ring that any teammate can enter with one line of SQL. Explaining the RBAC wedges is a senior signal.

The 2026 reality — MotherDuck is a real competitor for a specific and growing workload class.

• The small-data wedge is enormous and under-served. Independent surveys keep finding that the median analytical dataset in the wild is 5-50 GB. Snowflake and BigQuery charge cluster time; a MotherDuck query on the same dataset is often 10x cheaper and faster because the data does not travel over the network for every query.
• Notebook workflows are now the default surface for analysts and DEs. Jupyter, Hex, Deepnote, Observable, and Marimo have replaced the SQL IDE for exploratory work. MotherDuck was built for this surface first; the connection string is duckdb.connect('md:acme_prod?motherduck_token=...') and every teammate points at the same string.
• The hybrid pattern collapses the "extract, then query" tax. In a warehouse-first world, an analyst downloads a query result to a laptop, runs Pandas on it, then loses reproducibility because the extract is stale by tomorrow. In a MotherDuck world, the extract is the query — local Parquet joins to cloud fact tables in a single statement.
• DuckDB is the fastest single-machine analytical engine. Benchmark after benchmark (ClickBench, TPC-H at scale factor 100 on a laptop, DuckDB's own H2O group-by suite) shows DuckDB competitive with or beating cluster-oriented engines on datasets that fit on one machine. MotherDuck inherits this speed and adds cloud persistence.
• Free tier plus generous pricing tier is the growth engine. Free tier for individuals, ~$25/month for the pro tier at 10 GB storage, per-compute-second pricing beyond that. Compare to Snowflake's minimum cluster-hour billing model on the same workload.

What interviewers listen for.

• Do you name hybrid execution as MotherDuck's unique primitive without prompting? — senior signal.
• Do you say "the DuckDB planner splits work per-relation between local and cloud" in the first minute? — required answer.
• Do you push back on "just use Snowflake" with the small-data cost argument — cluster-startup tax on ad-hoc queries? — senior signal.
• Do you name DuckDB-WASM as the browser primitive that enables zero-install analytics dashboards? — senior signal.
• Do you describe Team Attach as "one ATTACH string, three role wedges, time-travel snapshots" — a governance primitive, not just a connection detail? — senior signal.

Worked example — the four-axis scale-envelope table

Detailed explanation. The single most useful artefact for a MotherDuck interview is a memorised 4-column scale-envelope table. Every senior open-warehouse discussion converges on this table within the first ten minutes; having it in your head is what separates a fluent answer from a stumbling one. Walk through building the table for a hypothetical team of eight data engineers who own an analytics stack with a fact.orders table, a dim.customer table, and a nightly feature.user_cohort model.

• Team size. Eight data engineers plus fifteen analysts.
• Dataset. fact.orders at 400 GB and growing 500 MB/day; dim.customer at 3 GB; feature.user_cohort at 80 GB with a nightly rebuild.
• Workloads. Ad-hoc SQL from notebooks (dozens of queries per analyst per day), scheduled feature-store refresh (one hour/day), a public-facing dashboard reading dim.customer (hundreds of queries per minute).
• Constraints. Under $2,000/month total warehouse spend; sub-second interactive latency; every analyst can spin up a fresh notebook and be productive in five minutes.

Question. Build the four-axis comparison for MotherDuck versus Snowflake / BigQuery / Databricks for this team's workloads and pick the vendor each workload should sit on.

Input.

Axis	MotherDuck	Snowflake	BigQuery	Databricks
Design centre	< 1 TB per db, single-machine + hybrid	multi-cluster, petabyte, enterprise	petabyte, serverless, GCP-native	ML + lakehouse + Delta
Cost floor per ad-hoc query	~$0 (local) to ~$0.001 (cloud second)	cluster-second, minimum warm-up	on-demand slot-time or reserved	cluster-startup + DBU
Interactive latency (< 1 TB)	milliseconds (local) to sub-second (cloud)	1-10 s cold + cluster spin-up	1-3 s slot-driven	5-30 s cluster warm-up
Notebook UX	native (duckdb.connect('md:'))	JDBC + connector	client + BQ SDK	notebook-first but heavy
Team sharing	Team Attach (one ATTACH string)	Data Sharing (contract-heavy)	dataset ACLs (GCP IAM)	Delta Sharing + Unity

Code.

# Notebook — one connection string, three vendors, one worked cost story
import duckdb, os, time

# ------- MotherDuck (local + hybrid) --------------------------------
con = duckdb.connect(f"md:acme_prod?motherduck_token={os.environ['MD_TOKEN']}")
t0 = time.time()
con.sql("""
    SELECT c.country, sum(o.total_cents)::DOUBLE / 100 AS revenue_usd
    FROM   fact.orders o
    JOIN   dim.customer c USING (customer_id)
    WHERE  o.created_at >= DATE '2026-07-01'
    GROUP  BY c.country
    ORDER  BY revenue_usd DESC
    LIMIT  10
""").show()
print(f"MotherDuck  {time.time() - t0:0.2f}s")

# ------- Snowflake (equivalent, via snowflake-connector-python) ------
# import snowflake.connector as sf
# sf_con = sf.connect(...)
# t0 = time.time()
# sf_con.cursor().execute(SAME_SQL).fetchall()
# print(f"Snowflake   {time.time() - t0:0.2f}s  (+ warehouse warm-up minutes)")

Step-by-step explanation.

1. The scale-envelope table forces the four axes into a single view: design centre, cost per ad-hoc query, interactive latency, and team-sharing ergonomics. MotherDuck wins on the first three for this team's dataset size; the fourth is a tie against Snowflake's Data Sharing (both are one-liners) but MotherDuck's ATTACH is markedly less contract-heavy.
2. The team's workload sits squarely in MotherDuck's sweet spot: fact.orders is 400 GB (well under the 1 TB soft boundary), dim.customer is small enough to fit local memory, and the nightly feature build is exactly the workload MotherDuck was built for (bulk cloud compute for one workload, interactive local compute for the rest).
3. The dashboard reading dim.customer at hundreds of QPS is where the decision splinters: MotherDuck can serve it, but for hundreds of QPS with sub-second SLAs, a purpose-built serving layer (Postgres read replica, ClickHouse, or a dashboard cache) is often the right answer regardless of vendor. This is a senior signal — refuse to over-fit one tool to every workload.
4. The Snowflake column reveals its cost curve: cluster warm-up minutes and per-second billing dominate for ad-hoc notebooks. If the team ran 500 ad-hoc queries/day and each triggered even a small XSMALL warehouse for 30 seconds, that alone eats the $2,000/month budget without any batch load.
5. The teaching pattern for any senior scale-envelope question is: name the design centre, name the cost floor per ad-hoc query, name the interactive latency for the actual dataset size, and name the team-sharing story. Any interviewer can hand you a new workload and you can walk the four-axis table without pausing.

Output.

Workload	Recommended vendor	Why
Ad-hoc notebook SQL	MotherDuck	< 1 TB, interactive, notebook-native, zero cluster-startup tax
Nightly feature refresh	MotherDuck (cloud compute)	80 GB rebuild fits comfortably; per-second billing wins vs cluster-hour
Public dashboard (100s QPS)	Postgres read replica or ClickHouse	Purpose-built serving layer; keep the warehouse for analysts
Team sharing across 23 people	MotherDuck Team Attach	One ATTACH string; per-role RBAC; time-travel snapshots included

Rule of thumb. Never pick a warehouse based on "which one is trendy" or "which one everyone in this Slack uses." Pick it based on (design centre x cost floor x interactive latency x team-sharing ergonomics) — the four axes. Write the table on a whiteboard first; the vendor falls out of the constraints.

Worked example — what interviewers actually probe on MotherDuck

Detailed explanation. The senior data-engineering MotherDuck interview has a predictable structure: the interviewer opens with an ambiguous question ("what's your take on the warehouse landscape in 2026?"), then progressively narrows to test whether you understand the hybrid execution model, the WASM envelope, and the Team Attach RBAC. The candidates who name the primitive in sentence one score highest; the candidates who describe "just another OLAP database" score lowest. Walk through the interview grading rubric.

• Ambiguous opener. "How would you evaluate MotherDuck for a small analytics team?" — invites you to name hybrid execution.
• Follow-up 1. "When does MotherDuck lose to Snowflake?" — probes the scale envelope.
• Follow-up 2. "How does the hybrid planner decide where to run a scan?" — probes the boundary.
• Follow-up 3. "What's the practical performance envelope of DuckDB-WASM in the browser?" — probes the WASM axis.
• Follow-up 4. "How do teammates share a MotherDuck database with different permissions?" — probes the Team Attach RBAC axis.

Question. Draft a 5-minute senior MotherDuck answer that covers all four axes without waiting to be asked.

Input.

Interview signal	Weak answer	Senior answer
Primitive named	"it's DuckDB in the cloud"	"it's hybrid execution — planner splits per-relation between laptop and cloud"
Scale envelope	"handles anything"	"< 1 TB sweet spot; multi-TB via hybrid; petabyte belongs on Snowflake / BigQuery"
Hybrid boundary	"not sure"	"SQL planner marks each relation as local (files, in-memory) or cloud (md: tables), decides per cost heuristic"
WASM envelope	"runs in browser"	"duckdb-wasm; ~2 GB heap; 10-100 M rows OK; Parquet over HTTP range"
Team Attach	"ATTACH statement"	"ATTACH 'md:db' → three role wedges + time-travel snapshots + one governance ring"

Code.

Senior MotherDuck answer template (5 minutes)
=============================================

Minute 1 — name the primitive up front
  "MotherDuck's unique value is hybrid execution. The DuckDB SQL
   planner decides per-relation whether to scan locally (Parquet
   files, in-memory DataFrames, CSVs) or push down to the cloud
   ('md:acme_prod.public.orders'), and assembles the result on
   whichever side is smaller."

Minute 2 — scale envelope
  "Design centre is < 1 TB per database. Multi-TB works via hybrid
   push-down. Petabyte-scale enterprise workloads belong on
   Snowflake or BigQuery — the cluster-startup tax that hurts
   MotherDuck's ad-hoc use case is what those platforms are
   optimised for at scale."

Minute 3 — hybrid boundary
  "The planner marks each relation. Local Parquet, CSV, JSON, and
   in-memory DataFrames scan locally. Cloud tables (persisted in
   MotherDuck) push down. Cross-source joins assemble results on
   whichever side is smaller. All invisible to the analyst — one
   query, one result."

Minute 4 — DuckDB-WASM
  "DuckDB compiles to WebAssembly. duckdb-wasm 1.1 runs a full
   analytical engine inside a browser tab; ~2 GB heap ceiling
   depending on the browser; 10-100 M rows is comfortable in
   practice. Reads Parquet over HTTP range requests directly from
   S3. Zero install; ideal for embedded dashboards and notebooks."

Minute 5 — Team Attach + RBAC
  "One ATTACH string — 'md:acme_prod' — shares the database with
   the whole team. Role wedges: read-only, read-write, admin.
   Time-travel snapshots for reproducibility ('AS OF' predicate).
   RBAC granted per role on the MotherDuck governance surface.
   Replaces Snowflake Data Sharing's contract overhead with a
   single grant statement."

Step-by-step explanation.

1. Minute 1 is the crucial framing. Naming hybrid execution immediately signals you understand the product primitive, not just the marketing surface. Weak candidates dive into features ("it has notebooks and…") before naming the differentiator.
2. Minute 2 addresses the scale envelope before the interviewer asks. This preempts the common trap where you commit to MotherDuck, then admit you don't know where it stops scaling. Naming the boundary up front is senior signal.
3. Minute 3 is the hybrid boundary probe. The per-relation planning story is what makes MotherDuck qualitatively different from either DuckDB (all-local) or Snowflake (all-cloud). Saying it out loud demonstrates depth.
4. Minute 4 is the WASM envelope. The 2 GB heap ceiling and 10-100 M row envelope are honest numbers; over-promising here loses credibility. Naming HTTP range reads shows you know how it actually loads data.
5. Minute 5 covers Team Attach and RBAC — the collaboration axis. The three-role wedge model is the answer to "how does a team share this?" — and comparing it favourably to Snowflake Data Sharing's contract-heavy setup is a senior signal.

Output.

Grading criterion	Weak score	Senior score
Names hybrid execution in minute 1	rare	mandatory
Names scale envelope boundary	rare	required
Names per-relation planning	occasional	mandatory
Names WASM heap ceiling	rare	senior signal
Names Team Attach role wedges	rare	senior signal

Rule of thumb. The senior MotherDuck answer is a 5-minute monologue that covers all four axes without waiting for the follow-ups. Rehearse it once; deploy it every time. If the interviewer only asks the opener, you deliver the whole thing anyway — it signals you've built for real teams, not just followed the docs.

Senior interview question on picking MotherDuck vs the big three

A senior interviewer often opens with: "You inherit a Snowflake bill of $9,000/month running mostly interactive analytics for a team of twelve on datasets that total 800 GB. The CFO has asked you to cut cloud costs 60% without harming analyst productivity. Walk me through your evaluation framework, the migration plan you'd propose, and the workloads you would not migrate off Snowflake."

Solution Using a MotherDuck pilot with workload-fenced migration and cost telemetry

# Step 1 — quantify current Snowflake spend by workload class
# Query Snowflake's ACCOUNT_USAGE for the last 30 days
snowflake_sql = """
    SELECT  warehouse_name,
            query_tag,
            count(*)                                AS query_count,
            sum(credits_used)                       AS credits,
            sum(credits_used) * 3.0                 AS approx_usd,
            avg(execution_time) / 1000.0            AS avg_seconds
    FROM    snowflake.account_usage.query_history
    WHERE   start_time >= dateadd('day', -30, current_timestamp())
    GROUP   BY warehouse_name, query_tag
    ORDER   BY approx_usd DESC;
"""
# → run this; expect ~70% of spend on ad-hoc notebook queries,
#   ~20% on scheduled ETL, ~10% on the public dashboard

# Step 2 — one-shot Parquet export of the analytical tables MotherDuck will own
snowflake_export = """
    COPY INTO @acme_stage/orders/
    FROM  analytics.fact.orders
    FILE_FORMAT = (TYPE = PARQUET COMPRESSION = ZSTD)
    OVERWRITE = TRUE
    HEADER    = TRUE
    MAX_FILE_SIZE = 268435456;   -- 256 MB target files

    COPY INTO @acme_stage/customer/
    FROM  analytics.dim.customer
    FILE_FORMAT = (TYPE = PARQUET COMPRESSION = ZSTD)
    OVERWRITE = TRUE
    HEADER    = TRUE;
"""

# Step 3 — MotherDuck side: create the database + load
import duckdb
con = duckdb.connect("md:acme_prod?motherduck_token=" + os.environ["MD_TOKEN"])
con.sql("CREATE DATABASE IF NOT EXISTS acme_prod")
con.sql("USE acme_prod")

con.sql("""
    CREATE OR REPLACE TABLE fact.orders AS
    SELECT * FROM read_parquet('s3://acme-stage/orders/*.parquet')
""")

con.sql("""
    CREATE OR REPLACE TABLE dim.customer AS
    SELECT * FROM read_parquet('s3://acme-stage/customer/*.parquet')
""")

# Step 4 — grant the team read/write per role
con.sql("CREATE ROLE analyst_readonly")
con.sql("CREATE ROLE analyst_readwrite")
con.sql("GRANT SELECT ON DATABASE acme_prod TO analyst_readonly")
con.sql("GRANT SELECT, INSERT, UPDATE ON DATABASE acme_prod TO analyst_readwrite")

# Step 5 — analyst-side notebook: one connection string, run the same SQL
con = duckdb.connect("md:acme_prod?motherduck_token=" + os.environ["MD_TOKEN"])

t0 = time.time()
result = con.sql("""
    SELECT c.country,
           date_trunc('week', o.created_at) AS wk,
           sum(o.total_cents)::DOUBLE / 100 AS revenue_usd,
           count(distinct o.customer_id)    AS active_customers
    FROM   fact.orders    o
    JOIN   dim.customer   c USING (customer_id)
    WHERE  o.created_at >= DATE '2026-01-01'
    GROUP  BY c.country, wk
    ORDER  BY wk DESC, revenue_usd DESC
""").fetchdf()
print(f"MotherDuck  {time.time() - t0:0.2f}s  rows={len(result):,}")

# Step 6 — cost-per-workload telemetry (run weekly)
con.sql("""
    SELECT query_id, user_name,
           duration_ms,
           bytes_scanned,
           bytes_scanned / (1024.0*1024*1024) * 0.003 AS approx_cost_usd
    FROM   motherduck_ui.query_history
    WHERE  start_time >= now() - INTERVAL '7 days'
""")

Step-by-step trace.

Step	Before (Snowflake $9K/mo)	After (MotherDuck + Snowflake residual)
Ad-hoc notebook queries	~70% of spend on XSMALL cluster time	MotherDuck; per-second billing; ~$800/mo
Scheduled ETL	~20% of spend on scheduled warehouse	MotherDuck cloud compute for < 1 TB tables; ~$400/mo
Public dashboard (100s QPS)	~10% of spend on a dedicated warehouse	Stay on Snowflake OR move to a serving layer; $600/mo
Team sharing	Snowflake shares + roles	MotherDuck Team Attach with three role wedges
Time to first query on fresh notebook	30-90 s cluster spin-up	200 ms — laptop-native connection
Storage cost	Snowflake compressed columnar	MotherDuck Parquet-backed; equivalent
Total monthly cost	~$9,000	~$1,800 (80% cut)

After the migration, ad-hoc analyst queries land in MotherDuck within milliseconds (local cache) to sub-second (cloud). Scheduled feature refreshes run on MotherDuck's cloud compute at per-second billing. The dashboard that serves hundreds of QPS stays on Snowflake because its SLA and connection-pool shape are not what MotherDuck was designed for. Total spend drops from $9,000 to $1,800.

Output:

Metric	Before	After
Monthly warehouse spend	$9,000	$1,800
Ad-hoc query cost per query	~$0.50	~$0.001
Time-to-first-query on a fresh notebook	30-90 s	200 ms
Team sharing UX	Snowflake shares + role grants	one ATTACH string + three role wedges
Snapshots / time-travel	Snowflake Time Travel (paid)	included in Team Attach
Data location risk	Snowflake proprietary format	Parquet + DuckDB (open)

Why this works — concept by concept:

• Workload fencing — the migration succeeds because it does not pretend one warehouse fits every workload. Ad-hoc + scheduled ETL move to MotherDuck; the public dashboard stays on Snowflake. This is the senior discipline that separates a migration from a rewrite.
• Hybrid execution — MotherDuck's cloud runs the persisted fact.orders and dim.customer tables; local DuckDB runs the analyst's exploratory joins against staging Parquet files. Same query engine on both sides; one connection string; no context switching.
• Per-second billing — the ad-hoc query saving of ~$0.50 → ~$0.001 per query is not a fluke. It is the direct consequence of MotherDuck not charging for cluster warm-up minutes. On a workload of 500 ad-hoc queries per day, this is where the 60% cut lives.
• Team Attach RBAC — the read-only / read-write / admin role wedges collapse the "who can touch what" story into a single grant. Snowflake requires separate role grants per schema per table, and Data Sharing requires contracts; MotherDuck's ATTACH is a single line.
• Cost — a $9K bill dropped to $1.8K on the same team and dataset. MotherDuck compute is measured in per-second dollars; storage is per-GB; the eliminated cost is cluster-warm-up time and per-warehouse minimums. Net O(seconds of actual compute) versus O(minutes of cluster startup) per ad-hoc query.

SQL
Topic — sql
SQL problems for MotherDuck-shaped analytical workloads

Practice →

Design Topic — design Warehouse migration and vendor-selection design problems

Practice →

---

2. Hybrid execution model

ATTACH 'md:' gives the DuckDB planner two engines to plan against — the one on the laptop and the one in the cloud

The mental model in one line: MotherDuck's hybrid execution is a planner-level feature — the DuckDB SQL optimiser, running in the analyst's local DuckDB process, receives a single SQL statement, walks the logical plan, marks each relation as local (files, in-memory DataFrames, staging CSVs) or cloud (persistent tables accessed via md:database.schema.table), pushes as much filter / projection / aggregation as possible down to whichever side owns the base data, and then assembles the final result on whichever side is smaller — all invisible to the analyst, who wrote one query and got one result. Every senior data engineer who has spent time with distributed query planners (Presto, Trino, Spark, Snowflake, BigQuery) recognises the pattern; what makes MotherDuck's version distinctive is that one of the two engines is the analyst's own laptop.

The four axes for hybrid execution.

• Where the planner runs. The SQL parser and optimiser live in the local DuckDB process. This is important because the decision about where to run each fragment happens in the same address space as the analyst's Python / notebook. Latency to the planner is zero.
• What runs local. Any relation whose base data lives on the analyst's machine: read_parquet('s3://...'), read_csv('local.csv'), a Pandas DataFrame registered as a view, an Arrow table, or a DuckDB table in a local .duckdb file. Local relations scan with zero network overhead.
• What runs cloud. Any relation whose base data lives in MotherDuck: md:acme_prod.public.orders, or a table created inside a USE md:acme_prod session. Cloud relations get filter / projection / aggregation push-down; only the reduced tuples cross the wire.
• Where the join lands. For cross-engine joins, the planner estimates cardinality on both sides and streams the smaller side to the larger side's engine. If the local side is 10 K rows and the cloud side is 100 M rows, the local rows ship to the cloud, the join runs cloud-side, and only the projected result comes back.

The ATTACH primitive — the single durable piece of state.

• What it is. ATTACH 'md:acme_prod?motherduck_token=...' — a DuckDB statement that registers a MotherDuck database as a schema-visible source in the current local DuckDB session. After ATTACH, any table under acme_prod. is queryable side-by-side with local tables.
• Where the token lives. In an environment variable ($motherduck_token), read by the client library on connect. Never hard-code in notebooks. Rotate through the MotherDuck web UI when a teammate leaves.
• Idempotency. ATTACH is idempotent; re-running against the same connection is a no-op after the first success. Connection reset (kernel restart in Jupyter) drops the attachment.
• Bootstrap. First run — after the analyst's pip install duckdb and pip install duckdb-motherduck, ATTACH 'md:' creates a default my_db under their account. Subsequent runs point at whichever team database the ATTACH string names.

The three failure modes senior engineers pre-empt.

• Network partitions. Cloud-resident relations become unreachable if the analyst is offline. Mitigation: keep exploratory work on local files (read_parquet('s3://...') with httpfs) so the analyst can still work; or explicitly materialise a cloud table to a local .duckdb file for offline work (CREATE TABLE local.orders AS SELECT * FROM acme_prod.fact.orders WHERE ...).
• Runaway push-downs. A poorly-formed query can push down a filter that returns most of a 100 M row cloud table, spending a lot of egress. Mitigation: EXPLAIN ANALYZE before running expensive queries; MotherDuck's plan output flags cloud-scan sizes explicitly. Add explicit LIMIT clauses during exploration.
• Version skew. The local DuckDB library must be broadly compatible with the MotherDuck cloud DuckDB. Mitigation: pin the client duckdb version in the team's requirements file; MotherDuck maintains a compatibility matrix. Newer client + older cloud sometimes rejects features; older client + newer cloud is usually fine.

Common interview probes on hybrid execution.

• "How does MotherDuck decide where to run a scan?" — required answer is "the planner marks each relation local or cloud per base-data location, pushes down filters, assembles the join on the smaller side."
• "What happens if the analyst is offline?" — cloud relations fail; local files still work; materialise for offline work.
• "Can I run cross-engine joins?" — yes; the planner ships the smaller side to the larger engine.
• "How is this different from a federated query in Trino?" — Trino is server-side federation; MotherDuck's planner runs client-side, so the laptop is one of the engines. Different centre of gravity.

Worked example — a local CSV joined to a cloud fact table

Detailed explanation. The canonical hybrid pattern is a small local file joined to a large cloud table. An analyst is investigating churn: they have a 5 K-row CSV of high-value customer accounts (exported from the CRM) and want to join it to the 400 GB fact.orders table in MotherDuck to see each account's last 90 days of order value. The join is impossible on the laptop alone (the fact table is too big) and unnecessarily slow on the cloud alone (the CSV is trivially small). Hybrid execution is exactly the pattern.

• Local. 5 K row high_value_accounts.csv from the CRM. Columns: customer_id, tier, account_owner.
• Cloud. acme_prod.fact.orders — 400 GB, 500 M rows.
• Query. For each high-value customer, sum their revenue over the last 90 days and rank them.
• Latency target. Under 5 seconds interactive.

Question. Write the hybrid query and describe what the planner decides for each relation.

Input.

Relation	Location	Size	Scan strategy
high_value_accounts.csv	local disk	5 K rows	local scan; ship to cloud
acme_prod.fact.orders	MotherDuck cloud	500 M rows	cloud scan with pushdown
final result	assembles cloud-side, returned local	5 K rows	pulled back

Code.

import duckdb, os

con = duckdb.connect(f"md:acme_prod?motherduck_token={os.environ['MD_TOKEN']}")

# The magic: read_csv is local, fact.orders is cloud, the planner
# routes each relation to the right engine automatically.
df = con.sql("""
    WITH hva AS (
        SELECT customer_id, tier, account_owner
        FROM   read_csv('/data/crm/high_value_accounts.csv',
                        columns = {'customer_id': 'BIGINT',
                                   'tier':        'TEXT',
                                   'account_owner': 'TEXT'})
    ),
    recent_orders AS (
        SELECT customer_id,
               sum(total_cents)::DOUBLE / 100 AS revenue_usd,
               count(*)                        AS order_count
        FROM   acme_prod.fact.orders
        WHERE  created_at >= now() - INTERVAL '90 days'
        GROUP  BY customer_id
    )
    SELECT hva.tier,
           hva.account_owner,
           hva.customer_id,
           coalesce(ro.revenue_usd, 0)  AS revenue_usd,
           coalesce(ro.order_count, 0)  AS order_count
    FROM   hva
    LEFT   JOIN recent_orders ro USING (customer_id)
    ORDER  BY revenue_usd DESC
""").fetchdf()

print(df.head(20))

Step-by-step explanation.

1. The planner parses the SQL and walks the logical plan. It marks read_csv('/data/crm/high_value_accounts.csv') as a local relation — the file lives on the analyst's disk. It marks acme_prod.fact.orders as a cloud relation — the table lives in MotherDuck.
2. For the recent_orders CTE, the planner sees a filter (created_at >= now() - INTERVAL '90 days') and a group-by. It pushes both into the cloud: the filter runs against MotherDuck's columnar cache (only the last 90 days of data is read), the group-by runs cloud-side, and the ~2 M unique customers with orders in the window are returned.
3. For the outer join, the planner has 5 K rows on the local side and ~2 M rows on the cloud side. It ships the 5 K local rows up to the cloud, runs the LEFT JOIN cloud-side (using the customer_id equijoin), and returns the ~5 K row result. The alternative — pulling 2 M cloud rows down to join with 5 K local rows — would be slower and more expensive.
4. The ORDER BY runs on whichever side has the final result. Because the result is only ~5 K rows, it's returned to the client and sorted locally.
5. The analyst sees a single dataframe; the total wall-clock time is ~2 seconds. Local scan of the CSV is ~50 ms; cloud filter + group-by is ~1.5 s; join + return is ~400 ms.

Output.

Query fragment	Where it ran	Why
read_csv(...) local file	local DuckDB	file lives on laptop
Filter + group-by on fact.orders	MotherDuck cloud	400 GB table; pushdown eliminates transfer
LEFT JOIN hva ↔ recent_orders	MotherDuck cloud	ship 5 K local rows up; smaller side ships
Final ORDER BY + return	local	5 K row result; trivial

Rule of thumb. Trust the planner to route each relation, but always EXPLAIN the first-run of a new hybrid query in production — you want to see the plan flag CLOUD_SCAN vs LOCAL_SCAN explicitly. Push-down failures usually announce themselves as a suspiciously large CLOUD_SCAN estimate; fix by adding filters or casting types so the pushdown rule fires.

Worked example — the cost heuristic and when to override it

Detailed explanation. The MotherDuck planner uses cost heuristics to pick where to run each fragment; those heuristics are usually right, but there are three canonical cases where the analyst wants to override. Walk through each with the PRAGMA flags and explicit hints.

• Case 1. Small cloud table + big local file — planner may push the join to the cloud, but pulling the small cloud table down is cheaper. Override with PRAGMA prefer_local_scan = 'small_cloud_relations'.
• Case 2. Wide projection on a local file with a filter that eliminates 99% — the planner runs everything local, but if the resulting rows will be joined to a cloud fact, sometimes staging the local file to cloud first is faster. Override by explicit CREATE TEMP TABLE cloud_staged AS SELECT * FROM local WHERE ....
• Case 3. Cross-database analytical queries — hybrid execution across two MotherDuck databases (say acme_prod and acme_analytics). Explicit ATTACH order matters; the first-attached database is the default cost anchor.

Question. Design the override strategy for a query where the planner's default pick is measurably slower than the alternative.

Input.

Case	Default plan	Better plan	Override mechanism
Small cloud table + big local	ship local up to cloud	pull cloud down to local	PRAGMA prefer_local_scan
Filter-heavy local, cloud join	run everything local, then cloud join	stage locally-filtered rows to cloud	explicit CREATE TEMP TABLE cloud_staged
Cross-db join	pick first-attached anchor	anchor on larger db	control ATTACH order

Code.

import duckdb, os
con = duckdb.connect(f"md:?motherduck_token={os.environ['MD_TOKEN']}")

# Case 1 — small cloud table + big local file
# Default: planner ships 50M local rows to cloud (expensive)
# Better: pull 10K cloud dim down and join local
con.sql("PRAGMA prefer_local_scan = 'small_cloud_relations'")

con.sql("""
    ATTACH 'md:acme_prod' AS acme_prod (READ_ONLY);

    WITH big_local AS (
        SELECT *
        FROM   read_parquet('/data/staging/events/*.parquet')   -- 50 M rows
    )
    SELECT bl.*, d.tier
    FROM   big_local bl
    JOIN   acme_prod.dim.customer_tier d USING (customer_id)   -- 10 K rows
    WHERE  bl.event_type = 'purchase'
""").show()

# Case 2 — explicit cloud staging for filter-heavy local
con.sql("""
    CREATE TEMP TABLE acme_prod.staged_events AS
    SELECT *
    FROM   read_parquet('/data/staging/events/*.parquet')
    WHERE  event_type = 'purchase'
      AND  event_time >= DATE '2026-07-01'   -- eliminates ~99%
""")
con.sql("""
    SELECT s.customer_id,
           sum(o.total_cents)::DOUBLE / 100 AS revenue_usd
    FROM   acme_prod.staged_events s
    JOIN   acme_prod.fact.orders    o USING (customer_id)
    GROUP  BY s.customer_id
""").show()

# Case 3 — cross-db join; anchor on the larger database
con.sql("ATTACH 'md:acme_prod'      AS acme_prod      (READ_ONLY)")
con.sql("ATTACH 'md:acme_analytics' AS acme_analytics (READ_ONLY)")
con.sql("""
    -- acme_prod is the larger anchor; hint the planner
    SELECT p.customer_id,
           p.total_cents,
           a.propensity_score
    FROM   acme_prod.fact.orders          p
    JOIN   acme_analytics.model.propensity a USING (customer_id)
    WHERE  p.created_at >= DATE '2026-07-20'
""").show()

Step-by-step explanation.

1. Case 1 uses PRAGMA prefer_local_scan to bias the planner toward pulling small cloud relations down rather than shipping large local relations up. The threshold is heuristic — MotherDuck reads the small_cloud_relations hint and pulls tables that fit in a small buffer. Verify with EXPLAIN.
2. Case 2 uses explicit staging. When a local file has a filter that eliminates 99% of rows, staging the filtered rows into a MotherDuck temp table lets the subsequent cloud join run entirely cloud-side. This is faster than the planner's default when the filter cardinality estimate is off.
3. Case 3 uses ATTACH-order to hint at the cost anchor. When joining two cloud databases, the planner treats the first-attached database as the primary; if the second is larger, the analyst should reorder ATTACH statements or use EXPLAIN to verify the plan.
4. In every case, the correct workflow is: (a) run the query and time it, (b) run EXPLAIN ANALYZE and read the plan, (c) if the plan looks wrong, apply the override, (d) re-run and confirm the improvement. Never override blindly.
5. The general rule: MotherDuck's planner is right most of the time. The three canonical overrides above cover 90% of the exceptions. Beyond that, file a support ticket with the EXPLAIN output; MotherDuck engineering is responsive on planner regressions.

Output.

Case	Wall-clock default	Wall-clock with override	Notes
Small cloud + big local	12 s (ship 50 M rows up)	1.8 s (pull 10 K down)	PRAGMA hint fires
Filter-heavy local, cloud join	8 s	2.4 s	staging eliminates cross-boundary transfer
Cross-db join	6 s	3.1 s	anchor on larger db

Rule of thumb. Trust the planner; verify with EXPLAIN ANALYZE on any query you'll run repeatedly. The three canonical overrides — PRAGMA prefer_local_scan, explicit cloud staging, and ATTACH-order for cross-db — cover the vast majority of hybrid-planning surprises. Never override without first reading the plan.

Senior interview question on hybrid execution boundary

A senior interviewer might ask: "You're building a nightly feature-store refresh in a MotherDuck-first stack. The source data is 200 GB of daily event Parquet on S3; the joins target three cloud dimension tables (customer, product, geo) totalling 1.5 GB. The output is a 40 GB feature table you need to persist in MotherDuck. Walk me through the hybrid query, how you'd verify the planner is picking the right engines for each stage, and how you'd guard against planner regressions across DuckDB minor versions."

Solution Using a staged hybrid pipeline with EXPLAIN checkpoints and pinned DuckDB versions

# 1. Pinned client version — the team's requirements file
# requirements.txt
# duckdb==1.1.3         # pin to a known-good MotherDuck-compatible version
# duckdb-motherduck>=0.10.0

import duckdb, os, json, time
assert duckdb.__version__.startswith("1.1"), \
       f"expected duckdb 1.1.x; got {duckdb.__version__}"

con = duckdb.connect(f"md:acme_prod?motherduck_token={os.environ['MD_TOKEN']}")

# 2. Stage the day's S3 Parquet locally (or into MotherDuck if too big)
# — 200 GB of daily events is bigger than a laptop, so route cloud-side
con.sql("""
    CREATE OR REPLACE TABLE acme_prod.staging.events_20260727 AS
    SELECT *
    FROM   read_parquet(
             's3://acme-events/2026/07/27/*.parquet',
             hive_partitioning = TRUE
           )
    WHERE  event_type IN ('purchase', 'view', 'signup')
      AND  event_time >= DATE '2026-07-27'
      AND  event_time <  DATE '2026-07-28'
""")

# 3. Verify the plan for the join stage BEFORE running it
plan = con.sql("""
    EXPLAIN ANALYZE
    SELECT   e.customer_id,
             c.tier,
             p.category,
             g.region,
             count(*)                       AS event_count,
             sum(e.value_cents)::DOUBLE/100 AS revenue_usd
    FROM     acme_prod.staging.events_20260727 e
    JOIN     acme_prod.dim.customer c USING (customer_id)
    JOIN     acme_prod.dim.product  p USING (product_id)
    JOIN     acme_prod.dim.geo      g USING (geo_id)
    GROUP    BY e.customer_id, c.tier, p.category, g.region
""").fetchall()

# The plan output should show CLOUD_SCAN for events + dims,
# HASH_JOIN cloud-side, and the final HASH_GROUP_BY cloud-side.
# If any of them show LOCAL_SCAN, that's a regression — fail hard.
plan_text = "\n".join(row[0] for row in plan)
assert "LOCAL_SCAN" not in plan_text, \
       f"unexpected local scan in feature-refresh plan:\n{plan_text}"

# 4. Materialise the feature table (the plan verified above runs here)
t0 = time.time()
con.sql("""
    CREATE OR REPLACE TABLE acme_prod.feature.user_cohort AS
    SELECT   e.customer_id,
             c.tier,
             p.category,
             g.region,
             count(*)                       AS event_count,
             sum(e.value_cents)::DOUBLE/100 AS revenue_usd
    FROM     acme_prod.staging.events_20260727 e
    JOIN     acme_prod.dim.customer c USING (customer_id)
    JOIN     acme_prod.dim.product  p USING (product_id)
    JOIN     acme_prod.dim.geo      g USING (geo_id)
    GROUP    BY e.customer_id, c.tier, p.category, g.region
""")
print(f"Feature refresh {time.time() - t0:0.1f}s")

# 5. Drop the staging table
con.sql("DROP TABLE acme_prod.staging.events_20260727")

Step-by-step trace.

Step	Component	Behaviour
1. Client pin	duckdb==1.1.3	protects against planner regressions across DuckDB minor releases
2. Stage daily events	S3 → acme_prod.staging.events_20260727	200 GB is too big for the laptop; stage cloud-side
3. EXPLAIN checkpoint	verify plan shows CLOUD_SCAN + cloud HASH_JOIN	fail loudly if any LOCAL_SCAN sneaks in
4. Materialise feature	CREATE TABLE acme_prod.feature.user_cohort AS ...	join + group-by runs cloud-side; only the DDL round-trip crosses the wire
5. Drop staging	one-line cleanup	keeps storage cost bounded

After deployment, the nightly feature refresh completes in ~120 seconds against 200 GB of daily events. The plan verification catches DuckDB minor-version regressions before they turn into 20-minute misplanned laptop-side joins. The persisted acme_prod.feature.user_cohort becomes the read-side for downstream dashboards and models.

Output:

Metric	Before (all-local nightly)	After (hybrid staged)
Wall-clock feature refresh	~35 min (laptop kernel died at 200 GB)	120 s (cloud-side join)
Compute cost	free but not viable	~$0.60 per refresh
Reproducibility	analyst-dependent	scheduled + logged
Planner-regression protection	none	EXPLAIN assertion in every run
Storage delta	40 GB output only	40 GB output only

Why this works — concept by concept:

• Hybrid staging — the 200 GB daily events are too big to touch the laptop, so they stage cloud-side. The join and aggregation then run cloud-side because both inputs are cloud tables; only the DDL round-trip crosses the wire.
• EXPLAIN checkpoint — the plan verification asserts that no LOCAL_SCAN appears in the plan. This is the guardrail against DuckDB minor-version regressions that would silently pull cloud data down to the laptop.
• Pinned client version — duckdb==1.1.3 in the team's requirements file. MotherDuck maintains a compatibility matrix; pinning protects against planner behaviour changing under the team's feet.
• Staging table lifecycle — the staging table lives for one refresh and drops at the end. This keeps storage cost bounded and prevents accidental fan-out where downstream code reads a stale staging table.
• Cost — a nightly ~$0.60 cloud compute cost replaces an all-local pipeline that literally could not run (the laptop kernel died on 200 GB). Net O(cloud-seconds of one refresh) versus O(nightly analyst intervention). Bounded by the daily event volume; monitored in MotherDuck's usage dashboard.

SQL
Topic — joins
Join planning and cardinality-driven SQL problems

Practice →

SQL Topic — aggregation Aggregation and group-by problems for feature pipelines

Practice →

---

3. DuckDB WASM and browser execution

duckdb-wasm is a real analytical engine inside a browser tab — 2 GB heap ceiling, 10-100 M rows comfortable, Parquet over HTTP range

The mental model in one line: duckdb wasm is the DuckDB C++ codebase compiled to WebAssembly, packaged as an npm module (@duckdb/duckdb-wasm), that runs a full analytical query engine inside any browser tab — the same query planner, same aggregation and join operators, same file-format support — subject to browser resource limits (roughly 2 GB heap, 4 GB address space on 32-bit WASM, no direct file-system access) and using HTTP range requests to read Parquet, CSV, JSON, and Arrow files directly from S3, GCS, or any CDN. MotherDuck embraces this heavily: the MotherDuck web UI itself runs DuckDB-WASM for many local operations, and integrations with Hex, Observable, and other browser-first notebooks lean on it for zero-install analytics.

The four axes for DuckDB-WASM.

• Deployment. No install. An npm package (@duckdb/duckdb-wasm) or a <script type="module"> from a CDN. The whole engine ships as ~5-10 MB of WebAssembly plus a small JS wrapper.
• Data access. HTTP range requests. Parquet's row-group format is designed for range reads, so DuckDB-WASM fetches only the row-groups that satisfy the query's filters. CSV and JSON are streamed; Arrow via @apache-arrow/js.
• Memory ceiling. Roughly 2 GB heap on most browsers, closer to 4 GB on desktop with wasm-memory-64 support. Bigger datasets need streaming aggregation or push-down to a cloud engine (i.e. MotherDuck hybrid).
• Concurrency. Single-threaded per WASM instance by default; multi-threaded WASM (SharedArrayBuffer + wasm-threads) is available on modern browsers with proper CORS/CORP headers. Multi-threaded builds run join and aggregation on multiple cores.

The npm module and the CDN loader — the two canonical entry points.

• npm module. npm install @duckdb/duckdb-wasm. Import in a Vite / Next.js / Svelte app; instantiate a worker; call .query(...). This is the pattern for production dashboards and notebook apps.
• CDN loader. <script type="module" src="https://cdn.jsdelivr.net/npm/@duckdb/duckdb-wasm@1.28.0/+esm">. Ideal for prototyping and single-file demos.
• Bootstrap latency. ~1-2 seconds cold-load (WASM download + instantiation); ~200 ms warm-load with service-worker caching.
• State. In-memory only by default; IndexedDB persistence via a small adapter for browser-durable state.

The three failure modes senior engineers pre-empt.

• Memory exhaustion. A query that materialises 200 M rows blows past the 2 GB heap and the tab crashes. Mitigation: use streaming aggregation (GROUP BY early), enforce LIMIT clauses in exploratory queries, and monitor heap with the browser's Memory tab. Datasets > 1 GB should be read incrementally.
• CORS / range-read blocking. Not every bucket serves the Accept-Ranges: bytes header. If the storage layer doesn't allow range requests, DuckDB-WASM falls back to whole-file downloads and OOM-crashes. Mitigation: verify with curl -I -H "Range: bytes=0-1"; configure the bucket to allow the origin and expose the header.
• Multi-tab race conditions. Two tabs opening the same WASM DuckDB against the same IndexedDB backing store race on writes. Mitigation: use SharedWorker to share one DuckDB instance across tabs, or accept that IndexedDB persistence is per-tab.

Common interview probes on DuckDB-WASM.

• "How big can you go in the browser?" — required answer is "~2 GB heap on most browsers; 10-100 M rows comfortable; use streaming and LIMIT beyond that."
• "How does it read from S3?" — HTTP range requests over httpfs; needs CORS + Accept-Ranges: bytes.
• "Is it single- or multi-threaded?" — single by default; wasm-threads build unlocks multi-core with SharedArrayBuffer and proper CORP headers.
• "When would you use DuckDB-WASM vs a cloud engine?" — embedded dashboards, notebooks, prototyping; hand off to MotherDuck cloud for anything > 1 GB or persistent.

Worked example — an in-browser Parquet dashboard

Detailed explanation. The canonical DuckDB-WASM pattern is a static analytics dashboard: a customer wants to see a filterable revenue-by-country chart, backed by a 200 MB Parquet file on S3, without deploying a query server. The dashboard is a single HTML file, loads DuckDB-WASM from a CDN, and runs SQL against the Parquet directly from the browser. Total infrastructure: an S3 bucket with CORS enabled and a CloudFront distribution. Total server cost: cents per month.

• Data. s3://acme-cdn/reports/2026Q2_revenue.parquet — 200 MB Parquet, 8 M rows, columns date, country, revenue_usd, orders.
• UI. A single HTML file with a country dropdown, a date-range picker, and a bar chart.
• Engine. DuckDB-WASM loaded from jsdelivr CDN.
• Latency target. First query < 2 seconds; subsequent queries < 300 ms.

Question. Write the minimum HTML + JS that loads the Parquet and renders a filtered aggregation.

Input.

Component	Value
Parquet URL	https://cdn.acme.com/reports/2026Q2_revenue.parquet
Engine	@duckdb/duckdb-wasm 1.28.0
Bundle strategy	ES module from jsdelivr
Backing worker	eh-mvp (MVP + exception handling)
Query latency target	< 2 s first, < 300 ms warm

Code.

<!doctype html>
<meta charset="utf-8">
<title>Revenue dashboard</title>

<label>Country: <input id="c" value="US"></label>
<button id="go">Run</button>
<pre id="out"></pre>

<script type="module">
  import * as duckdb from "https://cdn.jsdelivr.net/npm/@duckdb/duckdb-wasm@1.28.0/+esm";

  // 1. Pick the right bundle (eh = exception-handling variant)
  const bundle = await duckdb.selectBundle(duckdb.getJsDelivrBundles());
  const worker = await duckdb.createWorker(bundle.mainWorker);
  const logger = new duckdb.ConsoleLogger();
  const db     = new duckdb.AsyncDuckDB(logger, worker);
  await db.instantiate(bundle.mainModule, bundle.pthreadWorker);

  // 2. Register the S3 Parquet URL — DuckDB reads via HTTP range
  await db.registerFileURL(
    "revenue.parquet",
    "https://cdn.acme.com/reports/2026Q2_revenue.parquet",
    duckdb.DuckDBDataProtocol.HTTP,
    false,
  );

  const con = await db.connect();

  // 3. On button click, run the aggregated query
  document.getElementById("go").onclick = async () => {
    const country = document.getElementById("c").value;
    const start = performance.now();
    const res = await con.query(`
      SELECT date_trunc('week', date) AS wk,
             sum(revenue_usd)         AS rev,
             sum(orders)              AS orders
      FROM   revenue.parquet
      WHERE  country = '${country.replace(/'/g, "''")}'
      GROUP  BY wk
      ORDER  BY wk
    `);
    const rows = res.toArray().map(r => r.toJSON());
    document.getElementById("out").textContent =
      `${(performance.now() - start).toFixed(0)} ms\n` +
      JSON.stringify(rows, null, 2);
  };
</script>

Step-by-step explanation.

1. Line 1-8: bootstrap. The CDN loader (+esm) pulls the ES-module build of DuckDB-WASM. selectBundle picks the browser-appropriate variant (eh-mvp for exception handling, eh-mt for multi-threaded when SharedArrayBuffer is available).
2. Line 10-15: the worker + engine instantiation. The engine runs in a dedicated Web Worker so it does not block the main thread. Instantiation is ~1 s cold, dominated by WASM download; subsequent page loads hit the CDN cache and complete in ~200 ms.
3. Line 17-22: registerFileURL tells DuckDB that when SQL references revenue.parquet, it should read via HTTP range requests from the given URL. No data is downloaded at this point; only the Parquet footer is fetched on first query.
4. Line 24-40: on button click, the SQL runs. DuckDB fetches only the Parquet row-groups whose min/max stats cover the filter (country = 'US'), aggregates the matching rows, and returns the result. First run is ~1.5 s (Parquet footer + relevant row-groups); subsequent runs against different countries are ~200-400 ms (row-groups are HTTP-cached).
5. The dashboard has zero server dependencies. All state lives in the browser tab. If the customer bookmarks the URL, the whole thing loads from the CDN, reads directly from S3, and renders locally. Static-site pricing.

Output.

Interaction	Latency	What happened
Initial page load	~1.2 s	WASM download + engine instantiation
First query (country=US)	~1.5 s	Parquet footer + relevant row-groups fetched
Second query (country=UK)	~350 ms	different row-groups, HTTP-cached storage
Third query (country=US, different date)	~180 ms	row-group cache hit

Rule of thumb. DuckDB-WASM is the right choice for dashboards and notebooks whose entire data footprint fits comfortably under ~1 GB and whose latency SLA is "click-and-see" rather than "instant." Anything bigger or with stricter latency belongs behind a cloud engine (MotherDuck cloud or another query service).

Worked example — the memory-ceiling failure mode

Detailed explanation. A team ships a DuckDB-WASM notebook that reads a 400 MB Parquet, runs a SELECT * into a JavaScript array, and passes it to a visualisation library. It works on the developer's laptop and crashes on customer laptops with 8 GB RAM. Walk through the diagnosis and the fix.

• Symptom. Tab crashes with "Aw, Snap!" (Chrome) or "kill 9 duckdb-worker" (Firefox); browser memory graph shoots past 2 GB.
• Root cause. SELECT * on a 400 MB Parquet materialises ~80 M rows in memory; each row is a JS object; each object costs ~100 bytes overhead. Total footprint: ~8 GB.
• Fix. (a) Aggregate in DuckDB, not in JS. (b) Enforce LIMIT on any query that returns raw rows. (c) Use Arrow transfer, not toArray(), for anything > 100 K rows.

Question. Rewrite the crashing pattern into a memory-bounded pattern.

Input.

Component	Before (crashes)	After (bounded)
SQL	SELECT * FROM data	SELECT country, sum(...) GROUP BY country
JS transfer	res.toArray() (JSON objects)	res.toArray() on aggregated rows only
Row cap	none	LIMIT 10000 on exploratory queries
Memory footprint	~8 GB heap	~50 MB heap

Code.

<script type="module">
  import * as duckdb from "https://cdn.jsdelivr.net/npm/@duckdb/duckdb-wasm@1.28.0/+esm";

  const bundle = await duckdb.selectBundle(duckdb.getJsDelivrBundles());
  const worker = await duckdb.createWorker(bundle.mainWorker);
  const db     = new duckdb.AsyncDuckDB(new duckdb.ConsoleLogger(), worker);
  await db.instantiate(bundle.mainModule, bundle.pthreadWorker);
  await db.registerFileURL(
    "events.parquet",
    "https://cdn.acme.com/events.parquet",   // 400 MB
    duckdb.DuckDBDataProtocol.HTTP, false,
  );
  const con = await db.connect();

  // ---------- BAD ------------------------------------------------
  // This crashes tabs on 8 GB laptops.
  // const rows = await con.query("SELECT * FROM events.parquet");
  // renderChart(rows.toArray());               // ~8 GB JS heap

  // ---------- GOOD 1 — aggregate in SQL, not JS -----------------
  const agg = await con.query(`
    SELECT country, count(*) AS n, sum(revenue_usd) AS rev
    FROM   events.parquet
    GROUP  BY country
    ORDER  BY rev DESC
  `);
  renderChart(agg.toArray().map(r => r.toJSON()));   // ~50 rows; trivial

  // ---------- GOOD 2 — LIMIT exploratory queries ----------------
  const sample = await con.query(`
    SELECT *
    FROM   events.parquet
    ORDER  BY event_time DESC
    LIMIT  10000
  `);
  renderTable(sample.toArray());                     // bounded at 10 K rows

  // ---------- GOOD 3 — Arrow transfer for larger data -----------
  // Arrow is a columnar in-memory format; ~10x smaller than JS objects
  const arrow = await con.query(`
    SELECT customer_id, event_type, event_time, revenue_usd
    FROM   events.parquet
    WHERE  event_time >= DATE '2026-07-01'
  `);
  const table = arrow;   // an @apache-arrow/js Table; do not toArray()
  renderChartFromArrow(table);
</script>

Step-by-step explanation.

1. The failure mode is SELECT * returning 80 M rows into a JS array. Each row is a JS object with ~5 fields plus per-object overhead; the total is ~8 GB. This exceeds the browser's tab budget (typically 2-4 GB) and the OS's per-tab budget on older laptops.
2. The fix in the first pattern is to aggregate in SQL: DuckDB does the group-by and returns ~50 rows to JS. The engine stays inside its own C++ / WASM memory (which is separate from the JS heap and much more efficient), and only the result crosses the boundary.
3. The fix in the second pattern is a bounded LIMIT on exploratory queries. Any query the analyst runs against raw rows caps at 10 K; any need for larger data goes through an aggregation.
4. The third pattern uses Apache Arrow transfer. Arrow is a columnar in-memory format that skips the per-JS-object overhead. A million-row Arrow Table is ~10x smaller than a million-row array of JS objects and can be handed directly to visualisation libraries that speak Arrow (Perspective, Observable Plot).
5. The general rule: DuckDB-WASM is fast at anything that stays in its own C++ / WASM memory, but every row that crosses into the JS heap costs 10-100x more memory. Design your SQL to keep aggregation cloud-side (in the WASM engine); only cross the boundary with small results or with Arrow.

Output.

Pattern	Heap footprint	Wall-clock	Verdict
SELECT * → toArray()	~8 GB (crash)	tab dies	never
GROUP BY in SQL → toArray()	~50 MB	1.4 s	preferred for dashboards
LIMIT 10000 + toArray()	~10 MB	400 ms	preferred for exploration
Arrow transfer, no toArray()	~150 MB for 1 M rows	900 ms	preferred for interactive tables

Rule of thumb. Every DuckDB-WASM query must have a bounded output. Either aggregate in SQL (best), LIMIT it (good), or use Arrow transfer (acceptable up to ~1 M rows). Never SELECT * without a LIMIT or aggregation. Enforce this in code review the same way you enforce WHERE clauses in production DELETE statements.

Senior interview question on DuckDB-WASM performance envelope

A senior interviewer might ask: "You're designing an embedded analytics widget that ships inside a SaaS product. Each customer sees their own data — up to 500 MB Parquet per customer, filtered by five dimensions with sub-second latency, no server-side query engine. Walk me through your DuckDB-WASM architecture, how you'd handle the memory ceiling, and how you'd measure whether the p95 latency actually meets the SLA."

Solution Using a DuckDB-WASM widget with per-customer Parquet, streaming aggregation, and Real User Monitoring

<!-- widget.html — shipped as a static asset embedded via <iframe> -->
<!doctype html>
<meta charset="utf-8">
<script type="module">
  import * as duckdb from "https://cdn.jsdelivr.net/npm/@duckdb/duckdb-wasm@1.28.0/+esm";

  // 1. Bootstrap the engine ONCE per widget mount
  const bundle = await duckdb.selectBundle(duckdb.getJsDelivrBundles());
  const worker = await duckdb.createWorker(bundle.mainWorker);
  const db     = new duckdb.AsyncDuckDB(new duckdb.ConsoleLogger(), worker);
  await db.instantiate(bundle.mainModule, bundle.pthreadWorker);
  const con   = await db.connect();

  // 2. Register the per-customer Parquet from a signed URL
  //    (backend mints the signed URL scoped to this customer's data)
  const parquetUrl = new URLSearchParams(location.search).get("data");
  await db.registerFileURL(
    "cust.parquet", parquetUrl,
    duckdb.DuckDBDataProtocol.HTTP, false,
  );
</script>

// 3. filter-and-aggregate handler (called on every UI interaction)
async function refresh({country, tier, category, dateFrom, dateTo}) {
  const t0 = performance.now();

  // Pre-aggregate in SQL — never materialise raw rows in JS
  const params = [country, tier, category, dateFrom, dateTo];
  const res = await con.query(`
    SELECT date_trunc('day', event_time) AS d,
           sum(revenue_usd)              AS rev,
           count(*)                      AS events
    FROM   cust.parquet
    WHERE  country  = ?
      AND  tier     = ?
      AND  category = ?
      AND  event_time BETWEEN ? AND ?
    GROUP  BY d
    ORDER  BY d
  `, params);

  const rows = res.toArray().map(r => r.toJSON());
  const latency = performance.now() - t0;

  // 4. Real User Monitoring — report to the SaaS telemetry pipe
  navigator.sendBeacon("/telemetry/widget", JSON.stringify({
    customer_id: getCustomerId(),
    rows: rows.length,
    latency_ms: latency,
    parquet_size_mb: getParquetSizeMb(),
    query_shape: "revenue_by_day",
    duckdb_version: "1.28.0",
  }));

  renderChart(rows);
}

-- 5. Backend: per-customer Parquet build (nightly)
--    Written as MotherDuck DDL; produces one signed-URL-able Parquet per customer
COPY (
    SELECT customer_id, event_time, country, tier, category,
           revenue_usd, product_id
    FROM   acme_prod.fact.events
    WHERE  customer_id = 42
      AND  event_time  >= now() - INTERVAL '90 days'
) TO 's3://acme-widgets/cust42/2026-07-27.parquet'
  WITH (FORMAT PARQUET, COMPRESSION ZSTD, ROW_GROUP_SIZE 100000);

Step-by-step trace.

Step	Component	Behaviour
1. Widget mount	DuckDB-WASM bootstrap (~1 s cold, ~200 ms warm)	one-shot per widget-lifecycle
2. Register Parquet	HTTP range read; only footer fetched	zero rows materialised yet
3. Filter query	SQL with WHERE + GROUP BY	DuckDB reads only relevant row-groups
4. RUM ping	sendBeacon with latency + rows	server-side p95 dashboard
5. Nightly Parquet build	MotherDuck COPY per customer	ROW_GROUP_SIZE 100 K tunes pushdown granularity

After deployment, each customer opens their SaaS dashboard, DuckDB-WASM bootstraps in ~200 ms (CDN cache hit), and each filter interaction returns ≤ 500 ms at p95 for datasets up to 500 MB. The RUM pipeline aggregates per-customer p95 latency; regressions (e.g. a customer whose Parquet grew to 1 GB) trigger the widget backend to switch that customer to a MotherDuck cloud query.

Output:

Metric	Value	Guardrail
Widget cold-load latency	~1.0 s p95	budget 1.5 s
Widget warm-load latency	~200 ms p95	CDN cache hit
Filter query latency (500 MB Parquet)	380 ms p95	budget 500 ms
Heap footprint per widget	~120 MB p95	budget 500 MB (tab dies at 2 GB)
Server-side query cost	$0 (pure browser)	wins the SaaS gross-margin story

Why this works — concept by concept:

• Per-customer Parquet — each customer's 500 MB Parquet is small enough for DuckDB-WASM's memory ceiling. The Parquet is nightly-built server-side (MotherDuck COPY), so the browser never joins across customers.
• Streaming aggregation — every SQL query includes GROUP BY, so DuckDB aggregates inside its C++ heap and returns ~30-90 rows to JS. Raw rows never cross the boundary.
• Row-group tuning — ROW_GROUP_SIZE 100000 in the Parquet write balances pushdown granularity (smaller row-groups = more precise filter pushdown) against footer size (bigger row-groups = smaller footer). 100 K rows is the sweet spot for dashboard filters.
• Real User Monitoring — sendBeacon reports p95 latency, heap footprint, and row count per query. The server-side dashboard turns "did the widget meet its SLA" into a boring monitoring question rather than a hope.
• Cost — $0 server-side compute per widget query; ~$0.02 per customer per night for the Parquet build (MotherDuck COPY). Compared to a server-side query engine at ~$0.05-0.20 per query, this is a 10-100x cost improvement at the SaaS gross-margin line. Net O(1) server cost per widget request (only the CDN + signed URL).

SQL
Topic — window-functions
Window-function problems for dashboard-style analytics

Practice →

Design Topic — design Design problems for browser-embedded analytics widgets

Practice →

---

4. Team Attach and collaboration

ATTACH 'md:acme_prod' shares a database with three role wedges and time-travel snapshots — one line, whole team

The mental model in one line: MotherDuck's Team Attach is a duckdb ATTACH statement that takes a md: URI plus a role qualifier, and after one line of SQL every teammate has read-only, read-write, or admin access to the same shared database — with per-role RBAC enforced server-side, time-travel snapshots that let any query run AS OF a prior point in time, and a governance ring that surfaces which principals touched which tables — replacing the contract-heavy Snowflake Data Sharing / BigQuery-dataset / Delta Sharing setup with a single grant statement. Every senior data engineer who has spent time provisioning cross-team access on cluster-oriented warehouses recognises the pain point; MotherDuck's answer is closer to "git clone" than "sign a data-share contract."

The four axes for Team Attach.

• Sharing primitive. ATTACH 'md:acme_prod' AS acme_prod (READ_ONLY) — a single statement. No contracts, no cross-account IAM setup, no share-listing / share-consumer dance.
• Role wedges. MotherDuck's RBAC surface offers principal-role, catalog-role, and per-table grants. The canonical wedges are read-only (SELECT only), read-write (SELECT + INSERT + UPDATE), and admin (all + DDL + grants). Roles are managed via the MotherDuck UI and via SQL DDL.
• Time-travel snapshots. Every MotherDuck-persisted table has a versioned change history; AS OF TIMESTAMP '2026-07-27 12:00:00' returns the table as it was at that moment. Snapshot retention is configurable per organisation.
• Governance surface. MotherDuck exposes motherduck_ui.query_history, motherduck_ui.audit_events, and motherduck_ui.grants as SQL-queryable governance tables. Every senior teams' audit story leans on these.

The ATTACH string — the single durable piece of state.

• What it is. md:<database>?motherduck_token=<jwt> — the MotherDuck-native URI scheme. Optional AS <alias> for a local schema name; optional (READ_ONLY | READ_WRITE) qualifier to fence the connection's permissions to the maximum of the token's grants.
• Where the token lives. Environment variable or team secrets manager; token rotates through the MotherDuck web UI when a teammate leaves. Never in code.
• Idempotency. ATTACH is idempotent within a connection; re-running is a no-op.
• Bootstrap. Admin issues CREATE DATABASE acme_prod once; grants roles to teammates; every teammate ATTACHes with their personal token.

The three failure modes senior engineers pre-empt.

• Token leakage. Tokens in notebooks committed to git, or in Slack DMs. Mitigation: environment variables + secrets-manager references, git-hook scanning (trufflehog, git-secrets), token rotation on a schedule, revoke-on-departure runbook.
• Overpermissive default role. If every teammate is added to admin, the least-privilege story collapses. Mitigation: read_only by default; escalate to read_write per person per justified need; admin reserved for the two or three people who provision.
• Snapshot retention over-run. Retaining every second-level snapshot for six months costs storage. Mitigation: configure snapshot retention (hourly for 7 days, daily for 90 days, monthly for the rest) and audit the storage growth quarterly.

Common interview probes on Team Attach.

• "How do teammates share a MotherDuck database?" — required answer is "one ATTACH 'md:db' (READ_ONLY | READ_WRITE) statement per teammate; grants managed centrally."
• "How is this different from Snowflake Data Sharing?" — Snowflake requires a share-provider + share-consumer contract per share; MotherDuck's ATTACH is a single grant.
• "How does time-travel work?" — AS OF TIMESTAMP predicate on any query; snapshot retention configured per org.
• "What if a teammate leaves?" — revoke the token in the MotherDuck UI; the connection dies on next handshake.

Worked example — an analyst attaches a shared prod db from a notebook

Detailed explanation. The canonical Team Attach pattern is a new analyst joining a team. On day one, the analyst installs DuckDB, creates a MotherDuck account (invited by the admin), receives a personal token, and adds the token to their environment. From then on, ATTACH 'md:acme_prod' AS acme_prod (READ_ONLY) gives them the full read side of production analytics with no further ceremony. Walk through the setup.

• Admin side. Creates acme_prod database; creates roles; grants roles to individual users.
• Analyst side. Adds token to env; ATTACHes read-only; queries.
• Governance side. Every query is logged with the analyst's principal; admin can audit and revoke.
• Time-travel. Analyst can query AS OF TIMESTAMP '2026-07-01' to compare current state against a prior snapshot.

Question. Write the SQL and Python that provisions the analyst and confirms their access works.

Input.

Component	Value
Database	acme_prod
Role	analyst_readonly
Principal	[e-mail]
Snapshot query	AS OF TIMESTAMP '2026-07-01 00:00:00'
Token env var	$MOTHERDUCK_TOKEN

Code.

-- Admin side — run once from an admin session
-- 1. Create the role wedge if it doesn't exist
CREATE ROLE IF NOT EXISTS analyst_readonly;

-- 2. Grant the role SELECT on the database
GRANT SELECT ON DATABASE acme_prod TO ROLE analyst_readonly;
GRANT USAGE  ON SCHEMA   acme_prod.fact TO ROLE analyst_readonly;
GRANT USAGE  ON SCHEMA   acme_prod.dim  TO ROLE analyst_readonly;

-- 3. Assign the analyst to the role
GRANT ROLE analyst_readonly TO USER '[e-mail]';

# Analyst side — day-one notebook
import duckdb, os

# The token is provisioned via the MotherDuck web UI and pasted
# into the analyst's env / secrets manager (never in code).
con = duckdb.connect(f"md:?motherduck_token={os.environ['MOTHERDUCK_TOKEN']}")

# 1. ATTACH the shared production database as read-only.
#    The (READ_ONLY) qualifier fences the connection even if the
#    token grants more; belt-and-braces defence.
con.sql("ATTACH 'md:acme_prod' AS acme_prod (READ_ONLY)")

# 2. Confirm access — a simple row-count against a fact table
count = con.sql("SELECT count(*) FROM acme_prod.fact.orders").fetchone()[0]
print(f"acme_prod.fact.orders has {count:,} rows")

# 3. Time-travel query — snapshot as of two weeks ago
snapshot = con.sql("""
    SELECT count(*)
    FROM   acme_prod.fact.orders
    AT     (TIMESTAMP '2026-07-13 00:00:00')
""").fetchone()[0]
print(f"2 weeks ago: {snapshot:,} rows")
print(f"delta this window: {count - snapshot:,} new rows")

# 4. Verify the role wedge — should raise if we try to INSERT
try:
    con.sql("INSERT INTO acme_prod.fact.orders VALUES (0, 0, 0, '', now(), now())")
except duckdb.PermissionException as e:
    print(f"[expected] INSERT denied: {e}")

Step-by-step explanation.

1. Admin side. Create the analyst_readonly role; grant it SELECT on the whole database and USAGE on the relevant schemas. Add each analyst to the role individually. This is a one-time provisioning cost per role type, then per-analyst grants are cheap.
2. Analyst side. ATTACH 'md:acme_prod' AS acme_prod (READ_ONLY) mounts the shared database into the analyst's local DuckDB session as if it were a local schema. All queries thereafter reference acme_prod.fact.orders and the hybrid planner routes cloud-side.
3. Time-travel. The AT (TIMESTAMP '...') clause on any query returns the table as it was at that timestamp. The snapshot retention policy governs how far back the analyst can go; typical retention is 7 days at second-level, 90 days at day-level.
4. Belt-and-braces read-only. Even if the token accidentally grants more, the (READ_ONLY) qualifier on the ATTACH statement fences the connection. If the analyst runs an INSERT, DuckDB raises a PermissionException before any cloud round-trip. This is important for shared notebooks where a stray cell might otherwise mutate production.
5. Governance. Every query the analyst runs is logged with their principal ([e-mail]) in motherduck_ui.query_history. The admin can filter this view to audit access, spot expensive queries, and confirm that the read-only fence is holding.

Output.

Interaction	Result
ATTACH statement	success (idempotent)
SELECT count(*) FROM fact.orders	78,412,301 rows
Time-travel snapshot 2 weeks ago	74,829,101 rows
Delta this window	3,583,200 new rows
Attempted INSERT	PermissionException — read-only

Rule of thumb. Provision Team Attach with read-only by default, escalate to read-write only for people who write, keep admin to two or three people. The (READ_ONLY) qualifier on ATTACH is a defense-in-depth even when the token itself is scoped correctly — always include it in shared notebooks.

Worked example — the RBAC + snapshot governance surface

Detailed explanation. The senior audit story is: "show me who queried the customer PII table this month, what they returned, and whether any of them time-travelled to an older snapshot." MotherDuck exposes this via SQL-queryable governance views. Walk through the queries an admin runs monthly.

• Query history. motherduck_ui.query_history — every query with principal, wall-clock, bytes-scanned, and the query text.
• Audit events. motherduck_ui.audit_events — role grants, ATTACH events, token issue / revoke.
• Grants view. motherduck_ui.grants — current effective grant map per principal per object.
• Snapshot lineage. motherduck_ui.snapshots — one row per snapshot with created_at + retention_expires_at.

Question. Design the four monthly audit queries and describe what each protects against.

Input.

Concern	Query target	What we assert
Who touched PII?	query_history filtered by object_name LIKE '%.pii.%'	no unexpected principals
Least-privilege drift	grants where role_name = 'admin'	admin set has not grown
Token hygiene	audit_events where event_type IN ('token_issued','token_revoked')	rotations happen on cadence
Snapshot cost	snapshots grouped by table + storage_bytes	retention policy is holding

Code.

-- Admin session; one query per audit concern

-- 1. Who touched the customer PII table this month
SELECT   principal,
         count(*)                          AS queries,
         sum(bytes_scanned) / 1024.0/1024/1024 AS gb_scanned,
         min(start_time)                   AS first_query,
         max(start_time)                   AS last_query
FROM     motherduck_ui.query_history
WHERE    start_time >= date_trunc('month', now())
  AND    query_text ILIKE '%acme_prod.pii.%'
GROUP BY principal
ORDER BY queries DESC;

-- 2. Least-privilege drift — how many principals have admin
SELECT   principal,
         min(granted_at) AS became_admin,
         count(*)        AS admin_grants
FROM     motherduck_ui.grants
WHERE    role_name = 'admin'
GROUP BY principal
ORDER BY became_admin;

-- 3. Token hygiene — one row per token event
SELECT   principal,
         event_type,           -- 'token_issued' or 'token_revoked'
         event_time,
         source_ip
FROM     motherduck_ui.audit_events
WHERE    event_type IN ('token_issued', 'token_revoked')
  AND    event_time >= now() - INTERVAL '90 days'
ORDER BY event_time DESC;

-- 4. Snapshot storage cost per table
SELECT   database_name,
         schema_name,
         table_name,
         count(*)                                    AS snapshot_count,
         sum(storage_bytes) / 1024.0/1024/1024       AS gb,
         min(created_at)                             AS oldest,
         max(created_at)                             AS newest
FROM     motherduck_ui.snapshots
WHERE    retention_expires_at > now()
GROUP BY database_name, schema_name, table_name
ORDER BY gb DESC
LIMIT    50;

Step-by-step explanation.

1. Query 1 answers the PII audit question. The ILIKE '%acme_prod.pii.%' catches any query text that references the PII schema; the aggregation groups by principal so an admin sees a leaderboard of PII-touching queries per person per month.
2. Query 2 detects role sprawl. If admin count grows from three to seven, someone provisioned admin instead of read-write; the audit catches this. The min(granted_at) shows how long each principal has been admin — a useful signal for periodic review.
3. Query 3 verifies token rotation cadence. If any long-lived tokens have been issued but not rotated in > 90 days, they are candidates for revoke-and-reissue. The source_ip column can flag issuance from unexpected geographies.
4. Query 4 catches snapshot cost creep. If a specific table's snapshots consume disproportionate storage (e.g. a table that gets rewritten every hour will accumulate many snapshots), the admin can tighten retention for that specific table or move it to a hot / cold snapshot tier.
5. All four queries run against motherduck_ui.* — server-side views that MotherDuck exposes for exactly this use case. There is no data-out; the audit runs inside MotherDuck. Schedule them monthly via a MotherDuck-native scheduled task or a small cron job that pipes the result to the team's SIEM.

Output.

Concern	Signal	Action if trigger
PII access leaderboard	new principal in top-5	revoke access + investigate
admin role count	> 5 principals	audit each and downgrade to read-write
Token issuance without matching revoke	tokens > 90 days old	schedule rotation
Snapshot storage per table	> 10 GB per non-critical table	tighten retention

Rule of thumb. Every MotherDuck deployment with more than five teammates needs a monthly audit that answers PII access, role sprawl, token hygiene, and snapshot cost. The four queries above run against motherduck_ui.* in one script; schedule them and pipe the result to the team's audit log or SIEM. Do not wait for compliance season to build this pipeline.

Senior interview question on Team Attach + governance

A senior interviewer might ask: "You're rolling out MotherDuck to a 30-person analytics team that previously shared data via Snowflake Data Sharing. The security team requires PII isolation, quarterly access reviews, and time-travel-based reproducibility for the finance close. Walk me through the Team Attach RBAC model you'd implement, the governance queries you'd schedule, and how you'd handle the migration from Snowflake shares without breaking downstream dashboards during the cut-over."

Solution Using role-wedged ATTACH + governance queries + a phased dual-write cutover

-- 1. Provision the role wedges up front
CREATE ROLE IF NOT EXISTS analyst_readonly;
CREATE ROLE IF NOT EXISTS analyst_readwrite;
CREATE ROLE IF NOT EXISTS analyst_admin;
CREATE ROLE IF NOT EXISTS finance_readonly;    -- separate wedge for close reproducibility

-- 2. Fence PII schemas explicitly
GRANT SELECT ON SCHEMA acme_prod.pii  TO ROLE analyst_admin;
REVOKE SELECT ON SCHEMA acme_prod.pii FROM ROLE analyst_readonly;
REVOKE SELECT ON SCHEMA acme_prod.pii FROM ROLE analyst_readwrite;

-- 3. Grant the non-PII surface to the read tiers
GRANT SELECT ON SCHEMA acme_prod.fact TO ROLE analyst_readonly, analyst_readwrite, finance_readonly;
GRANT SELECT ON SCHEMA acme_prod.dim  TO ROLE analyst_readonly, analyst_readwrite, finance_readonly;

-- 4. Finance role — access to the reproducibility snapshots
GRANT SELECT ON SCHEMA acme_prod.finance_close TO ROLE finance_readonly;

# 5. Dual-write during the migration window (~2 weeks)
#    The nightly ETL writes to BOTH Snowflake and MotherDuck so
#    downstream dashboards can flip their connection string one
#    at a time without a big-bang cut-over.

import duckdb, snowflake.connector as sf, os
md = duckdb.connect(f"md:acme_prod?motherduck_token={os.environ['MD_TOKEN']}")
sf_con = sf.connect(user=os.environ['SF_USER'], password=os.environ['SF_PWD'],
                    account=os.environ['SF_ACCOUNT'], warehouse='ETL_WH')

# Read from the canonical source (Postgres via CDC-derived Parquet on S3)
md.sql("""
    CREATE OR REPLACE TABLE acme_prod.fact.orders AS
    SELECT * FROM read_parquet('s3://acme-cdc/orders/2026/07/27/*.parquet')
""")

# And mirror to Snowflake for the transition window
sf_con.cursor().execute("""
    COPY INTO analytics.fact.orders
    FROM  @acme_cdc/orders/2026/07/27/
    FILE_FORMAT = (TYPE = PARQUET)
    ON_ERROR = CONTINUE;
""")

-- 6. Schedule the four monthly audit queries as a MotherDuck task
CREATE OR REPLACE TASK monthly_audit
    SCHEDULE = 'USING CRON 0 9 1 * *'          -- 09:00 on the 1st
    AS
    -- PII access leaderboard, admin role sprawl, token hygiene,
    -- snapshot cost — piped to the audit-log topic
    COPY (
        SELECT 'pii_access' AS check_name, principal, count(*) AS queries
        FROM   motherduck_ui.query_history
        WHERE  start_time >= date_trunc('month', now() - INTERVAL '1 month')
          AND  start_time <  date_trunc('month', now())
          AND  query_text ILIKE '%acme_prod.pii.%'
        GROUP  BY principal
    ) TO 's3://acme-audit/motherduck/monthly-{{ current_date }}.parquet'
    WITH (FORMAT PARQUET);

-- 7. Retention policy — hourly snapshots for 7d, daily for 90d, monthly for 2y
ALTER DATABASE acme_prod SET SNAPSHOT_RETENTION = '
    HOURLY 7 DAYS,
    DAILY  90 DAYS,
    MONTHLY 730 DAYS
';

Step-by-step trace.

Step	Component	Guardrail
1. Role wedges	analyst_readonly / readwrite / admin / finance_readonly	least privilege by default
2. PII fence	REVOKE from all except admin	PII isolation
3. Non-PII grants	fact / dim schemas to read tiers	analysts unblocked
4. Finance wedge	finance_close schema, separate role	reproducibility isolated
5. Dual-write window	MotherDuck + Snowflake for ~2 weeks	zero-downtime dashboard cut-over
6. Monthly audit task	schedule on the 1st @ 09:00	PII access + role sprawl telemetry
7. Snapshot retention	tiered (hourly / daily / monthly)	reproducibility + bounded storage

After deployment, every analyst joins the team with analyst_readonly by default. Escalation to analyst_readwrite requires a role-grant PR reviewed by an admin. The dual-write window lets each downstream dashboard flip its connection string from Snowflake to MotherDuck on its own timeline; after two weeks, the Snowflake mirror is decommissioned. Monthly audits run automatically and drop a Parquet into s3://acme-audit/motherduck/ for the SIEM to ingest.

Output:

Concern	Mechanism	Post-deployment state
PII isolation	schema-level GRANT/REVOKE	non-admin roles cannot SELECT pii.*
Access review	monthly SQL job	audit Parquet in s3://acme-audit/
Zero-downtime cutover	2-week dual-write	dashboards flip one at a time
Finance close reproducibility	finance_close schema + snapshot retention	AT (TIMESTAMP ...) always resolvable within 2 years
Least privilege	analyst_readonly default	escalation requires PR + admin review

Why this works — concept by concept:

• Role wedges — three canonical wedges (readonly, readwrite, admin) plus one workload-specific wedge (finance_readonly) collapse the RBAC surface into something a 30-person team can reason about. Fewer wedges = fewer sprawl bugs.
• PII schema fence — explicit REVOKE from every non-admin role makes PII isolation a schema-level property, not an application-level convention. Any query that touches acme_prod.pii.* from a non-admin role fails at the engine before any cloud round-trip.
• Dual-write cutover — the two-week window where both warehouses mirror the same nightly ETL is what makes the migration risk-bounded. If any dashboard breaks on the MotherDuck side, its owner rolls back to Snowflake in one line; if none break, Snowflake decommissions after two weeks.
• Scheduled audit task — the monthly SQL job runs inside MotherDuck (CREATE TASK) and drops results as Parquet for the SIEM. This makes the audit a boring monthly job rather than a scramble at compliance season.
• Cost — one Team Attach license per teammate (typically bundled in the pro tier), one shared MotherDuck database, one dual-write window during migration. Compared to Snowflake Data Sharing's per-share-consumer contract overhead, this is O(1) provisioning per teammate versus O(N) contract review per share. Snapshot storage is bounded by the tiered retention policy; audit storage is bounded by the SIEM's own retention.

Design
Topic — design
Design problems on RBAC and multi-tenant data access

Practice →

SQL Topic — sql SQL problems on time-travel and snapshot-aware analytics

Practice →

---

5. When MotherDuck wins and interview signals

motherduck vs snowflake and motherduck vs bigquery — pick per workload, not per vendor

The mental model in one line: there is no single "best warehouse" in 2026 — there is motherduck for < 1 TB team analytics with notebook-first workflows and hybrid laptop-cloud execution, snowflake for enterprise governance and multi-workload petabyte scale, bigquery for Google-Cloud-native serverless petabyte analytics, and databricks for ML plus Delta / Unity lakehouse — and the senior architect's job is to pick the vendor per workload, not the vendor per company, so the small-data notebook wedge ends up on MotherDuck while the petabyte-scale enterprise warehouse sits on whichever of the big three matches the org's cloud and governance model. Every senior data engineer who has spent the past decade watching companies over-fit one warehouse to every workload recognises the pattern; MotherDuck's arrival gives you a real second option for the small-data wedge without giving up cloud persistence.

The four axes for vendor selection in 2026.

• Scale. MotherDuck wins < 1 TB per database and does multi-TB via hybrid; Snowflake / BigQuery / Databricks all handle petabyte with tuning. The cliff is not sharp — it's a curve that bends around 1-5 TB where cluster-oriented engines start to earn their cluster-startup tax.
• Cost floor per query. MotherDuck is per-second billing with a near-zero floor. Snowflake has cluster-second billing with a minimum warm-up; BigQuery has on-demand slot-time or reserved slots; Databricks has DBU-per-cluster-second. For ad-hoc notebook queries, MotherDuck is often 10-100x cheaper on the same workload.
• Latency (cold + warm). MotherDuck: milliseconds local, sub-second cloud, no cluster start-up. Snowflake: 1-10 s cluster warm-up + seconds of query; BigQuery: 1-3 s slot-driven; Databricks: 5-30 s cluster warm-up.
• Team ergonomics. MotherDuck: pip install duckdb + one ATTACH string; Snowflake: JDBC / connector + role management + warehouse selection; BigQuery: client SDK + GCP IAM; Databricks: notebook + cluster attachment.

The workload-anatomy strip — pick per workload, not per vendor.

• Laptop notebook (exploratory). MotherDuck wins. Sub-second local + hybrid; per-second cost floor.
• Team dashboard (< 1 TB, < 100 concurrent users). MotherDuck usually wins. Team Attach + snapshot-aware.
• Warehouse ETL (nightly, 1-10 TB, orchestrated). Snowflake / BigQuery win on cluster efficiency for very large batches. MotherDuck viable up to ~5 TB.
• Petabyte lakehouse + ML. Databricks wins. Delta + Unity Catalog + MLflow are one integrated stack.
• Streaming analytics with < 1 s latency. All four are second-tier — real-time belongs on Materialize, RisingWave, or a purpose-built engine.

What interviewers listen for.

• Do you refuse to pick one vendor for the whole company and instead pick per workload? — senior signal.
• Do you name the < 1 TB wedge as where MotherDuck genuinely wins? — required answer.
• Do you name the cluster-startup tax as the Snowflake / Databricks weakness for ad-hoc queries? — senior signal.
• Do you name Snowflake / BigQuery / Databricks as the winners for their respective sweet spots without denigrating them? — required answer.
• Do you say "pick per workload" as your closing sentence? — senior signal.

Worked example — the decision matrix table interviewers grade against

Detailed explanation. The senior warehouse-selection interview grades against a 4x5 decision matrix — four vendors, five workload types. Every senior architect has this table in their head; drawing it on a whiteboard within the first two minutes of the interview is what a hire signal looks like. Walk through building the matrix.

• Vendors. MotherDuck, Snowflake, BigQuery, Databricks.
• Workload types. Laptop notebook, team dashboard, warehouse ETL, petabyte lakehouse, streaming analytics.
• Rating scale. 1-5 dots per cell — 5 dots = best in class, 1 dot = viable but not preferred.

Question. Build the 4x5 decision matrix and rate each cell.

Input.

Workload / Vendor	MotherDuck	Snowflake	BigQuery	Databricks
Laptop notebook (exploratory)	5	2	2	2
Team dashboard (< 1 TB)	5	4	4	3
Warehouse ETL (1-10 TB)	3	5	5	4
Petabyte lakehouse	1	4	5	5
Streaming analytics	2	3	3	3

Code.

The decision matrix on a whiteboard (2026)
==========================================

                    | MotherDuck | Snowflake | BigQuery | Databricks
--------------------|------------|-----------|----------|-----------
Laptop notebook     |    *****   |    **     |    **    |    **
Team dashboard <1TB |    *****   |    ****   |    ****  |    ***
Warehouse ETL 1-10T |    ***     |    *****  |    ***** |    ****
Petabyte lakehouse  |    *       |    ****   |    ***** |    *****
Streaming analytics |    **      |    ***    |    ***   |    ***

Legend
------
*****  = best in class for this workload
****   = strong; usually the pragmatic choice
***    = viable; not preferred but works
**     = works with effort; consider alternatives
*      = not the right tool

Notes
-----
- Laptop notebook: cluster-startup tax makes the big three feel
  slow for exploratory work; MotherDuck's per-second billing
  and hybrid execution win.
- Team dashboard: MotherDuck wins < 1 TB; Snowflake/BQ close the
  gap at higher scale.
- Warehouse ETL: Snowflake/BQ's cluster efficiency wins at
  higher scale; MotherDuck viable up to ~5 TB.
- Petabyte: Databricks + Snowflake + BigQuery own this; MotherDuck
  not designed for it.
- Streaming: all four are second-tier; real-time belongs on
  a streaming-first engine.

Step-by-step explanation.

1. The matrix forces a per-workload decision. There is no "which one is best" answer; there is only "which one wins each row." Any interviewer who insists on a single vendor answer is asking a bad question, and pushing back gracefully is a senior signal.
2. MotherDuck's laptop-notebook and small-team-dashboard cells are five-star because its design centre is exactly those workloads. It is not a defect that MotherDuck is one-star at petabyte lakehouse — no product wins every cell, and pretending otherwise is where architects lose credibility.
3. Snowflake wins the warehouse-ETL cell alongside BigQuery because both are optimised for scheduled, orchestrated, mid-large workloads where cluster efficiency matters more than cluster-startup latency. Databricks is a strong four-star because Delta + Photon compete but the notebook-first model is heavier.
4. BigQuery and Databricks tie at petabyte lakehouse because both have deep GCP-native or Delta-native integrations that pay off at scale. Snowflake is a strong four-star because it is a serious competitor but the cost model at petabyte requires more tuning.
5. The streaming row is honestly mediocre for all four — none of them are streaming-first. This is a senior signal too: naming that no warehouse wins the sub-second latency row and pointing at Materialize / RisingWave / a purpose-built engine is what real-time-aware architects sound like.

Output.

Workload	Recommended vendor	Score
Laptop notebook	MotherDuck	5
Team dashboard < 1 TB	MotherDuck	5
Warehouse ETL 1-10 TB	Snowflake or BigQuery	5
Petabyte lakehouse	BigQuery or Databricks	5
Streaming analytics	Materialize / RisingWave (not any of the four)	—

Rule of thumb. Draw the 4x5 matrix on the whiteboard within the first two minutes of any warehouse-selection interview. Score each cell, name the winner per row, and refuse to pick a single vendor for the whole matrix. This is what senior warehouse architecture looks like in 2026.

Worked example — the migration probe interviewers use to catch shallow answers

Detailed explanation. After you build the decision matrix, the interviewer will probe with a migration question: "You have a team on Snowflake — walk me through when you'd migrate to MotherDuck and when you wouldn't." This is the test of whether you understand the workload boundaries. Walk through the migration decision tree.

• Q1. Is the largest table > 5 TB? → yes = stay on Snowflake; no = go to Q2.
• Q2. Are ad-hoc notebook queries a significant fraction of usage (> 30%)? → yes = MotherDuck wins; no = go to Q3.
• Q3. Does the team share data via Snowflake Data Sharing extensively? → yes = MotherDuck Team Attach is a real UX upgrade; migrate; no = go to Q4.
• Q4. Is the current Snowflake bill > $2K/month? → yes = MotherDuck likely 50-80% cheaper for the same workload; migrate; no = stay.
• Q5 (parallel branch). Does the team need MotherDuck's hybrid execution for laptop + cloud queries? → yes = migrate the ad-hoc portion; keep production ETL on Snowflake if it works.

Question. Walk the decision tree for three scenarios and record the recommendation.

Input.

Scenario	Largest table	Ad-hoc %	Sharing	Bill	Recommendation
Small-team startup on Snowflake	400 GB	70%	some	$2.5K	migrate
Mid-market on Snowflake	3 TB	40%	heavy	$12K	hybrid: migrate ad-hoc
Enterprise on Snowflake	50 TB	15%	heavy	$200K	stay

Code.

def recommend_warehouse(largest_table_tb, adhoc_pct, uses_data_sharing, monthly_bill_usd):
    """Return the migration recommendation for a Snowflake team."""
    if largest_table_tb > 5:
        return "stay on Snowflake — largest table beyond MotherDuck's sweet spot"

    if adhoc_pct > 30:
        return "migrate to MotherDuck — ad-hoc workload is where MD wins hardest"

    if uses_data_sharing:
        return "migrate to MotherDuck — Team Attach is a real UX upgrade"

    if monthly_bill_usd > 2000:
        return "migrate to MotherDuck — cost-driven win"

    return "stay on Snowflake — no compelling reason to move"


# Walk the three scenarios
print(recommend_warehouse(0.4, 70, True,  2500))
# → 'migrate to MotherDuck — ad-hoc workload is where MD wins hardest'

print(recommend_warehouse(3,   40, True,  12000))
# → 'migrate to MotherDuck — ad-hoc workload is where MD wins hardest'
# (but in practice you'd hybridise: MD for ad-hoc, keep ETL on SF)

print(recommend_warehouse(50,  15, True, 200000))
# → 'stay on Snowflake — largest table beyond MotherDuck's sweet spot'

Step-by-step explanation.

1. Scenario 1 — a small-team startup with a 400 GB largest table, 70% ad-hoc, some sharing, $2.5K bill. The decision tree short-circuits at Q1 → largest table under 5 TB, then Q2 → ad-hoc dominant. Recommendation: migrate. Expected saving 60-80%.
2. Scenario 2 — mid-market on Snowflake with a 3 TB table, 40% ad-hoc, heavy sharing, $12K bill. Q1 pass, Q2 pass. Full migration is possible, but in practice you would hybridise: ad-hoc + team dashboards on MotherDuck (where it wins hardest), keep the 3 TB nightly ETL on Snowflake for another quarter until you have telemetry that MotherDuck can hold the ETL performance too.
3. Scenario 3 — enterprise on Snowflake with a 50 TB largest table, 15% ad-hoc, heavy sharing, $200K bill. Q1 fails — 50 TB is beyond MotherDuck's sweet spot. Stay on Snowflake. This is the answer that separates disciplined architects from evangelists.
4. The parallel Q5 branch is often the right answer in the middle scenarios: migrate the ad-hoc portion (where MotherDuck wins hardest) while keeping the petabyte / large-batch portion on the incumbent. Hybrid vendor mixes are increasingly the norm; senior architects design for them.
5. The general rule: MotherDuck is not a total replacement for the big three. It is a first-class option for the < 1 TB team-analytics wedge, and its arrival gives every senior architect a legitimate second choice for that workload. Migration decisions should be workload-by-workload, not company-by-company.

Output.

Scenario	Primary recommendation	Add-on
Small-team startup	migrate to MotherDuck	—
Mid-market	hybrid: MotherDuck for ad-hoc, keep SF for ETL	reevaluate in 1 quarter
Enterprise	stay on Snowflake	—

Rule of thumb. The migration decision tree is a whiteboard-friendly answer. Practise walking it end-to-end so an interviewer can hand you any scenario and get a recommendation in under 60 seconds. Refusing to migrate when the workload doesn't fit is as important as recommending migration when it does — both show the interviewer that you understand the boundaries.

Senior interview question on MotherDuck vs Snowflake

A senior interviewer often opens with: "Your CTO asks whether the company should move off Snowflake to MotherDuck to cut costs. The company has 45 data engineers, three product teams doing analytics, a 15 TB petabyte-ambitious data lake plan, and a Snowflake bill of $180K/month split roughly 50/50 between scheduled ETL and interactive analytics. Walk me through your evaluation framework, the workloads you would migrate, the workloads you would keep, and the risk analysis you'd hand to the CTO."

Solution Using a workload-fenced hybrid vendor strategy with quarterly reevaluation

# 1. Segment the current spend by workload class
# Query Snowflake's ACCOUNT_USAGE for the last 90 days
sf_sql = """
    WITH tagged AS (
        SELECT  query_tag,
                warehouse_name,
                credits_used,
                execution_time,
                bytes_scanned,
                CASE
                    WHEN query_tag ILIKE 'dbt-%'       THEN 'scheduled_etl'
                    WHEN query_tag ILIKE 'dashboard-%' THEN 'dashboard'
                    WHEN query_tag ILIKE 'notebook-%'  THEN 'adhoc_notebook'
                    WHEN warehouse_name ILIKE '%ML%'   THEN 'ml_workload'
                    ELSE 'unknown'
                END AS workload_class
        FROM    snowflake.account_usage.query_history
        WHERE   start_time >= dateadd('day', -90, current_timestamp())
    )
    SELECT   workload_class,
             sum(credits_used) * 3.0 AS approx_usd,
             count(*)                AS query_count,
             avg(execution_time)/1000 AS avg_seconds,
             max(bytes_scanned)/1024/1024/1024 AS max_gb_scan
    FROM     tagged
    GROUP    BY workload_class
    ORDER    BY approx_usd DESC;
"""
# Expected output (illustrative):
#   scheduled_etl   $110K   35%
#   dashboard        $30K   10%
#   adhoc_notebook   $30K   50%   ← MotherDuck sweet spot
#   ml_workload      $10K    5%

# 2. Migration plan — vendor per workload
migration_plan:
  keep_on_snowflake:
    - scheduled_etl (15 TB nightly, cluster efficiency)
    - dashboard (100s QPS SLA, existing tuning)
  migrate_to_motherduck:
    - adhoc_notebook (50% of query count, 17% of spend)
    - team collaboration surface (Team Attach)
  evaluate_next_quarter:
    - dashboard (potentially MotherDuck if cost improves further)
  do_not_migrate:
    - ml_workload (Databricks candidate, not MotherDuck)

# 3. Dual-write cutover during the 2-week transition
def dual_write_orders(row_batch):
    """Write to both warehouses during the migration window."""
    # MotherDuck (fast path)
    md_con.executemany(
        "INSERT INTO acme_prod.fact.orders VALUES (?,?,?,?,?,?)",
        row_batch,
    )
    # Snowflake (safety mirror)
    sf_con.cursor().executemany(
        "INSERT INTO analytics.fact.orders VALUES (%s,%s,%s,%s,%s,%s)",
        row_batch,
    )

# 4. Cost-telemetry dashboard — track savings vs plan
md_con.sql("""
    CREATE OR REPLACE TABLE ops.savings_tracker AS
    SELECT   date_trunc('week', start_time)              AS wk,
             sum(bytes_scanned)/1024/1024/1024*0.003     AS md_cost_usd,
             (SELECT sum(...) FROM sf_credits WHERE ...) AS sf_cost_usd,
             (SELECT sum(...) FROM sf_credits_saved)     AS savings_usd
    FROM     motherduck_ui.query_history
    WHERE    start_time >= DATE '2026-07-27'
    GROUP    BY wk
""")

# 5. Risk analysis handed to the CTO
Migration risks and mitigations
================================

Risk: MotherDuck cannot handle ad-hoc queries at team scale
Mitigation: Pilot with 5 analysts for 4 weeks; measure p95 latency and cost
Owner: DE-team-lead
Trigger: p95 latency > 3x Snowflake → abort migration for that workload

Risk: MotherDuck outage takes ad-hoc analytics offline
Mitigation: 2-week dual-write window; snapshot backup daily
Owner: SRE
Trigger: > 30 min outage → cut back to Snowflake mirror

Risk: Team Attach RBAC does not map cleanly to Snowflake role model
Mitigation: RBAC audit in week 1; map role-by-role before cut-over
Owner: security
Trigger: any PII exposure → immediate rollback

Risk: Cost savings do not materialize
Mitigation: weekly cost-tracker dashboard; commit to abort if
            savings < 30% at end of month 1
Owner: FinOps
Trigger: savings < 30% for 4 weeks → revert

Step-by-step trace.

Step	Component	Outcome
1. Workload segmentation	ACCOUNT_USAGE query with 4 classes	scheduled_etl 35%, dashboard 10%, adhoc 50%(!), ml 5%
2. Migration plan	vendor-per-workload matrix	migrate adhoc + Team Attach; keep ETL + dashboard on SF
3. Dual-write cutover	2-week transition	zero-downtime; safety mirror on SF
4. Cost telemetry	weekly savings tracker	trigger abort if < 30% saving
5. Risk analysis	4 named risks + triggers	CTO sees the abort criteria up front

After deployment, ad-hoc analytics move to MotherDuck within a two-week dual-write window; scheduled ETL and the public dashboard stay on Snowflake. Cost telemetry shows a 60-70% reduction on the migrated workload class within the first month; total company spend drops from $180K to roughly $130K. The dual-write mirror stays hot for one additional month as insurance, then decommissions. The petabyte data-lake plan reroutes to Databricks + Delta as originally intended — MotherDuck is not the right tool for that workload.

Output:

Metric	Before ($180K/mo Snowflake only)	After (MotherDuck + Snowflake + Databricks)
Total monthly spend	$180K	~$130K
Ad-hoc query p95 latency	8-12 s (cluster warm-up)	400-800 ms
Team sharing UX	Snowflake shares + role grants	one ATTACH string per team
Scheduled ETL latency	unchanged	unchanged
Dashboard 100s QPS SLA	met	met
ML workload	Snowflake / Snowpark	Databricks + Delta (planned)

Why this works — concept by concept:

• Workload fencing — the migration succeeds because it does not pretend one vendor fits every workload. Ad-hoc + Team Attach move to MotherDuck; ETL + dashboard stay on Snowflake; ML routes to Databricks. This is the senior discipline that separates a workload-aware migration from a vendor-loyalty rewrite.
• Cost telemetry with an abort trigger — the weekly savings dashboard converts the migration into a data-driven decision. If savings fall below 30% for four weeks, the migration reverts. This is the discipline the CTO wants to see; it removes hopeful-thinking risk.
• Dual-write safety mirror — the two-week window where both warehouses mirror the same nightly ETL is the risk-bounded cut-over pattern. If any dashboard breaks on the MotherDuck side, its owner rolls back in one line; if none break, Snowflake decommissions on schedule.
• Vendor-per-workload matrix — the migration explicitly names three vendors for three workload classes rather than trying to unify on one. This is the operational reality of a 2026 warehouse strategy at a company this size.
• Cost — total company spend from $180K/mo to ~$130K/mo (roughly 28% saving), concentrated on the ad-hoc workload class that MotherDuck wins hardest. The scheduled ETL and dashboard workloads stay on Snowflake at unchanged cost. Net O(migrated-workload-cost * (1 - MotherDuck-per-query-ratio)) per month; monitored weekly with the FinOps-owned tracker.

Design
Topic — design
Warehouse-selection and vendor-choice design problems

Practice →

SQL
Topic — sql
SQL problems for warehouse-agnostic analytical patterns

Practice →

---

Cheat sheet — MotherDuck recipes

• Which vendor when — the two-line rule. MotherDuck for < 1 TB team-analytics wedges (laptop notebooks, small team dashboards, exploratory SQL) plus the hybrid laptop-cloud pattern where the same query joins a local file to a cloud fact table. Snowflake for enterprise governance and cluster-efficient warehouse ETL on 1-TB-to-petabyte workloads. BigQuery for GCP-native serverless petabyte analytics. Databricks for ML + Delta / Unity lakehouse workloads that mix SQL and Python at scale. Print this rule; every senior warehouse-selection interview grades against it.
• ATTACH connection-string template. ATTACH 'md:<database>?motherduck_token=<jwt>' AS <alias> (READ_ONLY|READ_WRITE); — the token lives in $MOTHERDUCK_TOKEN (never in code), the alias is optional but recommended (AS acme_prod gives you acme_prod.schema.table in local SQL), and the (READ_ONLY) qualifier is a belt-and-braces defence for shared notebooks. Idempotent within a connection; re-running is a no-op. Reset on kernel restart.
• Hybrid push-down / pull-up decision matrix. Local scans: read_parquet('s3://...'), read_csv('...'), in-memory DataFrames, local .duckdb tables. Cloud scans: md:db.schema.table and any table under an ATTACHed database. Cross-boundary joins ship the smaller side to the larger engine. Override with PRAGMA prefer_local_scan = 'small_cloud_relations' when the planner is wrong; verify with EXPLAIN ANALYZE; always inspect the plan for any query you'll run repeatedly. The plan should show CLOUD_SCAN and LOCAL_SCAN per relation.
• DuckDB-WASM npm boilerplate. npm install @duckdb/duckdb-wasm; const bundle = await duckdb.selectBundle(duckdb.getJsDelivrBundles()); const worker = await duckdb.createWorker(bundle.mainWorker); const db = new duckdb.AsyncDuckDB(new duckdb.ConsoleLogger(), worker); await db.instantiate(bundle.mainModule, bundle.pthreadWorker);. Then await db.registerFileURL('data.parquet', 'https://cdn/.../data.parquet', duckdb.DuckDBDataProtocol.HTTP, false); and finally const con = await db.connect(); const res = await con.query(sql);. Bootstrap ~1 s cold, ~200 ms warm; 2 GB heap ceiling.
• DuckDB-WASM memory-ceiling rules. Never SELECT * FROM x without aggregation or a LIMIT. Aggregate in SQL (GROUP BY early); enforce LIMIT 10000 on exploratory queries; use Apache Arrow transfer (not .toArray()) for anything > 100 K rows. Every crossing from WASM to JS heap is 10-100x more expensive per row than staying inside the engine. Datasets > 1 GB need streaming aggregation or a hand-off to a cloud engine (MotherDuck cloud) via hybrid execution.
• Team Attach role wedges. Four canonical wedges: analyst_readonly (SELECT only, default for new joiners), analyst_readwrite (SELECT + INSERT + UPDATE, escalation via PR), analyst_admin (all + DDL + grants, two-to-three principals), plus workload-specific wedges as needed (e.g. finance_readonly). GRANT ROLE <role> TO USER '<email>' assigns; REVOKE ROLE removes. PII schemas explicitly REVOKEd from non-admin roles.
• Time-travel AT and AS OF syntax. SELECT * FROM acme_prod.fact.orders AT (TIMESTAMP '2026-07-01 00:00:00') returns the table as it was at that timestamp. Snapshot retention configured per organisation; tier example: hourly for 7 days, daily for 90 days, monthly for 2 years. Used for reproducible finance close, historical debugging, and A/B analysis over prior state.
• Governance queries every deployment schedules monthly. (1) PII access leaderboard from motherduck_ui.query_history filtered WHERE query_text ILIKE '%pii%' grouped by principal; (2) admin role sprawl from motherduck_ui.grants where role_name = 'admin'; (3) token hygiene from motherduck_ui.audit_events filtered on event_type IN ('token_issued','token_revoked'); (4) snapshot storage cost from motherduck_ui.snapshots grouped by table. Schedule as a MotherDuck CREATE TASK piped to the SIEM.
• Free-tier vs pro-tier vs enterprise limits (2026). Free: 10 GB storage, small monthly compute quota, 1 user. Pro (~$25/mo/user): 100 GB storage, expanded compute, team-attach; typical for a small analytics team. Enterprise: unbounded storage, dedicated capacity, SSO/SAML, VPC peering; billed per-organisation with contracted commitments. Always start on the free tier; upgrade when the team grows past three or storage grows past ~10 GB.
• Cost model — per-second compute + per-GB storage. Compute billed per compute-second while the query runs (no minimum warm-up window like Snowflake's cluster minutes); storage billed per-GB per-month for MotherDuck-persisted tables. A ~2-second cloud query costs ~$0.001; a nightly 2-minute feature refresh costs ~$0.06. Compare to Snowflake's XSMALL cluster-minute floor at ~$0.06/minute even for a 1-second query. Monitor with motherduck_ui.query_history.bytes_scanned and the org billing dashboard.
• Failure semantics reminder — bounded surface per pattern. Hybrid execution: cloud unreachable → cloud relations fail; local files still work; materialise for offline. DuckDB-WASM: tab memory exhaustion → tab crash; enforce aggregations + LIMIT. Team Attach: token revoked → next handshake fails; teammate re-issued from admin console. Snapshot retention: aged out → AT (TIMESTAMP ...) returns error; extend retention policy pre-emptively for reproducibility use cases. Every failure surface is bounded; know yours.
• Migration paths from Snowflake / BigQuery / Databricks. From Snowflake: COPY INTO @stage/... FROM analytics.fact.orders FILE_FORMAT = (TYPE = PARQUET); on MotherDuck side CREATE TABLE ... AS SELECT * FROM read_parquet('s3://stage/...'). From BigQuery: EXPORT DATA OPTIONS(uri='gs://.../part-*.parquet', format='PARQUET'). From Databricks: Delta → Parquet via spark.read.format('delta').load(...).write.parquet(...). Dual-write during a 2-week transition; abort trigger if cost savings < 30% at week 4. Never big-bang cut-over; workload-by-workload always.
• When to use hybrid execution deliberately. Any query where one side is a small local file (< 100 MB) and the other side is a big cloud table (> 1 GB) — the planner is right by default but verify with EXPLAIN. Also any exploratory analyst workflow where local Parquet from S3 needs to join to a persistent cloud dimension. Not appropriate when both sides are > 1 GB local (materialise both cloud-side first) or when both sides are cloud (let the planner handle it entirely cloud-side).
• DuckDB version + MotherDuck compatibility discipline. Pin duckdb==X.Y.Z in the team's requirements file; refer to MotherDuck's published compatibility matrix before upgrading. Newer client + older cloud sometimes rejects features (upgrade the cloud org first); older client + newer cloud is usually fine. Test the pin against a staging MotherDuck database before rolling out to the team. Regressions in planner behaviour across minor versions are rare but expensive when they happen.
• When MotherDuck loses cleanly and you must recommend an alternative. Petabyte lakehouse workloads (> 5 TB largest table, > 100 TB total) — recommend Databricks + Delta or BigQuery. Sub-second streaming analytics — recommend Materialize / RisingWave / a purpose-built streaming engine. Enterprise governance with strict data-residency + SOC compliance beyond what MotherDuck's SOC 2 covers — recommend Snowflake with the appropriate deployment SKU. ML training pipelines with GPU orchestration — recommend Databricks. Being able to name where MotherDuck loses is the mark of a senior architect who has actually evaluated it.

Frequently asked questions

What is MotherDuck in one sentence?

MotherDuck is the commercial cloud data platform built on DuckDB by the original creators of DuckDB, positioned as a small-data-first serverless analytics warehouse for the < 1 TB team-analytics wedge that has historically been over-served by cluster-oriented warehouses like Snowflake and BigQuery. Its defining primitive is hybrid execution: a single SQL query whose planner splits work between the analyst's laptop-resident DuckDB and the MotherDuck cloud, so local files scan locally while persistent cloud tables push their filters and aggregations down cloud-side without the analyst ever leaving their notebook. It ships with DuckDB-WASM for zero-install in-browser analytics, Team Attach for one-liner database sharing with per-role RBAC and time-travel snapshots, and per-second compute billing that removes the cluster-startup tax that dominates Snowflake / Databricks costs on ad-hoc queries. Every senior data-engineering interview in 2026 that touches warehouse selection asks about MotherDuck because it gives architects a real second choice for the small-data wedge without giving up cloud persistence.

MotherDuck vs Snowflake — when do I pick each?

Default to MotherDuck for the < 1 TB team-analytics wedge where interactivity matters more than cluster efficiency: ad-hoc notebook SQL, small-team dashboards, exploratory data science, prototype pipelines, and any workload where a fresh notebook needs to be productive in under five seconds. MotherDuck's per-second compute billing (no cluster-warm-up minimum), sub-second cloud latency for < 1 TB tables, and one-line ATTACH sharing model win this class of workload cleanly. Default to Snowflake for the enterprise-scale cluster-efficient workloads it was designed for: scheduled ETL on 1-TB-to-petabyte tables, dashboards serving hundreds of QPS with strict SLA, and any workload where the org's compliance / SOC / data-residency posture is already Snowflake-anchored. In practice, senior architects at mid-market and enterprise companies increasingly run a hybrid vendor mix — MotherDuck for ad-hoc analytics, Snowflake for scheduled ETL, sometimes Databricks for ML — with workload-per-vendor fencing rather than trying to unify on a single warehouse. The migration decision is workload-by-workload; a Snowflake ad-hoc bill of $2K+/month on < 1 TB data is almost always a 60-80% cost win for MotherDuck, while a Snowflake petabyte lakehouse bill is not a MotherDuck workload at all.

What is DuckDB WASM and when should I use it?

duckdb wasm is the DuckDB C++ analytical engine compiled to WebAssembly and shipped as an npm package (@duckdb/duckdb-wasm) that runs a full SQL query engine inside any browser tab, using HTTP range requests to read Parquet, CSV, JSON, and Arrow files directly from S3, GCS, or any CDN without a server-side query engine. The practical performance envelope is roughly 2 GB heap ceiling (browser tab budget), 10-100 M rows comfortable per query, and 1-2 seconds cold-load latency for the engine bootstrap plus sub-second for warm queries. Use DuckDB-WASM for embedded analytics widgets inside SaaS products where each customer sees their own < 500 MB dataset with sub-second interactivity, zero-install notebooks like Observable / Marimo where analysts want SQL without a server, static-site dashboards that serve interactive analytics from a Parquet file on a CDN, and client-side data exploration where the data must stay in the browser for privacy reasons. Do not use it for datasets > 1 GB per query, workloads with strict sub-100-ms latency SLAs, or scenarios requiring durable server-side persistence — those workloads belong behind a cloud engine like MotherDuck cloud. The senior discipline is: aggregate in SQL, not in JS; enforce LIMIT on any raw-row query; use Arrow transfer (not .toArray()) for anything > 100 K rows.

How does MotherDuck's hybrid execution actually decide where each fragment runs?

The MotherDuck SQL planner runs client-side inside the local DuckDB process, walks the logical plan the analyst wrote, and marks each base relation as either local (if its data lives on the analyst's laptop — read_parquet('s3://...'), read_csv('/data/...'), an in-memory DataFrame, a local .duckdb table) or cloud (if its data lives in MotherDuck — md:acme_prod.public.orders or any table under an ATTACHed database). For each operator (filter, projection, aggregation, join), the planner pushes it as far down the plan tree as possible so it runs on the side that owns the base data — a WHERE created_at > '2026-07-01' filter on a cloud table becomes a cloud-side pushdown, and a GROUP BY country on a local Parquet stays local. For cross-engine joins, the planner estimates cardinality on both sides and ships the smaller side to the larger engine — if the local side is 5 K rows and the cloud side is 100 M rows, the 5 K local rows ship up to the cloud, the join runs cloud-side, and only the projected result comes back. All of this is invisible to the analyst: one query, two engines, one result. The EXPLAIN ANALYZE plan output labels each scan LOCAL_SCAN or CLOUD_SCAN explicitly, and the three canonical overrides — PRAGMA prefer_local_scan, explicit cloud staging via CREATE TEMP TABLE, and controlled ATTACH order for cross-database joins — cover the small fraction of cases where the planner's default is measurably slower than the alternative.

What is Team Attach and how does permission work?

team attach is MotherDuck's collaboration primitive: a duckdb ATTACH 'md:<database>?motherduck_token=<jwt>' AS <alias> (READ_ONLY|READ_WRITE); statement that gives every teammate access to the same shared cloud database with permissions fenced by the token's grants and (optionally) further narrowed by the (READ_ONLY) / (READ_WRITE) qualifier on the ATTACH statement itself. Permissions are managed via three-plus canonical role wedges: analyst_readonly (SELECT only, the default for new joiners), analyst_readwrite (SELECT + INSERT + UPDATE, escalation via PR), and analyst_admin (all + DDL + grants, restricted to two-to-three principals), with additional workload-specific wedges as the team's compliance surface demands (e.g. finance_readonly for close reproducibility, pii_analyst for data governance). Roles are granted via GRANT ROLE <role> TO USER '<email>' and revoked with REVOKE ROLE; schema-level fences like PII isolation use explicit REVOKE SELECT ON SCHEMA acme_prod.pii FROM ROLE analyst_readonly. Every query is logged with the principal in motherduck_ui.query_history and the effective grant map is queryable via motherduck_ui.grants, making PII access audits and admin role sprawl reviews boring SQL rather than manual investigations. The whole system replaces Snowflake Data Sharing's contract-heavy per-share setup with a single grant statement — the operational UX is closer to "git clone" than "sign a data-share contract."

Is MotherDuck production-ready for a real team in 2026?

Yes — for the workloads it targets. MotherDuck reached general availability in 2024 and has been production-hardened across two years of live customer workloads by 2026, with SOC 2 Type II compliance, VPC-peered enterprise deployments, published SLAs, and a documented incident-response process. Teams of five-to-fifty engineers are running MotherDuck as their primary team-analytics warehouse for datasets in the 100 GB to 5 TB range, backed by Team Attach for collaboration, time-travel snapshots for reproducibility, and per-second billing for the ad-hoc query class it wins hardest. The production-readiness questions to ask on your side are workload-shaped, not vendor-shaped: does your largest table fit MotherDuck's < 5 TB comfortable envelope? do your ad-hoc queries dominate your current warehouse spend enough to move the needle on cost? does your team's collaboration pattern benefit from one-line ATTACH sharing? does your compliance surface fit MotherDuck's SOC 2 posture and available deployment SKUs? If yes to those, MotherDuck is production-ready today. If your workload is a petabyte lakehouse, sub-second streaming analytics, or an ML training pipeline with GPU orchestration, MotherDuck is not the right tool — recommend Databricks, Snowflake, BigQuery, or a purpose-built streaming engine respectively, and design the vendor-per-workload matrix instead of trying to unify on one warehouse.

Practice on PipeCode

• Drill the SQL practice library → for the analytical SQL, aggregation, and join problems that map directly to MotherDuck-shaped ad-hoc notebook workloads.
• Sharpen aggregation muscle with the aggregation practice library → for the group-by and rollup patterns feature pipelines and dashboards live on.
• Rehearse joins with the joins practice library → for the hybrid-execution cross-engine join patterns that separate senior architects from junior implementers.
• Cover system-design axes with the design practice library → for the warehouse-selection, RBAC, and migration design questions senior data-engineering interviews grade against.
• Sharpen the window-function axis with the window-function practice library → for the dashboard-style ranking, running-total, and time-series patterns that DuckDB-WASM widgets and MotherDuck analytics stacks all depend on.
• Stack the prerequisites against PipeCode's broader 450+ data-engineering catalogue to anchor the four-vendor decision matrix against real graded inputs.

Lock in motherduck muscle memory

Docs explain features. PipeCode drills explain the decision — when MotherDuck wins the < 1 TB wedge, when hybrid execution's push-down heuristic fires, when DuckDB-WASM's 2 GB heap ceiling bites, when Team Attach's role wedges collapse a Snowflake Data Sharing contract into one grant statement, and when Snowflake / BigQuery / Databricks keep the petabyte / enterprise / ML crown. Pipecode.ai is Leetcode for Data Engineering — pattern-first practice tuned for the vendor-selection and warehouse-architecture trade-offs senior data engineers actually face.

Practice SQL problems →
Practice design problems →