SQL Script

The SQL script can contain multiple SQL statements. However, since there is no way to recover the output of the script, the statements should not be SELECT. If you wish to write SELECT queries, the SQL transformation component allows for custom SELECT statements where the output can be used as part of the transformation flow.

There is no guarantee that multiple SQL statements will always run in the same session.

It is advised to avoid using transaction control statements such as commit or rollback in your scripts.

The Designer executes each query within an SQL Script component using a connection from a connections pool, meaning that each query may be executed by a different connection. When connections in the pool are first created, they will use the Snowflake warehouse that you have defined as the default in the environment configuration.

Designer automatically saves your SQL statements as you work. You can sample data and view variables while writing your SQL statments in the SQL Script field.

Variables

This component supports the use of pipeline and project variables. For more information, read Variables.

For any code written inside the SQL Script orchestration component, the variable syntax ${variable} is supported.

Additionally, Snowflake variable syntax and Databricks variable syntax are supported for Snowflake and Databricks projects, respectively.

${variable} syntax is supported for SQL statements written within the SQL Script component (i.e. when Script Location is set to Component). This syntax is not supported in .sql files in Code Editor.
Snowflake/Databricks variable syntax is supported regardless of whether you write your SQL statements directly in the SQL Script component or in a .sql file using Code Editor. Use the Script Location property to choose where to reference your SQL statements from.

Read Using variables in Code Editor to learn more.

Properties

Script Location = drop-down

Component: Use the SQL Script property to add your SQL statements to this component. This is the default setting.
File: Use the Select File property to choose a .sql file in your project. The drop-down will list all .sql files from the repository that is connected to your project.

Declare SQL Variables = drop-down (optional)

Select a strategy for declaring project and pipeline variables as SQL variables. Prior to the component executing, no project or pipeline variables are declared as SQL variables for SQL scripts.

Include selected: Choose which project and pipeline variables to declare as SQL variables. This is the default setting.
Include all: Declare all project and pipeline variables as SQL variables.

Not available on Amazon Redshift

Not currently available for Amazon Redshift projects.

Variables To Include = dual listbox (optional)

To include a project or pipeline variable in your SQL script and thus declare it as an SQL variable, move it from the Variables To Include column to the Select Variables to Include column (left to right).

Only available when Declare SQL Variables is set to Include selected.

Not available on Amazon Redshift

Not currently available for Amazon Redshift projects.

SQL Script = code editor

Add your SQL script into the code editor. This script is stored in the component.

Select File = drop-down

Choose a .sql file in your project to run via this component. The drop-down will list all .sql files from the repository that is connected to your project.

Double-click a .sql file in the Files panel to open that .sql file in the code editor.

Snowflake	Databricks	Amazon Redshift
✅	✅	✅