# AI and MotherDuck > Practical guides for using AI with MotherDuck. ## Included pages - [Connect to the MotherDuck MCP Server](https://motherduck.com/docs/key-tasks/ai-and-motherduck/mcp-setup): Set up the MotherDuck MCP Server with Claude, ChatGPT, Cursor, Claude Code, and other AI assistants - [Using the MotherDuck MCP Server](https://motherduck.com/docs/key-tasks/ai-and-motherduck/mcp-workflows): Effective workflows and best practices for getting the most out of the MotherDuck MCP Server with AI assistants - [Install MotherDuck Skills for coding agents](https://motherduck.com/docs/key-tasks/ai-and-motherduck/agent-skills): Install the MotherDuck Skills plugin catalog to teach Claude Code, Cursor, Codex, Copilot CLI, and Gemini CLI to work with MotherDuck. - [Restricting to read-only access](https://motherduck.com/docs/key-tasks/ai-and-motherduck/securing-read-only-access): Restrict the remote MCP server to read-only queries using client-side blocking, read scaling tokens, or proxy filtering - [AI Features in the MotherDuck UI](https://motherduck.com/docs/key-tasks/ai-and-motherduck/ai-features-in-ui): Use AI-powered SQL editing, FixUp, and natural language queries in the MotherDuck web interface. - [Custom AI Agent Builder's Guide](https://motherduck.com/docs/key-tasks/ai-and-motherduck/building-analytics-agents): Build AI-powered analytics agents using MotherDuck's SQL functions and MCP server integration. - [Create and claim accounts for AI agents](https://motherduck.com/docs/key-tasks/ai-and-motherduck/agent-account-signup): Use the MotherDuck signup API so an agent can create an account and get a token, then claim that account as a human owner. - [Text Search in MotherDuck](https://motherduck.com/docs/key-tasks/ai-and-motherduck/text-search-in-motherduck): Text search strategies from pattern matching to semantic search with embeddings in MotherDuck. Source: https://motherduck.com/docs/category/ai-and-motherduck --- ## Connect to the MotherDuck MCP Server Source: https://motherduck.com/docs/key-tasks/ai-and-motherduck/mcp-setup > Set up the MotherDuck MCP Server with Claude, ChatGPT, Cursor, Claude Code, and other AI assistants The MotherDuck MCP Server lets AI assistants query and explore your databases using the [Model Context Protocol (MCP)](https://modelcontextprotocol.io/). This guide walks you through connecting your preferred AI client to the **remote MCP server** (fully managed, zero setup). For local DuckDB files or self-hosted setups, see the [local MCP server](#remote-vs-local-mcp-server). :::info Connection URL The remote MCP server is hosted at `https://api.motherduck.com/mcp`. Most clients connect through OAuth automatically; clients that need a manual configuration use this URL with an HTTP transport. You can also authenticate with a [Bearer token](/key-tasks/authenticating-and-connecting-to-motherduck/authenticating-to-motherduck#creating-an-access-token) instead of OAuth. ::: ## Prerequisites - A MotherDuck account ([sign up free](https://app.motherduck.com/)) - An MCP-compatible AI client (Claude, ChatGPT, Cursor, Claude Code, Codex, or others) ## Set up the remote MCP server Select your MCP client and follow the instructions to connect. ### Claude [Add MotherDuck to Claude](https://claude.ai/directory/0929a5c7-38ce-40ab-8aad-af9ce34553c7) Or manually: 1. Go to **Settings** → **Connectors** 2. Click **Browse Connectors** to find the MotherDuck connector ![MotherDuck Connector in the Claude connector Directory](./img/claude-connectors-motherduck.png) A browser window should open for authentication. After authentication you can double check the connection by asking "List all my databases on MotherDuck." ### ChatGPT [Add MotherDuck to ChatGPT](https://chatgpt.com/apps/motherduck/asdk_app_696a54f1c91c81919002b9153ce0e336) 1. Open the ChatGPT desktop or web app 2. Go to **Settings** → **Apps** and click **Browse Apps** ![Browse Apps in ChatGPT settings](useBaseUrl('/img/key-tasks/ai-and-motherduck/chatgpt-browse-apps.png')) 3. Search for **MotherDuck** and select it ![Searching for MotherDuck in the ChatGPT App Store](useBaseUrl('/img/key-tasks/ai-and-motherduck/chatgpt-search-motherduck.png')) 4. Click **Continue to MotherDuck** and authenticate with your MotherDuck account ![Connect MotherDuck dialog in ChatGPT](useBaseUrl('/img/key-tasks/ai-and-motherduck/chatgpt-connect-motherduck.png')) After authentication, ChatGPT can access your MotherDuck data. Try asking "List all my databases on MotherDuck" to verify the connection. ### Cursor [Add MotherDuck to Cursor](cursor://anysphere.cursor-deeplink/mcp/install?name=motherduck&config=eyJ1cmwiOiJodHRwczovL2FwaS5tb3RoZXJkdWNrLmNvbS9tY3AifQ%3D%3D) 1. Open **Cursor Settings** (`Cmd/Ctrl + ,`) 2. Navigate to **Tools & MCP** 3. Click **+ New MCP Server** 4. Add the following to the configuration file: ```json { "MotherDuck": { "url": "https://api.motherduck.com/mcp", "type": "http" } } ``` 5. Save and click **Connect** to authenticate with your MotherDuck account > [Cursor MCP Documentation](https://docs.cursor.com/context/model-context-protocol) ### Claude Code 1. Run the following command in your terminal: ```bash claude mcp add MotherDuck --transport http https://api.motherduck.com/mcp ``` :::tip By default, this command adds the MCP server to the current project. You can also pass the `--scope user` flag, and the MCP server will be available for all sessions from your current user ([`--scope` documentation](https://code.claude.com/docs/en/mcp#mcp-installation-scopes)). ::: 2. Run `claude` to start Claude Code 3. Type `/mcp`, select **MotherDuck** from the list, and press **Enter** 4. Select **Authenticate** and confirm the authorization dialog > [Claude Code MCP Documentation](https://code.claude.com/docs/en/mcp) ### GitHub Copilot (VS Code) Configure GitHub Copilot in VS Code to use the MotherDuck MCP server through a workspace config file: 1. Open the Command Palette (`Cmd/Ctrl + Shift + P`) and run **MCP: Add Server** to open `.vscode/mcp.json`. You can also create the file manually in your workspace. Add this configuration: ```json { "servers": { "motherduck": { "type": "http", "url": "https://api.motherduck.com/mcp" } } } ``` 2. Save the file and start the server from the **Start** code lens that appears above the `motherduck` entry in `mcp.json`. You can also start it through the Command Palette: `MCP: List Servers` → **motherduck** → **Start Server**. 3. VS Code opens a browser window so you can sign in to MotherDuck through OAuth, then stores the credentials for subsequent server starts. 4. Open the Copilot Chat view, switch to **Agent** mode, and confirm that the MotherDuck tools appear in the tool picker. Try asking "List all my databases on MotherDuck" to verify the connection. **Authenticate with an access token instead of OAuth** If you'd rather provide a [MotherDuck access token](/key-tasks/authenticating-and-connecting-to-motherduck/authenticating-to-motherduck#creating-an-access-token) explicitly, use a `promptString` input and a `Bearer` Authorization header. VS Code prompts for the token when the server starts and stores it in its secret store: ```json { "inputs": [ { "type": "promptString", "id": "motherduck-token", "description": "MotherDuck access token", "password": true } ], "servers": { "motherduck": { "type": "http", "url": "https://api.motherduck.com/mcp", "headers": { "Authorization": "Bearer ${input:motherduck-token}" } } } } ``` > [VS Code MCP Documentation](https://code.visualstudio.com/docs/copilot/chat/mcp-servers) ### Copilot Studio [Microsoft Copilot Studio](https://learn.microsoft.com/en-us/microsoft-copilot-studio/) is a cloud-hosted platform for building agents that run inside Microsoft 365, Teams, and other Microsoft surfaces. Because the platform runs in Microsoft's cloud, it connects to the **remote** MotherDuck MCP server — either with OAuth (each user signs in with their own MotherDuck account) or with a shared API key backed by a service-account token. 1. In Copilot Studio, open your agent. Under **Tools**, click **Add a tool**. ![Copilot Studio agent Tools tab with Add a tool button](/img/key-tasks/ai-and-motherduck/copilot-studio/01-add-tool.png) 2. In the **Add tool** dialog, under **Create new**, click **Model Context Protocol**. ![Add tool dialog with Model Context Protocol highlighted under Create new](/img/key-tasks/ai-and-motherduck/copilot-studio/02-mcp-option.png) 3. Fill in the MCP server details and pick an authentication method: - **Server name**: `MotherDuck MCP` - **Server description**: `Connect to MotherDuck, query your data, create Dives and more!` - **Server URL**: `https://api.motherduck.com/mcp` - **Authentication**: either `OAuth 2.0` or `API key` (see below) **Option A — OAuth 2.0 (dynamic discovery).** Each end user signs in to MotherDuck with their own account when they first use the agent. Select **OAuth 2.0** and leave **Dynamic discovery** as the type, then click **Create**. ![MCP server configuration with OAuth 2.0 Dynamic discovery selected](/img/key-tasks/ai-and-motherduck/copilot-studio/03a-oauth-auth.png) **Option B — API key (shared service-account token).** All end users share a single MotherDuck token. Useful when you don't want every user to provision a MotherDuck account, for example a Teams bot exposed to a wide audience. Select **API key**, set **Type** to `Header`, enter `Authorization` as the **Header name**, and click **Create**. ![MCP server configuration with API key authentication, Header type, and Authorization header name](/img/key-tasks/ai-and-motherduck/copilot-studio/03b-api-key-auth.png) :::caution **Header name** must be `Authorization` — not `Bearer`. The `Bearer` prefix belongs in the *value* you enter in step 5. ::: 4. Back in the **Add tool** dialog for MotherDuck MCP, open the **Connection** dropdown and click **Create new connection**. ![Connection dropdown showing Create new connection option](/img/key-tasks/ai-and-motherduck/copilot-studio/04-create-connection.png) The next step depends on the authentication method you picked in step 3: - **OAuth 2.0**: Copilot Studio opens a browser window that redirects to MotherDuck. The end user signs in to their MotherDuck account and approves the request. The connection is created once authentication completes — skip to step 6. - **API key**: Copilot Studio shows the token entry dialog described in step 5. 5. In the **Connect to MotherDuck MCP** dialog, enter your MotherDuck access token prefixed with `Bearer `: ```text Bearer ``` Replace `` with an actual token from [MotherDuck → Settings → Access Tokens](https://app.motherduck.com/settings/tokens), then click **Create**. ![Connect to MotherDuck MCP dialog with the Bearer token entered](/img/key-tasks/ai-and-motherduck/copilot-studio/05-bearer-token.png) :::tip If the agent is published and used by many end users, create a dedicated [service account](/key-tasks/service-accounts-guide/) and use a [read scaling token](/key-tasks/authenticating-and-connecting-to-motherduck/read-scaling/) so the agent can't modify data. See [Restricting to read-only access](/key-tasks/ai-and-motherduck/securing-read-only-access/) for details. ::: 6. Once the connection shows a green check mark, click **Add and configure**. Copilot Studio confirms the tool was added successfully. 7. The MotherDuck MCP entry opens with the full tool list. Enable or disable tools based on what the agent should be allowed to do (for example, disable `query_rw` if the agent should stay read-only), then click **Save**. ![MotherDuck MCP tool list with toggles for query, query_rw, list_databases, list_tables, list_columns, search_catalog, ask_docs_question, and others](/img/key-tasks/ai-and-motherduck/copilot-studio/07-tools-list.png) 8. Open the agent's connection manager and click **Connect** on the MotherDuck MCP entry, then submit. This reuses the connection you created in step 5. 9. Switch to the **Test** pane and ask a question that exercises the tools, for example *"What's the highest rated movie with over 10k votes in my IMDB database?"*. The agent calls the MotherDuck tools and responds with live data from your databases. ![Copilot Studio test pane showing the agent calling the query tool and returning IMDB results from MotherDuck](/img/key-tasks/ai-and-motherduck/copilot-studio/09-test-agent.png) :::note When you authenticate with an API key, all users of the Copilot Studio agent share the same MotherDuck token. Queries run by any end user are attributed to the service account that owns the token, not to the individual Microsoft 365 user. Use OAuth 2.0 if you need per-user attribution. ::: > [Copilot Studio MCP documentation](https://learn.microsoft.com/en-us/microsoft-copilot-studio/mcp-add-existing-server-to-agent)
Alternative: Power Automate custom connector (OpenAPI) If you'd rather wire the MotherDuck MCP server in as a [Power Automate custom connector](https://learn.microsoft.com/en-us/connectors/custom-connectors/) (for example, to share the connector across Copilot Studio and Power Automate flows in the same environment), you can import the following OpenAPI 2.0 spec. The `x-ms-agentic-protocol: mcp-streamable-1.0` extension tells Copilot Studio to treat the connector as a streamable MCP server. ```yaml swagger: '2.0' info: title: MotherDuck Remote MCP description: The remote MCP to connect to MotherDuck tools, docs and more version: 1.0.0 host: api.motherduck.com basePath: / schemes: - https paths: /mcp: post: summary: MotherDuck Remote MCP description: The remote MCP to connect to MotherDuck tools, docs and more operationId: InvokeServer x-ms-agentic-protocol: mcp-streamable-1.0 responses: '200': description: Immediate Response securityDefinitions: api_key: type: apiKey in: header name: Authorization security: - api_key: [] ``` In Power Automate, go to **Custom connectors → New custom connector → Import an OpenAPI file**, paste the spec above, and save. When you create a connection, enter `Bearer ` as the API key value — the same format as the native MCP flow described above.
### Others If you're using **Windsurf**, **Zed**, or another MCP-compatible client, use the following JSON configuration: ```json { "mcpServers": { "MotherDuck": { "url": "https://api.motherduck.com/mcp", "type": "http" } } } ``` :::tip Authentication The remote MCP server uses OAuth, so you'll authenticate with your MotherDuck account during setup. Some clients also support [token-based authentication](/key-tasks/authenticating-and-connecting-to-motherduck/authenticating-to-motherduck#creating-an-access-token) through a Bearer header. ::: ## Configuring tool permissions Most MCP clients let you control how the AI uses each tool. The exact UI varies by client, but the general permission levels are: | Permission | Behavior | |------------|----------| | **Always allow** | The AI uses the tool automatically without asking. Faster iteration when errors occur, but no human confirmation before each action. | | **Needs approval** | The AI asks for your confirmation before each tool use. Gives you visibility into every action. | | **Blocked** | The AI cannot use this tool. | :::tip The MCP Server provides both read-only (`query`) and read-write (`query_rw`) tools. For exploratory analysis, setting read-only tools to "Always allow" enables faster back-and-forth when the AI needs to retry or refine queries. You can keep `query_rw` on "Needs approval" or block it if you only need read access. See [Restricting to read-only access](/key-tasks/ai-and-motherduck/securing-read-only-access/) for more options. ::: ## Remote vs local MCP server MotherDuck offers two MCP server options: | Server | Best for | Setup | Access | |--------|----------|--------|--------| | **Remote** (hosted by MotherDuck) | Most users who query and modify data on MotherDuck cloud | Zero setup; connect through URL and OAuth | Read-write | | **Local** ([mcp-server-motherduck](https://github.com/motherduckdb/mcp-server-motherduck)) | Self-hosted use; local DuckDB files; or when you need full customization | Install and run the server yourself | Fully customizable | The **remote server** is recommended for most use cases. Use the **local server** when you need to work with local DuckDB files, want custom tool configurations, or require full control over the server environment. [**Local MCP Server GitHub Repository** – Self-host the open-source MCP server for DuckDB and MotherDuck](https://github.com/motherduckdb/mcp-server-motherduck) ## Where to go from here - **[AI Data Analysis Getting Started](/getting-started/mcp-getting-started/)**: 5-minute walkthrough of querying data and creating Dives - **[MCP Workflows Guide](/key-tasks/ai-and-motherduck/mcp-workflows/)**: Best practices for getting accurate results from AI-powered analysis - **[MCP Server Reference](/sql-reference/mcp/)**: Server capabilities, available tools, and regional availability - **[Restricting to Read-Only Access](/key-tasks/ai-and-motherduck/securing-read-only-access/)**: Restrict your AI assistant to read-only queries --- ## Using the MotherDuck MCP Server Source: https://motherduck.com/docs/key-tasks/ai-and-motherduck/mcp-workflows > Effective workflows and best practices for getting the most out of the MotherDuck MCP Server with AI assistants The MotherDuck **remote** MCP Server, available at `https://api.motherduck.com/mcp`, connects AI assistants like Claude, ChatGPT, and Cursor to your data. This guide covers workflows for getting accurate, useful analysis results. If you haven't already, [set up your remote MCP connection](/key-tasks/ai-and-motherduck/mcp-setup/). :::info Remote vs local MCP This guide is written for the **remote MCP** (fully managed by MotherDuck). Most of the tips apply to the **local MCP** (fully customizable, self-hosted) as well. For local MCP setup and details, see the [MCP reference](/sql-reference/mcp/#local-mcp-server). ::: ## Prerequisites To use the MotherDuck remote MCP server, you will need: - A MotherDuck account with at least one database - An AI client like Claude, Cursor, or ChatGPT already connected to the remote MCP server ([setup instructions](/key-tasks/ai-and-motherduck/mcp-setup/)) :::note Read vs write tools The remote MCP server exposes two query tools: `query` for read-only SQL and `query_rw` for SQL that can change data or schema. See the [query](/sql-reference/mcp/core/query/) and [query_rw](/sql-reference/mcp/core/query-rw/) references for details. To enforce read-only access, see [Restricting to read-only access](/key-tasks/ai-and-motherduck/securing-read-only-access/). ::: ## How it works When you ask an AI assistant a question about your data, here's what happens behind the scenes: 1. **Schema exploration**: The AI examines your database structure to understand available tables and columns 2. **Query generation**: Based on your question, the AI writes DuckDB SQL 3. **Query execution**: The remote MCP Server runs the query on MotherDuck 4. **Results interpretation**: The AI explains the results in natural language You can inspect which SQL query the MCP executed by expanding the tool call in the conversation: ![Inspecting the query executed by MCP](./img/mcp_inspect_query.png) When you create a Dive: 1. **Data analysis**: The AI agent queries your database to understand the data relevant to your request 2. **Visualization generation**: The agent generates an interactive React component with the SQL queries and chart configuration 3. **Inline preview**: The Dive renders in the conversation so you can iterate before saving. In clients that support the Dive Viewer MCP App (Claude web and desktop at launch), the preview runs against live data with the same components used in the MotherDuck UI. In other clients, you see a static preview with sample data, and the Dive queries live data once you open it in MotherDuck. 4. **Save to MotherDuck**: Each save is stored in your workspace and always queries live data, so there are no stale snapshots. You can find the Dive in the [MotherDuck UI](/key-tasks/dives/#finding-your-dives) under the Object Explorer or **Settings** → **Dives**. With the Dive Viewer, every edit creates a separate version automatically. 5. **Share with your team**: The agent can [share the underlying data](/sql-reference/mcp/dives/share-dive-data) with your organization so others can view and interact with the Dive ## Start with schema exploration Before diving into analysis, help the AI understand your data. This is a form of **context engineering**: by exploring your schema upfront, you hydrate the conversation with knowledge about your tables, columns, and relationships. This context carries forward, helping the AI write more accurate queries throughout your session. Start conversations by asking about your database structure: **Good first prompts:** - *"What databases and tables do I have access to?"* - *"Describe the schema of my `analytics` database"* - *"What columns are in the `orders` table and what do they contain?"* The remote MCP server provides tools for schema exploration that surface table relationships, data types, and any documentation you've added to your schema. :::tip If you have well-documented tables with [`COMMENT ON`](https://duckdb.org/docs/stable/sql/statements/comment_on.html) descriptions, the AI can use these to better understand your data's business meaning. ::: ## Frame questions with context The more context you provide, the better the results. Include relevant details like: - **Time ranges**: *"Show me orders from the last 30 days"* vs *"Show me orders"* - **Filters**: *"Analyze customers in the US with more than 5 purchases"* - **Metrics**: *"Calculate revenue as `quantity * unit_price`"* - **Output format**: *"Return results as a summary table with percentages"* **Example - Vague vs. Specific:** | ❌ Vague | ✅ Specific | |----------|-------------| | "Show me sales data" | "Show me total sales by product category for Q4 2024, sorted by revenue descending" | | "Find top customers" | "Find the top 10 customers by total order value in the last 12 months" | | "Analyze trends" | "Compare monthly active users month-over-month for 2024, showing growth rate" | ## Iterate Complex analysis works best as a conversation. Start simple, validate the results, then build up. Each exchange adds shared context, helping the AI write better queries as you go. While there is a temptation to get the perfect query in one shot, often insight comes as part of the process of data exploration. When iterating, it can be helpful to have source data nearby to help verify outputs. Our users have noted that using their existing BI dashboard to quickly validate that metrics are correct helps to develop intuition about the information provided by the AI assistants. ## Common workflow patterns ### Data profiling Quickly understand a new dataset: ```text "Profile the `transactions` table - show me: - Row count and date range - Distribution of key categorical columns - Summary statistics for numeric columns - Any null values or data quality issues" ``` :::tip DuckDB functions for EDA DuckDB has a few SQL functions that are great for hydrating context: - `DESCRIBE` which retrieves the metadata for a specific table - `SUMMARIZE` which gets summary stats for a table (can be large) - The `USING SAMPLE 10` clause (at the end of the query) which samples the data (can be large) - using it with a where clause to narrow down is very helpful for performance ::: ### Generating charts Some AI clients can generate visualizations directly from your query results. ChatGPT on the web and Claude Desktop both support creating charts as "artifacts" alongside your conversation. Visualizations help you spot trends and outliers faster than scanning tables, validate that query results make sense at a glance, and share insights with stakeholders who prefer visual formats. **Example prompts:** - *"Chart monthly revenue for 2024 as a line graph"* - *"Create a bar chart showing the top 10 customers by order count"* - *"Visualize the distribution of order values as a histogram"* - *"Show me a time series of daily active users with a 7-day moving average"* Once you have a chart, you can iterate on it just like query results: *"Add a trend line"*, *"Change to a stacked bar chart"*, or *"Break this down by region"*. :::note When using the MCP with more IDE-like interfaces, the MCP plays very nicely with libraries like `matplotlib` for building more traditional charts. ::: ### Querying private S3 buckets You can use the MCP to analyze files in private S3 buckets (Parquet, CSV, JSON) by storing your AWS credentials as a [secret in MotherDuck](/sql-reference/motherduck-sql-reference/create-secret/). ### MotherDuck UI You can create secrets directly in the [MotherDuck UI](https://app.motherduck.com) under **Settings → Secrets**. ![The MotherDuck secrets UI](./img/md_create_secret_ui.png) ### AWS SSO with credential chain This is recommended for desktop AI clients. If you use AWS SSO, you can refresh your credentials and store them in MotherDuck: 1. Create an AWS credential profile ```bash aws configure sso ``` 2. Authenticate with AWS SSO: ```bash aws sso login --profile ``` 3. Open a DuckDB client (for example, the CLI) and create a secret using the credential chain: ```sql ATTACH 'md:'; CREATE OR REPLACE SECRET IN MOTHERDUCK ( TYPE s3, PROVIDER credential_chain, CHAIN 'sso', PROFILE '' ); ``` This stores your AWS credentials in MotherDuck, making them available to the remote MCP server. :::note Run `aws sso login --profile ` before creating the secret to refresh your SSO token. Starting with DuckDB v1.4.0, credentials are validated at creation time. If your local credentials are not resolvable, the command will fail: use the correct `CHAIN` and `PROFILE` for your credential type, or add `VALIDATION 'none'` as a last resort to skip local validation. ::: :::note Credential expiration If you use temporary credentials (SSO, IAM roles), you'll need to refresh the secret when they expire by running the `CREATE OR REPLACE SECRET` command again. ::: Once your credentials are set up, you can ask your AI assistant to query any S3 bucket you have access to: ```text "Give me some analytics about s3://my-bucket/sales-data.parquet" ``` ![Exploring S3 data with MCP](./img/mcp_explore_s3.png) ### Use DuckDB and MotherDuck from Claude's remote sandbox Claude on the web can run Python and shell commands in a remote code execution sandbox. This is separate from Claude Code or Claude Desktop running on your machine. Use the remote MCP server for schema discovery, query generation, and server-side analysis. Use DuckDB directly when the task needs a running client process for local code execution or file handling. In Claude web, that DuckDB client can run inside Claude's remote sandbox. For example, if a teammate uploads a CSV or Parquet file to Claude and wants to enrich it with data from MotherDuck, Claude can use DuckDB in the sandbox to read the uploaded file, query MotherDuck, join the data, and write a downloadable result file. That avoids sending a large file or result set through MCP tool responses, which are designed for conversation context rather than bulk file transfer. To let Claude install DuckDB, load the MotherDuck extension, and query MotherDuck from the sandbox, organization owners can configure **Settings** → **Capabilities** → **Code execution and file creation** → **Allow network egress**. The **All domains** option gives the sandbox enough network access for this workflow, subject to your organization's policy. See Anthropic's [code execution and file creation documentation](https://support.claude.com/en/articles/12111783-create-and-edit-files-wit) for the security tradeoffs. The same requirement applies in other sandboxed agent environments: the DuckDB Python package or CLI runs as a client process, and the sandbox must allow that process to reach the package host, DuckDB extension download host, and MotherDuck service. Add your MotherDuck token as an environment variable in `.env` format: ```text MOTHERDUCK_TOKEN= ``` Use a scoped token that matches the task. A [read scaling token](/key-tasks/authenticating-and-connecting-to-motherduck/read-scaling/) is enough when Claude only needs to read from MotherDuck and write output files in its sandbox. Only add tokens to cloud environments whose users should have that access. ![Updating a Claude cloud environment with full network access and MotherDuck token environment variables](./img/claude-cloud-environment-env-vars.png) Changes to a cloud environment apply to new sessions. Before you start the workflow, select the cloud environment that has network access and `MOTHERDUCK_TOKEN` configured. ![Selecting a Claude cloud environment before starting a session](./img/claude-select-cloud-environment.png) Start with a small connection test: ```text Install the duckdb Python package and use it to run SELECT 42 from my MotherDuck account. Use the MotherDuck token I provide, and don't print the token. ``` Example CSV or Parquet workflow prompt: ```text Use Python with DuckDB for this file workflow. Connect to MotherDuck with the token I provide, read the uploaded CSV or Parquet file, join it to the relevant MotherDuck table, and write the enriched result as a downloadable CSV or Parquet file. ``` If direct DuckDB access isn't available, keep the heavy work in MotherDuck: ```text Use the MotherDuck MCP to create a table with the result instead of returning all rows in the chat. Tell me the table name and the SQL you used so I can export it from MotherDuck. ``` This fallback works when Claude's sandbox can't reach the MotherDuck extension download host or can't make outbound requests to MotherDuck. It also keeps large intermediate results out of the model's context window. ### Ad-hoc investigation The MCP is especially useful for exploratory debugging when you're not sure what you're looking for. Rather than writing queries upfront, you can describe the problem and let the AI help you dig in. ```text "I noticed a spike in errors on Dec 10th. Help me investigate: - What types of errors increased? - Were specific users or endpoints affected? - What changed compared to the previous week?" ``` One pattern we use at MotherDuck is loading logs or event data into a database and using the MCP to interrogate it conversationally. Instead of manually crafting regex patterns or grep commands, you can ask questions like *"What are the most common error messages in the last hour?"* or *"Show me all requests from user X that resulted in a 500 error"*. This turns log analysis from a tedious grep session into an interactive investigation where each answer informs the next question. ## Working with query results ### Refining results Results rarely come out perfect on the first try. The conversational nature of MCP means you can refine incrementally rather than rewriting queries from scratch. If you're seeing test data mixed in, just say *"Add a filter to exclude test accounts"*. If the granularity is wrong, ask to *"Change the grouping from daily to weekly"*. Small adjustments like changing sort order or adding a column are easy follow-ups. ### Understanding queries When the AI generates complex SQL, don't hesitate to ask for an explanation. This is useful both for validating the approach and for learning. Ask *"Explain what this query is doing step by step"* to understand the logic, or *"Are there any edge cases this query might miss?"* to sanity-check the results before relying on them. ### Exporting for further use Once you have the results you need, ask for output in the format that fits your workflow. Small result sets can be returned as a markdown table, spreadsheet-friendly CSV, or written summary. For larger exports, don't ask the MCP to stream all rows into the chat. Ask the AI to keep the result in MotherDuck with `CREATE TABLE AS SELECT ...` and give you the table name, or run a DuckDB client somewhere that can access both MotherDuck and the file destination. That client can be on your machine, in Claude Code, or in Claude's remote sandbox when its network rules allow the required hosts. Asking for the final SQL is also useful when you want to hand the analysis to another teammate or tool. ## Tips for better results ### Be explicit about assumptions Your data likely has business rules that aren't obvious from the schema alone. If a "completed" order means status is either 'shipped' or 'delivered', say so. If revenue calculations should exclude refunds, mention it upfront. The AI can't infer these domain-specific rules, so stating them early prevents incorrect results and saves iteration time. ### Reference specific tables and columns When you already know your schema, being specific helps the AI get it right the first time. Instead of asking about "the timestamp", say *"Use the `user_events.event_timestamp` column"*. If you know how tables relate, specify the join: *"Join `orders` to `customers` on `customer_id`"*. This is especially helpful in larger schemas where column names might be ambiguous. ### Ask for validation When accuracy matters, ask the AI to sanity-check its own work. Questions like *"Does this total match what you'd expect based on the row counts?"* or *"Can you verify this join doesn't create duplicates?"* can catch subtle bugs before you rely on the results. The AI can run quick validation queries to confirm the logic is sound. ## Troubleshooting :::tip Beyond querying The remote MCP server includes tools beyond just running queries. Most are metadata lookups or search functions for finding tables and columns, but the [ask docs question](/sql-reference/mcp/core/ask-docs-question) tool is particularly useful when you're stuck on tricky syntax or DuckDB-specific features. If the AI is struggling with a query pattern, try asking it to look up the relevant documentation first. ::: | Issue | Solution | |-------|----------| | AI queries wrong table | Ask: *"What tables are available?"* then specify the correct one | | Results don't look right | Ask: *"Show me sample data from the source table"* to verify the data | | Query is slow | Ask: *"Can you optimize this query?"*, add filters to reduce data scanned, or [increase your Duckling size](/about-motherduck/billing/duckling-sizes/) | | AI doesn't understand the question | Rephrase with more specific column names and business context | | Can't type fast enough | Use voice-to-text to interact with your AI assistant | ## Related resources - [Connect to MCP Server](/key-tasks/ai-and-motherduck/mcp-setup/) - Setup instructions for all supported AI clients - [AI Features in the UI](/key-tasks/ai-and-motherduck/ai-features-in-ui/) - Built-in AI features for the MotherDuck interface - [Building Analytics Agents](/key-tasks/ai-and-motherduck/building-analytics-agents/) - Build custom AI agents with MotherDuck --- ## Install MotherDuck Skills for coding agents Source: https://motherduck.com/docs/key-tasks/ai-and-motherduck/agent-skills > Install the MotherDuck Skills plugin catalog to teach Claude Code, Cursor, Codex, Copilot CLI, and Gemini CLI to work with MotherDuck. [MotherDuck Skills](https://github.com/motherduckdb/agent-skills/) is an opinionated, installable catalog of [agent skills](https://agentskills.io/home) that teaches coding agents how to work with MotherDuck. The skills cover picking the right connection path, writing DuckDB SQL (not Postgres-shaped SQL), inspecting a live workspace, and shipping production analytics patterns safely. Skills work with any DuckDB client — they teach the agent behavior, not how to connect. Pairing them with the [MotherDuck MCP server](/key-tasks/ai-and-motherduck/mcp-setup/) is recommended but not required: MCP gives the agent live access to your workspace so it can inspect real schemas while it applies the guidance from the skills. ## What agent skills are Agent skills are reusable instruction bundles for AI coding agents. They give the agent domain-specific guidance it can apply during a task, such as which tools to use, which SQL dialect rules matter, what safety checks to run, and what good output should look like. MotherDuck Skills do not connect to your account or run queries on their own. Instead, your agent loads the relevant skill when a task calls for MotherDuck-specific knowledge. Use them when you want the agent to: - Choose between the MotherDuck MCP server, a Postgres-compatible endpoint, a native `md:` DuckDB connection, or the REST API. - Inspect a workspace and summarize databases, schemas, tables, and columns before writing queries. - Write DuckDB SQL that works in MotherDuck instead of PostgreSQL-shaped SQL. - Load files or application data into MotherDuck with repeatable validation steps. - Design a dashboard, Dive, customer-facing analytics app, or data pipeline on top of MotherDuck. - Plan a migration to MotherDuck, including validation, rollout, and rollback steps. ## Prerequisites Before you install: - Git available on your `PATH`. - Node.js 18 or later (required for the Skills CLI). - A MotherDuck account and one of the supported agent harnesses below. For live MotherDuck work, authenticate through your normal path — a `MOTHERDUCK_TOKEN`, the [Postgres endpoint](/sql-reference/postgres-endpoint/), a native `md:` DuckDB connection, or [MotherDuck MCP](/key-tasks/ai-and-motherduck/mcp-setup/). Do not paste tokens into prompts or skill files. ## Install Pick your agent harness and run the command. Each install pulls the full MotherDuck Skills catalog. | Harness | Install | |---|---| | Claude Code | `/plugin marketplace add motherduckdb/agent-skills` then `/plugin install motherduck-skills@motherduck-skills` | | GitHub Copilot CLI | `/plugin marketplace add motherduckdb/agent-skills` then `/plugin install motherduck-skills@motherduck-skills` | | Codex | `codex plugin marketplace add motherduckdb/agent-skills`, then install **MotherDuck Skills** from `/plugins` | | Cursor | `npx -y skills add motherduckdb/agent-skills --agent cursor --skill '*' --yes --global` | | Gemini CLI | `gemini extensions install https://github.com/motherduckdb/agent-skills --consent` | For other agents, project-scoped installs, or to install individual skills, use Vercel's portable [Skills CLI](https://github.com/vercel-labs/skills). See Vercel's [Agent Skills documentation](https://vercel.com/docs/agent-resources/skills) for more details. ```bash npx -y skills add motherduckdb/agent-skills --skill '*' --yes --global ``` Check what got installed: ```bash npx -y skills ls -g ``` ## Verify the installation After install, try this prompt to confirm the skills are wired up: > Use MotherDuck Skills to choose the best connection path for this project. You should get MotherDuck-specific connection guidance, including the Postgres endpoint and native DuckDB tradeoffs. ## Prompts to try Once the skills are installed, these prompts route to the right skill automatically: - `Use MotherDuck Skills to connect this app to MotherDuck.` - `Explore my MotherDuck workspace and identify the best table for a dashboard.` - `Write a DuckDB SQL query for this KPI and validate the syntax.` - `Design a Dive-backed dashboard from these tables.` - `Plan a Snowflake-to-MotherDuck migration with validation and rollback steps.` - `Design a customer-facing analytics architecture on MotherDuck.` - `Decide whether this workload needs DuckLake or native MotherDuck storage.` - `Use the MotherDuck REST API guidance to manage service accounts and tokens safely.` ## How the catalog is organized The catalog has three layers. Agents pick the right layer based on the task. **Utility skills** cover exact MotherDuck mechanics: connect, explore, query, use the REST API, or check DuckDB SQL behavior. Start here for narrow technical work. **Workflow skills** cover multi-step work with MotherDuck-specific tradeoffs: loading data, modeling, sharing, building [Dives](/key-tasks/dives/), evaluating DuckLake, planning security and governance, or framing pricing and ROI. **Use-case skills** cover designing or shipping a product surface: building customer-facing analytics, a dashboard, or a data pipeline; planning a migration to MotherDuck; rolling out self-serve analytics; or delivering repeatable partner implementations. For the full skill list and the latest install paths, see the [agent-skills repository](https://github.com/motherduckdb/agent-skills/). --- ## Restricting to read-only access Source: https://motherduck.com/docs/key-tasks/ai-and-motherduck/securing-read-only-access > Restrict the remote MCP server to read-only queries using client-side blocking, read scaling tokens, or proxy filtering # Restricting to read-only access The remote MCP server exposes both the read-only `query` tool and the read-write `query_rw` tool. If you want to ensure your AI assistant can only read data, there are three approaches depending on your setup. | Approach | Enforcement | Setup | Works with OAuth connectors | |----------|------------|-------|-----------------------------| | [Block the tool at the client](#block-the-query_rw-tool-at-the-client) | Client-side | Low (UI toggle) | Yes | | [Use a read scaling token](#use-a-read-scaling-token) | Server-side | Medium (manual config) | No (replaces OAuth) | | [Proxy filtering](#proxy-filtering) | Application-side | Varies | N/A (custom backend) | ## Block the `query_rw` tool at the client The simplest approach: keep using the OAuth connector, but configure your MCP client to never call the `query_rw` tool. The server still exposes the tool, but the client will never invoke it. Most clients support this at the **individual user** level. ChatGPT also lets **organization admins** enforce tool restrictions across all workspace members. ### Claude Each user can block tools individually. Go to **Settings → Connectors → MotherDuck**, expand **Write/delete tools**, and select the blocked icon next to `query_rw`: ![Blocking the query_rw tool in Claude's connector settings](./img/query-rw-blocked.png) :::note Claude does not support org-level per-tool blocking. Team/Enterprise admins can remove a connector entirely from **Organization settings → Connectors**, but cannot selectively disable individual tools like `query_rw` for all members. ::: > [Claude connector permissions documentation](https://support.claude.com/en/articles/11175166-get-started-with-custom-connectors-using-remote-mcp) ### ChatGPT **Enterprise/Edu admins:** Admins can [enable or disable specific app actions after publishing](https://help.openai.com/en/articles/12584461-developer-mode-and-full-mcp-connectors-in-chatgpt-beta). Go to **Workspace Settings → Apps**, click the `...` menu next to MotherDuck, select **Action control**, and deselect `query_rw`. New tools added by the MCP server are disabled by default — admins must explicitly enable them. **Business plans:** Per-tool Action control is not available for custom MCP apps after publishing. To change which tools are exposed, remove and recreate the app ([developer mode documentation](https://help.openai.com/en/articles/12584461-developer-mode-and-full-mcp-connectors-in-chatgpt-beta)). ### Cursor Open **Cursor Settings** → **Tools & MCP**, expand the MotherDuck server entry, and toggle off `query_rw`. :::note Tool toggles are stored locally in Cursor's database, not in the `mcp.json` config file. They cannot be shared across a team through config files. ::: ### Claude Code Add a deny rule to your `.claude/settings.json` (project-level) or `~/.claude/settings.json` (user-level): ```json { "permissions": { "deny": ["mcp__MotherDuck__query_rw"] } } ``` > [Claude Code permissions documentation](https://code.claude.com/docs/en/permissions) ### Copilot Studio Open your agent in Copilot Studio, go to **Tools**, and open the MotherDuck MCP entry. Toggle `query_rw` off in the tool list and click **Save**. The agent only sees `query` and the schema exploration tools. ![MotherDuck MCP tool list in Copilot Studio with query_rw toggled off](/img/key-tasks/ai-and-motherduck/copilot-studio/07-tools-list.png) ## Use a read scaling token For server-side enforcement, authenticate with a [read scaling token](/key-tasks/authenticating-and-connecting-to-motherduck/read-scaling/) instead of a regular access token. Read scaling tokens connect to dedicated read replicas that reject all write operations — even if the client calls `query_rw`, writes will fail. This requires manual configuration instead of the one-click OAuth connectors. :::note Read scaling connections are [eventually consistent](/key-tasks/authenticating-and-connecting-to-motherduck/read-scaling/#ensuring-data-freshness). Results may lag a few minutes behind the latest database state. ::: You can create a read scaling token from the [MotherDuck UI](https://app.motherduck.com) under **Settings → Access Tokens** or through the [REST API](/sql-reference/rest-api/users-create-token/). Read scaling tokens also unlock concurrent MCP sessions: each MCP instance that connects with a read scaling token is assigned to a read replica (duckling) from a pool. Up to the pool size (default 4, max 16), each connection gets its own duckling; once the pool is full, new connections are assigned to existing ducklings in round-robin. This means you can run many MCP sessions in parallel from the same account—for example, multiple AI agents or team members querying simultaneously. See [Read Scaling](/key-tasks/authenticating-and-connecting-to-motherduck/read-scaling/) for details on pool sizing and how replicas are assigned. ### Claude Claude's web connector only supports OAuth, so you need to use the desktop config instead. Open **Settings → Developer → Edit Config** and add: ```json { "mcpServers": { "MotherDuck": { "command": "npx", "args": [ "mcp-remote", "https://api.motherduck.com/mcp", "--header", "Authorization: Bearer ${MOTHERDUCK_TOKEN}" ], "env": { "MOTHERDUCK_TOKEN": "" } } } } ``` This uses [`mcp-remote`](https://www.npmjs.com/package/mcp-remote) to bridge the remote MCP server into Claude Desktop's local stdio transport. ### ChatGPT ChatGPT connectors can't set static headers. To use a read scaling token, run a proxy that injects the `Authorization` header and connect ChatGPT to that proxy. Example proxy (Cloudflare Worker): ```js export default { async fetch(request, env) { const upstreamUrl = new URL(request.url); upstreamUrl.protocol = "https:"; upstreamUrl.hostname = "api.motherduck.com"; upstreamUrl.pathname = "/mcp"; const upstreamRequest = new Request(upstreamUrl, request); upstreamRequest.headers.set( "Authorization", `Bearer ${env.MOTHERDUCK_READ_SCALING_TOKEN}` ); upstreamRequest.headers.delete("cookie"); return fetch(upstreamRequest); }, }; ``` 1. Deploy the proxy and store the read scaling token as a secret (for example, `MOTHERDUCK_READ_SCALING_TOKEN`). 2. In [ChatGPT Settings → Connectors](https://chatgpt.com/#settings/Connectors), click **Create App**. 3. Enter: - **Name:** `MotherDuck (Read Only)` - **MCP Server URL:** `` - **Authentication:** `No authentication` 4. Open a chat, select the connector, and run a query (for example: `SELECT * FROM information_schema.tables LIMIT 5`). `query_rw` may still appear, but writes fail because read scaling tokens are read-only. ### Cursor Open **Cursor Settings** → **Tools & MCP** → **+ New MCP Server** and add the following configuration: ```json { "MotherDuck": { "url": "https://api.motherduck.com/mcp", "type": "http", "headers": { "Authorization": "Bearer " } } } ``` ### Claude Code ```bash claude mcp add --transport http \ --header "Authorization: Bearer " \ MotherDuck https://api.motherduck.com/mcp ``` ### Copilot Studio Follow the [Copilot Studio MCP setup](/key-tasks/ai-and-motherduck/mcp-setup/?mcp-client=copilot-studio) with **API key** authentication, and when prompted for the connection value, enter your read scaling token: ```text Bearer ``` The `query_rw` tool may still appear in the agent's tool list, but writes fail at the server because read scaling replicas reject write operations. For belt-and-braces, also toggle `query_rw` off in the tool list so the model never sees it as an option. ![MotherDuck MCP tool list in Copilot Studio with query_rw toggled off](/img/key-tasks/ai-and-motherduck/copilot-studio/07-tools-list.png) ### Others For MCP-compatible clients that support simple authentication, use the following JSON configuration with a read scaling token as the Bearer value: ```json { "mcpServers": { "MotherDuck": { "url": "https://api.motherduck.com/mcp", "type": "http", "headers": { "Authorization": "Bearer " } } } } ``` For clients that only support local (stdio) servers, use `mcp-remote` to bridge the connection: ```json { "mcpServers": { "MotherDuck": { "command": "npx", "args": [ "mcp-remote", "https://api.motherduck.com/mcp", "--header", "Authorization: Bearer ${MOTHERDUCK_TOKEN}" ], "env": { "MOTHERDUCK_TOKEN": "" } } } } ``` ## Proxy filtering If you're integrating the remote MCP server into a backend service or custom agent framework, you can restrict access at the application layer. When proxying MCP tool calls, omit or reject calls to the `query_rw` tool and only forward calls to the read-only `query` tool and schema exploration tools. See [Building Analytics Agents](/key-tasks/ai-and-motherduck/building-analytics-agents) for patterns on building custom agent integrations with read-only access controls. --- ## AI Features in the MotherDuck UI Source: https://motherduck.com/docs/key-tasks/ai-and-motherduck/ai-features-in-ui > Use AI-powered SQL editing, FixUp, and natural language queries in the MotherDuck web interface. :::tip Quick overview For a hands-on walkthrough of FixIt and Edit in the web UI, see the [Web UI guide](/getting-started/interfaces/motherduck-quick-tour/#fix-errors-and-edit-queries-with-ai). ::: ## Automatically Edit SQL Queries in the MotherDuck UI Edit is a MotherDuck AI-powered feature which allows you to edit SQL queries in the MotherDuck UI. The AI is aware of DuckDB-specific SQL features and relevant database schemas to provide effective suggestions. Select the specific part of the query you want to edit, then press the keyboard shortcut to open the Edit dialog: * Windows/Linux: `Ctrl + Shift + E` * macOS: `⌘ + Shift + E` In the Edit dialog, enter your prompt (e.g., "extract the domain from the url, using a regex") and click Suggest edit. ![Edit](../img/edit-prompt.png) If the suggestion is not as desired, it can be further clarified with follow-up prompts. ![Edit](../img/edit-follow-up.png) When happy with the change, click 'Apply edit', and the change will be applied to the query. ![Edit](../img/edit-follow-up-2.png) ## Automatically Fix SQL Errors in the MotherDuck UI FixIt is a MotherDuck AI-powered feature that helps you resolve common SQL errors by offering fixes in-line. Read more about it in our [blog post](https://motherduck.com/blog/introducing-fixit-ai-sql-error-fixer/). FixIt can also be called programmatically using the `prompt_fix_line` . Find more information in the [prompt_fix_line documentation](/sql-reference/motherduck-sql-reference/ai-functions/sql-assistant/prompt-fix-line). ### How FixIt works By default, FixIt is enabled for all users. If you run a query that has an error, FixIt will automatically analyze the query and suggest in-line fixes. When accepting a fix, MotherDuck will automatically update your query and re-execute it. ![FixIt](../img/fixit-suggestion.png) When 'Auto-suggest' is un-toggled, FixIt will not automatically suggest fixes anymore. FixIt can still be manually triggered by clicking 'Suggest fix' at the bottom of the error message. ![FixIt](../img/fixit-manual-suggestion.png) ## Access SQL Assistant functions MotherDuck provides built-in AI features to help you write, understand and fix DuckDB SQL queries more efficiently. These features include: - [Answer questions about your data](/sql-reference/motherduck-sql-reference/ai-functions/sql-assistant/prompt-query) using the `prompt_query` pragma. - [Generate SQL](/sql-reference/motherduck-sql-reference/ai-functions/sql-assistant/prompt-sql) for you using the `prompt_sql` table function. - [Correct and fix up your SQL query](/sql-reference/motherduck-sql-reference/ai-functions/sql-assistant/prompt-fixup) using the `prompt_fixup` table function. - [Correct and fix up your SQL query line-by-line](/sql-reference/motherduck-sql-reference/ai-functions/sql-assistant/prompt-fix-line) using the `prompt_fix_line` table function. - [Help you understand a query](/sql-reference/motherduck-sql-reference/ai-functions/sql-assistant/prompt-explain) using the `prompt_explain` table function. - [Help you understand contents of a database](/sql-reference/motherduck-sql-reference/ai-functions/sql-assistant/prompt-schema) using the `prompt_schema` table function. ### Example usage of prompt_sql We use MotherDuck's sample [Hacker News dataset](/getting-started/sample-data-queries/hacker-news) from [MotherDuck's sample data database](/getting-started/sample-data-queries/datasets). ```sql CALL prompt_sql('what are the top domains being shared on hacker_news?'); ``` Output of this SQL statement is a single column table that contains the AI-generated SQL query. | **query** | |-----------------| | ```sql SELECT COUNT(*) as domain_count, SUBSTRING(SPLIT_PART(url, '//', 2), 1, POSITION('/' IN SPLIT_PART(url, '//', 2)) - 1) as domain FROM hn.hacker_news WHERE url IS NOT NULL GROUP BY domain ORDER BY domain_count DESC LIMIT 10``` | --- ## Custom AI Agent Builder's Guide Source: https://motherduck.com/docs/key-tasks/ai-and-motherduck/building-analytics-agents > Build AI-powered analytics agents using MotherDuck's SQL functions and MCP server integration. # Building analytics agents with MotherDuck Analytics agents are AI-powered systems that allow users to interact with data using natural language. Instead of writing SQL queries or building dashboards, users can ask questions like "What were our top-selling products last quarter?" and get immediate answers. This guide covers best practices for building production-ready analytics agents on MotherDuck. ## Prerequisites - **Agent framework**: [Claude Agent SDK](https://docs.anthropic.com/en/api/agent-sdk/overview), [OpenAI Agents SDK](https://openai.github.io/openai-agents-python/), or Claude Desktop with MotherDuck remote MCP connector - **MotherDuck account** with the data you want to query - **Clean, well-structured data**: The better your schema and metadata, the better your agent performs ## Step 1: Define your agent's interface Choose the interface your agent will use to query your MotherDuck database. ### Option A: Generated SQL The agent generates SQL queries and executes them through a tool/function call. This provides maximum flexibility - agents can answer any question your data supports - but requires good SQL generation capabilities. **Implementation approaches:** **MCP Server**: Use our [remote MCP Server](/key-tasks/ai-and-motherduck/mcp-setup/) (or [local MCP server](/key-tasks/ai-and-motherduck/mcp-setup/#remote-vs-local-mcp-server) for self-hosted, read-write) for Claude Desktop, Cursor, ChatGPT, or Claude Code **Custom tool calling**: Create a function that accepts SQL strings and executes them: ### Python ```python import duckdb def execute_sql(query: str) -> str: """Execute SQL query against MotherDuck""" conn = duckdb.connect('md:my_database?motherduck_token=') try: result = conn.execute(query).fetchdf() return result.to_string() except Exception as e: return f"Error: {str(e)}" ``` ### Option B: Parameterized query templates The agent receives structured parameters that fill predefined SQL templates. This provides strict correctness guarantees and is easier to validate, but is less flexible and requires more upfront development with queries limited to predefined questions. **Example**: Agent chooses calling a custom tool with a domain-specific signature like `get_sales_by_region(region: str, start_date: date, end_date: date)` instead of generating custom SQL. **Recommendation**: Start with Option A (SQL generation) unless you have strict correctness requirements or very limited query patterns. ## Step 2: Give your agent SQL knowledge Your LLM needs to know how to write good DuckDB queries. ### System prompt for DuckDB and MotherDuck A system prompt is the foundational instruction set that guides your agent's behavior and capabilities. It's critical for ensuring your agent generates correct, efficient SQL queries and understands how to explore data effectively. The query guide below should be added to your system prompt because it contains: - DuckDB SQL syntax and conventions - Common patterns and best practices - How to explore schemas efficiently
query_guide.md ```text # DuckDB SQL Query Syntax and Performance Guide ## General Knowledge ### Basic Syntax and Features **Identifiers and Literals:** - Use double quotes (`"`) for identifiers with spaces/special characters or case-sensitivity - Use single quotes (`'`) for string literals **Flexible Query Structure:** - Queries can start with `FROM`: `FROM my_table WHERE condition;` (equivalent to `SELECT * FROM my_table WHERE condition;`) - `SELECT` without `FROM` for expressions: `SELECT 1 + 1 AS result;` - Support for `CREATE TABLE AS` (CTAS): `CREATE TABLE new_table AS SELECT * FROM old_table;` **Advanced Column Selection:** - Exclude columns: `SELECT * EXCLUDE (sensitive_data) FROM users;` - Replace columns: `SELECT * REPLACE (UPPER(name) AS name) FROM users;` - Pattern matching: `SELECT COLUMNS('sales_.*') FROM sales_data;` - Transform multiple columns: `SELECT AVG(COLUMNS('sales_.*')) FROM sales_data;` **Grouping and Ordering Shortcuts:** - Group by all non-aggregated columns: `SELECT category, SUM(sales) FROM sales_data GROUP BY ALL;` - Order by all columns: `SELECT * FROM my_table ORDER BY ALL;` **Complex Data Types:** - Lists: `SELECT [1, 2, 3] AS my_list;` - Structs: `SELECT {'a': 1, 'b': 'text'} AS my_struct;` - Maps: `SELECT MAP([1,2],['one','two']) AS my_map;` - Access struct fields: `struct_col.field_name` or `struct_col['field_name']` - Access map values: `map_col[key]` **Date/Time Operations:** - String to timestamp: `strptime('2023-07-23', '%Y-%m-%d')::TIMESTAMP` - Format timestamp: `strftime(NOW(), '%Y-%m-%d')` - Extract parts: `EXTRACT(YEAR FROM DATE '2023-07-23')` ### Database and Table Qualification **Fully Qualified Names:** - Tables are accessed by fully qualified names: `database_name.schema_name.table_name` - There is always one current database: `SELECT current_database();` - Tables from the current database don't need database qualification: `schema_name.table_name` - Tables in the main schema don't need schema qualification: `table_name` - Shorthand: `my_database.my_table` is equivalent to `my_database.main.my_table` **Switching Databases:** - Use `USE my_other_db;` to switch current database - After switching, tables in that database can be accessed without qualification ### Schema Exploration **Get database and table information:** - List all databases: `SELECT alias as database_name, type FROM MD_ALL_DATABASES();` - List tables in database: `SELECT database_name, schema_name, table_name, comment FROM duckdb_tables() WHERE database_name = 'your_database';` - List views in database: `SELECT database_name, schema_name, view_name, comment, sql FROM duckdb_views() WHERE database_name = 'your_database';` - Get column information: `SELECT column_name, data_type, comment, is_nullable FROM duckdb_columns() WHERE database_name = 'your_database' AND table_name = 'your_table';` **Sample data exploration:** - Quick preview: `SELECT * FROM table_name LIMIT 5;` - Column statistics: `SUMMARIZE table_name;` - Describe table: `DESCRIBE table_name;` ### Performance Tips **QUALIFY Clause for Window Functions:** -- Get top 2 products by sales in each category SELECT category, product_name, sales_amount FROM products QUALIFY ROW_NUMBER() OVER (PARTITION BY category ORDER BY sales_amount DESC) <= 2; **Efficient Patterns:** - Use `arg_max()` and `arg_min()` for "most recent" queries - Filter early to reduce data volume - Use CTEs for complex queries - Prefer `GROUP BY ALL` for readability - Use `QUALIFY` instead of subqueries for window function filtering **Avoid These Patterns:** - Functions on the left side of WHERE clauses (prevents pushdown) - Unnecessary ORDER BY on intermediate results - Cross products and cartesian joins ```
### Function documentation MotherDuck maintains `function_docs.jsonl` - compact, LLM-friendly documentation for every DuckDB/MotherDuck function available at: https://app.motherduck.com/assets/docs/function_docs.jsonl **How to use**: 1. When user asks a question, search function docs using FTS or semantic search 2. Add the 5 most relevant function descriptions to the agent's context 3. This helps with specialized functions (window functions, date arithmetic, JSON operations, etc.) ## Step 3: Give your agent schema context Your agent needs to understand your database structure to generate correct queries. ### Finding relevant tables Our `query_guide.md` explains how agents can explore schemas autonomously to find relevant tables. For faster, non-agentic identification, use the built-in `INFORMATION_SCHEMA`. ```sql -- adjust the search terms and database(s) to your needs SELECT table_schema, table_name, table_comment FROM information_schema."tables" where table_catalog = current_database() and table_name like '%sales%' or table_name like '%customer%' or table_name like '%cust%' or table_comment like '%sales%' or table_comment like '%customer%'; ``` For column level information you can use `information_schema.columns`. ### Make schemas agent-friendly **Use clear naming**: Choose explicit, unambiguous table and column names ❌ Bad: `ord_dtl`, `cust_id`, `amt` ✅ Good: `order_details`, `customer_id`, `total_amount` **Add context with COMMENT ON**: ```sql COMMENT ON TABLE orders IS 'Customer orders since 2020. Join to customers via customer_id'; COMMENT ON COLUMN orders.status IS 'Possible values: pending, shipped, delivered, cancelled'; COMMENT ON COLUMN orders.total_amount IS 'Total in USD including tax and shipping'; ``` Comments help agents understand table relationships, valid values, and business logic. Learn more: [COMMENT ON documentation](https://duckdb.org/docs/stable/sql/statements/comment_on.html) ## Step 4: Configure access controls Secure your agent's database access with appropriate permissions and isolation. ### Read-only access Use [read-scaling tokens](/key-tasks/authenticating-and-connecting-to-motherduck/read-scaling/) to ensure your agent only has read access. Read-scaling tokens connect to dedicated read replicas that cannot modify data. ### Python ```python import duckdb # Using a read-scaling token ensures read-only access con = duckdb.connect('md:my_database?motherduck_token=') ``` **For multi-tenant [customer-facing analytics](/getting-started/customer-facing-analytics/) agents**: Use [service accounts](/key-tasks/service-accounts-guide/create-and-configure-service-accounts/) for your agents. You can grant these service accounts read-only access to specific databases using [shares](/key-tasks/sharing-data/sharing-overview/): ```sql ATTACH 'md:_share/my_org/abc123' AS shared_data; ``` Consider creating separate service accounts per user/tenant for full compute isolation. **Capacity planning**: Choose the number of [read scaling](/key-tasks/authenticating-and-connecting-to-motherduck/read-scaling/) replicas and [Duckling size](/about-motherduck/billing/duckling-sizes/) according to the expected query complexity and concurrency. ### Read-write access & sandboxing For agents that need to create tables, modify data, or experiment safely, use zero-copy clones to create an isolated sandbox. This provides safe experimentation completely isolated from production data, with instant creation through zero-copy operations. Agents get full capabilities to create tables, modify data, and experiment freely, with easy sharing of results back to production when ready. ```sql -- Create instant writable copy (clones must match source retention type) CREATE DATABASE my_sandbox FROM my_database_share; -- Agent can now read/write without affecting production data -- Changes are isolated to this copy ``` Learn more: [CREATE DATABASE documentation](/sql-reference/motherduck-sql-reference/create-database/) ## Step 5: Implement your agent Build your agent using an SDK or framework that supports function calling. **Quick start option**: For immediate experimentation, try [Claude Desktop with the MotherDuck remote MCP Server](/key-tasks/ai-and-motherduck/mcp-setup/) - no coding required. **Custom agent option**: Here's a simple example using the [OpenAI Agents SDK](https://openai.github.io/openai-agents-python/): ### Python ```python import duckdb from agents import Agent, Runner, function_tool # Connect to MotherDuck (use a read-scaling token for read-only access) conn = duckdb.connect('md:?motherduck_token=') @function_tool def query_motherduck(sql: str) -> str: """Execute SQL query against MotherDuck database. Args: sql: The SQL query to execute against the MotherDuck database. """ try: result = conn.execute(sql).fetchdf() return result.to_string() except Exception as e: return f"Error executing query: {str(e)}" # Load the DuckDB query guide (copy the system prompt template above into a local file) with open('query_guide.md', 'r') as f: query_guide = f.read() # Create agent with database tool agent = Agent( name="MotherDuck Analytics Agent", instructions=f"""You are a data analyst helping users query a MotherDuck database. Use the query_motherduck tool to execute SQL queries against the database. Always start with schema exploration before querying specific tables. {query_guide} """, tools=[query_motherduck] ) # Run the agent result = Runner.run_sync( agent, "What were the top 5 products by revenue last month?" ) print(result.final_output) ``` ### Validating queries before showing to users If a human reviews generated queries before execution, use `try_bind()` to validate SQL without running it. It checks syntax and referenced tables/columns in milliseconds. **Structured output:** `try_bind()` returns `error_message` (VARCHAR) and `error_type` (VARCHAR). Use `error_type` to decide what to do next: `ok` means validation passed, `parser` means SQL syntax is invalid, and `binder` means object resolution failed (for example, a missing table/column or invalid reference). On `parser` or `binder`, pass `error_message` back into the next generation attempt so the model can repair the query. ```sql -- Valid query - error_type is 'ok', error_message is empty CALL try_bind('SELECT customer_id, total FROM orders WHERE status = ''shipped'''); -- Invalid query - returns error_message and error_type (e.g. 'parser' or 'binder') CALL try_bind('SELECT * FORM orders'); ``` **Example integration:** ### Python ```python def generate_query_for_review(question: str) -> str: """Generate and validate SQL before showing to user.""" error_msg = None for attempt in range(3): sql = agent.generate_sql(question, error_feedback=error_msg) # Validate before showing (error_message, error_type) row = conn.execute("CALL try_bind(?)", [sql]).fetchall()[0] error_message, error_type = row[0], row[1] if error_type == "ok": return f"Generated query:\n{sql}" error_msg = error_message or f"Validation failed: {error_type}" return "Could not generate a valid query to answer the question" ``` Feed `error_message` and `error_type` from `try_bind()` into retries to fix syntax and binding errors. ## Step 6: Test and iterate Validate your agent's performance and refine its behavior based on real-world usage. ### Testing and quality Choose a set of realistic user questions that cover simple filters ("Show me sales from last month"), complex analysis ("What's the trend in customer retention by region?"), and edge cases like empty results ("Show me sales for December 2019") or ambiguous requests ("Show me the best customers"). Test each question and check the agent's behavior. Focus on SQL correctness, result accuracy and query performance. See the next section for how to tackle common issues. ### Common issues and solutions | Issue | Solution | |-------|----------| | Invalid SQL generation | Improve system prompt, add [function docs](#function-documentation) to context | | Wrong tables queried | Add [COMMENT ON](https://duckdb.org/docs/stable/sql/statements/comment_on.html), improve schema descriptions, implement table filtering | | Misunderstood questions | Add domain-specific examples to system prompt | | Query performance | [EXPLAIN ANALYZE](/sql-reference/motherduck-sql-reference/explain-analyze/) to diagnose query inefficiencies, adjust [Duckling size](/about-motherduck/billing/duckling-sizes/) to scale compute resources | ## Next steps - Explore our [MCP Server](/sql-reference/mcp/) docs (remote and local) - Try [AI Features in the MotherDuck UI](/key-tasks/ai-and-motherduck/ai-features-in-ui/) with Generate SQL & Edit - Learn about [Read Scaling](/key-tasks/authenticating-and-connecting-to-motherduck/read-scaling/) for multi-tenant agents - Review [Shares](/key-tasks/sharing-data/sharing-overview/) for read-only data access --- ## Create and claim accounts for AI agents Source: https://motherduck.com/docs/key-tasks/ai-and-motherduck/agent-account-signup > Use the MotherDuck signup API so an agent can create an account and get a token, then claim that account as a human owner. An AI agent working in a sandbox can create its own MotherDuck account with a single API request, get back a token, and start querying right away. No browser, no signup form, and no human in the loop. When the work is worth keeping, a human claims the account through a link and becomes its owner. This guide covers both halves of that flow: the agent creating the account, and the human claiming it. ## How it works 1. The agent sends a `POST` request to the signup endpoint. 2. The response bundles a `motherduck_token`, a `claim_org_url`, and a `how_to_use_motherduck` string that tells the agent how to connect. 3. The agent connects with the [DuckDB CLI](/key-tasks/authenticating-and-connecting-to-motherduck/connecting-to-motherduck/) and works in the account. 4. A human opens the `claim_org_url`, signs up or signs in, and takes ownership of the account. The signup endpoint creates a Free Plan organization owned by a [service account](/key-tasks/service-accounts-guide/create-and-configure-service-accounts/) until a human claims it. Because it's on the Free Plan, an agent can provision an account without spending a user's trial credits during the 7-day free trial period. ## Create an account (agent) Send a `POST` request to the signup endpoint. No request body is required. ```bash curl -X POST https://new.motherduck.com ``` The response is JSON: ```json { "motherduck_token": "eyJhbGci...5Ukxg", "claim_org_url": "https://app.motherduck.com/claim-org?inviteId=", "how_to_use_motherduck": "MotherDuck is a data warehouse for your data...", "region": "aws-us-east-1" } ``` | Field | Description | |-------|-------------| | `motherduck_token` | Access token the agent uses to connect to MotherDuck. | | `claim_org_url` | Link a human opens to take ownership of the account. | | `how_to_use_motherduck` | Plain-language instructions the agent can read to connect and start working. | | `region` | Region the account was created in. | The `how_to_use_motherduck` field is written for the agent to consume directly. Passing it into the agent's context steers it toward the right connection pattern and features.
Full how_to_use_motherduck instructions ```text MotherDuck is a datawarehouse for your data. You can store, share, analyse your data, create dashboards with the 'dive' feature, and create workflows like ingest + schedule jobs using the 'flight' feature. To use MotherDuck you need to install the duckdb cli using following command: `curl https://install.duckdb.org | sh` Connect to MotherDuck using the MotherDuck token you received: `motherduck_token= duckdb "md:" ` This will connect you to MotherDuck and default to using a default database, `my_db`. Create as many new databases as you want and switch between them with the `USE ` command https://motherduck.com/docs/key-tasks/database-operations/switching-the-current-database/. Create tables in this database for your data - from csv using: CREATE TABLE AS SELECT * FROM '.csv'; - from parquet using: CREATE TABLE AS SELECT * FROM '.parquet'; To learn more about: - Writing SQL to access MotherDuck, browse duckdb documentation here https://duckdb.org/docs/ - All the cloud functions of MotherDuck use https://motherduck.com/docs - Creating dives browse: https://motherduck.com/docs/sql-reference/motherduck-sql-reference/dives/ To own the organization created here, simply paste the claim_org_url in the browser and sign up with your email address to claim it. ```
The exact text of `how_to_use_motherduck` is returned by the endpoint and can change over time. Read it from the live response rather than relying on the copy reproduced here. ### Connect with the token Install the DuckDB CLI, set the token as an environment variable, and connect: ```bash curl https://install.duckdb.org | sh export motherduck_token='' duckdb "md:" ``` The connection defaults to the `my_db` database. From here the agent can create databases, load data, and run queries like any other MotherDuck connection. ## Claim the account (human) To keep an agent-created account, take ownership of it: 1. Copy the `claim_org_url` from the signup response. 2. Open it in a browser. 3. Sign up with your email address, or sign in if you already have a MotherDuck account. Completing the flow makes you the owner of the organization, with full access to its databases and everything the agent built. The link works in one step, so there's no separate email invitation to wait for. :::note An agent can create more than one account in a single session. Claim each account you want to keep with its own `claim_org_url`. ::: ## Limitations - Agent-created accounts are on the [Lite plan (with limits)](https://motherduck.com/docs/about-motherduck/billing/pricing/#plan-comparison). - Accounts are created in a single region, shown in the `region` field of the response. - Merging a claimed account into an organization you already own is not supported. Claiming creates ownership of the agent's organization as a separate organization. - Some agent sandboxes restrict outbound network access, which can block requests to the signup endpoint. If a request fails, check whether the environment allows outbound HTTPS to `new.motherduck.com`. ## Next steps - [Create a MotherDuck account](https://motherduck.com/create-motherduck-account/) - agent-oriented overview of the signup endpoint - [Build analytics agents with MotherDuck](/key-tasks/ai-and-motherduck/building-analytics-agents/) - [Connect to MotherDuck](/key-tasks/authenticating-and-connecting-to-motherduck/connecting-to-motherduck/) with the DuckDB CLI - [Create dashboards with Dives](/key-tasks/dives/) --- ## Text Search in MotherDuck Source: https://motherduck.com/docs/key-tasks/ai-and-motherduck/text-search-in-motherduck > Text search strategies from pattern matching to semantic search with embeddings in MotherDuck. # Text Search in MotherDuck Text search is a fundamental operation in data analytics - whether you're finding records by name, searching documents for relevant content, or building question-answering systems. This guide covers search strategies available in MotherDuck, from simple pattern matching to advanced semantic search, and how to combine them for optimal results. ## Quick Start: Common Search Patterns Start here to identify the best search method for your use case. The right search approach depends on what you're searching, how you expect to use search, and what results you need. Most use cases fall into one of three patterns, each linking to detailed implementation guidance below: **Keyword Search Over Identifiers**: When searching for specific items like company names, product codes, or customer names, use [Exact Match](#exact-match) for precise and low-latency lookups. If you need typo tolerance (e.g., "MotheDuck" → "MotherDuck"), use [Fuzzy Search](#fuzzy-search-text-similarity). **Keyword Search Over Documents**: When searching longer text like articles, product descriptions, or documentation, use [Full-Text Search](#full-text-search-fts). This ranks documents by keyword relevance, and handles cases where users provide a few keywords that should appear in the content. **Semantic Search**: When searching by meaning and similarity rather than exact keywords, use [Embedding-based Search](#embedding-based-search). This covers: - Understanding synonyms (e.g., matching "data warehouse" with "analytics platform") - Understanding natural language queries (e.g., "wireless headphones with good battery life") - Finding similar content (e.g., support tickets describing similar customer issues) --- For answering natural language questions about *structured* data (e.g., "How many customers do we have in California?"), see [Analytics Agents](/key-tasks/ai-and-motherduck/building-analytics-agents/). ## Refining Your Search Strategy If the patterns above don't fully match your use case, use these four questions to navigate to the right method. Each question links to specific sections with implementation details: 1. **What is the search corpus?** Consider what you're searching through: - **Identifiers** like company names, product IDs, or person names → [Exact Match](#exact-match) or [Fuzzy Search](#fuzzy-search-text-similarity) - **Documents** like articles, descriptions, or reports → [Keyword search (regex)](#exact-match) or [Full-Text Search](#full-text-search-fts) (FTS) or [Embedding-Based Search](#embedding-based-search) or [Hybrid](#fts-pre-filtering-hybrid-search) (combining FTS + embeddings) - **Structured (numerical) data** → [Analytics Agents](/key-tasks/ai-and-motherduck/building-analytics-agents/) that convert natural language questions to SQL 2. **What is the user input?** Think about how users express their search: - **Single terms** like "MotherDuck" → [Exact Match](#exact-match) or [Fuzzy Search](#fuzzy-search-text-similarity) - **Keyword phrases** like "data warehouse analytics" → [Keyword search (regex)](#exact-match) or [Full-Text Search](#full-text-search-fts) or [Embedding-based search](#embedding-based-search) - **Questions** like "What companies offer cloud analytics?" → [Embedding-based search](#embedding-based-search) with [HyDE](#hypothetical-document-embeddings-hyde) - **Example documents** (finding similar content) → [Embedding-based search](#embedding-based-search) 3. **What is the desired output?** Clarify what you're returning: - **Ranked list** (retrieval of documents/records) → Covered by this guide - **Generated text answers** (RAG-style Q&A, chatbots, summarization) → Use retrieval methods from this guide in combination with the [`prompt()`](/sql-reference/motherduck-sql-reference/ai-functions/prompt/#retrieval-augmented-generation-rag) function. 4. **What is the desired search behavior?** Think about what search qualities matter: - **Exact match** for specific words (IDs and codes) → [Exact Match](#exact-match) or [Keyword search (regex)](#using-regular-expressions) - **Typo resilience** to handle misspellings like "MotheDuck" → "MotherDuck" → [Fuzzy search](#fuzzy-search-text-similarity) - **Synonym resilience** to match "data warehouse" with "analytics platform" → [Embedding-based search](#embedding-based-search) - **Customizable ranking** → See [Reranking](#reranking) in the [Advanced Methods](#advanced-methods) section - **Latency and concurrency** → See [Performance Guide](#performance-guide) ## Search Methods ### Exact Match Use exact match search for specific identifiers, codes, or when you need guaranteed matches. This is the fastest search method. #### Using LIKE For substring matching, use `LIKE` (or `ILIKE` for case-insensitive). In patterns, `%` matches any sequence of characters and `_` matches exactly one character. ```sql -- Find places with 'Starbucks' in their name SELECT name, locality, region FROM foursquare.main.fsq_os_places WHERE name LIKE '%Starbucks%' LIMIT 10; ``` See also: [Pattern Matching](https://duckdb.org/docs/stable/sql/functions/pattern_matching.html) in DuckDB documentation #### Using Regular Expressions For more complex pattern matching or matching multiple keywords, use `regexp_matches()` with `(?i)` for case-insensitive searches: ```sql -- Find Hacker News posts with 'python', 'javascript', or 'rust' in text SELECT title, "by", score FROM sample_data.hn.hacker_news WHERE regexp_matches(text, '(?i)(python|javascript|rust)') LIMIT 10; ``` See also: [Regular Expressions](https://duckdb.org/docs/stable/sql/functions/regular_expressions) in DuckDB documentation ### Fuzzy Search (Text Similarity) Fuzzy search handles typos and spelling variations in entity names like companies, people, or products. Use `jaro_winkler_similarity()` for most fuzzy matching scenarios - it offers the best balance of accuracy and performance compared to `damerau_levenshtein()` or `levenshtein()`. ```sql -- Find places similar to 'McDonalds' (handles typo 'McDonalsd') SELECT name, locality, region, jaro_winkler_similarity('McDonalsd', name) AS similarity FROM foursquare.main.fsq_os_places ORDER BY similarity DESC LIMIT 10; ``` See also: [Text Similarity Functions](https://duckdb.org/docs/stable/sql/functions/text#text-similarity-functions) in DuckDB documentation ### Full-Text Search (FTS) Full-Text Search ranks documents by keyword relevance using BM25 scoring, which considers both how often terms appear in a document and how rare they are across all documents. Use this for articles, descriptions, or longer text where you need relevance ranking. FTS automatically handles word stemming (e.g., "running" matches "run") and removes common stopwords (like "the", "and", "or"), but requires exact word matches - it won't handle typos in search queries. #### Basic FTS Setup FTS requires write access to the table. Since we're using a read-only example database, we first create a copy of the table in a read-write database we own: ```sql CREATE TABLE hn_stories AS SELECT id, title, text, "by", score, type FROM sample_data.hn.hacker_news WHERE type = 'story' AND LENGTH(text) > 100 LIMIT 10000; ``` Build the FTS index on the text column. This creates a new schema called `fts_{schema}_{table_name}` (in this case `fts_main_hn_stories`): ```sql PRAGMA create_fts_index( 'hn_stories', -- table name 'id', -- document ID column 'text' -- text column to index ); ``` Search the index using the `match_bm25` function from the newly created schema: ```sql SELECT id, title, text, fts_main_hn_stories.match_bm25(id, 'database analytics') AS score FROM hn_stories ORDER BY score DESC LIMIT 10; ``` #### Index Maintenance FTS indexes need to be updated when the underlying data changes. Rebuild the index using the `overwrite` parameter: ```sql PRAGMA create_fts_index('hn_stories', 'id', 'text', overwrite := 1); ``` See also: [Full-Text Search Guide](https://duckdb.org/docs/stable/guides/sql_features/full_text_search.html) and [Full-Text Search Extension](https://duckdb.org/docs/stable/core_extensions/full_text_search) in DuckDB documentation ### Embedding-Based Search Embedding-based search finds conceptually similar text by meaning, not keywords. Use this for natural language queries, handling synonyms, or when users search with questions. Embeddings handle synonyms and typos naturally without manual configuration. :::note Embedding generation and lookups are priced in [AI Units](/about-motherduck/billing/pricing#advanced-ai-functions). For paid organizations, Business and Lite plans have a default soft limit of 10 AI Units per user/day (sufficient to embed around 600,000 rows) to help prevent unexpected costs. If you'd like to adjust these limits, [just ask!](/troubleshooting/support) ::: :::info The DuckDB [VSS extension](https://duckdb.org/docs/stable/core_extensions/vss) for approximate vector search (HNSW) is currently experimental, and not supported in MotherDuck's cloud service (Server-Side). [Learn more](/concepts/duckdb-extensions/) about MotherDuck's support for DuckDB extensions. ::: #### Basic Embedding-Based Search Setup Generate embeddings for your text data, then search using exact vector similarity. For search queries phrased as questions (like "What are the best practices for...?"), see [Hypothetical Document Embeddings](#hypothetical-document-embeddings-hyde). ```sql -- Reusing the hn_stories table from the FTS section, add embeddings ALTER TABLE hn_stories ADD COLUMN text_embedding FLOAT[512]; UPDATE hn_stories SET text_embedding = embedding(text); -- Semantic search - this will also match texts with related concepts like 'neural networks', 'deep learning', etc. SELECT title, text, array_cosine_similarity( embedding('machine learning and artificial intelligence'), text_embedding ) AS similarity FROM hn_stories ORDER BY similarity DESC LIMIT 10; ``` See also: [MotherDuck Embedding Function](/sql-reference/motherduck-sql-reference/ai-functions/embedding/), and [array_cosine_similarity](https://duckdb.org/docs/stable/sql/functions/array#array_cosine_similarityarray1-array2) in DuckDB documentation #### Document Chunking for Embedding-Based Search When documents are longer than ~2000 characters, consider breaking them into smaller chunks to improve retrieval precision and focus results. For production pipelines with PDFs or Word docs, you can use the [MotherDuck integration for Unstructured.io](https://motherduck.com/blog/effortless-etl-unstructured-data-unstructuredio-motherduck/). Otherwise, you can also do document chunking in the database - here are some helpful macros: ```sql -- Fixed-size chunking with configurable overlap CREATE MACRO chunk_fixed_size(text_col, chunk_size, overlap) AS TABLE ( SELECT gs.generate_series as chunk_number, substring(text_col, (gs.generate_series - 1) * (chunk_size - overlap) + 1, chunk_size) AS chunk_text FROM generate_series(1, CAST(CEIL(LENGTH(text_col) / (chunk_size - overlap * 1.0)) AS INTEGER)) gs WHERE LENGTH(substring(text_col, (gs.generate_series - 1) * (chunk_size - overlap) + 1, chunk_size)) > 50 ); -- Paragraph-based chunking (splits on double newlines) CREATE MACRO chunk_paragraphs(text_col) AS TABLE ( WITH chunks AS (SELECT string_split(text_col, '\n\n') as arr) SELECT UNNEST(generate_series(1, array_length(arr))) as chunk_number, UNNEST(arr) as chunk_text FROM chunks ); -- Sentence-based chunking (splits on sentence boundaries) CREATE MACRO chunk_sentences(text_col) AS TABLE ( WITH chunks AS (SELECT string_split_regex(text_col, '[.!?]+\s+') as arr) SELECT UNNEST(generate_series(1, array_length(arr))) as chunk_number, UNNEST(arr) as chunk_text FROM chunks ); ``` Use one of the macros to create chunks from your documents. Fixed-size chunks (300-600 chars with 10-20% overlap) work well for most use cases: ```sql CREATE OR REPLACE TABLE hn_text_chunks AS SELECT id AS post_id, title, chunks.chunk_number, chunks.chunk_text FROM hn_stories CROSS JOIN LATERAL chunk_fixed_size(text, 500, 100) chunks; -- Alternative: CROSS JOIN LATERAL chunk_paragraphs(text) chunks; -- Alternative: CROSS JOIN LATERAL chunk_sentences(text) chunks; ``` Generate embeddings for the chunks: ```sql ALTER TABLE hn_text_chunks ADD COLUMN chunk_embedding FLOAT[512]; UPDATE hn_text_chunks SET chunk_embedding = embedding(chunk_text); ``` Once you have chunks with embeddings, search them the same way as full documents using `array_cosine_similarity()` - the chunk-level results often provide more precise matches than searching entire documents. ## Performance Guide Search performance depends on several factors, from the chosen search method, to cold vs. warm reads, Duckling sizing, and tenancy model. When running a search query against your data for the first time (cold read), it may have a higher latency than subsequent queries (warm reads). For production search workloads, ideally dedicate a service account's Duckling primarily to search, so other queries don't compete with search queries. Account for [Duckling cooldown periods](/about-motherduck/billing/duckling-sizes/) - the first search query after cooldown may experience more latency. The DuckDB analytics engine divides data into chunks and processes them in parallel across threads. More data means more chunks to process in parallel, so larger datasets don't necessarily take proportionally longer to search - they just use more threads simultaneously. **Duckling sizing:** Optimal latency requires warm reads and enough threads to process your data in parallel. With the ideal [Duckling sizing](/about-motherduck/billing/duckling-sizes/) configuration matched to your dataset size, keyword search over identifiers ([exact match](#exact-match), [fuzzy match](#fuzzy-search-text-similarity)) typically achieves latencies in the range of a few hundred milliseconds, while document search ([regex](#using-regular-expressions), [Full-Text Search](#full-text-search-fts), [embedding search](#embedding-based-search)) typically achieves 0.5-3 second latency. Our team is happy to help advise on the right resource allocation for your specific workload and latency targets - [get in touch](/troubleshooting/support) to discuss how we can meet your needs. **Handling Concurrent Requests:** For handling multiple simultaneous search requests effectively, consider using [read scaling](/key-tasks/authenticating-and-connecting-to-motherduck/read-scaling/) to distribute load across multiple read scaling Ducklings. Alternatively, consider [hypertenancy](/concepts/hypertenancy), providing isolated compute resources for each user. To optimize further, see the strategies below. For questions or requirements beyond this guide, please [get in touch](/troubleshooting/support). ### Search Optimization Strategies When optimizing search performance, consider the following options. #### Pre-filtering Reduce the search space using structured metadata (e.g. location, categories, date ranges) that can be inferred from the user's context, before running similarity searches: ```sql -- Create a local copy with embeddings for place names (using a subset) CREATE TABLE places AS SELECT fsq_place_id, name, locality, region, fsq_category_labels FROM foursquare.main.fsq_os_places WHERE name IS NOT NULL LIMIT 10000; -- Add embeddings for semantic search ALTER TABLE places ADD COLUMN name_embedding FLOAT[512]; UPDATE places SET name_embedding = embedding(name); -- Pre-filter by location before semantic search WITH filtered_candidates AS ( SELECT fsq_place_id, name, locality, fsq_category_labels, name_embedding FROM places WHERE locality = 'New York' -- Filter by location and region AND region = 'NY' ) SELECT name, locality, fsq_category_labels, array_cosine_similarity( embedding('italian restaurant'), name_embedding ) AS similarity FROM filtered_candidates ORDER BY similarity DESC LIMIT 20; ``` #### Reducing Embedding Dimensionality Halving embedding dimensions roughly halves compute time. OpenAI embeddings can be truncated at specific dimensions (256 for `text-embedding-3-small`, 256 or 512 for `text-embedding-3-large`). Use lower dimensions for initial pre-filtering, then rerank with full embeddings: ```sql -- Setup: Create normalization macro CREATE MACRO normalize(v) AS ( CASE WHEN len(v) = 0 THEN NULL WHEN sqrt(list_dot_product(v, v)) = 0 THEN NULL ELSE list_transform(v, element -> element / sqrt(list_dot_product(v, v))) END ); -- Add lower-dimensional column (e.g., 256 dims instead of 512) ALTER TABLE hn_stories ADD COLUMN text_embedding_short FLOAT[256]; UPDATE hn_stories SET text_embedding_short = normalize(text_embedding[1:256]); ``` Then use a two-stage search: ```sql -- Stage 1: Fast pre-filter with short embeddings SET VARIABLE query_emb = embedding('machine learning algorithms', 'text-embedding-3-large'); SET VARIABLE query_emb_short = normalize(getvariable('query_emb')[1:256])::FLOAT[256]; WITH candidates AS ( SELECT id, array_cosine_similarity(getvariable('query_emb_short'), text_embedding_short) AS similarity FROM hn_stories ORDER BY similarity DESC LIMIT 500 -- Get more candidates if needed ) -- Stage 2: Rerank with full embeddings SELECT p.title, p.text, array_cosine_similarity(getvariable('query_emb'), p.text_embedding) AS final_similarity FROM hn_stories p WHERE p.id IN (SELECT id FROM candidates) ORDER BY final_similarity DESC LIMIT 10; ``` #### FTS Pre-filtering (Hybrid Search) FTS typically has lower latency than embedding search, making it effective as a pre-filter to reduce similarity comparisons. Use a large LIMIT in the FTS stage to ensure good recall: ```sql -- FTS pre-filter with large limit, then semantic rerank SET VARIABLE search_query = 'artificial intelligence neural networks'; WITH fts_candidates AS ( SELECT id, fts_main_hn_stories.match_bm25(id, getvariable('search_query')) AS fts_score FROM hn_stories ORDER BY fts_score DESC LIMIT 10000 -- Large limit to ensure recall ) SELECT h.id, h.title, h.text, array_cosine_similarity( embedding(getvariable('search_query')), h.text_embedding ) AS similarity FROM hn_stories h INNER JOIN fts_candidates f ON h.id = f.id ORDER BY similarity DESC LIMIT 10; ``` See also: [Search Using DuckDB Part 3 (Hybrid Search)](https://motherduck.com/blog/search-using-duckdb-part-3/) ## Advanced Methods This section covers additional techniques to customize and improve your search. The methods below demonstrate common approaches - many other variants are possible. :::note Some methods in this section make use of the `prompt()` function, which is priced in [AI Units](/about-motherduck/billing/pricing#advanced-ai-functions). For paid organizations, Business and Lite plans have a default soft limit of 10 AI Units per user/day (sufficient to process around 80,000 rows) to help prevent unexpected costs. If you'd like to adjust these limits, [just ask!](/troubleshooting/support) ::: ### LLM-Enhanced Keyword Expansion Generate synonyms with an LLM, then use them in pattern matching: ```sql -- Generate synonyms using LLM with structured output SET VARIABLE search_term = 'programming'; WITH synonyms AS ( SELECT prompt( 'Give me 5 synonyms for ''' || getvariable('search_term') || '''', struct := {'synonyms': 'VARCHAR[]'} ).synonyms AS synonym_list ) -- Search with expanded terms SELECT title, text FROM sample_data.hn.hacker_news, synonyms WHERE regexp_matches(text, getvariable('search_term') || '|' || array_to_string(synonym_list, '|')) LIMIT 10; ``` See also: [MotherDuck `prompt()` Function](/sql-reference/motherduck-sql-reference/ai-functions/prompt/) ### Hypothetical Document Embeddings (HyDE) HyDE improves question-based retrieval by generating a hypothetical answer first, then searching with that answer's embedding. This works because questions and answers have different linguistic patterns - the hypothetical answer better matches actual document content. Use with semantic search or the semantic component of hybrid search. ```sql -- HyDE: Generate hypothetical answer, then search with it WITH hypothetical_answer AS ( SELECT prompt( 'Answer this question in 2-3 sentences: "What are the key challenges in building scalable distributed systems?" Focus on typical technical challenges and solutions.' ) AS answer ) -- Search using the hypothetical answer's embedding SELECT title, text, array_cosine_similarity( (SELECT embedding(answer) FROM hypothetical_answer), text_embedding ) AS similarity FROM hn_stories ORDER BY similarity DESC LIMIT 10; ``` See also: [Precise Zero-Shot Dense Retrieval without Relevance Labels (HyDE paper)](https://arxiv.org/abs/2212.10496) ### Reranking Reranking typically happens in two stages: initial retrieval to get top candidates (100-500 results), then precise reranking of that smaller set. #### Rule-Based Reranking with Metadata Refine results based on business rules and metadata like score, category, or freshness: ```sql -- Find similar posts with metadata-based reranking WITH initial_similarity AS ( -- Step 1: Fast vector similarity for top candidates SELECT title, text, score as author_score, array_cosine_similarity( embedding('artificial intelligence and machine learning applications'), text_embedding ) AS emb_similarity FROM hn_stories ORDER BY emb_similarity DESC LIMIT 100 ), reranked_scores AS ( -- Step 2: Rerank with metadata (author score) SELECT title, text, author_score, emb_similarity, -- Score boost (normalize to 0-1 range based on actual data) (author_score / MAX(author_score) OVER ()) AS author_score_norm, -- Combined final score: 60% semantic + 40% author score (emb_similarity * 0.6 + author_score_norm * 0.4) AS reranked_score FROM initial_similarity ) SELECT title, text, author_score, ROUND(emb_similarity, 3) as semantic_score, ROUND(author_score_norm, 3) as author_score_normalized, ROUND(reranked_score, 3) as final_score FROM reranked_scores ORDER BY reranked_score DESC LIMIT 10; ``` #### LLM-Based Reranking For complex relevance criteria that are hard to express as rules, use an LLM to judge and score results. The [`prompt()` function](/sql-reference/motherduck-sql-reference/ai-functions/prompt/) is optimized for batch processing and processes requests in parallel - so reranking 50 results typically adds only a few hundred milliseconds. ```sql -- LLM reranking for top search results SET VARIABLE search_query = 'best practices for code review and software quality'; WITH top_candidates AS ( -- Initial retrieval (e.g., via semantic search) SELECT id, title, text, array_cosine_similarity( embedding(getvariable('search_query')), text_embedding ) AS initial_score FROM hn_stories ORDER BY initial_score DESC LIMIT 20 ), llm_reranked AS ( SELECT *, prompt( format( 'Rate how well this post matches the query ''{}''. Post: {} - {}', getvariable('search_query'), title, text ), struct := {'rating': 'INTEGER'} ).rating AS llm_score FROM top_candidates ) SELECT title, text, ROUND(initial_score, 3) as initial_score, llm_score, ROUND((0.6 * initial_score + 0.4 * llm_score / 10.0), 3) AS final_score FROM llm_reranked ORDER BY final_score DESC LIMIT 10; ``` ## Next Steps - Check out the MotherDuck [Embedding Function](/sql-reference/motherduck-sql-reference/ai-functions/embedding/) and [Prompt Function](/sql-reference/motherduck-sql-reference/ai-functions/prompt/) - Review the [Full-Text Search Guide](https://duckdb.org/docs/stable/guides/sql_features/full_text_search.html) in DuckDB documentation - Read the MotherDuck blog series: [Search Using DuckDB Part 1](https://motherduck.com/blog/search-using-duckdb-part-1/), [Part 2](https://motherduck.com/blog/search-using-duckdb-part-2/), [Part 3](https://motherduck.com/blog/search-using-duckdb-part-3/) - Explore [Building Analytics Agents with MotherDuck](/key-tasks/ai-and-motherduck/building-analytics-agents/) ---