Setting Up the Snowflake SQL Tools

This guide will walk you through the steps to configure and authenticate the Snowflake SQL tools and use them in agents. The instructions below should be followed to add both the

1. Snowflake SQL Query Tool
2. Snowflake Metadata Retrieval Tool

Both tools are required to properly query your Snowflake Database.

Adding and Configuring the Snowflake SQL Query Tool

1. Add the Snowflake SQL Query Tool Navigate to the library and select the "Snowflake SQL Query" tool to add it to your project.

2. Provide your Snowflake credentials To add the Snowflake tool to your project, you will need the following details:

   • Account ID
   • User name
   • Database name
   • Warehouse name
   • Private key file

   These details can be retrieved from your Snowflake account. See the section below on how to create an account with least-priveledges to authenticate your Snowflake account.

3. Retrieve your Snowflake connection details from Snowflake In order to connect your Snowflake account, you will require your account identifier. Your account will be as follows: "ORGANIZATION-ACCOUNT", for example, "ODWKPWH.QZE19753". To obtain this ID:

   • Login to your Snowflake account
   • Retrieve your Account identifier as follows: Select your profile menu, select your acount, and user.

4. Setup Key-Pair Authentication via OpenSSL Key-Pair authentication is a required setup to obtain your Private Key file, which will be used to connect to your Snowflake instance.

• Follow instructions on Snowflake to set up your private key file: https://docs.snowflake.com/en/user-guide/key-pair-auth
• And upload your corresponding Public key file to Snowflake with ALTER USER example_user SET RSA_PUBLIC_KEY='MIIBIjANBgkqh...';
• Save your Private Key file securely

6. Provide Snowflake connection details to Tool
   To configure the tool, provide the values required, inluding your Account ID and Private Key file. Your Warehouse details can be found when logging into your Snowflake account.

Required Snowflake Permissions

To connect your Snowflake tools to the Airia platform, it is recommended that you use a User Account that has least-privilege permissions needed to work with your data.

The user account configured in the Airia tool needs a role with the following privilege:

• USAGE privilege on the target Warehouse.
• USAGE privilege on the target Database.
• USAGE privilege on the target Schema(s) in the database.
• SELECT privilege on the specific Tables in the schema(s) that you want to use the Tools to query Metadata for and run queries against

The user must also have set up key-pair authentication and have configured the Snowflake user with a public key file (see instructions above).

A Snowflake administrator (using a role like ACCOUNTADMIN or SECURITYADMIN) can use the following SQL template to create a dedicated read-only role and user with the necessary grants.

USE ROLE ACCOUNTADMIN; -- Or SECURITYADMIN

-- 1. Create a dedicated role for Airia tool access (if it doesn't exist)
CREATE ROLE IF NOT EXISTS TOOL_READ_ONLY_ROLE
    COMMENT = 'Role for read-only access for Airia tool access';

-- 2. Grant USAGE privilege on the compute warehouse
GRANT USAGE ON WAREHOUSE <WAREHOUSE> TO ROLE TOOL_READ_ONLY_ROLE;

-- 3. Grant USAGE privilege on the target database
GRANT USAGE ON DATABASE <DB> TO ROLE TOOL_READ_ONLY_ROLE;

-- 4. Grant USAGE privilege on the target schema
GRANT USAGE ON SCHEMA <DB.SCHEMA> TO ROLE TOOL_READ_ONLY_ROLE;

-- 5. Grant SELECT privilege on tables within the schema
GRANT SELECT ON ALL TABLES IN SCHEMA <DB.SCHEMA> TO ROLE TOOL_READ_ONLY_ROLE;
GRANT SELECT ON FUTURE TABLES IN SCHEMA <your_database_name>.<your_schema_name> TO ROLE TOOL_READ_ONLY_ROLE;

-- 6. Create the dedicated user for Airia (if it doesn't exist)
CREATE USER IF NOT EXISTS <airia_service_user_name>
    RSA_PUBLIC_KEY = '<paste_public_key_content_here>'
    MUST_CHANGE_PASSWORD = FALSE -- Required for key-pair authentication
    DEFAULT_WAREHOUSE = '<your_warehouse_name>' -- Recommended: Set default warehouse
    DEFAULT_ROLE = 'AIRIA_READ_ONLY_ROLE' -- Recommended: Set default role
    COMMENT = 'Read only user account for Airia tool access';

-- 7. Grant the dedicated role to the user
GRANT ROLE TOOL_READ_ONLY_ROLE TO USER <USER>;

-- 8. Verify grants (Optional)
SHOW GRANTS TO ROLE TOOL_READ_ONLY_ROLE;
SHOW GRANTS TO USER <USER>

Ensure that the instructions above are carried out from an account that has sufficient priveledges to create a new user account, such as an Account Admin or Security Admin, and that you have created a Private Key file using the OpenSSL protocol as per Snowflake's Key-Pair Authentication guide. The private key file is used within the Airia tool configuration.

Adding and Configuring the Snowflake Metadata retrieval tool

1. Follow the instructions above to configure the Snowflake Metadata retrieval tool, providing the same values to it as well.

Now that the tool has been added, you can add it to an agent and use it to query your Snowflake Database. See these instructions on the page: Use Snowflake tools