👩‍💻Native SQL: SQL / Code mode and simple mode

Only for connectors which support nativeSQL

When working with a datasource supporting NativeSQL, you have two modes for writing your queries.

After connecting to your database, when you click on "create a dataset from datasource", you land on a new page where, on the right, you have three modals: "Configuration", "Query", and "Review" which will lead you to the creation of a dataset.

Query configuration mode

If your workspace is fueled by our New Data Execution System, you have access to a fully redesigned form, refer to this part

Simple mode

In Simple mode, (which is the default mode for all connectors which support NativeSQL), you can query your database without the hassle of writing a SQL query, and look at your database model constantly to avoid errors.

After choosing the database you want to query and clicking on "Validate configuration", you switch to the "Query" tab

Edit datasource - configuration

you switch to the "Query" tab where you can select the table to query

Edit datasource - Query - Table selection

You can navigate through the database schema to choose the table you want to query.

Edit datasource - Query - Table selection

Then you can choose the columns you want to keep in your dataset by clicking/un-clicking on the tick box.

You can choose to select all columns by clicking on "Select all" or unselect all columns by clicking on "Clear All".

You can click on the "Preview" button to display on the right the columns of your selection.

Edit datasource - Query - Table selection -> Columns selection

Once you are satisfied with your query, you can click on the "Apply Data Selection" button. You are redirected to the "Review" tab, where you can review the query you will send to your database.

On the right of each field, you have an Edit icon for each field which will redirect you to the designated section when clicked.

Edit datasource - Review

By clicking on the "Preview" button, you can view the data.

By clicking on "Save query" you will be redirected to the dataset creation where you will be able to apply data transformation.

New dataset from source

Code/SQL mode

In the configure datasource interface, once you have chosen the database in the configuration tab, in the query mode, at the bottom right, there's an icon (burger menu). You can click on the message "Switch to code mode" that is displayed.

PostgreSQL - Edit datasource - Query -Columns selection - switch to code mode

You can switch to code mode since table selection and after: And the current SQL request will be displayed. You can insert a variable using the '/' key or by clicking on insert a variable at the top right of the input box. Learn more about variables in the dedicated section.

Edit datasource - Query - Columns selection - code mode

This field is a playground SQL where you can write the query you desire to get data from your database. You can erase the current query to write your own.

Edit datasource - Query -Columns selection - code mode

New query configuration form

If the new Data Execution System is activated on your workspace and your connector is nativeSQL compatible, A new query configuration form has been designed.

Check the latest release note to see which data connectors are supported and nativeSQL compatibles.

Simple mode

In Simple Mode, users can build queries without writing SQL manually (the query sent is in the following form SELECT [selected columns] FROM [schema].[selected table] LIMIT 400). This mode provides a guided interface to select databases, tables, and columns.

• Workflow:

  1. Select a Database from a list (or use the default variable if dynamic selection is enabled).

  2. Browse and choose a Table; all tables available in the selected database schemas are displayed.

  3. Select or deselect individual Columns that should appear in the query output.

  4. Execute the query against the selected database through the Preview button

  5. Or / then click on Next button to pass to the YouPrep

Selection through a Fixed Database

• Users explicitly choose a database from a dropdown list.

• Once chosen, the relevant tables from that database are listed for selection.

• The query will always target this fixed database.

Selection through a Dynamic Database

• A variable is used to define the target database.

• A default value for the variable is provided, so Simple Mode can still list the available tables and columns.

• The actual query execution adapts based on the variable's runtime value.

Code/SQL Mode

Workflow:

1. Select a Database from a dropdown list (Fixed database) or via a variable(Dynamic database).

2. Use the SQL editor input box, where you can enter multiline queries.

3. Execute the query against the selected database through a preview button

4. Then click on Preview button to have a preview of your query, the query is sent with LIMIT 400 for performance purposes

5. Then/or click on Next button to pass to the YouPrep

Fixed Database

• The user chooses a database explicitly from a fixed list.

• SQL queries written in the editor will always target this database.

Dynamic Database

• The database is chosen through a variable.

• The editor displays the tables and autocompletion based on the variable’s default database, but queries execute against whichever database is resolved at runtime.

Code/SQL mode: with dynamic database

---

Explore Database

The Database Explorer provides navigation and exploration tools for users to better understand their database structure and build queries more efficiently.

• Users can browse multiple schemas and tables within the selected database.

• The explorer helps in quickly referencing table names, column structures, and relationships.

• This feature is especially useful in Code/SQL Mode to assist in building complex queries manually.

code SQL mode with one table selected

code SQL mode with multiple tables selected