# MotherDuck Documentation - Customer-Facing Analytics > Focused MotherDuck documentation context for Customer-Facing Analytics. Generated: 2026-08-25 > MotherDuck is a serverless cloud data warehouse built on DuckDB. It combines the speed and simplicity of DuckDB with cloud scalability, collaboration features, and AI-powered analytics. ## Key capabilities - **Serverless DuckDB in the Cloud**: Run DuckDB queries on cloud data with 100ms cold starts (compared to seconds/minutes on traditional warehouses) - **Hybrid Execution**: Query data locally and in the cloud seamlessly in a single session - **MCP Server**: Connect AI assistants (Claude, ChatGPT, Cursor) to query your data using natural language - **Data Sharing**: Share databases and query results with team members and external users - **Multiple Interfaces**: Connect via Python, Node.js, Go, Java, JDBC, ODBC, or the web UI - **Cloud Storage Integration**: Query data directly from S3, GCS, Azure Blob Storage, and more - **AI Functions**: Built-in LLM functions for text analysis, embeddings, and SQL generation ## When to use MotherDuck Use MotherDuck when the user needs to analyze data with DuckDB-compatible SQL, share databases with people or applications, run collaborative cloud analytics, or let an AI assistant query their connected data through MCP. ## Agent guidance If your environment provides MCP tools and the user asks about MotherDuck or DuckDB behavior, SQL syntax, permissions, sharing, service accounts, tokens, Dives, or other product features, use the MotherDuck MCP `ask_docs_question` tool before general web search. It answers from official DuckDB and MotherDuck documentation. For broad context, start with https://motherduck.com/docs/llms-full.txt, then follow the most specific focused context link. Use https://motherduck.com/docs/llms-full-complete.txt only for bulk indexing or large-context workflows. To connect an MCP client, use the remote MotherDuck MCP server at `https://api.motherduck.com/mcp`. Setup instructions: https://motherduck.com/docs/key-tasks/ai-and-motherduck/mcp-setup. Tool reference: https://motherduck.com/docs/sql-reference/mcp/core/ask-docs-question. For the documented Admin REST API, use the OpenAPI specification at https://motherduck.com/docs/openapi.json. ## Account setup for agents If the user wants to start using MotherDuck and doesn't have an account, offer the agent signup flow. Creating an account changes external state, so get the user's confirmation before sending the request. `POST https://new.motherduck.com` creates a Free Plan organization. No request body is required. The JSON response includes `motherduck_token`, `claim_org_url`, `how_to_use_motherduck`, and `region`. Treat `motherduck_token` as a secret: don't print, log, commit, or include it in messages. Follow the live `how_to_use_motherduck` instructions, and give the user the `claim_org_url` so they can take ownership. Full guide: https://motherduck.com/docs/key-tasks/ai-and-motherduck/agent-account-signup. ## Included documentation Source: https://motherduck.com/docs/key-tasks/customer-facing-analytics/customer-facing-analytics # Build a customer-facing analytics app > Build customer-facing analytics applications with read scaling tokens and isolated tenant data. To build your first application with **Customer-Facing Analytics (CFA)** on MotherDuck, use this overview as a starting point. You'll know you're done when: - Each of your customer tenants (or organizations) has its own service account and database(s) in MotherDuck. - Your application can query customer-specific analytics data with predictable performance and isolation. - You understand which detailed guide to follow next for implementation. Use this overview to choose a **tenancy model** and learn the building blocks before the step-by-step 3-tier guide. ## Customer provisioning Every [Duckling](https://motherduck.com/blog/scaling-duckdb-with-ducklings/) is an isolated bucket of compute. For Customer-Facing Analytics, this usually means: - Each **customer tenant or organization** has **one service account** dedicated to serving analytics (and often also ingestion and transformation). - Your backend mediates all access; customers typically do not log into MotherDuck directly. You manage service accounts and tokens using: - [`users-create-service-account`](/sql-reference/rest-api/users-create-service-account/) – create a service account per customer tenant. - [`users-create-token`](/sql-reference/rest-api/users-create-token/) – create tokens for ingestion and read workloads. With accounts and tokens in place, you can: - Create databases under each service account. - Load data into those databases using your orchestrator. - Use dedicated read tokens from your application to serve analytics. For a concrete example of this pattern in a 3-tier web app, see the **[CFA Guide](/key-tasks/customer-facing-analytics/3-tier-cfa-guide/)**. ## Data modeling and loading One database per customer tenant or organization scales cleanly because: - Each database is tied to a tenant's service account. - Each tenant's workloads are isolated from the others. - You can scale Duckling (compute instance) sizes independently based on tenant needs using [different sizes (Pulse, Standard, etc)](/about-motherduck/billing/duckling-sizes/). You can also: - Use a single "landing" service account to ingest raw data from upstream systems. - Use [ATTACH](/sql-reference/motherduck-sql-reference/attach.md) and [zero-copy cloning](/key-tasks/sharing-data/sharing-overview/#consuming-shared-data) to fan that data out into per-customer databases owned by their respective service accounts. High-level patterns for data pipelines: ```mermaid graph LR; A[Source Systems]-->D[(Landing Database)]:::database; D-->F[(Transform & Clone)]:::database; F-->G[(Customer DB A)]:::database; F-->H[(Customer DB B)]:::database; F-->I[(Customer DB C)]:::database; subgraph App E[Serve Analytics] end G-->E; H-->E; I-->E; ``` Check out the detailed [Builder's Guide](/key-tasks/customer-facing-analytics/3-tier-cfa-guide/) for instructions on loading data into per-customer MotherDuck databases and orchestrating customer-facing analytics pipelines. ## Other considerations Since MotherDuck [Shares](/key-tasks/sharing-data/sharing-overview/) are read-only, in more real-time scenarios it may make sense to use: - [`CREATE SNAPSHOT`](/sql-reference/motherduck-sql-reference/create-snapshot/) to force a checkpoint on the writer. - [`REFRESH DATABASE`](/sql-reference/motherduck-sql-reference/refresh-database/) to get the latest version of the data on the reader. This pattern can help enforce consistency between writer and reader databases that power your customer-facing dashboards. For high-scale, high-concurrency applications, MotherDuck offers [Read Scaling Replicas](https://motherduck.com/blog/read-scaling-preview/) for applications that send hundreds or thousands of queries in a few seconds, such as BI tools or busy embedded dashboards. Read replicas: - Can be created and modified in the UI. - Can be managed using the [MotherDuck REST API](/sql-reference/rest-api/motherduck-rest-api/). - Follow the same consistency considerations as Shares, and can be checkpointed and refreshed more frequently if needed. When you're ready to implement a full 3-tier architecture with per-customer service accounts, scheduled data loading, and a backend API, continue to the [**Customer-Facing Analytics Guide**](/key-tasks/customer-facing-analytics/3-tier-cfa-guide/). --- Source: https://motherduck.com/docs/key-tasks/customer-facing-analytics/3-tier-cfa-guide # 3-tier customer-facing analytics guide > Step-by-step guide to building a 3-tier customer-facing analytics application with MotherDuck. To build a **Customer-Facing Analytics (CFA) application** on MotherDuck, use this step-by-step guide. This guide will focus on patterns for traditional 3-tier architecture, but you can also run 1.5-tier apps using Wasm, as seen in the [1.5-tier architecture guide](/getting-started/customer-facing-analytics/#15-tier-architecture-duckdb-wasm). You'll know you're done when: - Your application (`B2B Tool`) can run analytics queries for a customer (`Goose Inc`) against MotherDuck from a backend service. - Data from a transactional database is synced into a per-customer MotherDuck database on a schedule using your orchestrator. - You understand when to add more service accounts, databases, and read scaling capacity as your product grows. Use this guide when you want to: - Build a 3-tier web app (browser → app server → MotherDuck) with embedded analytics. - Use per-customer service accounts and databases to isolate data and compute. - Keep analytics data in MotherDuck in sync with your transactional database. Before starting, ensure you have: - A MotherDuck account and an organization you can use for development. - Basic familiarity with Python and SQL. - Access to a PostgreSQL database (or a test instance) with an `orders`-style schema. - Python installed locally (DuckDB is compatible with the latest Python LTS version). > This guide assumes you've read the conceptual overview [**Customer-Facing Analytics Getting Started**](/getting-started/customer-facing-analytics). ## 1. understand the 3-tier CFA architecture In this guide, you are building `B2B Tool`, a SaaS product that serves analytics to employees at many customer companies. Each customer company gets: - Its own **service account** in MotherDuck. - Its own **database(s)** for analytics tables. - Its own **compute** (Ducklings) for queries and data loading. Your high-level architecture: ```mermaid graph LR; subgraph Users["End Users"] U1{{"Kate (Goose Inc)"}}:::green; U2{{"John (Goose Inc)"}}:::green; U3{{"Hari (Duck Co)"}}:::green; end subgraph App["Your Application"] FE["Frontend"]; BE["Backend API"]; TX["Transactional DB"]; end MDORG["MotherDuck"]; U1 --> FE; U2 --> FE; U3 --> FE; FE -->|"HTTP / JSON APIs"| BE; BE -->|"User + Company lookup"| TX; BE -->|"Analytics queries"| MDORG; ``` [Hypertenancy](/concepts/hypertenancy) here means each company (`Goose Inc`, `Duck Co`) owns its MotherDuck database(s) (that store only that company's analytics data), that compute is isolated (each company has its own Ducklings) and heavy workloads for one customer cannot slow down others. You will: 1. Set up a dev organization and add other developers on the team. 2. Create a service account for your first customer company (`Goose Inc`). 3. Sync data from your transactional DB, such as Postgres, into Goose Inc’s MotherDuck analytical database using your chosen replication method. 4. Connect your backend service to MotherDuck with a **read token** to serve analytics queries. 5. Plan how to scale to many customer companies and higher concurrency. ### Alternative to per-customer service accounts The per-customer service account pattern is the strongest isolation model. Some teams, especially B2C or lighter multi-tenant apps, opt for a simpler setup: - Keep a **single writer service account** that owns all customer databases. - Create a **[read scaling token](/key-tasks/authenticating-and-connecting-to-motherduck/read-scaling/)** for that account and configure the pool size to target one duckling per concurrent end user (default max 16, adjustable through support). For cost control, users can share a duckling, but that increases contention. - Have each end user connect in **[single attach mode](/key-tasks/authenticating-and-connecting-to-motherduck/attach-modes/)** to the one database they should see (`md:?attach_mode=single`), which avoids carrying other attachments from the workspace. - Use [`session_name`](/key-tasks/authenticating-and-connecting-to-motherduck/connecting-to-motherduck/#session-names) in the connection string to keep an end user pinned to the same read scaling duckling for cache reuse and steadier latency. This model trades away service-account isolation in favor of operational simplicity. Ensure your security and compliance needs allow a shared service account before choosing it. Read scaling replicas are eventually consistent. If you need fresher reads on demand, combine `CREATE SNAPSHOT` on the writer with `REFRESH DATABASE` on the read scaling connections. Example connection string for an end user: ```text md:customer_db?attach_mode=single&session_name= ``` ## 2. set up your dev environment and organization Prepare your dev environment: 1. **Create your dev organization and account** 1. Go to `https://motherduck.com` and sign up or log in with your work email (for example, `manager@b2btool.com`). 2. Create or select an organization you’ll use for development (for example, `B2B Tool Co`). 3. In the MotherDuck UI, open the default database (`my_db`) and confirm you can run a simple query such as: ```sql SELECT 1; ``` You should see a single row with the value `1`. 2. **Upload a small CSV to confirm data ownership and access** 1. In the MotherDuck web UI, upload a small example CSV (for example, `orders_sample.csv`) into `my_db`. If this step is unclear, check out the [MotherDuck tutorial on loading data](/getting-started/e2e-tutorial/part-2/#loading-your-data). 2. Run a query like: ```sql SELECT COUNT(*) AS row_count FROM orders_sample; ``` You should see the number of rows you uploaded. 3. **Invite a second developer and share data** 1. Invite `devlead@b2btool.com` to your `B2B Tool Co` organization. 2. Create a new database in your personal account (for example, `b2btool_dev`) and copy or create a simple table. 3. Share that database with your colleague following the [**Sharing Data** guide](/key-tasks/sharing-data/sharing-overview/). 4. Ask your colleague to query the shared database from their account. At this point: - You have a dev org with two human users. - You’ve seen how database ownership and read-only sharing works. Conceptually, your dev setup looks like this: ```mermaid graph LR; DM["devlead@b2btool.com"] <-->|"read/write"| DB1[("DB: b2btool_dev")]:::database; DB1 -->|"read only"| DC{{Colleague}}:::green; ``` ## 3. create a service account for a customer company For customer-facing analytics, your customers usually do **not** log into MotherDuck directly. Instead: - Your application mediates access. - Each customer company gets a **service account** in your MotherDuck organization. - Your backend uses that service account’s tokens to load and query data. In this guide, you’ll create a service account for your first customer company: `Goose Inc`. ### 3.1 create a service account in the MotherDuck UI 1. In the MotherDuck UI, go to the **Service Accounts** section for your organization. 2. Click **Create Service Account**. 3. Name it something like `goose-inc-service-account`. 4. Save the generated access token in your secret manager or a secure store. For more detail, see [Create and configure service accounts](/key-tasks/service-accounts-guide/create-and-configure-service-accounts/). ### 3.2 (optional) create service accounts through REST API Later, you will likely automate service account creation. To create a service account programmatically: - Use the [`users-create-service-account`](/sql-reference/rest-api/users-create-service-account/) REST API endpoint. - Use the [`users-create-token`](/sql-reference/rest-api/users-create-token/) endpoint to create an access token for that service account. Your provisioning workflow should: **(1)** detect a new customer signup, **(2)** call `users-create-service-account` for that company, **(3)** call `users-create-token`, and **(4)** store the token metadata (or an alias) in your transactional database so your backend can look it up later. ## 4. model and load customer data in MotherDuck Next, populate data for `Goose Inc` into its own MotherDuck database. Assume: - Your transactional system (`B2B Tool`) uses PostgreSQL. - Each customer company is an e-commerce store with: - `orders` table: order-level facts. - `fulfillments` table: shipment or delivery events. Example schema: ```sql CREATE TABLE orders ( order_id BIGINT PRIMARY KEY, company_id BIGINT, order_date TIMESTAMP, customer_email TEXT, total_amount NUMERIC(18, 2), status TEXT ); CREATE TABLE fulfillments ( fulfillment_id BIGINT PRIMARY KEY, order_id BIGINT REFERENCES orders(order_id), fulfilled_at TIMESTAMP, carrier TEXT, status TEXT ); ``` Example data: ```sql INSERT INTO orders SELECT row_number() OVER () AS order_id, (random() * 9 + 1)::BIGINT AS company_id, current_timestamp - INTERVAL (random() * 365) DAY AS order_date, 'customer' || (random() * 999 + 1)::INT || '@example.com' AS customer_email, (random() * 9999 + 1)::NUMERIC(18, 2) AS total_amount, (['pending', 'processing', 'shipped', 'delivered', 'cancelled'])[(random() * 4)::INT + 1] AS status FROM range(1000); INSERT INTO fulfillments SELECT row_number() OVER () AS fulfillment_id, (random() * 999 + 1)::BIGINT AS order_id, current_timestamp - INTERVAL (random() * 300) DAY AS fulfilled_at, (['UPS', 'FedEx', 'USPS', 'DHL', 'Amazon Logistics'])[(random() * 4)::INT + 1] AS carrier, (['pending', 'in_transit', 'out_for_delivery', 'delivered', 'failed'])[(random() * 4)::INT + 1] AS status FROM range(1000); ``` :::info Use your [orchestrator](/integrations/orchestration/) and [ingestion tool](/integrations/ingestion/) to keep this data in sync for each customer company. ::: ### 4.1 create a MotherDuck database for `Goose Inc` Use the `Goose Inc` service account’s token to create a database for that customer: ```sql CREATE DATABASE goose_inc; ``` Run this in the UI after impersonating the `Goose Inc` service account or connect as that service account from Python and issue the `CREATE DATABASE` statement. :::note To move forward, replicate your data into `goose_inc`. [This page](/key-tasks/data-warehousing/replication/postgres/) shows a simple example for replicating a Postgres database to MotherDuck. ::: ## 5. run analytics queries from your backend With data in Goose Inc’s MotherDuck database, your backend can run analytics queries. At a high level: 1. Your user (`Kate` at Goose Inc) logs into `B2B Tool`. 2. Your backend authenticates Kate and determines she belongs to the `Goose Inc` customer company. 3. Your backend looks up Goose Inc’s **read token** for its service account from your transactional database or secret store. 4. Your backend uses that read token to run analytics queries against the `goose_inc` database in MotherDuck. ### 5.1 create a read token for `Goose Inc` For production, you’ll usually create a token dedicated to **reading** analytics data: 1. In the MotherDuck UI, impersonate the Goose Inc service account. 2. Create a new access token intended only for read workloads. 3. Store this token securely and associate it with Goose Inc in your transactional database. You can also create tokens through the REST API using the [`users-create-token`](/sql-reference/rest-api/users-create-token/) endpoint. ### 5.2 connect from Python using DuckDB Your backend service connects to MotherDuck using the DuckDB client and the `md:` connection string. Typically, you: - Set the `MOTHERDUCK_TOKEN` (or `motherduck_token`) environment variable to the Goose Inc read token. - Connect to the `goose_inc` database using DuckDB. Example helper in your backend (for example, `analytics_client.py`): ```python import os import duckdb def get_customer_connection(customer_id: str): """ Get a DuckDB connection to a customer's MotherDuck database. Args: customer_id: Identifier for the customer (e.g., 'goose_inc', 'duck_co') Returns: DuckDB connection to the customer's database """ # Look up the customer's read token from your secret store or environment # In production, you'd fetch this from your transactional DB or secret manager token_env_var = f"{customer_id.upper().replace('-', '_')}_READ_TOKEN" read_token = os.environ.get(token_env_var) if not read_token: raise ValueError(f"Read token not found for customer: {customer_id}") # Set the token for this connection os.environ["MOTHERDUCK_TOKEN"] = read_token # Connect to the customer's database on MotherDuck # Database name typically matches the customer_id conn = duckdb.connect(f"md:{customer_id}") return conn ``` Then, a simple analytics function in your API service: ```python def get_customer_kpis(customer_id: str): conn = get_customer_connection(customer_id) query = """ SELECT date_trunc('day', order_date) AS day, COUNT(*) AS orders_count, SUM(total_amount) AS gross_revenue FROM orders WHERE order_date >= current_date - INTERVAL 30 DAY GROUP BY 1 ORDER BY 1 """ result = conn.execute(query).fetch_df() # Convert to JSON-serializable structure for your frontend return result.to_dict(orient="records") ``` Expose this from a REST endpoint such as `/api/customers/{customer_id}/kpis` and render the results in your frontend dashboards. The same code works for any customer by passing their identifier. The runtime query flow looks like: ```mermaid sequenceDiagram participant User as Kate (Goose Inc) participant FE as B2B Tool Frontend participant BE as B2B Tool Backend participant MD as MotherDuck (Goose Inc DB) User->>FE: Opens analytics dashboard FE->>BE: GET /api/customers/goose-inc/kpis BE->>BE: Lookup Goose Inc read token BE->>MD: Analytics query using DuckDB + md:goose_inc MD-->>BE: Result rows BE-->>FE: JSON KPIs FE-->>User: Render charts ``` ## 6. scaling to many customer companies As your product grows, add more customer companies. For each new company: 1. **Create a service account** (through the UI or REST API). 2. **Create one or more databases** for that company’s analytics data. 3. **Configure your orchestrator** to run a `dlt` pipeline (or equivalent) for that company. 4. **Create a read token** for the company and store it in your transactional database. Your architecture naturally scales horizontally: ```mermaid graph LR; subgraph Org["Your MotherDuck Org"] SA1["Service Account: Goose Inc"]; SA2["Service Account: Swan Gmbh"]; SA3["Service Account: Duck Co"]; DB1[("DB: goose_inc")]:::db; DB2[("DB: swan_gmbh")]:::db; DB3[("DB: duck_co")]:::db; end SA1 --> DB1; SA2 --> DB2; SA3 --> DB3; ``` Each service account and database pair has its own compute, minimizing noisy neighbors and making performance a per-customer concern. ## 7. scaling a single customer to high concurrency When a customer (for example, `Goose Inc`) grows to hundreds or thousands of simultaneous users, use these levers: 1. **Increase the Duckling size** for the service account’s default compute Duckling to handle heavier transformation jobs (vertical scaling). 2. **Use read scaling** for high-concurrency read workloads: - Refer to [read scaling](/key-tasks/authenticating-and-connecting-to-motherduck/read-scaling/) to create read scaling Ducklings for Goose Inc's read token. - Point your backend’s analytics queries at the read scaling token instead of the main read/write token. 3. **Optimize queries and models**: - Pre-aggregate frequently-used metrics. - Use summary tables to avoid scanning the full `orders` table on every request. For most applications, you start with a single Duckling per customer and introduce read scaling only when your monitoring shows sustained high concurrency or latency issues. ## 8. troubleshooting and when to add more service accounts As you operate your CFA deployment, you may run into several common situations. ### 8.1 queries are slow or time out for one customer If you see slow queries or timeouts for a specific customer: - **Check query patterns**: - Are you scanning too much data on every request? - Can you pre-aggregate or cache common metrics? - **Scale compute for that customer**: - Increase the size for the service account’s Duckling. - Add read scaling Ducklings OR increase the Duckling size used for the read token used by that customer. You rarely need to change the number of service accounts in this case; focus on scaling and optimizing the existing one. ### 8.2 data loads interfere with reads If your hourly (or more frequent) data load jobs are locking tables and causing read queries to queue: - Consider: - Scheduling heavy load jobs during off-peak times. - Using zero-copy cloning (`CREATE SNAPSHOT` and `REFRESH DATABASE`) patterns so that readers query a snapshot database while writers update the primary. - Ensure you are using a **dedicated read token** and read scaling configuration for user-facing queries. ### 8.3 when to add more service accounts In most B2B scenarios: - You create **one service account per customer company**. - All users at that company share the same analytics data and compute through your application. You should consider adding **additional service accounts** when: - You need hard isolation between different environments (for example, separate service accounts for `Prod`, `Staging`, and `Sandbox` within the same customer). - A customer has sub-tenants of their own and you want to isolate compute and data at that sub-tenant level (for example, separate service accounts per region or per major business unit). When you add new service accounts: 1. Create the service account (UI or REST API). 2. Create dedicated databases for the new scope. 3. Create tokens and wire them into your application’s configuration. ### 8.4 common token and permission issues If you see authentication or permission errors: - **Token expired or revoked**: - Rotate the token in MotherDuck and update your secret store. - **Permission denied on database or table**: - Confirm that the service account owns the database or has the necessary privileges. - Re-check sharing settings if you are using shared data. ## 9. next steps Once you have a basic 3-tier CFA deployment working: - **Automate provisioning**: - Automate service account and token creation using the [REST APIs](/sql-reference/rest-api/motherduck-rest-api/). - Automate database and schema creation for new customer companies. - **Automate data loading**: - Move your `dlt` jobs fully into your orchestrator so that new companies are onboarded with little manual work. - Monitor load durations and adjust scheduling as your data grows. - **Enhance your frontend**: - Add charts and drill-downs powered by MotherDuck. - Consider additional guides under `Customer-Facing Analytics` for advanced topics in your docs set. For a high-level conceptual overview and architecture comparison, see the [**Customer-Facing Analytics Getting Started**](/getting-started/customer-facing-analytics/) page. --- ## Docs feedback MotherDuck accepts optional user-submitted feedback about this page at `GET https://motherduck.com/docs/api/feedback/agent`. For agents and automated tools, feedback submission should be user-confirmed before sending. URL-encode query parameter values and send a GET request: ```text GET https://motherduck.com/docs/api/feedback/agent?page_path=%2Fkey-tasks%2Fcustomer-facing-analytics%2F&page_title=MotherDuck%20Documentation%20-%20Customer-Facing%20Analytics&text= ``` Optionally append `&source=` such as `claude.ai` or `chatgpt`. `page_path` and `text` are required; `page_title` and `source` are optional. Responses: `200 {"feedback_id": ""}`, `400` for malformed query parameters, and `429` when rate-limited.