# snowflake>: Execute SQL statements on Snowflake

The **snowflake>** operator executes SQL statements on Snowflake.


```
_export:
  snowflake:
    account_identifier: MYORGANIZATION-MYACCOUNT
    user: MY_USER
    database: MY_DATABASE

+statement_from_file:
  snowflake>: my_statement.sql

+statement_with_bindings:
  snowflake>:
  statement: select * from my_table where column1 = :my_string and column2 = :my_number
  bindings:
    my_string: some_value
    my_number: 123

+multiple_statements:
  snowflake>:
  statement: |
    insert into my_table (column1, column2) values ('my_value', 100);
    update my_table set column2 = column2 + 1 where column1 = 'my_value';

+store_results:
  snowflake>:
  statement: select count(*) as "total_count" from my_table
  store_last_results: true

+print_total_count:
  echo>: The total count is ${snowflake.last_results.total_count}
```

## Secrets

The operator supports two authentication methods: [key pair authentication](https://docs.snowflake.com/en/user-guide/key-pair-auth) and [programmatic access token authentication](https://docs.snowflake.com/en/user-guide/programmatic-access-tokens).

You must configure secrets for one of these authentication methods based on the `authentication_method` parameter (defaults to `key_pair`).

### Key Pair Authentication (Default)

* **snowflake.private_key**: STRING
Required when using key pair authentication.
The private key in PEM format.
Only RSA keys are supported.
* **snowflake.private_key_password**: STRING
Optional.
The password for the private key if it is encrypted.


### Programmatic Access Token Authentication

* **snowflake.programmatic_access_token**: STRING
Required when using programmatic access token authentication.
The programmatic access token.


## Options

* **snowflake>**: FILE
Path to a SQL file containing the SQL statement(s) to execute.
The file can contain `${...}` syntax to embed variables.
Examples:

```
snowflake>: my_statement.sql
```
* **statement**: STRING
The SQL statement(s) to execute.
Use this parameter to specify the SQL statements inline instead of using a file.
This parameter can contain `${...}` syntax to embed variables.
Examples:

```
statement: select current_version()
```

```
statement: |
  insert into my_table (column1, column2) values ('my_value', 100);
  update my_table set column2 = column2 + 1 where column1 = 'my_value';
```
* **account_identifier**: STRING
Required. The Snowflake [account identifier](https://docs.snowflake.com/en/user-guide/admin-account-identifier).
Examples:

```
account_identifier: MYORGANIZATION-MYACCOUNT
```
* **authentication_method**: key_pair | programmatic_access_token
The authentication method to use.
*Default*: `key_pair`.
Examples:

```
authentication_method: key_pair
```

```
authentication_method: programmatic_access_token
```
* **user**: STRING
Required when using key pair authentication.
The Snowflake user to authenticate as.
Examples:

```
user: MY_USER
```
* **database**: STRING
The Snowflake database in which to execute the SQL statement.
Examples:

```
database: MY_DATABASE
```
* **schema**: STRING
The Snowflake schema in which to execute the SQL statement.
Examples:

```
schema: MY_SCHEMA
```
* **warehouse**: STRING
The Snowflake warehouse to use for executing the SQL statement.
Examples:

```
warehouse: MY_WAREHOUSE
```
* **role**: STRING
The Snowflake role to use when executing the SQL statement.
Examples:

```
role: MY_ROLE
```
* **bindings**: ARRAY or OBJECT
The values for [bind variables](https://docs.snowflake.com/en/sql-reference/bind-variables) in the SQL statement.
For positional bind variables (`?`), provide an array of values in the order they appear in the SQL statement.
For named bind variables (`:name`), provide an object with variable names as keys (without the colon prefix) and the corresponding values.
**Note:** Bind variables are not supported when executing multiple SQL statements.
Examples:

```
bindings:
  - some_value
  - 123
```

```
bindings:
  my_string: some_value
  my_number: 123
```
* **query_tag**: STRING
The [query tag](https://docs.snowflake.com/en/sql-reference/parameters#label-query-tag) to associate with the SQL statement.
Examples:

```
query_tag: my-query-tag
```
* **timezone**: STRING
The timezone to use when executing the SQL statement.
Examples:

```
timezone: America/Los_Angeles
```
* **store_last_results**: BOOLEAN
Whether to store the results of the SQL statement in the `snowflake.last_results` output parameter.
*Default*: `false`.
When set to `true`, the first row of the result set is stored in the `snowflake.last_results` output parameter as an object, with column names as keys.
When multiple statements are executed, only the results of the last statement are stored.
Date, time, and timestamp values are formatted using the `YYYY-MM-DD`, `HH24:MI:SS`, and `YYYY-MM-DD HH24:MI:SS.FF3[ TZHTZM]` formats, respectively.
Binary values are encoded as hexadecimal strings.
Examples:

```
store_last_results: true
```
* **_timeout**: DURATION
The maximum time to wait for the task to complete before timing out.
If the timeout is reached, statement execution will be canceled.
Examples:

```
_timeout: 30m
```


## Output parameters

* **snowflake.last_results**
The first row of the statement execution results as an object.
This is only set when `store_last_results: true` is specified.
For example, if the statement `select count(*) as "total_count" from my_table` returns a single row with a column named `total_count`, you can access it as `${snowflake.last_results.total_count}`.
* **snowflake.last_statement.handle**
The handle of the executed statement.
If multiple statements are executed, the handle of the last statement is stored.
Example: `01bf6433-0106-8cd2-0070-6287007a936a`
* **snowflake.last_statement.num_records**
The number of rows returned by the executed statement.
If multiple statements are executed, the number of rows returned by the last statement is stored.
Example: `1`


## Limitations

The operator uses the [Snowflake SQL API](https://docs.snowflake.com/en/developer-guide/sql-api/index), which has certain limitations:

* The [GET](https://docs.snowflake.com/en/sql-reference/sql/get) and [PUT](https://docs.snowflake.com/en/sql-reference/sql/put) commands are not supported.
* Certain types of stored procedures are not supported.


See the [official documentation](https://docs.snowflake.com/en/developer-guide/sql-api/intro#label-sql-api-limitations) for more details on the limitations of the Snowflake SQL API.