Skip to main content

Use Coalesce to Store, Deploy, and Call Stored Procedures

    Overview​

    You are able to store, deploy, and call Snowflake Stored Procedures using Coalesce. This is accomplished via a custom Node Type (aka User Defined Node or UDN), in which you create and store your Stored Procedure SQL. You are also able to call Stored Procedures, both those created and stored in Coalesce as well as those created directly in Snowflake, when refreshing your data pipeline.

    This guide details the steps to accomplish this in Coalesce, as well as provides the code for the custom Node Type used in this technique.

    Video Demo​

    Creating Stored Procedures in Coalesce​

    1. Load the custom Node Type: Start by loading the custom Node Type, CREATE SQL (see below), into the desired Project & Workspace in your Coalesce organization. This custom Node Type takes a user-defined chunk of SQL and executes it during Coalesce's Create / Deploy process; in this case, the SQL provided will be the create SQL for the Stored Procedure. For information on how to load this custom node type into your Coalesce organization, see the article: .
    2. Create & define the Stored Procedure:
      • Create a New Node: To begin, in the Browser of your Workspace, create a new Node using the CREATE SQL Node Type you loaded in Step 1.
      • Name the Node: Name the Node after the Stored Procedure you wish to create.
      • Set the Storage Location: Choose a Storage Location for the Node. This reflects where the Stored Procedure will be created in Snowflake.
      • Define the DDL: Fill in the Data Definition Language (DDL) for the Stored Procedure; instead of using a fully qualified name, it is recommended to use an alias in Coalesce for better flexibility.
    3. Validate your in your Workspace: Once you have defined the Stored Procedure in your Workspace, you should execute and validate it to ensure it is configured as required.

    Custom Node Type Code​

    {% if config.SQL1 %}
    {{ stage('SQL1') }}
    {{ config.SQL1 }}
    {% endif %}

    Deploying Stored Procedures in Coalesce​

    • Committing to Git: After creating the Stored Procedure, commit it to your Git repository. Label the commit and push it to your desired branch.
    • Deployment to Upstream Environment: Choose the Environment for deployment. Initiate the deployment from the chosen branch in Git. The deployment process will automatically create the Stored Procedure in the selected Environment.

    Calling Stored Procedures in Coalesce​

    Stored procedures can be called within a Coalesce pipeline, regardless of whether they were created in and by Coalesce or in Snowflake directly.

    In Coalesce, a Stored Procedure can be called:

    • As a piece of pre-SQL or post-SQL within a related Node.
      • CALL my_sp('param_one', 'param_two', 'param_three');
    • As a independent Node; this requires adding a Run Template to the custom Node Type detailed above to facilitate calling the Stored Procedure within a pipeline Run / Refresh.
    Tags: