AI SQL Query

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.

The AI SQL Query panel

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.

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):

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.

Example output:

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.

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.