Snowflake package

The Snowflake package contains actions that enable you to automate various Snowflake operations such as database management and executing SQL statements and stored procedures.

Overview

The Snowflake package actions can be used for automating workflows such as row management in Snowflake database tables using SQL statements. The package also contains actions for executing SQL statements and stored procedures. The Snowflake package uses Microsoft Entra based OAuth connection for authenticating the connection between the Control Room and Snowflake platform.

Authentication

The Snowflake package uses OAuth-based authentication and supports both Snowflake OAuth and External OAuth. The External OAuth method supports Microsoft Entra, Okta, and PingFederate.

The following section illustrates Snowflake authentication using Microsoft Entra. The OAuth connection requires an enterprise application for Microsoft Entra tenant in the Microsoft Azure. This application serves as the identity manager to authenticate the connection between the Control Room and Snowflake. After you set up an application in the Microsoft Azure portal, use the client ID and client secret to set up the OAuth connection. For more information about setting up OAuth connections, see Create OAuth connection.

Note: Use api://<AZURE_APP_CLIENT_ID>/session:role-any as the scope when setting up OAuth connection. Replace <AZURE_APP_CLIENT_ID> with your Microsoft Azure Client ID.

The Snowflake package requires a supporting configuration to authenticate an OAuth connection from the Control Room through Microsoft Entra ID. This configuration allows the Control Room user to connect to Snowflake and create, manipulate, and modify data. The configuration includes a dedicated OAuth role, user account mapping, additional privileges and security integration.

During the authentication process, Control Room obtains the OAuth token from Microsoft Entra and presents it to Snowflake. Snowflake validates the token based on the configuration and maps the Microsoft Entra preferred user name to the Snowflake login name.

Use the following Snowflake setup script template to create the required configuration to authenticate the connection to Snowflake through Microsoft Entra: 
-- 0. (Optional) Use a powerful role to run setup
USE ROLE ACCOUNTADMIN;

--------------------------------------------------
-- 1. Create Role
--------------------------------------------------
CREATE ROLE IF NOT EXISTS <OAUTH_ROLE_NAME>;

--------------------------------------------------
-- 2. Create User (MUST match Azure token claim)
--------------------------------------------------
CREATE USER IF NOT EXISTS <SNOWFLAKE_USER_NAME>
  LOGIN_NAME = '<azure_upn_or_preferred_username>'
  DISPLAY_NAME = '<Display Name>'
  DEFAULT_ROLE = <OAUTH_ROLE_NAME>
  MUST_CHANGE_PASSWORD = FALSE;

--------------------------------------------------
-- 3. Grant Role to User
--------------------------------------------------
GRANT ROLE <OAUTH_ROLE_NAME> TO USER <SNOWFLAKE_USER_NAME>;

--------------------------------------------------
-- 4. Grant Privileges to Role
--------------------------------------------------
GRANT USAGE ON DATABASE <DATABASE_NAME> TO ROLE <OAUTH_ROLE_NAME>;
GRANT USAGE ON SCHEMA <DATABASE_NAME>.<SCHEMA_NAME> TO ROLE <OAUTH_ROLE_NAME>;
-- Add further grants as needed, e.g.:
-- GRANT SELECT ON ALL TABLES IN SCHEMA <DATABASE_NAME>.<SCHEMA_NAME> TO ROLE <OAUTH_ROLE_NAME>;
-- GRANT INSERT, UPDATE ON ALL TABLES IN SCHEMA <DATABASE_NAME>.<SCHEMA_NAME> TO ROLE <OAUTH_ROLE_NAME>;

--------------------------------------------------
-- 5. Create OAuth Integration (Azure AD / Entra ID)
--------------------------------------------------
CREATE OR REPLACE SECURITY INTEGRATION <INTEGRATION_NAME>
  TYPE = EXTERNAL_OAUTH
  ENABLED = TRUE
  EXTERNAL_OAUTH_TYPE = AZURE
  EXTERNAL_OAUTH_ISSUER = 'https://login.microsoftonline.com/<AZURE_TENANT_ID>/v2.0'
  EXTERNAL_OAUTH_JWS_KEYS_URL = 'https://login.microsoftonline.com/<AZURE_TENANT_ID>/discovery/v2.0/keys'
  EXTERNAL_OAUTH_AUDIENCE_LIST = ('<AZURE_APP_CLIENT_ID>')
  EXTERNAL_OAUTH_TOKEN_USER_MAPPING_CLAIM = 'preferred_username'
  EXTERNAL_OAUTH_SNOWFLAKE_USER_MAPPING_ATTRIBUTE = 'LOGIN_NAME';

--------------------------------------------------
-- 6. (Optional) Enable ANY ROLE MODE
--------------------------------------------------
-- Allows the OAuth session to activate any role granted to the user,
-- not just DEFAULT_ROLE. Only enable if the connecting application
-- needs to switch roles dynamically.
ALTER SECURITY INTEGRATION <INTEGRATION_NAME>
  SET EXTERNAL_OAUTH_ANY_ROLE_MODE = 'ENABLE';

--------------------------------------------------
-- 7. Verify setup
--------------------------------------------------
DESC SECURITY INTEGRATION <INTEGRATION_NAME>;

Replace the placeholders with appropriate parameters and execute the script in Snowflake to create the configuration.

Actions in Snowflake package

Action Description
Connect action in Snowflake package Securely connects to Snowflake and authenticates the automation session.
Insert row action in Snowflake package Inserts one or more rows into a Snowflake database table using the insert SQL statement.
Select rows Retrieves the details of rows of a Snowflake database using the select SQL statement.
Update rows Updates one or more rows in a Snowflake database table.
Delete rows action in Snowflake package Deletes one or more rows in a Snowflake database table.
Execute DDL Runs a Data Definition Language (DDL) statement to create, alter, and drop database objects in Snowflake.
Execute stored procedure Runs a stored procedure that is available in Snowflake with specified input parameters.
Disconnect Disconnects and closes the automation session.