> ## Documentation Index
> Fetch the complete documentation index at: https://docs.maia.ai/llms.txt
> Use this file to discover all available pages before exploring further.

# SQL Script

export const designer = "Designer";

export const ComponentMetadata = ({warehouses, unsupportedWarehouses = [], componentType, connectionInputs, connectionOutputs}) => {
  const allWarehouses = [...warehouses.map(w => ({
    name: w,
    supported: true
  })), ...unsupportedWarehouses.map(w => ({
    name: w,
    supported: false
  }))];
  return <div style={{
    background: 'var(--colors-background-light, #f9fafb)',
    border: '1px solid var(--colors-border-default, #e5e7eb)',
    borderRadius: '12px',
    padding: '20px 28px',
    marginBottom: '28px',
    boxShadow: '0 1px 4px rgba(0,0,0,0.10)'
  }}>
      <table style={{
    width: '100%',
    borderCollapse: 'collapse'
  }}>
        <tbody>
          <tr>
            <td style={{
    fontWeight: '600',
    paddingRight: '32px',
    paddingBottom: '14px',
    whiteSpace: 'nowrap',
    verticalAlign: 'middle',
    width: '180px'
  }}>Project Availability</td>
            <td style={{
    paddingBottom: '14px',
    verticalAlign: 'middle'
  }}>
              <div style={{
    display: 'flex',
    flexWrap: 'wrap',
    gap: '8px'
  }}>
                {allWarehouses.map((w, i) => <span key={i} style={{
    background: w.supported ? '#dcfce7' : '#fee2e2',
    color: w.supported ? '#15803d' : '#b91c1c',
    border: `1px solid ${w.supported ? '#bbf7d0' : '#fca5a5'}`,
    borderRadius: '9999px',
    padding: '3px 12px',
    fontSize: '0.85rem',
    fontWeight: '500',
    whiteSpace: 'nowrap'
  }}>
                    {w.name} {w.supported ? '✅' : '❌'}
                  </span>)}
              </div>
            </td>
          </tr>
          <tr>
            <td style={{
    fontWeight: '600',
    paddingRight: '32px',
    paddingBottom: '14px',
    whiteSpace: 'nowrap',
    verticalAlign: 'middle'
  }}>Component Type</td>
            <td style={{
    paddingBottom: '14px',
    verticalAlign: 'middle'
  }}>{componentType}</td>
          </tr>
          <tr>
            <td style={{
    fontWeight: '600',
    paddingRight: '32px',
    paddingBottom: '14px',
    whiteSpace: 'nowrap',
    verticalAlign: 'middle'
  }}>Connection Inputs</td>
            <td style={{
    paddingBottom: '14px',
    verticalAlign: 'middle'
  }}>{connectionInputs}</td>
          </tr>
          <tr>
            <td style={{
    fontWeight: '600',
    paddingRight: '32px',
    whiteSpace: 'nowrap',
    verticalAlign: 'middle'
  }}>Connection Outputs</td>
            <td style={{
    verticalAlign: 'middle'
  }}>{connectionOutputs}</td>
          </tr>
        </tbody>
      </table>
    </div>;
};

<ComponentMetadata warehouses={["Snowflake", "Databricks", "Amazon Redshift", "Google BigQuery"]} componentType="Orchestration, Test" connectionInputs="One" connectionOutputs="Unlimited" />

The SQL Script component supports both Data Definition Language (DDL) and Data Manipulation Language (DML) statements. Use it to run statements such as `CREATE`, `ALTER`, and `DROP` (DDL), or `INSERT`, `UPDATE`, `DELETE`, and `MERGE` (DML) against your warehouse.

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](/docs/components/sql) 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.

