Skip to main content

Set Session Parameters in Snowflake Using Coalesce

With Snowflake, parameters can be set that change the behavior of a given session. This is completed via their ALTER SESSION command. These parameters can be set in Coalesce to influence the behavior of the data pipelines you build, deploy, and refresh in the Coalesce platform.

Coalesce does not utilize a single session to execute all Nodes within a pipeline, so a specific technique must be used to set session parameters in Coalesce and have them apply throughout your pipeline. This technique uses pre-SQL on each of your Nodes to set the session parameters, ensuring that no matter how many sessions are initiated during the course of running your data pipeline, the session parameters will be set before a Node is ever executed.

There are two approaches to implementing this technique:

  • Include the SQL to Set the Snowflake Parameters in the Pre-SQL of Each Node.
  • Create a Custom Node Type that Includes the SQL to Set the Snowflake Parameters in Pre-SQL.
  • Use Coalesce Parameters to define the values for Snowflake parameters.

This article details the steps to implement each of these approaches as well as how to use Coalesce parameters to define your Snowflake parameter values.

Include the SQL to Set the Snowflake Parameters in the Pre-SQL of Each Node​

With this approach, the developer will manually include the SQL to set the Snowflake parameters in the pre-SQL area of the Node Config of each Node in the pipeline.

  1. In the Node Editor, expand the Options area of the Node Config.

    This image shows a configuration screen for a table named `FCT_CUSTOMERS`. The left side displays columns with details like column name, transformation, data type, and source, while the right side has options for creating the table, setting a business key, enabling tests, and adding pre-SQL and post-SQL statements.

  2. In the Pre-SQL area, enter the SQL to set your Snowflake parameters using Snowflake's ALTER SESSION command. Note, Coalesce expects a single SQL statement in the Pre-SQL box. If you have multiple parameters to set, we recommend setting them with a single ALTER SESSION command. For example:

    ALTER SESSION 
    SET QUOTED_IDENTIFIERS_IGNORE_CASE = True
    QUERY_TAG = 'Coalesce';
    This image shows configuration screen for the `FCT_CUSTOMERS` table, with the `Pre-SQL` section on the right side, which contains SQL commands to alter the session, set quoted identifiers to ignore case, and set a query tag to `'Coalesce'`. The configuration options for the table creation and business key settings remain visible on the right panel.

  3. Create the Node if you have not already created it, and then Run the node.

  4. In the Run Results, notice the SQL has been executed to set your parameters before the SQL to refresh your Node.

This image shows the same `FCT_CUSTOMERS` table configuration, with the `Pre-SQL` section expanded at the bottom to display the execution result. The SQL commands `ALTER SESSION`, `SET QUOTED_IDENTIFIERS_IGNORE_CASE = True`, and `QUERY_TAG = 'Coalesce'` were successfully executed, as indicated by the status message and the execution time of 403ms.

Create a Custom Node Type that Includes the SQL to Set the Snowflake Parameters in Pre-SQL​

With this approach, a Custom Node Type that will be created that includes the SQL to set the Snowflake parameters in the pre-SQL stage in the Run Template so this SQL is automatically included and executed for any Node created of that Node Type. This removes the requirement for the SQL to set the Snowflake parameters to be manually added to each Node. This streamlines your development efforts while reducing maintenance and injecting consistency since the logic is defined in a single place and reused for all Nodes of the Node Type.

  1. In the Build Settings of your Workspace, navigate to Node Types.

  2. Duplicate an existing Node Type that you are looking to add the SQL to set the Snowflake parameters to. If you want this behavior to apply to all Node Types in your pipeline, you will need to complete this and all following steps for all existing Node Types in your Build Settings.

  3. Edit the Node Type you just created and complete the following:

    1. Rename accordingly.
    2. In the Run Template, locate the existing pre-SQL area and modify to include your logic to set the Snowflake parameters using the approach detailed below. By including the logic in a separate {{ stage('Pre-SQL') }} area, end users are still able to input additional pre-SQL statements in their individual Nodes and have them execute in addition to the pre-SQL to set the Snowflake parameters.
    {% if config.preSQL %}
    {{ stage('Pre-SQL') }}
    ALTER SESSION
    SET QUOTED_IDENTIFIERS_IGNORE_CASE = True
    QUERY_TAG = 'Coalesce';
    {{ stage('Pre-SQL') }}
    {{ config.preSQL }}
    {% endif %}
    This image shows a YAML configuration file for the `FCT_CUSTOMERS` table with a specific focus on the `Run Template` section. The highlighted code includes a conditional block that checks if the `node.materializationType` is set to `'table'` and then executes a `Pre-SQL` stage that alters the session, sets the quoted identifiers to ignore case, and assigns a query tag `'Coalesce'`.

  4. In your Browser, edit each Node this is applicable to and change each to this Node Type in the Node Properties area of the Node Config.

    This image shows the `FCT_CUSTOMERS` table configuration screen with focus on the Node Properties panel on the right. The Node Type is set to Fact w Snowflake Parameters, and the description field contains text indicating that the customer data is defined by TPC-H, with the option to enable deployment toggled on.

  5. Create the Node if you have not already created it, and then Run the node.

  6. In the Run Results, notice the SQL has been executed to set your parameters before the SQL to refresh your Node.

Use Coalesce Parameters to Define the Values for Snowflake Parameters​

With Coalesce parameters, you are able to define the values for the Snowflake parameters once and then reuse them throughout your pipeline vs. hard coding their values in each Node or Node Type template. This streamlines your development efforts while reducing maintenance and injecting consistency.

  1. In your Workspace and/or Environment Settings, navigate to Parameters.
  2. Define your Coalesce parameters and their default values for each Snowflake parameter you wish to set and save. This must be completed in valid JSON format.
  3. In your SQL logic to set your Snowflake parameters, reference the Coalesce parameter to define the value instead of hard-coding the value.
ALTER SESSION
SET QUOTED_IDENTIFIERS_IGNORE_CASE = {{parameters.QUOTED_IDENTIFIERS_IGNORE_CASE}} 
QUERY_TAG = {{parameters.QUERY_TAG}};
Default Values

Default values for Coalesce parameters can be overwritten on deploy and/or refresh if required.