Skip to main content
Sign in

How-To: Set Session Parameters in Snowflake Using Coalesce

  • Updated

Overview

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 via 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:

In addition, you are able to 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.
  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';

  1. Create the Node if you have not already created it, and then Run the node.
  2. In the Run Results, notice the SQL has been executed to set your parameters before the SQL to refresh your Node.

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 %}

  1. 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.
  2. Create the Node if you have not already created it, and then Run the node.
  3. In the Run Results, notice the SQL has been executed to set your parameters before the SQL to refresh your Node.mceclip10.pngmceclip11.png

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. hardcoding 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. Note: default values for Coalesce parameters can be overwritten on deploy and/or refresh if required.
  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}};

Additional Information