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
2CREATE ROLE CREDAL_READ;
3GRANT ROLE CREDAL_READ TO ROLE ACCOUNTADMIN;
4
5-- 2. Grant the appropriate access to read database/schema/tables
6GRANT USAGE ON DATABASE <database_name> TO ROLE CREDAL_READ;
7GRANT USAGE ON ALL SCHEMAS IN DATABASE <database_name> TO ROLE CREDAL_READ;
8GRANT USAGE ON WAREHOUSE <warehouse_name> TO ROLE CREDAL_READ;
9
10GRANT SELECT ON ALL TABLES IN DATABASE <database_name> TO ROLE CREDAL_READ;
11GRANT SELECT ON ALL VIEWS IN DATABASE <database_name> TO ROLE CREDAL_READ;
12
13-- 3. Test that the role has the correct access (Optional).
14USE ROLE CREDAL_READ;
15SHOW SCHEMAS IN DATABASE <database_name>;
16SHOW TABLES;
17SELECT * FROM <database_name>.INFORMATION_SCHEMAS.TABLES;
2. Create a new User that is granted the CREDAL_READ role
1-- 1. Create the new User
2CREATE USER CREDAL_USER; -- can be any username
3...
4GRANT ROLE CREDAL_READ TO USER CREDAL_USER;
3. Setup Snowflake key pair authentication
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