Working with SQL Editor

SQL Editor allows you to create, execute, debug, and optimize SQL statements to streamline data migration processes, delivering the following key benefits (but not limited to):

Build and test custom rules or views for ETL Tasks directly within the SQL Editor
Fine-tune queries to handle large datasets efficiently
Generate or update Target reports and data reconciliation queries to verify migration accuracy and completeness
Create temporary tables to assist with updating data in the working table
Validate that the records you need are actually present in the table before building out views
Instantly identify and resolve data mapping rules

Get started using SQL Editor to run DQL, DDL, and DML commands on your datastore.

Object Explorer

Use the Object Explorer panel, a tree structure representation located on the left side of the page, to browse schemas and objects on your datastore. Currently, SQL Editor displays the following datastores:

Construct Datastore
Migrate Datastore
Snapshot Datastores
Datastores of Working Databases

Note
Ensure that you have registered the required datastores as Datasources in Catalog > Datasources.

Select the required datastore using the Select Datastore list. The schemas and objects associated with the selected datastore is displayed. Currently, the Object Explorer displays the following objects for a schema:

Functions
Tables
Triggers
Views
Procedures
Synonyms

Metadata for these objects is set to lazy-load when you click on them. You can also filter and refresh the objects using the Options (…) button associated with each one. Currently, filtering is supported by object name. To apply a filter, select the desired object, then click Options (…) > Filter to open the Filter dialog box.

Querying Panel

The Querying panel, or Query Editor, located at the centre of the page, allows you to run SQL commands to create and work with new or existing objects in your datastore. You can open a new query tab for scripting or use Options (…) on an existing object to open a new query tab with a SQL statement generated based on the intended operation.

For better SQL scripting experience, you can expand the querying panel by collapsing the Object Explorer and Insights panels.

Note
You can only perform the operations granted to your role and the operations allowed on an object in SQL Editor. The Query Editor auto-suggests SQL syntax based on your role and user permissions, helping you write queries more efficiently by suggesting relevant commands and functions. Refer to the Roles and Permissions section for more information.

The Querying panel functions similarly to commonly used SQL editors, allowing users to run SQL commands and view corresponding results or error messages. When a SQL query is executed, a successful run updates the Results tab with the retrieved records, while a failed execution displays an error message in the Console tab for debugging and troubleshooting.

You can navigate through query history or between different query versions using the Previous and Next icons.

Pin/Unpin a Query Tab

To pin a tab, click the Pin button in the tab’s title. To unpin it, click the Unpin button.

To close all tabs except pinned, click More Options > Close All but Pinned.

Rename a Query Tab

To rename a query tab, double-click its title. Enter a new name for the query tab in the Rename Tab dialog box, and then click Save.

Import SQL Query

To import SQL script files (.sql) from your local machine to the SQL Editor, click the Import icon at the top-right corner of the Querying panel.

Export Query or Result Data

To save the query locally for later use or for sharing with team members, click the Download icon at the top-right corner of the Querying panel and:

Select Download to save the query from the current query tab as a .sql file.
Select Download All to save queries from all query tabs as a .zip file.

To save or export data from a successfully executed script, right-click the data in the Result tab and click Export.

You can then export the data as a .csv or .xlsx file.

Insights Panel

With the Insights panel, you can gain a deeper understanding and actionable information, along with helpful guidance, recommendations, and context-driven knowledge about the SQL object you're working on. Currently, the Insights panel includes an AI-powered SQL assistant and query history features.

SQL Assist Tab

The SQL Assist tab is displayed by default in the Insights panel. SQL Assist transforms the way users interact with databases by leveraging conversational AI to streamline SQL query creation and optimization. This intelligent service interprets your instructions and database schemas to generate contextually relevant SQL queries and explanations.

Refer to SQL Assist for more information.

History Tab

Click the History tab in the Insights panel to see query execution history and its results, query modification changes, copy query functionality to copy previous queries to reuse or modify existing SQL statements.

When a script tab in the SQL Editor is closed, the history logs associated with that tab are cleared. All remaining logs are automatically cleared when the session ends.

The History tab displays the following details:

Field Name	Description
Index	Sequential number indicating the order of query execution or modification.
Time	Timestamp showing when the query was executed/modified.
Type	SQL: Denotes the script execution. Auto: Denotes execution of an AI-modified script.
Query	The actual SQL statement that was executed or modified to.
Duration (ms)	Time taken to execute the query, in milliseconds.
Rows	Number of rows returned by the query.
Result	Status of the query execution. For example, Success, Error.
Log Message	Message returned to the console after each query execution.
Copy	Option to copy the executed query to the clipboard.