Initial Snowflake Setup

Before we jump into making our snowflake agent, lets go through the initial setup.

Auth Setup

• To connect to snowflake you’ll need to generate a role on snowflake, attach a user to that role, and use private key-pair auth to add that role to snowflake.
• You can have multiple snowflake credentials in credal each tied to their own snowflake user/role to ensure secure access.

1. Create a role in snowflake which will control read access to tables/views (e.g CREDAL_READ)

1
-- 1. Create the new Role
2
CREATE ROLE CREDAL_READ;
3
GRANT ROLE CREDAL_READ TO ROLE ACCOUNTADMIN;
4
5
-- 2. Grant the appropriate access to read database/schema/tables
6
GRANT USAGE ON DATABASE <database_name> TO ROLE CREDAL_READ;
7
GRANT USAGE ON ALL SCHEMAS IN DATABASE <database_name> TO ROLE CREDAL_READ;
8
GRANT USAGE ON WAREHOUSE <warehouse_name> TO ROLE CREDAL_READ;
9
10
GRANT SELECT ON ALL TABLES IN DATABASE <database_name> TO ROLE CREDAL_READ;
11
GRANT SELECT ON ALL VIEWS IN DATABASE <database_name> TO ROLE CREDAL_READ;
12
13
-- 3. Test that the role has the correct access (Optional).
14
USE ROLE CREDAL_READ;
15
SHOW SCHEMAS IN DATABASE <database_name>;
16
SHOW TABLES;
17
SELECT * FROM <database_name>.INFORMATION_SCHEMAS.TABLES;

2. Create a new User that is granted the CREDAL_READ role

1
-- 1. Create the new User
2
CREATE USER CREDAL_USER; -- can be any username
3
...
4
GRANT ROLE CREDAL_READ TO USER CREDAL_USER;

3. Setup Snowflake key pair authentication

• Follow this guide to setup key-pair auth: Snowflake Key Pair Auth

4. Add your snowflake credential on the [action provider page]

• They will need the Subdomain, Account Identifier, Username, Private Key and SHA-256 Fingerprint to add the snowflake connection.

Implementation Steps

Step 1: Create Your Agent

1. Navigate to the Agents page and click “Create New Agent”
2. Name your agent “Snowflake Data Extractor”
3. Add this description:

   This assistant helps employees query data from snowflake getting metrics, analysis and human-readable responses on the data.

4. Enable Code Interpreter (required for image generation)

   Output from the runSnowflakeQuery Action is passed into Open AI Code Interpreter for image generation and analysis (depending on your parameter setup)

Tip: A detailed description helps users understand the purpose of your Agent.

Step 2: Create the runSnowflake Action

1. Database: the default database to use when querying
2. Warehouse: the compute warehouse to run your queries (will likely be hardcoded)
3. Query: Depends on whether you take the Query Templates Approach or the Snowflake Views approach (explained more in next steps)
4. Account Name: Your snowflake account name (will likely be hardcoded)
5. Format: The format of your output csv or json (prefer csv)
6. Role: The role you would like to use with the credentials (optional)
7. Limit (Optional): The limit on number of rows to return, anything over this limit will mention “data queried was too large”
8. Code Interpreter Limit (Optional): The minimum number of rows required to pass to code interpreter for analysis (thinking models handle small sets of results (e.g < 100) better than code interpreter)
9. Code Interpreter Image Gen Limit (Optional): The minimum number of rows required for image generation (in case you want thinking models for analysis and image generation for more than X results)

Step 3: Configure Access Control for Snowflake Credentials

Access to Snowflake data is controlled at two levels:

1. Credential Access:
   • Only the creator of a Snowflake credential can attach it to an Action.
2. Action Access:
   • You can specify exactly which users or groups can use this Action through the End User Access settings
   • This ensures that only authorized personnel can execute queries using the attached Snowflake credentials

Important Security Note: Any user with end-user access to an Action will be able to use the attached Snowflake credentials through that Action. Ensure you carefully manage the End User Access list to maintain proper data security.

Step 4: Model Choice

The base model you select will affect queries generated and analysis of results. It’s recommended to use a thinking model with reasoning enabled such as o3 or Claude 3.7, however the model you select is also dependant on the query approach. You may want to use a smaller non-reasoning model for query templates which are more well defined compared to snowflake views.

Step 5: Choose your query approach

Right now there are 2 approaches to querying snowflake for data

• 1. Query Templates

• 2. Snowflake Views

Each one has their own pros and cons and will likely be usecase dependant. Read about each approach and implementation steps in the Query Approaches