SQL Transforms allow you to manipulate and combine Datasets within NinjaCat using SQL and an AI-powered chat assistant. This feature enables complex transformations, joins, unions, and aggregations beyond basic filtering and roll-ups.

Accessing SQL Transforms

To access SQL Transforms:

1. Navigate to Data Cloud, then click Datasets.
2. Click Create Data Set in the top right.
3. Select Transform Data under "Choose a Data Set Type" and click Continue.

Creating a SQL Transformation

Step 1: Selecting Data Sources

1. In the Datasets panel on the left, click Add Dataset.
2. Select one or more datasets as input sources from the modal.
3. Click Select & Continue.

Once added, each dataset appears in the left-hand Datasets panel with its columns listed. You can click a dataset name or a column name in this panel to insert it directly into your SQL query at the cursor position.

Step 2: Writing SQL

There are two ways to write your SQL query:

Option 1 — SQL Editor (Manual)

Use the SQL editor to write transformation queries directly. The editor includes:

• Autocomplete — As you type, the editor suggests SQL keywords, functions, table names, and column names. Suggestions are color-coded by type (keywords, functions, tables, and columns).
• Click-to-insert — Click any dataset name or column name in the left-hand Datasets panel to insert it directly into your query at your cursor position.
• Keyboard shortcut — Press Ctrl+Enter (Windows) or Cmd+Enter (Mac) to run your query.

When complete, click Run Now to execute your query.

Option 2 — AI Chat Assistant

Open the AI Chat drawer by clicking the ✨ spark icon in the left sidebar. The AI assistant can generate SQL queries from natural language instructions.

• Provide clear instructions, for example: "Join these two datasets on account_id and sum the spend by date."
• The assistant will generate the SQL and update the query editor automatically.
• If the output is incorrect, refine your prompt with more specific details.
• When complete, click Run Now to execute the query.

Step 3: Validating SQL

1. After clicking Run Now, confirm the query executes successfully and the output table looks correct.
2. If there are errors, check that all column names and case sensitivity are correct — NinjaCat uses Snowflake SQL syntax.
3. Errors can be corrected in two ways:
   • Via AI Chat: Describe the issue in the chat and the assistant will suggest a corrected query.
   • Manually: Edit the SQL directly in the editor.

Step 4: Mapping Fields

Before saving, you must map the output columns so NinjaCat knows how to interpret the data for reporting and AI Agents.

1. Click the Edit Mapping button in the top header.
2. A two-step mapping wizard will open:

Step 1 — Date & Account Matching

📘

Required for Reporting

Both the Date Field and Account ID mappings are required if you want this dataset to be accessible in NinjaCat's template builder and reporting. Specifically:

• Account ID must be mapped as a Dimension and set to System Account ID
• Date must be mapped as a Dimension and set to Date

Both fields can be skipped if you do not need the dataset in reporting (e.g., the dataset is only used by an AI Agent).

Step 2 — Column Definitions

Review and customize each output column:

3. Click Save Mapping when complete.

Step 5: Saving the Dataset

1. Click Save Mapping to confirm your field mappings.
2. Click Save Dataset in the top header.
3. The new dataset will now be available for use with:
   • AI Agents
   • Data Cloud Ship (Export)
   • NinjaCat Report Templates

📘

Remember the three finishing steps every time:

1. Click Run Now to execute the query
2. Click Edit Mapping to map your fields
3. Click Save Dataset to save

Adding Agent Directions to a SQL Transform Dataset

You can provide contextual instructions to AI Agents directly on a SQL Transform dataset using the Agent Directions field. This helps agents understand what the dataset contains, when to use it, and how to interpret its fields — regardless of which Agent the dataset is assigned to.

To add Agent Directions:

1. Navigate to Data Cloud → Datasets.
2. Select your SQL Transform dataset.
3. In the right-hand panel, find the Agent Directions field and click the pencil icon to edit.
4. Enter a description explaining:
   • What the dataset represents
   • Any important information about specific fields
   • Any context that is always applicable when this dataset is used

📘

Note

Agent Directions travel with the dataset — meaning the instructions will be included automatically whenever this dataset is assigned to any AI Agent.

At the Agent level, you can also set Additional Agent Directions for instructions that are specific to how that particular Agent should use the dataset.

Editing an Existing SQL Transformation

To modify an existing SQL Transform dataset:

1. Navigate to Data Cloud → Datasets.
2. Select the desired SQL Transform dataset and click Edit SQL.
3. Make your changes to the SQL query, then click Run Now.
4. Re-validate your mapping by clicking Edit Mapping — modifying the SQL may change the output columns, so it's important to review mappings after any edit.
5. Click Save Mapping, then Save Dataset.

Important Considerations

• Deleting a source dataset used in a SQL Transform will break the transformation, and the dataset will no longer be editable.
• Supported SQL Syntax: NinjaCat uses Snowflake SQL, including:
  • SELECT statements
  • GROUP BY
  • JOINs & UNIONs
  • Scalar functions (e.g., DATE_TRUNC, DATEADD, PARSE_JSON)
• Standard Reference Datasets: Upon request, NinjaCat can generate three standard reference datasets to help you cross-reference provider data and create custom roll-ups or groupings:
  • Account Groups
  • Account Network Settings
  • Account Reference
• Retired: "Unions" Combine Option — The legacy Unions feature has been retired. Existing Union datasets will continue to function, but all new data combining and transformation should be done through the SQL Transform Builder.

Troubleshooting

Issue: SQL query fails due to column mismatches.

• Solution: Check that column names match exactly (including case sensitivity). Use the autocomplete or click-to-insert from the Datasets panel to avoid typos.

Issue: AI-generated SQL does not appear in the editor.

• Solution: Ask the assistant to insert the SQL into the editor explicitly.

Issue: Deleted source dataset breaks a transformation.

• Solution: Contact NinjaCat support to restore the transformation.

Issue: The Save Dataset button is greyed out.

• Solution: Ensure all of the following are true: the query has been run successfully, column mappings are complete with no warnings, the dataset name is unique, and there are unsaved changes pending.

Issue: Column mapping shows a warning triangle.

• Solution: Click Edit Mapping and complete or correct the required date and account field mappings.