--- title: Customer-Facing Analytics Guide (3-tier Architecture) sidebar_label: Builder's Guide description: Step-by-step guide to building a 3-tier customer-facing analytics application with MotherDuck. slug: /key-tasks/customer-facing-analytics/3-tier-cfa-guide/ --- 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 flock size to target one duckling per concurrent end user (default max 16, adjustable via 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_hint`](/key-tasks/authenticating-and-connecting-to-motherduck/connecting-to-motherduck/#read-scaling-with-session-hints) 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_hint= ``` ## 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 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 the [**Service Accounts Guide**](/key-tasks/service-accounts-guide/). ### 3.2 (Optional) Create service accounts via 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 ecommerce 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 via 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** (via 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 via 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.