> AI agents: this is one page from Mammoth Analytics documentation. The index of all pages as Markdown is https://docs.mammoth.io/llms.txt. Append `.md` to any docs URL, or send `Accept: text/markdown`, to get Markdown.

# AI SQL Query

> Use AI SQL Query in Mammoth to turn a plain-English request into a SQL query on your View, review or edit the SQL, and apply it as a pipeline task.

AI SQL Query lets you query your dataset using plain English or hand-written SQL. Describe what you want—Mammoth generates valid SQL, tests it against your data, and applies it as a task in your transformation pipeline.

## How to open AI SQL Query

1. Open a View in the Data Editor.
2. Click the blue **+** button (tooltip "Add a task") in the action bar.
3. The Transform menu opens. The **AI Powered** category is selected by default. If you type your request in the **Describe your transformation** search box first, **AI SQL Query** always appears in the results and your text is carried into its prompt.
4. Click **AI SQL Query—Run AI-assisted SQL queries directly on your data**.

The AI SQL Query panel opens at the bottom of the View.

![Transform menu open on AI Powered, listing AI SQL Query, Generative AI, Extract Text, Bulk Replace and Conditional Filter](https://docs.mammoth.io/api/v1/images/20260605_092517_transformation-menu1.jpg)

## The AI SQL Query panel

![AI SQL Query panel with an empty prompt box, a disabled Generate SQL button and an empty SQL editor](https://docs.mammoth.io/api/v1/images/20260605_092555_ai-sql-query-panel1.jpg)

| Element | Description |
| --- | --- |
| **Intent textarea** | Free-text field—describe what you want to query in plain English |
| **Generate SQL** | Triggers AI generation; active once you type an intent |
| **Status bar** | Bar showing live generation progress (appears during generation) |
| **AI SQL Query editor** | SQL editor with syntax highlighting |
| **Insert Snippet** | Opens a panel to insert reusable SQL snippets |
| **Cancel** | Closes the panel without applying any changes |
| **Apply** | Applies the SQL as a new transformation task in the pipeline |
| **Apply dropdown** | Expands to reveal **Add to Pipeline** as an alternative apply action |

## Generating SQL from plain English

1. In the intent textarea, type a description of what you want.
2. Click **Generate SQL** (or press Enter).
3. Watch the status bar—it shows each phase of the generation process in real time.
4. When complete, the generated SQL appears in the code editor.
5. Review the SQL, then click **Apply**.

![AI SQL Query panel with the prompt 'Show total sales amount grouped by transaction type' and Generate SQL enabled](https://docs.mammoth.io/api/v1/images/20260605_092707_intent-filled1.jpg)

**Example intents:**

- "Show total sales amount grouped by transaction type"
- "Find the top 10 customers by order count"
- "Show all rows where the register is Online Orders and the transaction type is sale"
- "Count unique cashiers per register"

## What happens during generation

When you click Generate SQL, Mammoth runs a multi-step process behind the scenes. The status bar shows progress messages such as the following (the exact messages vary with your request, and extra messages appear if a query needs correcting):

![AI SQL Query panel while generating, with a Generating button and a Validating request size message](https://docs.mammoth.io/api/v1/images/20260605_092746_sql-generating1.jpg)

| Phase | Status message |
| --- | --- |
| 1\. Start | Starting SQL generation process... |
| 2\. Metadata | Preparing data and metadata... |
| 3\. Column selection | Identifying relevant columns for your request... |
| 4\. Token validation | Validating request size... |
| 5\. Sample values | Fetching sample values for selected columns... |
| 6\. Context | Preparing query context with your data... |
| 7\. Understanding | Understanding your data and request... |
| 8\. Writing | Writing SQL query to match your request... |
| 9\. Testing | Testing query correctness... |
| 10\. Validation | Query generated successfully, verifying if results match intent... |

Once generation finishes, the SQL populates the editor and the **Apply** button becomes active. If the query could not be confirmed to match your request, a warning tells you to review and test it before applying.

## Reviewing and editing generated SQL

Mammoth generates SQL that references your View's name and column names exactly as they appear in your dataset. Column names and the View name are double-quoted to handle spaces and special characters.

![AI SQL Query panel showing the generated SELECT statement grouped by Transaction Type and an enabled Apply button](https://docs.mammoth.io/api/v1/images/20260605_092843_sql-generated1.jpg)

**Example output:**

```sql
SELECT "Transaction Type", CAST(SUM("Total") AS NUMERIC) AS total_sales_amount
FROM "View 1"
GROUP BY "Transaction Type"
```

You can edit the SQL directly in the code editor before applying.

You do not have to use AI generation—you can type or paste SQL directly into the editor and apply it without generating anything.

## Writing SQL directly (without AI)

You can skip AI generation entirely and write SQL by hand:

1. Open the AI SQL Query panel.
2. Leave the intent field blank.
3. Type your SQL directly in the **AI SQL Query** code editor.
4. Click **Apply**.

This is useful when you already know the exact query you want to run, or when you want to adapt a query from another tool.

**SQL dialect:** AI-generated queries are written in DuckDB SQL. Column names with spaces or special characters must be double-quoted. The View name is the table name.

## Using SQL snippets

Snippets let you save reusable SQL fragments and insert them into any query. They are useful for standard calculations, filters, or expressions that your team uses repeatedly.

Click **Insert Snippet** to open the snippets panel.

![Insert Snippet menu open in the AI SQL Query panel, showing No snippets available and a New Snippet link](https://docs.mammoth.io/api/v1/images/20260605_092951_insert-snippet-panel1.jpg)

### Creating a snippet

1. Click **New Snippet** in the snippets panel.
2. Give the snippet a name and enter the SQL fragment in the value field. A snippet name must start with a letter or underscore and contain only letters, digits, and underscores. A description is optional.
3. Click **Create Snippet**. Snippets cannot reference other snippets.

### Using a snippet in a query

In the snippets panel, choose how to insert a snippet:

- **Reference**—inserts the token `{{snippet_name}}`. The reference stays linked: Mammoth resolves it to the snippet's current content when the query runs.
- **Paste value**—drops the snippet's raw text into the query. The text is frozen and has no link back to the snippet.

### Stale snippets

If a snippet referenced by a saved query task is updated, Mammoth marks that task as stale. Rerun the affected pipeline to apply the updated snippet content.

---

## Applying the query

The **Apply** button adds the SQL query as a new transformation task in the pipeline. It has two modes:

| Option | What it does |
| --- | --- |
| **Apply** | Adds the task to the pipeline and executes immediately |
| **Add to Pipeline** (dropdown) | Adds the task to the pipeline without running it; it runs when you apply the pending pipeline changes |

After applying, the SQL Query task appears as a card in the pipeline panel on the left. You can reorder, edit, duplicate, suspend, or restore it like any other pipeline task.

---

## Error states

If the AI cannot produce a query that passes validation after multiple attempts, it shows an error message:

> Query validation failed after N attempts: Query doesn't match intent

| Error message | What it means |
| --- | --- |
| Query validation failed after N attempts | The AI generated SQL but could not confirm the results matched your intent after 6 attempts in total (the first query plus up to 5 corrections) |
| Failed to generate SQL query | The AI service request failed |
| No SQL query was generated | The AI returned an empty response |
| An error occurred while processing your SQL query | Unexpected processing error |

When generation fails, the SQL editor may contain the last attempted query. You can edit it manually and apply it directly, or rephrase your intent and try again.

**Tips for better results:**

- Be specific about column names (e.g., "Transaction Type column" rather than "type")
- Mention aggregations explicitly ("total", "count", "average")
- If a column for your concept doesn't exist, describe the calculation (e.g., "revenue as quantity times price")

## Tips and best practices

**Be descriptive with intent.** The more specific your description, the more accurate the SQL. Instead of "show sales by region", try "show total quantity sold grouped by region, sorted highest to lowest".

**Review before applying.** Always check the generated SQL before clicking Apply. Confirm that column references match your actual column names and the logic reflects your intent.

**Use snippets for team standards.** Save common calculations or filter conditions as snippets so everyone on the team uses consistent logic.

**Edit freely.** Generated SQL is a starting point. Edit it in the code editor as needed—there is no lock on AI-generated output.

**Pipeline position matters.** The task's position in the pipeline determines which state of the data is queried. If you insert an AI SQL Query task after a filter task, it queries the already-filtered data, not the original source.

---
Source: https://docs.mammoth.io/learn/transform/ai-sql-query.md · Updated: 2026-10-13