Accessing SQL Transforms

To access SQL Transforms:

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

Creating a SQL Transformation

Step 1: Selecting Data Sources

1. Click Add Data Set.
2. Select one or more data sets as input sources.
3. Click Select and Continue.

Step 2: Writing SQL

1. Use the SQL editor to write transformation queries.
2. Refer to the column list on the left for available fields.
3. Preview the SQL output by clicking Run Now.

Step 3: Using AI Assistance

1. Use the chat assistant to help generate SQL queries.
2. Provide clear instructions, e.g., "Combine these data sets into a single table."
3. If the output is incorrect, refine the prompt with more details.

Step 4: Validating SQL

1. Ensure the query runs successfully.
2. In the event of errors, check that all column names and case sensitivity are correct.
3. SQL query corrections can be made via AI Chat in the AI Chat window and will translate to an updated query in the SQL query editor window.
4. If the AI-generated SQL fails, you can manually correct SQL directly in the SQL query editor window.

Step 5: Mapping Fields

When you are ready to publish your new dataset, the columns of the set must be mapped to become relevant columns to NinjaCat for potential use in reporting and other areas.

1. Click Edit Mapping.
2. Assign the appropriate field types:
   • Date Field: Set the primary date column.
   • Account ID: Assign the NinjaCat account identifier.

     📘

     The two fields above are required if the desired, SQL transformed dataset is to be accessible to the NinjaCat template builder and used in NinjaCat Reporting.

     • Account ID needs to be mapped as a Dimension and needs to = System Account ID
     • Metric Date needs to be mapped as a Dimension and needs to = Date

3. Ensure dimensions and metrics are correctly categorized.

Step 6: Saving the Data Set

1. Click Save Mapping.
2. Click Save Data Set.
3. The new data set will now be available for use with AI Agents, exporting with Data Cloud Ship, and using in a template to create reports or dashboards.

📘

Always remember the three SQL transform finishing steps:

1. Click "Run Now"
2. "Edit Mapping"
3. Click "Save Dataset"

Editing an Existing SQL Transformation

To modify an existing transformation:

1. Navigate to Data Sets.
2. Select the desired SQL transform created data set and click Edit SQL.
3. Make necessary changes, then click Run Now.
4. Validate mapping. By editing your SQL transformation, you may have changed some fundamental details of the data set. Going through the mapping process again guarantees that your data set is prepared for use.
5. Save updates.

Important Considerations

• Deleting a source data set used in a transformation will cause the transformation to break. The data set will no longer be able to be edited.
• Supported SQL Syntax: NinjaCat supports Snowflake SQL functions, including:
  • Select Statements
  • Group By
  • Joins & Unions
  • Scalar Functions (e.g., DATE_TRUNC)
• Standard Reference Data Sets - Upon request, NinjaCat can generate three standard data sets. These data sets contain details about the different entities that you may be storing data for inside NInjaCat's Data Cloud. These data sets can help to cross-reference with provider data to better organize, create custom roll-ups, or groupings.
  • Account Groups
  • Account Network Settings
  • Account Reference
• “Unions” Combination option - has been retired. Existing Union datasets won’t be impacted, but any transforms or even "Union-ing" of data can be accomplished through this SQL Editor. Either through SQL or by using the AI chat experience.

Troubleshooting

Issue: SQL query fails due to column mismatches.

• Solution: Ensure correct column names and case sensitivity.

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

• Solution: Request the assistant to insert the SQL into the editor.

Issue: Deleted source data set breaks transformation.

• Solution: Contact support to restore the transformation.

By following these steps, users can efficiently leverage SQL Transforms to manipulate data within NinjaCat.