Snowflake for Workflows is currently in preview release and is available to selected customers only. If you would like to share feedback or ideas, join the preview by signing up via the Request access to Snowflake Workflow Connector form or contacting your Customer Success Manager.
For more information, see preview releases.
After you join the preview program for Snowflake for Workflows, we'll provide Hub subscription details. With those subscription details, follow the Add Hub subscriptions procedure to you activate the capability.
Execute a SQL statement and store the result in Grail with the Snowflake for Workflows actions.
Action
Description
Execute statement
It executes a single SQL statement on your Snowflake database.
Store Statement Result
It executes the provided statement and then stores its result directly in Grail without presenting them as the action's result.
The Execute Statement action allows you to execute a single SQL statement on your Snowflake database. It returns the statement's result as the action's result, including data and header information. The action will only return the first partition of the data returned by Snowflake, to avoid reaching the maximum result size of workflow actions. We recommend using this action to execute statements with smaller results.
To execute a single SQL statement in your workflow, you need to provide the information listed below.
| Field | Description | Required |
|---|---|---|
| Connection | A preconfigured Snowflake connection. | Required |
| Query | The SQL statement. | Required |
| Binding parameters | Binding parameters for the statement. For more information, see Dynamic statements section. | Optional |
| Database | The default database used by the statement. | Optional |
| Schema | The default database schema used by the statement. | Optional |
| Snowflake warehouse | The Snowflake warehouse used to execute the statement. | Optional |
| Database role | The Snowflake role used to execute the statement. | Optional |
| Timeout (in seconds) | The maximum execution time for the statement on Snowflake. | Optional |
The Execute Statement action result is the direct response from executing the Snowflake REST API. For more information, see Snowflake ResultSet.
The most important fields are:
Property
Description
resultSetMetaData
Contains the meta-information about the data, including header names and types.
data
An array containing rows with the data results from executing the statement. Each row contains columns represented as a list of values.
This result can be used as input for other workflow actions via workflow expressions.
The result of the action can contain sensitive data. If you need to work with data that not everybody should see, consider making the workflow private.
The recommended way of making dynamic statements is to use Snowflake bind variables.
The action supports both ? placeholders and named parameters.
? placeholder, the parameter is named according to the order in which it appears: the first ? is named 1, the second is named 2, and so on.The Store Statement Result action executes the provided statement and then stores its result directly in Grail without presenting the Snowflake response as the action's result. This action loads all data partitions returned by Snowflake.
Storing the data in Grail might introduce additional costs, depending on how many records and how many columns were ingested. You can find more information about related costs on the Events powered by Grail page.
There are a couple of ways to store the data in Grail:
To store the statement result in Grail using your workflow, you need to provide the information listed below.
| Field | Description | Required |
|---|---|---|
| Connection | A preconfigured Snowflake connection. | Required |
| Query | The SQL statement. | Required |
| Binding parameters | Binding parameters for the statement. For more information, see Dynamic statements section. | Optional |
| Database | The default database used by the statement. | Optional |
| Schema | The default database schema used by the statement. | Optional |
| Snowflake warehouse | The Snowflake warehouse used to execute the statement. | Optional |
| Database role | The Snowflake role used to execute the statement. | Optional |
| Timeout (in seconds) | The maximum execution time for the statement on Snowflake. | Optional |
| Grail storage type | The way of storing the data in Grail. By default, it is Business events. Additional options are displayed depending on the storage type you select. For more information, see Store the result as bizevents and Store the result as lookup data. | Optional |
The field names in Grail are based on the Snowflake column names, and all data types are automatically converted to Grail-supported types.
All date and time columns are presented as timestamp strings with a timezone, with nanosecond resolution (for example, 2024-05-14T11:40:33.952000000+02:00).
When column names are long, duplicated, or contain whitespaces or special characters, the action generates a new custom column name.
You can also manually define the generated name by adding as <Column name> to each relevant column in your SQL statement, replacing the placeholder with a Grail-supported column name.
When selecting the bizevents Grail storage type, additional parameters become available.
| Field | Description | Required |
|---|---|---|
| Event type | The value of the event.type field is added to every bizevent. By default: snowflake_connector. | Optional |
After executing the action, you can find Snowflake data stored in Grail as bizevents.
Each bizevent contains additional metadata fields, that can't be overwritten:
requestId (which includes the unique UUID string generated for the execution)event.providerevent.type.The action produces a result that summarizes the outcome of what was done.
Property
Description
count
The number of bizevents that were ingested
chunks
Diagnostic information on the number of chunks of bizevents was created to avoid loading them all at once.
eventMetadata
A JSON object containing fields (requestId, event.type, event.provider) that were added to every ingested bizevent. The requestId is unique for each execution and can be used to search for bizevents or to find related logs on Snowflake.
Using the information from the result, the data can be queried as follows:
fetch bizevents| filter event.type == "snowflake_connector" // The value from the eventMetadata["event.type"] field| filter requestId == "some-random-request-id-for-this-specific-execution" // The value from the eventMetadata["requestId"] field
When selecting the lookup table Grail storage type, you need to provide additional parameters related to the lookup data. For more information on storage limits and additional details about parameters, see the lookup data documentation.
| Field | Description | Required |
|---|---|---|
| Table file path | The unique path where the table is saved. | Required |
| Lookup field | The column name that will contain unique values for the lookup table. Duplicates are removed from the table. | Required |
| Overwrite table | If not selected and the table already exists in the environment, the action fails with an error. | Optional |
| Table display name | A user-friendly name for the created file. | Optional |
| Table description | An additional description for the created file. | Optional |
After the action finishes, the Snowflake result is stored in Grail as a lookup table. The action produces a result that summarizes the outcome of what was done.
Property
Description
count
The number of records returned by Snowflake.
It can differ from the number of records stored in the lookup table if there are any records with duplicated lookupField values.
table
The file path for the created lookup table. Can be used within the load DQL command.
lookupField
The name of the Snowflake column that was used as a lookup field.
Using the information from the result, you can query the data as follows:
load "/lookups/my/path" // The file path from the `table` field