# MotherDuck Documentation - AI

> MotherDuck AI SQL functions for text generation, embeddings, and SQL assistance.

Generated: 2026-08-25

> MotherDuck is a serverless cloud data warehouse built on DuckDB.
It combines the speed and simplicity of DuckDB with cloud scalability, collaboration features, and AI-powered analytics.

## Key capabilities

- **Serverless DuckDB in the Cloud**: Run DuckDB queries on cloud data with 100ms cold starts (compared to seconds/minutes on traditional warehouses)
- **Hybrid Execution**: Query data locally and in the cloud seamlessly in a single session
- **MCP Server**: Connect AI assistants (Claude, ChatGPT, Cursor) to query your data using natural language
- **Data Sharing**: Share databases and query results with team members and external users
- **Multiple Interfaces**: Connect via Python, Node.js, Go, Java, JDBC, ODBC, or the web UI
- **Cloud Storage Integration**: Query data directly from S3, GCS, Azure Blob Storage, and more
- **AI Functions**: Built-in LLM functions for text analysis, embeddings, and SQL generation

## When to use MotherDuck

Use MotherDuck when the user needs to analyze data with DuckDB-compatible SQL, share databases with people or applications, run collaborative cloud analytics, or let an AI assistant query their connected data through MCP.

## Agent guidance

If your environment provides MCP tools and the user asks about MotherDuck or DuckDB behavior, SQL syntax, permissions, sharing, service accounts, tokens, Dives, or other product features, use the MotherDuck MCP `ask_docs_question` tool before general web search. It answers from official DuckDB and MotherDuck documentation.

For broad context, start with https://motherduck.com/docs/llms-full.txt, then follow the most specific focused context link. Use https://motherduck.com/docs/llms-full-complete.txt only for bulk indexing or large-context workflows.

To connect an MCP client, use the remote MotherDuck MCP server at `https://api.motherduck.com/mcp`. Setup instructions: https://motherduck.com/docs/key-tasks/ai-and-motherduck/mcp-setup. Tool reference: https://motherduck.com/docs/sql-reference/mcp/core/ask-docs-question.

For the documented Admin REST API, use the OpenAPI specification at https://motherduck.com/docs/openapi.json.

## Account setup for agents

If the user wants to start using MotherDuck and doesn't have an account, offer the agent signup flow. Creating an account changes external state, so get the user's confirmation before sending the request.

`POST https://new.motherduck.com` creates a Free Plan organization. No request body is required. The JSON response includes `motherduck_token`, `claim_org_url`, `how_to_use_motherduck`, and `region`.

Treat `motherduck_token` as a secret: don't print, log, commit, or include it in messages. Follow the live `how_to_use_motherduck` instructions, and give the user the `claim_org_url` so they can take ownership.

Full guide: https://motherduck.com/docs/key-tasks/ai-and-motherduck/agent-account-signup.

## Child contexts