{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 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 statements in the **SQL script** field.

### Use cases

This component is versatile and can be used to run DDL and DML operations directly against your warehouse. For example, you can use it to:

* Create, alter, or drop database objects such as tables, views, and schemas.
* Insert, update, delete, or merge records in your warehouse tables.
* Create Snowflake stored procedures to encapsulate reusable logic.

***

## Variables

This component supports the use of pipeline and project variables. For more information, read [Project and pipeline variables](/docs/guides/variables).

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

Additionally, native variable syntax is supported for each warehouse in its respective projects:

* **Snowflake:** [Snowflake variable](https://docs.snowflake.com/en/sql-reference/session-variables) syntax.
* **Databricks:** [Databricks variable](https://docs.databricks.com/en/sql/language-manual/sql-ref-variables.html) syntax.
* **Google BigQuery:** [BigQuery variable](https://docs.cloud.google.com/bigquery/docs/reference/standard-sql/procedural-language#declare) syntax.

`${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](/docs/guides/code-editor).

Snowflake/Databricks/BigQuery 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](/docs/guides/code-editor). Use the `Script location` property to choose where to reference your SQL statements from.

Read [Using variables in Code Editor](/docs/guides/code-editor#using-variables-in-the-code-editor) to learn more.

***

## Properties

<ResponseField name="Name" type="string" required>
  A human-readable name for the component.
</ResponseField>

{/* <!-- param-start:[scriptLocation] | warehouses: [snowflake, databricks, redshift, bigquery] --> */}

<ResponseField name="Script location" type="drop-down" required>
  * **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.
</ResponseField>

{/* <!-- param-start:[declareSqlVariables] | warehouses: [snowflake, databricks, bigquery] --> */}

<ResponseField name="Declare SQL variables" type="drop-down">
  Select a strategy for declaring project and pipeline [Project and pipeline variables](/docs/guides/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.

  <Warning>
    Not currently available for Amazon Redshift projects.
  </Warning>
</ResponseField>

{/* <!-- param-start:[variablesToInclude] | warehouses: [snowflake, databricks, bigquery] --> */}

<ResponseField name="Variables to include" type="dual listbox">
  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`.

  <Warning>
    Not currently available for Amazon Redshift projects.
  </Warning>
</ResponseField>

<ResponseField name="Allow inline variable replacement" type="drop-down">
  **No** is selected by default. We recommend leaving this set to **No**, and using native SQL variables. When set to **No**, you will need to use the `Declare SQL variables` property to declare variables in your SQL script as SQL variables.

  To resolve variables inline using `${variable_name}` syntax in your SQL script, select **Yes**. We recommend only selecting **Yes** if you already have scripts that use `${}` syntax and do not want to convert these. If you select **Yes**, we recommend making sure that your script is not vulnerable to injection attacks from the content of your `${}` expressions.
</ResponseField>

<ResponseField name="SQL script" type="code editor" required>
  Add your SQL script into the code editor. This script is stored in the component.
</ResponseField>

{/* <!-- param-start:[fileName] | warehouses: [snowflake, databricks, redshift, bigquery] --> */}

<ResponseField name="Select file" type="drop-down" required>
  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.
</ResponseField>

## Variable substitution in Databricks

Variable substitution won't work under the following conditions:

* The `Script location` parameter is set to **File**.
* You're using a SQL Warehouse (Classic or Serverless).

In this scenario, the `$(...)` variable substitution syntax (for example, `CREATE TABLE $(schema)`) doesn't function as expected.

<Note>
  * This limitation doesn't affect procedural logic (e.g. `DECLARE my_var = 123; CREATE TABLE my_var`).
  * This limitation doesn't apply when using an all-purpose compute cluster. However, make sure the variable is enclosed in backticks to prevent potential errors (e.g. `` `${var}` ``).
  * Snowflake and Amazon Redshift aren't affected.
</Note>

Workarounds:

In the `File location` parameter, use the drop-down to select **File** with an all-purpose compute cluster, or select **Component**, and place your SQL directly in the `SQL script` parameter.

***

## Creating Snowflake stored procedures

Since `CREATE PROCEDURE` is a DDL statement, there is no dedicated low-code component for creating stored procedures. The SQL Script component is the appropriate tool for this operation. Snowflake supports stored procedures written in SQL, JavaScript, Python, Java, and Scala.

Use the following SQL to create a stored procedure:

```sql theme={null}
CREATE OR REPLACE PROCEDURE <schema_name>.<procedure_name>(<parameter_name> <data_type>)
RETURNS VARCHAR
LANGUAGE SQL
AS
$$
BEGIN
    -- Your procedure logic here
    RETURN 'Done';
END;
$$;
```

To call the stored procedure, use a separate SQL Script component:

```sql theme={null}
CALL <schema_name>.<procedure_name>('<argument>');
```

<Note>
  Each query in an SQL Script component may execute on a different connection. Create and call stored procedures in separate components to avoid session-related issues.
</Note>