DOCSEARCHbyAlgolia
Submit Docs

MotherDuck Documentation

website·Indexed 37 days ago

https://motherduck.com/docs

A serverless cloud data warehouse built on DuckDB. Documentation covers data warehousing, customer-facing analytics, and architectural concepts including hypertenancy, storage lifecycle management, and workload scaling patterns.

Tokens
7.7K
Snippets
14
Records
59
Agent score
72%

What's inside MotherDuck

  1. Overview of MotherDuck serverless architecture

    MotherDuck is a serverless cloud data warehouse built on DuckDB. It uses a hypertenancy model where every user or agent is provided a dedicated compute instance, eliminating the need to manage clusters or warehouses. The architecture consists of a cloud service for managed storage and SQL execution, a DuckDB SDK for local integration, and a web UI for analysis and administration.

  2. Overview of MotherDuck as a Cloud Data Warehouse

    MotherDuck is a serverless cloud data warehouse built on DuckDB. It uses a hypertenancy architecture where every user, service account, or agent is provided a dedicated compute instance (Duckling) that starts in under a second and bills per second. It integrates with standard data engineering tools for ingestion (dltHub, Estuary, Fivetran, Airbyte), transformation (dbt, dbt Cloud), visualization (Tableau, Power BI, Looker), and orchestration (Airflow, Dagster).

  3. Overview of MotherDuck capabilities

    MotherDuck is a serverless cloud data warehouse built on DuckDB that provides cloud scalability and collaboration. Key capabilities include:

    • Serverless DuckDB: Cloud queries with ~100ms cold starts.
    • Hybrid Execution: Ability to query data locally and in the cloud within a single session.
    • Cloud Storage Integration: Direct querying of S3, GCS, and Azure Blob Storage.
    • AI Integration: Built-in LLM functions for embeddings, text analysis, and SQL generation.
    • Connectivity: Supports Python, Node.js, Go, Java, JDBC, ODBC, and a web UI.

  4. Overview of Customer-Facing Analytics (CFA) with MotherDuck

    Customer-Facing Analytics (CFA), also known as embedded analytics, involves integrating analytical capabilities directly into a product for external users (customers, partners). Unlike traditional BI, CFA requires sub-second response times, per-customer isolation, and the ability to scale to thousands or millions of concurrent users. MotherDuck enables this using a serverless cloud data warehouse built on DuckDB, focusing on two primary architectural capabilities: Hypertenancy for isolation and Dual Execution for low-latency exploration.

  5. Compare MotherDuck CFA implementation patterns

    Choose an architecture based on your requirements for latency, data volume, and development effort:

    FactorEmbedded Dives3-Tier1.5-Tier (DuckDB-Wasm)
    Frontend EffortNone (iframe)Custom UICustom UI
    Query LatencySub-second (cloud)~50-100ms~5-20ms
    Server CostLow (session minting)Medium (per request)Low (data transfer)
    Data per UserAny sizeAny size<1GB optimal
    Offline SupportNoNoYes
    Server LogicLimitedFullLimited
    PlanBusiness PlanAll PlansAll Plans
    Best ForFast shippingComplex logic/authRead-heavy dashboards

  6. Understand MotherDuck Hypertenancy

    MotherDuck uses a tenancy model called hypertenancy, where every user, service account, or agent is assigned a dedicated DuckDB compute instance known as a Duckling. Unlike traditional shared-compute warehouses, this provides full compute isolation at the individual level, eliminating 'noisy neighbor' effects, resource contention, and unpredictable query times. Ducklings feature fast cold starts (under ~100ms) and include dedicated CPU, memory, and SSD spill space.

  7. Understand the MotherDuck storage lifecycle stages

    MotherDuck uses a multi-stage storage system to manage data safety and billing. Data flows in one direction through these five stages:

    1. Active bytes: Data currently referenced and accessible via direct queries.
    2. Historical bytes: Non-active bytes referenced by historical snapshots or shares; used for time travel and self-service restore.
    3. Retained for clone bytes: (Native storage only) Bytes referenced by zero-copy cloned databases that are no longer active or historical in the source database.
    4. Failsafe bytes: Bytes no longer referenced by any database or share. These are retained as a last-resort recovery option (requires contacting support, not guaranteed). Standard databases have a 7-day retention; transient databases have a 1-day retention.
    5. Deleted: Bytes are fully removed and inaccessible.

  8. Understand MotherDuck's Serverless Execution and Parallelization

    MotherDuck is aggressively serverless and leverages DuckDB's ability to automatically parallelize workloads across all available resources. Because of this automatic parallelization, scheduling multiple queries simultaneously does not significantly increase throughput.

  9. Manage multi-tenant data isolation with Hypertenancy

    MotherDuck implements multi-tenant analytics through a concept called 'Hypertenancy'. Unlike shared platforms that may suffer from 'noisy-neighbor' performance issues, Hypertenancy provides every tenant with a dedicated DuckDB instance (referred to as a 'Duckling'). This ensures predictable performance and strict data isolation between customers.

  10. Perform Dual Execution between local DuckDB and MotherDuck

    Every MotherDuck client functions as a DuckDB engine. This allows users to query local data and perform operations like JOIN or UNION with data stored in the MotherDuck cloud data warehouse. The query planner automatically determines the optimal execution location for each part of the query.

  11. Implement Customer-Facing Analytics using a 3-tier architecture

    The 3-tier architecture is best for applications requiring server-side authorization, complex business logic, or deployment to stateful platforms (e.g., Kubernetes, ECS). The frontend communicates with an application server (e.g., Express, FastAPI), which maintains a persistent connection to MotherDuck.

    Performance Optimizations:

    • Connection Pooling: Use persistent connections to save ~200ms per request.
    • Query Efficiency: Use a single well-structured SQL statement with multiple aggregates, CASE/FILTER, or UNION ALL instead of multiple calls.
    • Atomicity: Wrap multi-step workflows in BEGIN ... COMMIT transactions.
    • Bulk Operations: Use COPY or INSERT ... SELECT for data movement rather than row-by-row calls.
    • Pre-aggregation: Create intermediate tables on MotherDuck to speed up common queries.
    • Caching: Cache rarely-changing data on the application server.

  12. Implement Customer-Facing Analytics using a 1.5-tier architecture (DuckDB-Wasm)

    The 1.5-tier architecture leverages DuckDB-Wasm to execute queries directly in the user's browser. This is ideal for read-heavy dashboards with less than 1GB of data per user, providing sub-10ms query latency and offline support.

    Key Benefits:

    • Zero-Latency Exploration: Data processing is offloaded to the client's machine.
    • Cost Efficiency: Server costs are limited to data transfer.
    • Scalability: Compute scales linearly with the number of users.

    Performance Optimizations:

    • Initial Load: Use Parquet compression and limit initial data loads to <50MB.
    • Persistence: Use IndexedDB to ensure data survives page reloads.
    • Syncing: Implement incremental sync to fetch only new data since the last update.