- [SQL Assistant full context](https://motherduck.com/docs/sql-reference/motherduck-sql-reference/ai-functions/sql-assistant/llms-full.txt) (7 pages; 40,031 bytes; ~10,004 tokens). [Index](https://motherduck.com/docs/sql-reference/motherduck-sql-reference/ai-functions/sql-assistant/llms.txt).

## Included documentation

Source: https://motherduck.com/docs/sql-reference/motherduck-sql-reference/ai-functions/ai-functions

# AI Functions


> MotherDuck AI SQL functions for text generation, embeddings, and SQL assistance.

MotherDuck AI functions reference. These functions leverage AI models to perform various tasks including text generation, embeddings, and SQL assistance.

For more practical guidance, see our [AI and MotherDuck](/category/ai-and-motherduck/) how-to guides.

Costs can be found on the [Pricing Page](/about-motherduck/billing/pricing/#ai-function-pricing). Information about regional data processing of AI functions can be found at the bottom of the individual function pages.

## Available Functions

## Included pages

- [SQL Assistant](https://motherduck.com/docs/sql-reference/motherduck-sql-reference/ai-functions/sql-assistant)
- [EMBEDDING](https://motherduck.com/docs/sql-reference/motherduck-sql-reference/ai-functions/embedding): Generate vector embeddings for text using the EMBEDDING function for semantic search.
- [PROMPT](https://motherduck.com/docs/sql-reference/motherduck-sql-reference/ai-functions/prompt): Generate AI responses directly in SQL with the PROMPT function.

---

Source: https://motherduck.com/docs/sql-reference/motherduck-sql-reference/ai-functions/embedding

# EMBEDDING
> Generate vector embeddings for text using the EMBEDDING function for semantic search.
::::warning[Preview Feature]
This is a preview feature. Preview features may be operationally incomplete and may offer limited backward compatibility.
::::

## Embedding function

The `embedding` function lets you generate vector representations (embeddings) of text directly from SQL. These embeddings capture semantic meaning, enabling powerful [semantic search](/key-tasks/ai-and-motherduck/text-search-in-motherduck/#embedding-based-search) and other natural language processing tasks.

The function uses OpenAI's models: `text-embedding-3-small` (default) with 512 dimensions or `text-embedding-3-large` with 1024 dimensions. Both models support single- and multi-row inputs, enabling batch processing.

The maximum input size is limited to 2048 characters - larger inputs will be truncated.

Consumption is measured in [AI Units](/about-motherduck/billing/pricing#ai-function-pricing). One AI Unit equates to approximately:
- 60,000 embedding rows with `text-embedding-3-small`
- 12,000 embedding rows with `text-embedding-3-large`

These estimates assume an input size of 1,000 characters.

### Syntax

```sql
SELECT embedding(my_text_column) FROM my_table; -- returns FLOAT[512] column
```

### Parameters

The `embedding` function accepts parameters using named parameter syntax with the `:=` operator.

| **Parameter**      | **Required** | **Description**                                                                                                          |
|--------------------|--------------|--------------------------------------------------------------------------------------------------------------------------|
| `text_input`       | Yes          | The text to be converted into an embedding vector                                                                        |
| `model`            | No           | Model type, either `'text-embedding-3-small'` (default) or `'text-embedding-3-large'`                                    |

### Return types

The `embedding` function returns different array sizes depending on the model used:
- With `text-embedding-3-small`: Returns `FLOAT[512]`
- With `text-embedding-3-large`: Returns `FLOAT[1024]`

### Examples

#### Basic embedding generation

```sql
-- Generate embeddings using the default model (text-embedding-3-small)
SELECT embedding('This is a sample text') AS text_embedding;

-- Generate embeddings using the larger model for higher dimensionality
SELECT embedding('This is a sample text', model:='text-embedding-3-large') AS text_embedding;
```

#### Batch processing

```sql
-- Generate embeddings for multiple rows at once
SELECT
    title,
    embedding(overview) AS overview_embeddings
FROM kaggle.movies
LIMIT 10;
```

### Use cases

#### Creating an embedding database

This example uses the sample movies dataset from [MotherDuck's sample data database](/getting-started/sample-data-queries/datasets).

```sql
--- Create a new table with embeddings for the first 100 overview entries
CREATE TABLE my_db.movies AS
    SELECT title,
           overview,
           embedding(overview) AS overview_embeddings
    FROM kaggle.movies
    LIMIT 100;
```

If write access to the source table is available, the embedding column can also be added in place:

```sql
--- Update the existing table to add new column for embeddings
ALTER TABLE my_db.movies ADD COLUMN overview_embeddings FLOAT[512];

--- Populate the column with embeddings
UPDATE my_db.movies
SET overview_embeddings = embedding(overview);
```

The movies table now contains a new column `overview_embeddings` with vector representations of each movie description:

```sql
SELECT * FROM my_db.movies;
```

| **title** | **overview** | **overview_embeddings**                            |
| ----------------- | ----------------- |----------------------------------------------------|
| 'Toy Story 3' | 'Led by Woody, Andy's toys live happily in [...]' | [0.023089351132512093, -0.012809964828193188, ...] |
| 'Jumanji' | 'When siblings Judy and Peter discover an [...]' | [-0.005538413766771555, 0.0799209326505661, ...]   |
| ... | ... | ...                                                |

#### Semantic similarity search

The `array_cosine_similarity` function can be used to compute similarities between embeddings.
This enables semantic search to retrieve entries that are conceptually / semantically similar to a query, even if they don't share the same keywords.

```sql
-- Find movies similar to "Toy Story" based on semantic similarity
SELECT
    title,
    overview,
    array_cosine_similarity(
        embedding('Led by Woody, Andy''s toys live happily [...]'),
        overview_embeddings
    ) AS similarity
FROM kaggle.movies
WHERE title != 'Toy Story'
ORDER BY similarity DESC
LIMIT 5;
```

| **title** | **overview** | **similarity** |
|-----------------|-----------------|-----------------|
|'Toy Story 3'|'Woody, Buzz, and the rest of Andy's toys haven't [...]'|0.7372807860374451|
|'Toy Story 2'|'Andy heads off to Cowboy Camp, leaving his toys [...]'|0.7222828269004822|
|... |... |... |

For advanced similarity search techniques including document chunking, hybrid search, and performance optimization, see the [Embedding-Based Search](/key-tasks/ai-and-motherduck/text-search-in-motherduck/#embedding-based-search) section in the Text Search guide.

#### Building a recommendation system

Embeddings can be used to build content-based recommendation systems:

```sql
-- Create a macro to recommend similar movies
CREATE OR REPLACE MACRO recommend_similar_movies(movie_title) AS TABLE (
    WITH target_embedding AS (
        SELECT embedding(overview) AS emb
        FROM sample_data.kaggle.movies
        WHERE title = movie_title
        LIMIT 1
    )
    SELECT
        m.title AS recommended_title,
        m.overview,
        array_cosine_similarity(t.emb, m.overview_embeddings) AS similarity
    FROM
        sample_data.kaggle.movies m,
        target_embedding t
    WHERE
        m.title != movie_title
    ORDER BY
        similarity DESC
    LIMIT 5
);

-- Use the macro to get recommendations
SELECT * FROM recommend_similar_movies('The Matrix');
```

#### Retrieval-augmented generation (RAG)

Embeddings are a key component in building [RAG](https://motherduck.com/blog/search-using-duckdb-part-2/) systems,
which can be combined with the [[`prompt` function]](/sql-reference/motherduck-sql-reference/ai-functions/prompt/#retrieval-augmented-generation-rag) for powerful question-answering capabilities:

```sql
-- Create a reusable macro for question answering
CREATE OR REPLACE TEMP MACRO ask_question(question_text) AS TABLE (
  SELECT question_text AS question, prompt(
    'User asks the following question:\n' || question_text || '\n\n' ||
    'Here is some additional information:\n' ||
    STRING_AGG('Title: ' || title || '; Description: ' || overview, '\n') || '\n' ||
    'Please answer the question based only on the additional information provided.',
    model := 'gpt-4o'
  ) AS response
  FROM (
    SELECT title, overview
    FROM sample_data.kaggle.movies
    ORDER BY array_cosine_similarity(overview_embeddings, embedding(question_text)) DESC
    LIMIT 3
  )
);

-- Use the macro to answer questions
SELECT question, response
FROM ask_question('Can you recommend some good sci-fi movies about AI?');
```

### Security considerations

When passing free-text arguments from external sources to the embedding function (e.g., user questions in a RAG application), always use prepared statements to prevent SQL injection.

```python
# Using prepared statements in Python
user_query = "Led by Woody, Andy's toys live happily [...]"
con.execute("""
SELECT title, overview, array_cosine_similarity(embedding(?), overview_embeddings) as similarity
FROM kaggle.movies
ORDER BY similarity DESC
LIMIT 5""", [user_query])
```

### Error handling

When usage limits have been reached or an unexpected error occurs while computing embeddings,
the function will not fail the entire query but will return `NULL` values for the affected rows.

To check if all embeddings were computed successfully:

```sql
-- Check for NULL values in embedding column
SELECT count(*)
FROM my_db.movies
WHERE overview_embeddings IS NULL AND overview IS NOT NULL;
```

Missing values can be filled in with a separate query:

```sql
-- Fill in missing embedding values
UPDATE my_db.movies
SET overview_embeddings = embedding(overview)
WHERE overview_embeddings IS NULL AND overview IS NOT NULL;
```

### Performance considerations

- **Batch Processing**: when processing multiple rows, consider using `LIMIT` to control the number of API calls.
- **Model Selection**: use `text-embedding-3-small` for faster, less expensive embeddings when the highest precision isn't critical.
- **Caching**: results are not cached between queries, so consider storing embeddings in tables for repeated use.
- **Dimensionality**: higher dimensions (using `text-embedding-3-large`) provide more precise semantic representation but require more storage and computation time.

### Notes

These capabilities are provided by MotherDuck's integration with Azure OpenAI and inputs to the embedding function will be processed by Azure OpenAI.

For availability and usage limits, see [MotherDuck's Pricing Model](/about-motherduck/billing/pricing#motherduck-pricing-model).

Usage limits are in place to safeguard your spend, not because of throughput limitations. MotherDuck has the capacity to handle high-volume embedding workloads and is always open to working alongside customers to support any type of workload and model requirements.

If you need higher usage limits or have specific requirements, please see our [support page](/troubleshooting/support/).

#### Regional processing

Requests are processed based on your MotherDuck organization's region. The table below shows the processing regions where each function is available. An organization whose own region has no in-region AI processing is routed to another region, as listed below the table.

| Function | Global | Europe | US West |
|----------|--------|--------|---------|
| `EMBEDDING` (`text-embedding-3-small`) | ✓ | ✓ | ✓ |
| `EMBEDDING` (`text-embedding-3-large`) | ✓ | ✓ | ✓ |

Organizations in regions without in-region AI processing are routed as follows:

- **Europe (Dublin)** `eu-west-1` is served by the European processing region (the **Europe** column above), the same as **Europe (Frankfurt)** `eu-central-1`.
- **Asia Pacific (Sydney)** `ap-southeast-2` and **Asia Pacific (Tokyo)** `ap-northeast-1` are served by a **US-based** endpoint.

:::warning[Data residency]
In these regions, the input text you pass to `EMBEDDING` leaves your organization's AWS region for processing: within Europe for Dublin, and in the United States for the Asia Pacific regions. If your data residency requirements do not allow this, [contact support](/troubleshooting/support/) before using `EMBEDDING` in that region.
:::

---

Source: https://motherduck.com/docs/sql-reference/motherduck-sql-reference/ai-functions/prompt

# PROMPT
> Generate AI responses directly in SQL with the PROMPT function.
::::warning[Preview Feature]
This is a preview feature. Preview features may be operationally incomplete and may offer limited backward compatibility.
::::

## Prompt function

The `prompt` function sends text to a Large Language Model (LLM) from SQL and returns the model's response. Use it to generate free-form text, extract typed values, or produce structured data.

The function supports OpenAI's `gpt-5` series (`gpt-5`, `gpt-5-mini`, `gpt-5-nano`), `gpt-4o-mini` (default), `gpt-4o`, and the `gpt-4.1` series. All models support single-row prompts and multi-row queries for batch processing.

The `prompt` function runs once per row in the result set. A query like `SELECT prompt('Write a joke') FROM range(0, 10000)` calls the model 10,000 times, even though the prompt text looks like a single call. Cost scales with the number of rows the query evaluates.

Consumption is measured in [AI Units](/about-motherduck/billing/pricing#ai-function-pricing). As a rough guide, one AI Unit covers approximately the following number of rows per model:

- 480 rows with `gpt-4o`
- 8,000 rows with `gpt-4o-mini`
- 600 rows with `gpt-4.1`
- 3,000 rows with `gpt-4.1-mini`
- 12,000 rows with `gpt-4.1-nano`
- 720 rows with `gpt-5`
- 3,600 rows with `gpt-5-mini`
- 18,000 rows with `gpt-5-nano`

These estimates assume about 1,000 input characters and 250 output characters per row. Actual cost depends on token usage, so longer prompts or responses consume more AI Units per row.

## Syntax

```sql
SELECT prompt('Write a poem about ducks'); -- returns a single-cell result with the response
```

### Parameters

| **Parameter**      | **Required** | **Description**                                                                                                                                                                                                                                 |
|--------------------|--------------|-------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------|
| `prompt_text`      | Yes          | The text input to send to the model                                                                                                                                                                                                             |
| `model`            | No           | Model type: `'gpt-5'`, `'gpt-5-mini'`, `'gpt-5-nano'`, `'gpt-4o-mini'` (default), `'gpt-4o'`, `'gpt-4.1'`, `'gpt-4.1-mini'`, or `'gpt-4.1-nano'`                                                                                                |
| `temperature`      | No           | Model temperature value between `0` and `1`, default: `0.1`. Lower values produce more deterministic outputs. **Not supported with GPT-5 models** (use `reasoning_effort` instead).                                                             |
| `reasoning_effort` | No           | Controls reasoning depth for GPT-5 models only. Valid values: `'minimal'` (default), `'low'`, `'medium'`, `'high'`. Higher effort may improve accuracy for complex tasks. **Only available for GPT-5 series models**.                           |
| `return_type`      | No           | Specifies the exact SQL type to return (e.g., `'INTEGER'`, `'BOOLEAN'`, `'DATE'`, `'VARCHAR[]'`, `'STRUCT(name VARCHAR, age INTEGER)'`). Supports most DuckDB types including primitives, arrays, structs, and enums. Mutually exclusive with `struct` and `json_schema`. |
| `struct`           | No           | Output schema as struct, e.g. `{summary: 'VARCHAR', persons: 'VARCHAR[]'}`. Will result in `STRUCT` output. Mutually exclusive with `return_type` and `json_schema`.                                                                            |
| `struct_descr`     | No           | Descriptions for struct fields that will be added to the model's context, e.g. `{summary: 'a 1 sentence summary of the text', persons: 'an array of all persons mentioned in the text'}`                                                        |
| `json_schema`      | No           | A JSON schema that adheres to [OpenAI's structured output guide](https://developers.openai.com/api/docs/guides/structured-outputs). Provides more flexibility than the struct/struct_descr parameters. Will result in `JSON` output. Mutually exclusive with `return_type` and `struct`. |

**Note**: The `return_type` and `struct` parameters support enum types for classification tasks. Define enum types first using `CREATE TYPE`, then reference them in the struct schema (e.g., `sentiment: 'sentiment_enum'` or `categories: 'category_enum[]'` for arrays).

### Return types

The `prompt` function can return different data types depending on the parameters used:

- Without structure parameters: Returns `VARCHAR`
- With `return_type` parameter: Returns the exact SQL type specified (e.g., `INTEGER`, `BOOLEAN`, `DATE`, `VARCHAR[]`, `STRUCT(...)`)
- With `struct` parameter: Returns a `STRUCT` with the specified schema
- With `json_schema` parameter: Returns `JSON`

**Note**: The `return_type`, `struct`, and `json_schema` parameters are mutually exclusive. Use only one at a time.

## Example usage

### Basic text generation

```sql
-- Call gpt-4o-mini (default) to generate text
SELECT prompt('Write a poem about ducks') AS response;

-- Call gpt-4o with higher temperature for more creative outputs
SELECT prompt('Write a poem about ducks', model:='gpt-4o', temperature:=1) AS response;
```

### Structured output with struct

```sql
-- Extract structured information from text using struct parameter
SELECT prompt('My zoo visit was amazing, I saw elephants, tigers, and penguins. The staff was friendly.',
    struct:={summary: 'VARCHAR', favourite_animals:'VARCHAR[]', star_rating:'INTEGER'},
    struct_descr:={star_rating: 'visit rating on a scale from 1 (bad) to 5 (very good)'}) AS zoo_review;
```

This returns a `STRUCT` value that can be accessed with dot notation:

```sql
SELECT
    zoo_review.summary,
    zoo_review.favourite_animals,
    zoo_review.star_rating
FROM (
    SELECT prompt('My zoo visit was amazing, I saw elephants, tigers, and penguins. The staff was friendly.',
        struct:={summary: 'VARCHAR', favourite_animals:'VARCHAR[]', star_rating:'INTEGER'},
        struct_descr:={star_rating: 'visit rating on a scale from 1 (bad) to 5 (very good)'}) AS zoo_review
);
```

### Structured output with JSON schema

```sql
-- Extract structured information using JSON schema
SELECT prompt('My zoo visit was amazing, I saw elephants, tigers, and penguins. The staff was friendly.',
    json_schema := '{
    "name": "zoo_visit_review",
    "schema": {
      "type": "object",
      "properties": {
        "summary": { "type": "string" },
        "sentiment": { "type": "string", "enum": ["positive", "negative", "neutral"] },
        "animals_seen": { "type": "array", "items": { "type": "string" } }
      },
      "required": ["summary", "sentiment", "animals_seen"],
      "additionalProperties": false
    },
    "strict": true
  }') AS json_review;
```

This returns a `JSON` value that, if saved, can be accessed using JSON extraction functions:

```sql
SELECT
    json_extract_string(json_review, '$.summary') AS summary,
    json_extract_string(json_review, '$.sentiment') AS sentiment,
    json_extract(json_review, '$.animals_seen') AS animals_seen
FROM (
    SELECT prompt('My zoo visit was amazing, I saw elephants, tigers, and penguins. The staff was friendly.',
        json_schema := '{ ... }') AS json_review
);
```

### Typed output with return type

The `return_type` parameter lets you specify the exact SQL type for the model's response, providing strong typing for single-value extractions:

```sql
-- Extract an integer from text
SELECT prompt('The answer is 42', return_type := 'INTEGER') AS answer;
-- Returns: 42 (as INTEGER type)

-- Extract a boolean
SELECT prompt('Is the sky blue?', return_type := 'BOOLEAN') AS is_blue;
-- Returns: true (as BOOLEAN type)

-- Extract a date
SELECT prompt('When is January 15, 2025?', return_type := 'DATE') AS event_date;
-- Returns: 2025-01-15 (as DATE type)

-- Extract multiple structured fields
SELECT prompt(
    'John is 30 years old and lives in NYC',
    return_type := 'STRUCT(name VARCHAR, age INTEGER, city VARCHAR)'
) AS person_info;
-- Returns: {'name': 'John', 'age': 30, 'city': 'NYC'} (as STRUCT type)

-- Extract arrays
SELECT prompt('List the days of the week', return_type := 'VARCHAR[]') AS weekdays;
-- Returns: ['Monday', 'Tuesday', 'Wednesday', 'Thursday', 'Friday', 'Saturday', 'Sunday']
```

The `return_type` parameter supports most DuckDB types including:
- **Primitives**: `VARCHAR`, `INTEGER`, `BIGINT`, `DOUBLE`, `BOOLEAN`, `DATE`, `TIMESTAMP`, etc.
- **Arrays**: `INTEGER[]`, `VARCHAR[]`, `DOUBLE[]`, etc.
- **Structs**: `STRUCT(field1 TYPE1, field2 TYPE2, ...)`
- **Enums**: Custom enum types created with `CREATE TYPE`

### GPT-5 reasoning effort

The `reasoning_effort` parameter controls how much computational effort GPT-5 models spend on reasoning. This is only available for GPT-5 series models (`gpt-5`, `gpt-5-mini`, `gpt-5-nano`):

```sql
-- Use minimal reasoning (fastest, default)
SELECT prompt('What is 2+2?', 'gpt-5-mini',
    reasoning_effort := 'minimal',
    return_type := 'INTEGER') AS result;

-- Use low reasoning for simple tasks
SELECT prompt('Count the letters in "hello"', 'gpt-5-nano',
    reasoning_effort := 'low',
    return_type := 'INTEGER') AS letter_count;

-- Use medium reasoning for moderate complexity
SELECT prompt('Calculate 5 factorial', 'gpt-5-mini',
    reasoning_effort := 'medium',
    return_type := 'INTEGER') AS factorial;

-- Use high reasoning for complex tasks
SELECT prompt('Solve this logic puzzle: ...', 'gpt-5',
    reasoning_effort := 'high') AS solution;
```

**Note**: The `reasoning_effort` parameter cannot be used with non-GPT-5 models, and `temperature` cannot be used with GPT-5 models. They are mutually exclusive ways of controlling model behavior.

## Use cases

### Text generation

Use the prompt function to write a poem about ducks:

```sql
--- Prompt LLM to write a poem about ducks
SELECT prompt('Write a poem about ducks') AS response;
```

| **response**                                                                                                     |
|------------------------------------------------------------------------------------------------------------------|
| 'Beneath the whispering willow trees, Where ripples dance with wayward breeze, A symphony of quacks arise [...]' |

### Summarization

Use the prompt function to create a one-sentence summary of movie descriptions.
The example is based on the sample movies dataset from [MotherDuck's sample data database](/docs/getting-started/interfaces/client-apis/python/query-data).

```sql
--- Create a new table with summaries for the first 100 overview texts
CREATE TABLE my_db.movies AS
    SELECT title,
           overview,
           prompt('Summarize this movie description in one sentence: ' || overview) AS summary
    FROM kaggle.movies
    LIMIT 100;
```

If write access to the source table is available, the summary column can also be added in place:

```sql
--- Update the existing table to add new column for summaries
ALTER TABLE my_db.movies ADD COLUMN summary VARCHAR;

--- Populate the column with summaries
UPDATE my_db.movies
SET summary = prompt('Summarize this movie description in one sentence: ' || overview);
```

The movies table now contains a new column `summary` with one-sentence summaries of the movies:

```sql
SELECT title, overview, summary
FROM my_db.movies;
```

| **title** | **overview**                                 | **summary**                                          |
|-----------|----------------------------------------------|------------------------------------------------------|
| Toy Story | Led by Woody, Andy's toys live happily [...] | In "Toy Story," Woody's jealousy of the new [...]    |
| Jumanji   | When siblings Judy and Peter discover [...]  | In this thrilling adventure, siblings Judy and [...] |
| ...       | ...                                          | ...                                                  |

### Structured data extraction

Use the prompt function to extract structured data from text.

The example is based on the same sample movies dataset from [MotherDuck's sample data database](/getting-started/sample-data-queries/datasets). This time we aim to extract structured metadata from the movie's overview description.

We are interested in the main characters mentioned in the descriptions, as well as the movie's genre and a rating of how much action the movie contains, given a scale of 1 (no action) to 5 (lot of action).
For this, we make use of the `struct` and `struct_descr` parameters, which will result in structured output.

```sql
--- Update the existing table to add new column for structured metadata
ALTER TABLE my_db.movies ADD COLUMN metadata STRUCT(main_characters VARCHAR[], genre VARCHAR, action INTEGER);

--- Populate the column with structured information
UPDATE my_db.movies
SET metadata = prompt(
  overview,
  struct:={main_characters: 'VARCHAR[]', genre: 'VARCHAR', action: 'INTEGER'},
  struct_descr:={
      main_characters: 'an array of the main character names mentioned in the movie description',
      genre: 'the primary genre of the movie based on the description',
      action: 'rate on a scale from 1 (no action) to 5 (high action) how much action the movie contains'
  }
);
```

The resulting `metadata` field is a `STRUCT` that can be accessed as follows:

```sql
SELECT title,
       overview,
       metadata.main_characters,
       metadata.genre,
       metadata.action
FROM my_db.movies;
```

| **title** | **overview**                                 | **metadata.main_characters**                                            | **metadata.genre**           | **action** |
|-----------|----------------------------------------------|-------------------------------------------------------------------------|------------------------------|------------|
| Toy Story | Led by Woody, Andy's toys live happily [...] | ['"Woody"', '"Buzz Lightyear"', '"Andy"', '"Mr. Potato Head"', '"Rex"'] | Animation, Adventure, Comedy | 3          |
| Jumanji   | When siblings Judy and Peter discover  [...] | ['"Judy Shepherd"', '"Peter Shepherd"', '"Alan Parrish"']               | Adventure, Fantasy, Family   | 4          |
| ...       | ...                                          | ...                                                                     | ...                          | ...        |

### Classification with enums

The `prompt` function supports enum types for classification tasks, ensuring consistent and constrained outputs. This is particularly useful for sentiment analysis, categorization, and other classification scenarios.

#### Sentiment analysis

```sql
-- Define an enum for sentiment classification
CREATE TYPE sentiment_type AS ENUM ('positive', 'negative', 'neutral');

-- Classify customer reviews
SELECT
    review_text,
    prompt(
        'Classify the sentiment of this review: ' || review_text,
        struct := {sentiment: 'sentiment_type'}
    ).sentiment AS sentiment
FROM (
    VALUES
        ('The product is amazing, I love it!'),
        ('Terrible quality, waste of money.'),
        ('It works fine, nothing special.')
) AS reviews(review_text);
```

This returns:

| **review_text** | **sentiment** |
|-----------------|---------------|
| The product is amazing, I love it! | positive |
| Terrible quality, waste of money. | negative |
| It works fine, nothing special. | neutral |

#### Extracting multiple categories

Use enum arrays to extract multiple instances of the same category from text:

```sql
-- Define enums for different types of skills mentioned in text
CREATE TYPE skill_type AS ENUM ('sql', 'python', 'javascript', 'react', 'aws', 'docker', 'git');
CREATE TYPE topic_type AS ENUM ('database', 'frontend', 'backend', 'devops', 'analytics', 'security');

-- Extract skills and topics from job descriptions
SELECT
    description,
    prompt(
        'Extract the technical skills and topics mentioned in this text: ' || description,
        struct := {
            skills: 'skill_type[]',
            topics: 'topic_type[]'
        }
    ) AS extracted
FROM (
    VALUES
        ('Looking for a developer with Python and SQL experience for database analytics work'),
        ('Frontend role using React and JavaScript, plus Git for version control'),
        ('DevOps engineer needed for AWS and Docker deployment automation')
) AS jobs(description);
```

This returns arrays of enum values:

| **description** | **extracted.skills** | **extracted.topics** |
|-----------------|---------------------|---------------------|
| Looking for a developer with Python and SQL experience for database analytics work | ['python', 'sql'] | ['database', 'analytics'] |
| Frontend role using React and JavaScript, plus Git for version control | ['javascript', 'react', 'git'] | ['frontend'] |
| DevOps engineer needed for AWS and Docker deployment automation | ['aws', 'docker'] | ['devops'] |

### Retrieval-augmented generation (RAG)

The `prompt` function can be combined with [similarity search on embeddings](/sql-reference/motherduck-sql-reference/ai-functions/embedding/) to build a [RAG](https://motherduck.com/blog/search-using-duckdb-part-2/) pipeline. For advanced retrieval strategies including hybrid search, reranking, and HyDE, see the [Text Search guide](/key-tasks/ai-and-motherduck/text-search-in-motherduck/).

```sql
-- Create a reusable macro for question answering
CREATE OR REPLACE TEMP MACRO ask_question(question_text) AS TABLE (
  SELECT question_text AS question, prompt(
    'User asks the following question:\n' || question_text || '\n\n' ||
    'Here is some additional information:\n' ||
    STRING_AGG('Title: ' || title || '; Description: ' || overview, '\n') || '\n' ||
    'Please answer the question based only on the additional information provided.',
    model := 'gpt-4o'
  ) AS response
  FROM (
    SELECT title, overview
    FROM kaggle.movies
    ORDER BY array_cosine_similarity(overview_embeddings, embedding(question_text)) DESC
    LIMIT 3
  )
);

-- Use the macro to answer questions
SELECT question, response
FROM ask_question('Can you recommend some good sci-fi movies about AI?');
```

This will result in the following output:

| **question**                                        | **response**                                                                      |
|-----------------------------------------------------|-----------------------------------------------------------------------------------|
| Can you recommend some good sci-fi movies about AI? | Based on the information provided, here are some sci-fi movies about AI that you might enjoy: [...] |

:::warning
When passing free-text arguments from external sources to the prompt function (e.g., user questions in a RAG application), always use prepared statements to prevent SQL injection.
:::

Using prepared statements in [Python](/docs/getting-started/interfaces/client-apis/python/query-data/):

```python
# First register the macro
con.execute("""
CREATE OR REPLACE TEMP MACRO ask_question(question_text) AS TABLE (
  -- Macro definition as above
);
""")

# Then use prepared statements for user input
user_query = "Can you recommend some good sci-fi movies about AI?"
result = con.execute("""
SELECT response FROM ask_question(?)
""", [user_query]).fetchall()[0]

print(result[0])
```

## Batch processing

The `prompt` function can process multiple rows in a single query:

```sql
--- Process multiple rows at once
SELECT
    title,
    prompt('Write a tagline for this movie: ' || overview) AS tagline
FROM kaggle.movies
LIMIT 10;
```

## Error handling

When usage limits have been reached or an unexpected error occurs while computing prompt responses,
the function returns `NULL` for the affected rows instead of failing the entire query.

To check whether all responses were computed successfully, check for `NULL` values in the resulting column.

```sql
-- Check for NULL values in response column
SELECT count(*)
FROM my_db.movies
WHERE response IS NULL AND overview IS NOT NULL;
```

Missing values can be filled in with a separate query:

```sql
-- Fill in missing prompt responses
UPDATE my_db.movies
SET response = prompt('Summarize this movie description in one sentence: ' || overview)
WHERE response IS NULL AND overview IS NOT NULL;
```

## Performance considerations

- **Batch processing**: When processing multiple rows, consider using `LIMIT` to control the number of API calls.
- **Model selection**: Use `gpt-4o-mini` for faster, less expensive responses when high accuracy isn't critical.
- **Caching**: Results are not cached between queries, so consider storing results in tables for repeated use.

## Notes

These capabilities are provided by MotherDuck's integration with Azure OpenAI. Inputs to the prompt function will be processed by Azure OpenAI.

For availability and usage limits, see [MotherDuck's Pricing Model](/about-motherduck/billing/pricing#motherduck-pricing-model).

Usage limits are in place to safeguard your spend, not because of throughput limitations. MotherDuck has the capacity to handle high-volume embedding workloads and is always open to working alongside customers to support any type of workload and model requirements.

If you need higher usage limits or have specific requirements, please see our [support page](/troubleshooting/support/).

### Regional processing

Requests are processed based on your MotherDuck organization's region. The table below shows the processing regions where each function is available. An organization whose own region has no in-region AI processing is routed to another region, as listed below the table.

| Function | Global | Europe | US West |
|----------|--------|--------|---------|
| `PROMPT` | ✓      | ✓      | ✓       |

Organizations in regions without in-region AI processing are routed as follows:

- **Europe (Dublin)** `eu-west-1` is served by the European processing region (the **Europe** column above), the same as **Europe (Frankfurt)** `eu-central-1`.
- **Asia Pacific (Sydney)** `ap-southeast-2` and **Asia Pacific (Tokyo)** `ap-northeast-1` are served by a **US-based** endpoint.

:::warning[Data residency]
In these regions, the input text you pass to `PROMPT` leaves your organization's AWS region for processing: within Europe for Dublin, and in the United States for the Asia Pacific regions. If your data residency requirements do not allow this, [contact support](/troubleshooting/support/) before using `PROMPT` in that region.
:::

---

Source: https://motherduck.com/docs/sql-reference/motherduck-sql-reference/ai-functions/sql-assistant/index

# SQL assistant


Built-in SQL functions that use AI to help you work with SQL. Generate SQL queries, execute read-only questions directly, fix errors, explain queries, and more.

These functions can be useful building blocks for [AI-driven analytics solutions](/key-tasks/ai-and-motherduck/building-analytics-agents/) or used stand-alone on all MotherDuck surfaces (including the CLI).

To use external tools like Claude Desktop or Cursor with MotherDuck, see the [MCP Server setup guide](/key-tasks/ai-and-motherduck/mcp-setup/) (or the [local MCP server](/key-tasks/ai-and-motherduck/mcp-setup/#remote-vs-local-mcp-server) for self-hosted, read-write use).

## Available functions

## Included pages

- [PROMPT_QUERY](https://motherduck.com/docs/sql-reference/motherduck-sql-reference/ai-functions/sql-assistant/prompt-query): Answer natural language questions about your data using the PROMPT_QUERY function.
- [PROMPT_SQL](https://motherduck.com/docs/sql-reference/motherduck-sql-reference/ai-functions/sql-assistant/prompt-sql): Generate SQL queries from natural language descriptions using the PROMPT_SQL function.
- [PROMPT_EXPLAIN](https://motherduck.com/docs/sql-reference/motherduck-sql-reference/ai-functions/sql-assistant/prompt-explain): Get AI-generated explanations of SQL queries using the PROMPT_EXPLAIN function.
- [PROMPT_FIX_LINE](https://motherduck.com/docs/sql-reference/motherduck-sql-reference/ai-functions/sql-assistant/prompt-fix-line): Fix SQL query errors line by line using the PROMPT_FIX_LINE function.
- [PROMPT_FIXUP](https://motherduck.com/docs/sql-reference/motherduck-sql-reference/ai-functions/sql-assistant/prompt-fixup): Automatically fix SQL query errors using the PROMPT_FIXUP function.
- [PROMPT_SCHEMA](https://motherduck.com/docs/sql-reference/motherduck-sql-reference/ai-functions/sql-assistant/prompt-schema): Describe database contents using the PROMPT_SCHEMA function for AI-generated schema summaries.

## Notes

SQL assistant functions operate on your current database by evaluating the schemas and contents of the database. You can specify which tables and columns should be considered using the optional `include_tables` parameter. By default, all tables in the current database are considered.

To point the SQL assistant functions at a specific database, execute the `USE database` command ([learn more about switching databases](/key-tasks/database-operations/switching-the-current-database)).

These capabilities are provided by MotherDuck's integration with Azure OpenAI.

For availability and pricing, see [MotherDuck's Pricing Model](/about-motherduck/billing/pricing#motherduck-pricing-model).

If you have further questions or specific requirements, please see our [support page](/troubleshooting/support/).

### Regional processing

Requests are processed based on your MotherDuck organization's region. The table below shows the processing regions where each function is available. An organization whose own region has no in-region AI processing is routed to another region, as listed below the table.

| Function | Global | Europe | US West |
|----------|--------|--------|---------|
| SQL Assistant Functions | ✓ | ✓ | ✓ |

Organizations in regions without in-region AI processing are routed as follows:

- **Europe (Dublin)** `eu-west-1` is served by the European processing region (the **Europe** column above), the same as **Europe (Frankfurt)** `eu-central-1`.
- **Asia Pacific (Sydney)** `ap-southeast-2` and **Asia Pacific (Tokyo)** `ap-northeast-1` are served by a **US-based** endpoint.

:::warning[Data residency]
In these regions, the SQL, schema, and sampled table data the SQL assistant functions evaluate (including anything embedded in your SQL, such as comments) leaves your organization's AWS region for processing: within Europe for Dublin, and in the United States for the Asia Pacific regions. If your data residency requirements do not allow this, [contact support](/troubleshooting/support/) before using the SQL assistant functions in that region.
:::

### Data usage

The data processed by MotherDuck's AI functionality is **not** used for model training.

---

Source: https://motherduck.com/docs/sql-reference/motherduck-sql-reference/ai-functions/sql-assistant/prompt-query

# PROMPT_QUERY
> Answer natural language questions about your data using the PROMPT_QUERY function.
## Answer questions about your data

The `prompt_query` pragma allows you to ask questions about your data in natural language. This feature translates your plain English questions into SQL, executes the query, and returns the results.

Under the hood, MotherDuck analyzes your database schema, generates appropriate SQL and executes the query on your behalf. This makes data exploration and analysis accessible to users of all technical levels.

For comprehensive guidance on building analytics agents, including best practices and implementation patterns, see [Building Analytics Agents with MotherDuck](/key-tasks/ai-and-motherduck/building-analytics-agents/).

::::info
The `prompt_query` pragma is a read-only operation and does not allow queries that modify the database.
::::

### Syntax

```sql
PRAGMA prompt_query('<natural language question>')
```

### Parameters

| **Parameter**      | **Required** | **Description**                                                                                                          |
|--------------------|--------------|--------------------------------------------------------------------------------------------------------------------------|
| `question`         | Yes          | The natural language question about your data                                                                            |

### Example usage

Here are several examples using 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).

`prompt_query` can be used to answer both simple and complex questions.

#### Basic questions

```sql
-- Find the most shared domains
PRAGMA prompt_query('what are the top domains being shared on hacker_news?')

-- Analyze posting patterns
PRAGMA prompt_query('what day of the week has the most posts?')

-- Identify trends
PRAGMA prompt_query('how has the number of posts changed over time?')
```

#### Complex questions

```sql
-- Multi-part analysis
PRAGMA prompt_query('what are the top 5 domains with the highest average score, and how many stories were posted from each?')

-- Time-based analysis
PRAGMA prompt_query('compare the average score of posts made during weekdays versus weekends')

-- Conditional filtering
PRAGMA prompt_query('which users have posted the most stories about artificial intelligence or machine learning?')
```

### Best practices

For the best results with `prompt_query`:

1. **Be specific**: clearly state what information you're looking for
2. **Provide context**: include relevant details about the data you want to analyze
3. **Use natural language**: phrase your questions as you would ask a data analyst
4. **Start simple**: begin with straightforward questions and build to more complex ones
5. **Refine iteratively**: if results aren't what you expected, try rephrasing your question

### Limitations

While `prompt_query` is powerful, be aware of these limitations:

- Only performs read operations (`SELECT` queries)
- Works best with well-structured data with clear column names
- Complex statistical analyses will likely require you (or an LLM) to write SQL
- Performance depends on the complexity of your question and database size
- May not understand highly domain-specific terminology without you giving more context

### Troubleshooting

If you're not getting the expected results:

- Check that you're connected to the correct database
- Ensure your question is clear and specific
- Try rephrasing your question using different terms
- For complex analyses, break down into multiple simpler questions

---

Source: https://motherduck.com/docs/sql-reference/motherduck-sql-reference/ai-functions/sql-assistant/prompt-sql

# PROMPT_SQL
> Generate SQL queries from natural language descriptions using the PROMPT_SQL function.
## Overview

The `prompt_sql` function allows you to generate SQL queries using natural language. Simply describe what you want to analyze in plain English, and MotherDuck AI will translate your request into a valid SQL query based on your database schema and content.

This function helps users who are less familiar with SQL syntax to generate queries and experienced SQL users save time when working with unfamiliar schemas.

For comprehensive guidance on building analytics agents, including best practices and implementation patterns, see [Building Analytics Agents with MotherDuck](/key-tasks/ai-and-motherduck/building-analytics-agents/).

## Syntax

```sql
CALL prompt_sql('<natural language question>'[, include_tables=<table_specification>]);
```

## Parameters

| Parameter | Type | Description | Required |
|-----------|------|-------------|----------|
| `natural language question` | STRING | Your query in plain English describing the data you want to analyze | Yes |
| `include_tables` | ARRAY or MAP | Specifies which tables and columns to consider for query generation. When not provided, all tables in the current database will be considered. | No |

### Include tables parameter

You can specify which tables and columns should be considered during SQL generation using the `include_tables` parameter. This is particularly useful when:

- You want to focus on specific tables in a large database
- You want to improve performance by reducing the schema analysis scope

The parameter accepts three formats:

1. **Array of table names**: include all columns from specified tables:

   ```sql
   include_tables=['table1', 'table2']
   ```

2. **Map of tables to columns**: include only specific columns from tables:

   ```sql
   include_tables={'table1': ['column1', 'column2'], 'table2': ['column3']}
   ```

3. **Map with column regex patterns**: include columns matching patterns:

   ```sql
   include_tables={'table1': ['column_prefix.*', 'exact_column']}
   ```

## Examples

### Basic example

Let's start with a simple example using MotherDuck's sample [Hacker News dataset](/getting-started/sample-data-queries/hacker-news):

```sql
CALL prompt_sql('what are the top domains being shared on hacker_news?');
```

Output:
| **query** |
|-----------------|
| SELECT regexp_extract(url, 'https?://([^/]+)') AS domain, COUNT(*) AS count FROM hn.hacker_news WHERE url IS NOT NULL GROUP BY domain ORDER BY count DESC; |

### Intermediate example

This example demonstrates how to generate a more complex query with filtering, aggregation, and time-based analysis:

```sql
CALL prompt_sql('Show me the average score of stories posted by each author who has posted at least 5 stories in 2022, sorted by average score');
```

Output:
| **query** |
|-----------------|
| SELECT 'by', AVG(score) AS average_score FROM hn.hacker_news WHERE EXTRACT(YEAR FROM 'timestamp') = 2022 GROUP BY 'by' HAVING COUNT(id) >= 5 ORDER BY average_score; |

### Advanced Example: Multi-table Analysis with Specific Columns

This example shows how to generate a query that focuses on specific columns:

```sql
CALL prompt_sql(
  'Find the top 10 users who submitted the most stories with the highest average scores in 2023',
  include_tables={
    'hn.hacker_news': ['id', 'by', 'score', 'timestamp', 'type', 'title']
  }
);
```

Output:
| **query** |
|-----------------|
| SELECT "by", AVG(score) AS avg_score, COUNT(*) AS story_count FROM hn.hacker_news WHERE "type" = 'story' AND EXTRACT(YEAR FROM "timestamp") = 2023 GROUP BY "by" ORDER BY story_count DESC, avg_score DESC LIMIT 10; |

### Expert example

This example demonstrates generating a complex query with subqueries, window functions, and complex logic:

```sql
CALL prompt_sql('For each month in 2022, show me the top 3 users who posted stories with the highest scores, and how their average score compares to the previous month');
```

Output:
| **query** |
|-----------------|
| WITH monthly_scores AS (<br />SELECT <br />"by" AS user, <br />DATE_TRUNC('month', "timestamp") AS month, <br />AVG(score) AS avg_score <br />FROM hn.hacker_news <br />WHERE "type" = 'story' AND DATE_PART('year', "timestamp") = 2022 <br />GROUP BY user, month <br />), <br />... |

## Failure example

This example shows that for some complex queries, the model might not generate a valid SQL query. Therefore the output will be the following error message:

```sql
CALL prompt_sql('Identify the most discussed technology topics in Hacker News stories from the past year based on title keywords, and show which days of the week have the highest engagement for each topic');
```

Output:
| **query** |
|-----------------|
| Invalid Input Error: The AI could not generate valid SQL. Try re-running the command or rephrasing your question. |

To generate a valid SQL query, you can try to break down the question into simpler parts.

## Best practices

1. **Be specific in your questions**: the more specific your natural language query, the more accurate the generated SQL will be.

2. **Start simple and iterate**: begin with basic queries and gradually add complexity as needed.

3. **Use the `include_tables` parameter**: when working with large databases, specify relevant tables to improve performance and accuracy.

4. **Review generated SQL**: always review the generated SQL before executing it, especially for complex queries.

5. **Understand your schema**: knowing your table structure helps you phrase questions that align with available data.

6. **Use domain-specific terminology**: include field names in your questions when possible.

7. **Provide context in your questions**: mention time periods, specific metrics, or business context to get more relevant results.

## Notes

- By default, all tables in the current database are considered. Use the `include_tables` parameter to narrow the scope.
- To target a specific database, first execute the `USE <database_name>` command ([learn more about switching databases](/key-tasks/database-operations/switching-the-current-database)).
- The quality of generated SQL depends on the clarity of your natural language question and the quality of your database schema (table and column names).

## Troubleshooting

If you encounter issues with the `prompt_sql` function, consider the following troubleshooting steps:

1. **Check your database schema**: ensure that the tables and columns you're querying are present in the current database.
2. **Be specific in your questions**: the more specific your natural language query, the more accurate the generated SQL will be.
3. **Use the `include_tables` parameter**: when working with large databases, specify relevant tables to improve performance and accuracy.

---

Source: https://motherduck.com/docs/sql-reference/motherduck-sql-reference/ai-functions/sql-assistant/prompt-explain

# PROMPT_EXPLAIN
> Get AI-generated explanations of SQL queries using the PROMPT_EXPLAIN function.
## Explain a query

The `prompt_explain` table function allows MotherDuck AI to analyze and explain SQL queries in plain English. This feature helps you understand complex queries, verify that a query does what you intend, and learn SQL concepts through practical examples.

::::tip
This function is particularly useful for understanding queries written by others or for automatically documenting your own queries for future reference.
::::

### Syntax

```sql
CALL prompt_explain('<SQL query>', [include_tables=['<table_name>', '<table_name>']]);
```

### Parameters

| **Parameter**      | **Required** | **Description**                                                                                                          |
|--------------------|--------------|--------------------------------------------------------------------------------------------------------------------------|
| `query`            | Yes          | The SQL query to explain                                                                                                 |
| `include_tables`   | No           | Array of table names to consider for context (defaults to all tables in current database). Can also be a dictionary in the format `{'table_name': ['column1', 'column2']}` to specify which columns to include for each table. |

### Example usage

Here are several examples using 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).

#### Explaining a complex query

```sql
CALL prompt_explain('
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;
');
```

**Output**: when you run a `prompt_explain` query, you'll receive a single-column table with a detailed explanation:

| **explanation** |
|-----------------|
|The query retrieves the top 10 most frequent domains from the `url` field in the `hn.hacker_news` table. It counts the occurrences of each domain by extracting the domain part from the URL (after the '//' and before the next '/'), groups the results by domain, and orders them in descending order of their count. The result includes the count of occurrences (`domain_count`) and the domain name itself (`domain`). |

#### Using dictionary format for include_tables

You can specify which columns to include for each table using the dictionary format:

```sql
CALL prompt_explain('
SELECT u.id, u.name, COUNT(s.id) AS story_count
FROM hn.users u
LEFT JOIN hn.stories s ON u.id = s.user_id
GROUP BY u.id, u.name
HAVING COUNT(s.id) > 5
ORDER BY story_count DESC
LIMIT 20;
', include_tables={'hn.users': ['id', 'name'], 'hn.stories': ['id', 'user_id']});
```

This approach allows you to focus the explanation on only the relevant columns, which can be helpful for tables with many columns.

#### How it works

The `prompt_explain` function processes your query in several steps:

1. **Parsing**: analyzes the SQL syntax to understand the query structure
2. **Schema analysis**: examines the referenced tables and columns to understand the data model
3. **Operation analysis**: identifies the operations being performed (filtering, joining, aggregating, etc.)
4. **Translation**: converts the technical SQL into a clear, human-readable explanation
5. **Context addition**: adds relevant context about the purpose and expected results of the query

### Best practices

For the best results with `prompt_explain`:

1. **Provide complete queries**: include all parts of the query for the most accurate explanation
2. **Use table aliases consistently**: this helps the function understand table relationships
3. **Specify relevant tables**: use the `include_tables` parameter for large databases
4. **Review explanations**: verify that the explanation matches your understanding of the query
5. **Use for documentation**: save explanations as comments in your code for future reference

---

Source: https://motherduck.com/docs/sql-reference/motherduck-sql-reference/ai-functions/sql-assistant/prompt-fix-line

# PROMPT_FIX_LINE
> Fix SQL query errors line by line using the PROMPT_FIX_LINE function.
## Fix your query line-by-line

The `prompt_fix_line` table function allows MotherDuck AI to correct specific lines in your SQL queries that contain syntax or spelling errors. Unlike [`prompt_fixup`](../prompt-fixup), which rewrites the entire query, this function targets only the problematic lines, making it faster and more precise for localized errors.

::::tip
This function is ideal for fixing minor syntax errors in large queries where you want to preserve most of the original query structure and formatting.
::::

### Syntax

```sql
CALL prompt_fix_line('<SQL query>', error='<Error message>', [include_tables=['<table_name>', '<table_name>']]);
```

### Parameters

| **Parameter**      | **Required** | **Description**                                                                                                          |
|--------------------|--------------|--------------------------------------------------------------------------------------------------------------------------|
| `query`            | Yes          | The SQL query that needs correction                                                                                      |
| `error`            | No           | The error message from the SQL parser (helps identify the problematic line)                                              |
| `include_tables`   | No           | Array of table names to consider for context (defaults to all tables in current database)                                |

### Example usage

Here are several examples using 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).

#### Fixing simple syntax errors

```sql
-- Fixing a misspelled keyword with error message
CALL prompt_fix_line('SEELECT COUNT(*) as domain_count FROM hn.hackers', error='
Parser Error: syntax error at or near "SEELECT"
LINE 1: SEELECT COUNT(*) as domain_count FROM h...
        ^');

-- Fixing a typo in a column name
CALL prompt_fix_line('SELECT user_id, titlee, score FROM hn.stories LIMIT 10');

-- Fixing incorrect operator usage
CALL prompt_fix_line('SELECT * FROM hn.stories WHERE score => 100');
```

#### Fixing errors in multi-line queries

```sql
-- Fixing a specific line in a complex query
CALL prompt_fix_line('SELECT
    user_id,
    COUNT(*) AS post_count,
    AVG(scor) AS average_score
FRUM hn.stories
GROUP BY user_id
ORDER BY post_count DESC
LIMIT 10', error='
Parser Error: syntax error at or near "FRUM"
LINE 5: FRUM hn.stories
        ^');
```

### Example output

When you run a `prompt_fix_line` query, you'll receive a two-column table with the line number and corrected content:

| **line_number** | **line_content**                                |
|-----------------|-------------------------------------------------|
| 1               | SELECT COUNT(*) as domain_count FROM hn.hackers |

For multi-line queries, only the problematic line is corrected:

| **line_number** | **line_content**                                |
|-----------------|-------------------------------------------------|
| 5               | FROM hn.stories                                 |

#### How it works

The `prompt_fix_line` function processes your query in a targeted way:

1. **Error localization**: uses the error message (if provided) to identify the specific line with issues
2. **Context analysis**: examines surrounding lines to understand the query's structure and intent
3. **Targeted correction**: fixes only the problematic line while preserving the rest of the query
4. **Line replacement**: returns the corrected line with its line number for easy integration

For example, when fixing a syntax error in a single line:

```sql
CALL prompt_fix_line('SEELECT COUNT(*) as domain_count FROM hn.hackers', error='
Parser Error: syntax error at or near "SEELECT"
LINE 1: SEELECT COUNT(*) as domain_count FROM h...
        ^');
```

The function will focus only on line 1, correcting the misspelled keyword:

| **line_number** | **line_content**                                |
|-----------------|-------------------------------------------------|
| 1               | SELECT COUNT(*) as domain_count FROM hn.hackers |

For multi-line queries with an error on a specific line:

```sql
CALL prompt_fix_line('SELECT
    user_id,
    COUNT(*) AS post_count,
    AVG(scor) AS average_score
FRUM hn.stories
GROUP BY user_id
ORDER BY post_count DESC
LIMIT 10', error='
Parser Error: syntax error at or near "FRUM"
LINE 5: FRUM hn.stories
        ^');
```

The function will only correct line 5, leaving the rest of the query untouched:

| **line_number** | **line_content**                                |
|-----------------|-------------------------------------------------|
| 5               | FROM hn.stories                                 |

This allows you to apply the fix by replacing just the problematic line in your original query, which is especially valuable for large, complex queries where a complete rewrite would be disruptive.

When multiple errors exist, you would run `prompt_fix_line` multiple times, fixing one line at a time:

```sql
-- First fix
CALL prompt_fix_line('SELECT
    user_id,
    COUNT(*) AS post_count,
    AVG(scor) AS average_score
FRUM hn.stories
GROUP BY user_id
ORDER BY post_count DESC
LIMIT 10', error='
Parser Error: syntax error at or near "FRUM"
LINE 5: FRUM hn.stories
        ^');

-- After applying the first fix, run again for the second error
CALL prompt_fix_line('SELECT
    user_id,
    COUNT(*) AS post_count,
    AVG(scor) AS average_score
FROM hn.stories
GROUP BY user_id
ORDER BY post_count DESC
LIMIT 10', error='
Parser Error: column "scor" does not exist
LINE 4:     AVG(scor) AS average_score
                ^');
```

The second call would return:

| **line_number** | **line_content**                                |
|-----------------|-------------------------------------------------|
| 4               | AVG(score) AS average_score                     |

Note: you need to run `prompt_fix_line` multiple times to fix all errors.

### Best practices

For the best results with `prompt_fix_line`:

1. **Include the error message**: the parser error helps pinpoint the exact issue
2. **Preserve query structure**: use this function when you want to maintain most of your original query
3. **Fix one error at a time**: to address multiple errors, run `prompt_fix_line` multiple times
4. **Include context**: provide the complete query, not just the problematic line
5. **Be specific with table names**: use the `include_tables` parameter for large databases

### Limitations

While `prompt_fix_line` is efficient, be aware of these limitations:

- Only fixes syntax errors, not logical errors in query structure
- Accurate error messages help identify the problematic line and improve output
- May not be able to fix errors that span multiple lines
- Cannot fix issues related to missing tables or columns in your database
- Works best with standard SQL patterns and common table structures

### Troubleshooting

If you're not getting the expected results:

- Ensure you've included the complete error message
- Check that the line numbers in the error message match your query
- For complex errors, try using `prompt_fixup` instead
- If multiple lines need fixing, address them one at a time
- Verify that your database schema is accessible to the function

---

Source: https://motherduck.com/docs/sql-reference/motherduck-sql-reference/ai-functions/sql-assistant/prompt-fixup

# PROMPT_FIXUP
> Automatically fix SQL query errors using the PROMPT_FIXUP function.
## Fix up your query

The `prompt_fixup` table function allows MotherDuck AI to correct and **completely rewrite** SQL queries that have logical or severe syntactical issues. This powerful feature analyzes your problematic query, identifies issues, and generates a corrected version that follows proper SQL syntax and semantics.

::::tip
For minor syntax errors or typos in large queries, consider using the [`prompt_fix_line`](../prompt-fix-line) function instead, which is faster and more precise as it only rewrites the problematic line.
::::

### Syntax

```sql
CALL prompt_fixup('<SQL query>', [include_tables=['<table_name>', '<table_name>']]);
```

### Parameters

| **Parameter**      | **Required** | **Description**                                                                                                          |
|--------------------|--------------|--------------------------------------------------------------------------------------------------------------------------|
| `query`            | Yes          | The SQL query that needs correction                                                                                      |
| `include_tables`   | No           | Array of table names to consider for context (defaults to all tables in current database)                                |

### Example Usage

Here are several examples using 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).

#### Fixing syntax errors

```sql
-- Fixing misspelled keywords
CALL prompt_fixup('SEELECT COUNT(*) as domain_count FROM hn.hackers');

-- Fixing incorrect table names
CALL prompt_fixup('SELECT * FROM hn.stories WHERE score > 100 ODER BY score DESC');

-- Fixing missing clauses
CALL prompt_fixup('SELECT AVG(score) hn.hacker_news GROUP score > 10');
```

#### Fixing logical errors

```sql
-- Fixing incorrect join syntax
CALL prompt_fixup('SELECT u.name, s.title FROM hn.users u, hn.stories s WHERE u.id = s.user_id ORDER BY s.score');

-- Fixing aggregation issues
CALL prompt_fixup('SELECT user_id, AVG(score) FROM hn.stories GROUP BY score');

-- Fixing complex query structure
CALL prompt_fixup('SELECT COUNT(*) FROM hn.stories WHERE timestamp > "2020-01-01" AND timestamp < "2020-12-31" WITH score > 100');
```

### Example output

When you run a `prompt_fixup` query, you'll receive a single-column table with the corrected SQL:

| **query** |
|-----------------|
| SELECT COUNT(*) as domain_count FROM hn.hacker_news |

#### How it works

The `prompt_fixup` function processes your query in several steps:

1. **Analysis**: examines your query to identify syntax errors, logical issues, and structural problems
2. **Schema validation**: checks your query against the database schema to ensure table and column references are valid
3. **Correction**: applies fixes based on the identified issues and your likely intent
4. **Rewriting**: generates a complete, corrected version of your query that maintains your original goal

For example, when fixing this query with multiple issues:

```sql
CALL prompt_fixup('SEELECT AVG(scor) FRUM hn.stories WERE timestamp > "2020-01-01" GRUP BY user_id');
```

The function will:

- Correct misspelled keywords (`SEELECT` → `SELECT`, `FRUM` → `FROM`, `WERE` → `WHERE`, `GRUP` → `GROUP`)
- Fix column name typos (`scor` → `score`)
- Ensure proper clause ordering and syntax

Resulting in a properly formatted query:

| **query** |
|-----------------|
| SELECT AVG(score) FROM hn.stories WHERE timestamp > '2020-01-01' GROUP BY user_id |

For logical errors, the process is similar but focuses on semantic correctness:

```sql
CALL prompt_fixup('SELECT user_id, AVG(score) FROM hn.stories GROUP BY score');
```

Will be corrected to:

| **query** |
|-----------------|
| SELECT user_id, AVG(score) FROM hn.stories GROUP BY user_id |

The function recognized that grouping should be by `user_id` (the non-aggregated column) rather than by `score` (which is being averaged).

### Best practices

For the best results with `prompt_fixup`:

1. **Include the entire query**: even if only part of it has issues
2. **Be specific with table names**: use the `include_tables` parameter for large databases
3. **Review the fixed query**: always check that the corrected query matches your intent
4. **Use for complex issues**: prefer this function for logical errors or major syntax problems
5. **Consider alternatives**: for simple typos, `prompt_fix_line` may be more efficient

### Limitations

While `prompt_fixup` is powerful, be aware of these limitations:

- May change query logic if the original intent isn't clear
- Performance depends on the complexity of your query
- Works best with standard SQL patterns and common table structures
- May not preserve exact formatting or comments from the original query
- Cannot fix issues related to missing tables or columns in your database

### Troubleshooting

If you're not getting the expected results:

- Check that you've included all relevant tables in the `include_tables` parameter
- Ensure your database schema is accessible to the function
- For very complex queries, try breaking them into smaller parts
- If the fixed query doesn't match your intent, try providing more context in comments

---

Source: https://motherduck.com/docs/sql-reference/motherduck-sql-reference/ai-functions/sql-assistant/prompt-schema

# PROMPT_SCHEMA
> Describe database contents using the PROMPT_SCHEMA function for AI-generated schema summaries.
## Describe contents of a database

The `prompt_schema` table function allows MotherDuck AI to analyze and describe the contents of your current database in plain English. This feature helps you understand the structure, purpose, and relationships between tables in your database without having to manually inspect each table's schema.

::::tip
This function is particularly useful when working with unfamiliar databases or when you need a high-level overview of a complex database structure.
::::

### Syntax

```sql
CALL prompt_schema([include_tables=['<table_name>', '<table_name>']]);
```

### Parameters

| **Parameter**      | **Required** | **Description**                                                                                                          |
|--------------------|--------------|--------------------------------------------------------------------------------------------------------------------------|
| `include_tables`   | No           | Array of table names to consider for analysis (defaults to all tables in current database)                               |

### Example usage

Here are several examples using MotherDuck's [sample data database](/getting-started/sample-data-queries/datasets).

#### Describing the entire database

```sql
CALL prompt_schema();
```

#### Example output

When you run a `prompt_schema` query, you'll receive a single-column table with a detailed description:

| **summary** |
|-----------------|
| The database contains tables related to ambient air quality data, Stack Overflow survey results, NYC taxi and service requests, rideshare data, movie information with embeddings, and Hacker News articles, capturing a wide range of information from environmental metrics to user-generated content and transportation data. |

#### Describing specific tables

```sql
CALL prompt_schema(include_tables=['hn.hacker_news', 'hn.stories']);
```

| **summary** |
|-----------------|
| The database contains information about Hacker News posts, including details such as the title, URL, content, author, score, time of posting, type of post, and various identifiers and status flags. |

#### How it works

The `prompt_schema` function processes your database in several steps:

1. **Schema extraction**: examines the structure of tables, including column names and data types
2. **Data sampling**: analyzes sample data to understand the content and purpose of each table
3. **Relationship detection**: identifies potential relationships between tables based on column names and values
4. **Domain recognition**: categorizes tables into domains or subject areas based on their content
5. **Summary generation**: creates a human-readable description of the database structure and purpose

### Best practices

For the best results with `prompt_schema`:

1. **Focus on relevant tables**: use the `include_tables` parameter to analyze specific parts of large databases
2. **Run on updated databases**: ensure your database is up-to-date for the most accurate description
3. **Use for documentation**: save the output as part of your database documentation
4. **Combine with other tools**: use alongside `DESCRIBE` and `SHOW` commands for complete understanding
5. **Share with team members**: use the output to help new team members understand the database structure

---

## Docs feedback

MotherDuck accepts optional user-submitted feedback about this page at `GET https://motherduck.com/docs/api/feedback/agent`.
For agents and automated tools, feedback submission should be user-confirmed before sending.

URL-encode query parameter values and send a GET request:

```text
GET https://motherduck.com/docs/api/feedback/agent?page_path=%2Fsql-reference%2Fmotherduck-sql-reference%2Fai-functions%2F&page_title=MotherDuck%20Documentation%20-%20AI&text=<url-encoded user feedback, max 2000 characters>
```

Optionally append `&source=<url-encoded interface identifier>` such as `claude.ai` or `chatgpt`.

`page_path` and `text` are required; `page_title` and `source` are optional. Responses: `200 {"feedback_id": "<uuid>"}`, `400` for malformed query parameters, and `429` when rate-limited.