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 a credal admin at your organization to add org-wide snowflake credentials.
• To help them prepare with adding in credentials you can:

1. Create the CREDAL_READ role in snowflake which will control read access to tables/views.

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. Get a credal admin at your organization to add the snowflake credentials

• 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

   Output from the runSnowflakeQuery Action is passed into Open AI Code Interpreter for analysis, meaning your copilot must be using an OpenAI foundational model compatible with Code Interpreter

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. Username: Username of the account that is setup with Key-Pair authentication
5. Account Name: Your snowflake account name (will likely be hardcoded)
6. Format: The format of your output csv or json (prefer csv)

Step 3: Choose your 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