# Connect your Snowflake Account

URL: https://select.dev/docs/reference/setup-guide/connect-snowflake-account
Index of all SELECT docs: https://select.dev/docs/llms.txt

> Create a new Snowflake User and connect your Snowflake account to SELECT

[YouTube video](https://www.youtube.com/embed/BYVPZS4QFkk?rel=0&controls=0)

Connecting your Snowflake account to SELECT is an easy process:

1. **Step 1:** You'll create a new Snowflake service user with read only access to your Snowflake metadata and a few other permissions.
2. **Step 2:** You'll connect your Snowflake account to SELECT using the user's credentials.

## Step 1: Create Snowflake Service User

As outlined in our [security documentation](https://select.dev/security), SELECT requires a new Snowflake service user to be created with read-only access to your [Snowflake account metadata database](https://docs.snowflake.com/en/sql-reference/snowflake-db.html). 

### 1. Create key pair for service user

First, create a new [key pair](https://select.dev/docs/resources/snowflake-developer-guide/snowflake-key-pair) that will be used as the authentication method for the new user created by the script.  See our [guide](https://select.dev/docs/resources/snowflake-developer-guide/snowflake-key-pair) on how to create the key pair.

### 2. Run the setup script

Now that we have created the user's key pair credentials, we can run the setup script.

The script below handles creating the new user, role and associated permissions used by SELECT. 

The script has plenty of code comments to help you understand what is happening. You can refer to the setup script explanation [section below](https://select.dev/docs/reference/setup-guide/connect-snowflake-account#setup-script-explanation) for more details.

Please fill out the variable values in lines 1-22 and **provide the public key on line 26.**  

```sql
-- Attention users:  Edit variable values up to line 26! Do not change variable names, only change the values to the right of the equals sign.
SET select_role = 'SELECT_ROLE';
SET select_warehouse = 'SELECT_WH'; -- you can use an existing warehouse if you'd like.
SET select_user_name = 'SELECT_USER';

/* 
SELECT uses a storage integration and a stage to access your Snowflake usage data.
For more info, see docs:
https://select.dev/security/model#select-architecture

Also, SELECT uses a stored procedure which tells you avg depth and cluster scores of each table.

If you would like the storage integration, stage, and procudure in an isolated database, type a new database and schema name below.
If you'd like this in an existing database, type the existing database and schema name.

*/

SET select_database = 'SELECT_DEV';
SET select_schema = 'UTILS';
SET storage_integration_name = 'SELECT_STORAGE_INTEGRATION';
SET describe_object_procedure = 'SPROC_DESCRIBE_OBJECT';
SET stage_name = 'SELECT_EXPORT';

use role useradmin;

-- ‼️ edit the public key line below ‼️
CREATE USER IF NOT EXISTS identifier($select_user_name)
  RSA_PUBLIC_KEY = '‼️ your public key here‼️ '
  DEFAULT_ROLE = $select_role
  DEFAULT_WAREHOUSE = $select_warehouse
  COMMENT = 'Used by select.dev'
  type = 'SERVICE';

-- No edits are required below this line.
--------------------------------------------

-- This is setting the bucket name, which is used in the bucket name for the stage.
-- CRITICAL: Do not modify this gcs_path variable!! Leave GCS even if you use Azure or AWS.
-- You do not need to create the bucket, it will be created for you.
SET gcs_path = (
  SELECT 
    'gcs://' || 
    CASE 
      WHEN RIGHT(bucket_base, 1) = '_' 
      THEN LEFT(bucket_base, LENGTH(bucket_base) - 1)
      ELSE bucket_base
    END || '/'
  FROM (
    SELECT 
      LEFT(
        'select_ingest_' || 
        regexp_replace(lower(CURRENT_ORGANIZATION_NAME() || '_' || CURRENT_ACCOUNT_NAME()), '\\W', '_'),
        63
      ) AS bucket_base
  )
);


USE ROLE ACCOUNTADMIN;

------- create role and warehouse used by SELECT --------------
CREATE ROLE IF NOT EXISTS identifier($select_role) COMMENT = 'Used by select.dev';

CREATE WAREHOUSE IF NOT EXISTS identifier($select_warehouse)
  WAREHOUSE_SIZE = XSMALL
  AUTO_SUSPEND = 60
  INITIALLY_SUSPENDED = TRUE
  COMMENT = 'Used by select.dev';


GRANT ROLE identifier($select_role) TO USER identifier($select_user_name);

GRANT MONITOR, OPERATE, USAGE, MODIFY
  ON WAREHOUSE identifier($select_warehouse) TO ROLE identifier($select_role);

-- Grant additional privileges needed by SELECT
-- Grant read-only access to the Snowflake metadata database
GRANT IMPORTED PRIVILEGES ON DATABASE SNOWFLAKE TO ROLE identifier($select_role);
-- Grants the ability to monitor account-level usage and historical information for databases and warehouses
GRANT MONITOR USAGE ON ACCOUNT TO ROLE identifier($select_role);
-- Grant the ability ability to monitor warehouse settings through the manage warehouses permission
-- This permission is also required for SELECT to retrieve query statistics using get_query_operator_stats, and for the Automated Savings feature
GRANT MANAGE WAREHOUSES ON ACCOUNT TO ROLE identifier($select_role); 

-------- Create DB and schema to house SELECT objects --------------
CREATE DATABASE IF NOT EXISTS identifier($select_database);
GRANT USAGE ON DATABASE identifier($select_database) TO ROLE identifier($select_role);

USE DATABASE identifier($select_database);

CREATE SCHEMA IF NOT EXISTS identifier($select_schema);

GRANT USAGE ON SCHEMA identifier($select_schema) TO ROLE identifier($select_role);

USE SCHEMA identifier($select_schema);

-- Create the GCS storage integration
-- Do not change to AWS or Azure, SELECT's Snowflake account is in GCS and this enables copying data to SELECT's account
CREATE STORAGE INTEGRATION IF NOT EXISTS identifier($storage_integration_name)
  TYPE = EXTERNAL_STAGE
  STORAGE_PROVIDER = 'GCS' -- do not change!
  ENABLED = TRUE
  STORAGE_ALLOWED_LOCATIONS = ($gcs_path); -- the storage integration will only be used for the dedicated GCS bucket created on your behalf

GRANT USAGE ON INTEGRATION identifier($storage_integration_name) TO ROLE identifier($select_role);

--Create stage
CREATE OR REPLACE STAGE identifier($stage_name)
  URL = $gcs_path
  STORAGE_INTEGRATION = $storage_integration_name;

GRANT USAGE ON STAGE identifier($stage_name) TO ROLE identifier($select_role);

-- The stored procedure below used to analyze clustering statistics for your table
-- It is optional, but helpful to have
CREATE OR REPLACE PROCEDURE identifier($describe_object_procedure) (
        object_type varchar, -- Generic, so SELECT can describe tables, dynamic tables and materialized views
        object_fqn varchar, -- The full path of the object to be described (database_name.schema_name.object_name)
        run_describe_object boolean, -- Whether to call "describe table" or not
        add_clustering_information boolean, -- Whether to include clustering information in the results or not
        columns_filter_clustering_information array -- Sometimes SELECT only needs clustering information on certain columns
    )
    RETURNS variant
    LANGUAGE javascript
    EXECUTE AS OWNER
AS
$$
    /**
    * Function to call "describe table" and parse the results.
    */
    function describeObject(objectType, objectFqn) {
        const sql = `describe ${objectType} ${objectFqn}`

        const statement = snowflake.createStatement({ sqlText: sql })
        const resultSet = statement.execute()

        const columnsInResult = []
        for (let columnIndex = 0; columnIndex < statement.getColumnCount(); columnIndex++) {
            columnsInResult.push(statement.getColumnName(columnIndex + 1)) // Starts from 1
        }

        const output = [];

        for (let rowIndex = 0; resultSet.next(); rowIndex++) {
            let row = { _index: rowIndex }
            columnsInResult.forEach(column => row[column] = resultSet.getColumnValue(column))
            output.push(row)
        }

        return output
    }

    /**
    * Function to call "system$clustering_information" and parse the results.
    * Snowflake requires one call per column, but we combine all calls into one SQL statement
    * using UNION ALL.
    */
    function extendColumnsWithClusteringInformation(tableFqn, columns, runDescribeObject, columnsFilterClusteringInformation) {
        let filteredColumns

        if (runDescribeObject) { // If describe object has been called
            // Filter out unsupported column types (causes errors on Snowflake)
            filteredColumns = columns.filter(column => !['OBJECT', 'VARIANT', 'ARRAY'].includes(column.type))

            // If a list of columns to filter on is provided, use it
            if (columnsFilterClusteringInformation)
                filteredColumns = filteredColumns.filter(column => columnsFilterClusteringInformation.includes(column.name))
        } else { // If describe object hasn't been called, use the columns provided in the filter
            // Convert it to the expected format
            filteredColumns = columnsFilterClusteringInformation.map(columnName => ({ name: columnName }))
        }

        if (!filteredColumns.length) return

        const sql = filteredColumns.map(column =>
            `select ${column?._index || 0} as column_index, '${column.name}' as column_name, system$clustering_information('${tableFqn}', '("${column.name}")') as clustering_information`
        ).join("\nunion all\n")
        const resultSet = snowflake.execute({ sqlText: sql })

        while (resultSet.next()) {
            let colIndex = resultSet.getColumnValue(1)
            let clusteringInformation = JSON.parse(resultSet.getColumnValue(3))

            if (runDescribeObject) { // If there's already data available from describe object, extend it
                columns[colIndex] = { ...columns[colIndex], clustering_information: clusteringInformation }
            } else { // Otherwise, append to the array
                columns.push({ clustering_information: clusteringInformation })
            }

        }
    }

    /**
    * Function to call "system$clustering_information" without any expression, which defaults
    * to the existing clustering key if it's defined in the table.
    */
    function fetchClusteringKeyInformation(tableFqn) {
        try {
            const resultSet = snowflake.execute({ sqlText: `select system$clustering_information('${tableFqn}')` })
            resultSet.next()
            return JSON.parse(resultSet.getColumnValue(1))
        } catch (err)  {
            // "Invalid clustering keys or table ... is not clustered"
            return null;
        }
    }

    let columns = null
    let clusteringKeyClusteringInformation = null

    if (RUN_DESCRIBE_OBJECT) {
        columns = describeObject(OBJECT_TYPE, OBJECT_FQN)

        // If the ADD_CLUSTERING_INFORMATION argument is true, append the output with
        // the results from system$clustering_information
        if (ADD_CLUSTERING_INFORMATION) {
            extendColumnsWithClusteringInformation(OBJECT_FQN, columns, true, COLUMNS_FILTER_CLUSTERING_INFORMATION)
            clusteringKeyClusteringInformation = fetchClusteringKeyInformation(OBJECT_FQN)
        }
    } else if (ADD_CLUSTERING_INFORMATION && COLUMNS_FILTER_CLUSTERING_INFORMATION) {
        // If only one column is passed as a filter and it is an empty string, fetch clustering data about
        // the clustering key
        if (COLUMNS_FILTER_CLUSTERING_INFORMATION?.length === 1 && COLUMNS_FILTER_CLUSTERING_INFORMATION[0] === '') {
            clusteringKeyClusteringInformation = fetchClusteringKeyInformation(OBJECT_FQN)
        } else {
            columns = []
            extendColumnsWithClusteringInformation(OBJECT_FQN, columns, false, COLUMNS_FILTER_CLUSTERING_INFORMATION)
        }
    }

    return { clusteringKey: clusteringKeyClusteringInformation, columns: columns, version: 'v1' }
$$;


-- Grant the procedure to the role used by SELECT
SET full_proc = $select_database || '.' || $select_schema || '.' || $describe_object_procedure;
SET grant_sql = 'GRANT USAGE ON PROCEDURE ' || $full_proc || '(VARCHAR, VARCHAR, BOOLEAN, BOOLEAN, ARRAY) TO ROLE ' || $select_role;
select $grant_sql; -- optional, just so you can see what it will do.
EXECUTE IMMEDIATE $grant_sql;


-------------------------------------------------------------------
-------------------------------------------------------------------
--------------- END OF SCRIPT ---------------------------------------


-- Run the select statment below to assist in filling out the connect account form in the SELECT UI:

SELECT {
    'account_identifier': CURRENT_ORGANIZATION_NAME() || '-' || CURRENT_ACCOUNT_NAME(),
    'user': $select_user_name,
    'stage_name': $select_database || '.' || $select_schema || '.' || $stage_name,
    'procedure_name': $full_proc
} as select_form_data; 
```

### 3. Verify your account has access to organization_usage views

SELECT relies on a few views from the `snowflake.organization_usage` schema. For example, `snowflake.organization_usage.rate_sheet_daily` is used to look up your daily rate for each Snowflake service (i.e. your cost per credit for compute, cost per terabyte for storage, etc).

Please run the following SQL statement to check if your account has these views populated:

```sql
use role accountadmin;

select *
from snowflake.organization_usage.rate_sheet_daily;
```

If records are returned, you're all set.

If no records are returned, the ORGADMIN role likely isn't enabled for the account. To enable it, log into a Snowflake account in your organization which has ORGADMIN enabled (at least one account will), then head to Admin -> Accounts in the sidebar. Then, enable ORGADMIN for the account you're connecting to SELECT.

![Snowflake organization admin role](https://cdn.sanity.io/images/wn0ylp2c/production/6e3b847271c5959812717ff11b63f098c7fe7d0b-1270x793.png)

It can take up to 1 day for these views to become populated with data after running this statement. As a result, you may not see data in your Snowflake account until the morning after connecting your account. Reach out to our team using the Intercom chat on the website if you're still not seeing data after 1 day.

> [!TIP]
> Users who purchased their Snowflake contract/credits through a reseller (i.e. AWS Marketplace) will not have access to the `organization_usage` views even after running the command above. In this case, you can manually add your cost per credit and storage cost in the "Advanced" section of the "[Add New Snowflake Account](https://select.dev/docs/reference/setup-guide/connect-snowflake-account#step-2-connect-your-snowflake-account)" screen.

### 4. [Optional] Update Network Policies

If your Snowflake account has configured a network policy to restrict access based on IP address, you'll need to update the network policy to allow SELECT to connect.

In Snowsight, go to **Admin » Security » Network Policies**.  From there, add the following IP addresses to your **Allowed IP Addresses** list:

```sql
34.23.79.180
34.139.189.198
35.229.105.183
35.243.245.125
34.138.184.161
34.73.135.174
34.148.143.80
34.23.67.112
```

You can also apply the changes via SQL instead. The example below shows how to create the policy and assign it to the SELECT service user. Be sure to update the policy and user names as needed:

```sql
CREATE OR REPLACE NETWORK POLICY select_network_policy
  ALLOWED_IP_LIST = (
    '34.23.79.180',
    '34.139.189.198',
    '35.229.105.183',
    '35.243.245.125',
    '34.138.184.161',
    '34.73.135.174',
    '34.148.143.80',
    '34.23.67.112'
  );

ALTER USER select_user SET NETWORK_POLICY = select_network_policy;

```

#### Updating an account wide network policy

If you're updating a network policy that is already active for all users in your Snowflake account, **you do not need to reassign the policy** to the SELECT user. Simply adding the new IPs is enough.

For more details, see the [Snowflake documentation](https://docs.snowflake.com/en/user-guide/network-policies.html) on Network Policies, or reach out using the chat button on the bottom right if you run into any issues.

### Setup Script Explanation

In the sections below, we describe the setup script in more detail.

#### Create User, Role, and Warehouse

The first action taken by the script (after you set your variables) is to create a new user and role that will only be used by SELECT. 

If you'd like SELECT to use an existing warehouse, set line 3 to an existing warehouse name.  Otherwise, the warehouse indicated on line 3 will be created as an extra-small warehouse. 

After the user, role, and warehouse are created:

- The role is granted read-only access to the Snowflake metadata database.
- The role is also grant ability to monitor warehouse settings through the [manage warehouses permission](https://docs.snowflake.com/en/user-guide/warehouses-tasks#label-grant-manage-warehouses-privilege). This permission is also required for SELECT to retrieve query statistics using [get_query_operator_stats](https://docs.snowflake.com/en/sql-reference/functions/get_query_operator_stats) and for the [Automated Savings](https://select.dev/features/automated-savings) feature.

#### Create a Storage Integration and Stage used by SELECT

The script will create a `stage` and `storage integration` in your Snowflake account that will be used to give SELECT access to your Snowflake spend metadata.  The bucket used in Google Cloud Storage will be created on the fly by SELECT when you connect your Snowflake account.  There is nothing else you need to do to provide access.

Google Cloud Storage is used as an intermediary between SELECT's Snowflake account and the customer's. This is done because the [Snowflake account usage views](https://select.dev/security#what-snowflake-metadata-do-we-access) used by SELECT cannot be data shared directly, and the customer accounts may be in different regions which would require setting up data replication. Using Google Cloud Storage makes setup a lot simpler and more cost-effective for the customer.

As per our [security documentation](https://select.dev/security/model), each customer's Snowflake metadata is stored in a dedicated Google Cloud Storage bucket. Data is stored in the us-east-1 Google Cloud region. There is a [data egress cost](https://www.snowflake.com/legal-files/CreditConsumptionTable.pdf) charged by Snowflake, but it's incredibly small for the metadata volumes we're processing (the highest we've seen across our customer base is $20 per year).

The image below shows how the Snowflake Metadata is unloaded to GCS in SELECT's infrastructure using this `storage integration`.

![SELECT security architecture](https://cdn.sanity.io/images/wn0ylp2c/production/318e36376593d859341a5efa46b829a04ba1f6e7-2332x881.png)

These objects will get created in the database and schema you specified on lines 18 & 19.  If you used a new database / schema name, the database and Schema will be created at this time.  If you used an existing database and Schema name, the SELECT role will be granted usage in the existing database and only the new objects will be created.

#### Create a Stored Procedure used by SELECT

Some features in SELECT require information about objects in your account by:

- Running a [Describe Table](https://docs.snowflake.com/en/sql-reference/sql/desc-table) statement in your Snowflake account to fetch additional metadata from columns in a particular object (table, view, materialized table, etc).
- Running Snowflake's system function [system$clustering_information](https://docs.snowflake.com/en/sql-reference/functions/system_clustering_information) to fetch metadata about the clustering health status of columns in a table.

Instead of granting the necessary permissions to the `select_user` on all your existing objects and using future grants to also include new objects (which can conflict with your existing future grants), we ask our customers to create a custom stored procedure for when SELECT needs to run the commands above.

Just like above, these objects will be created in the database and schema specified on lines 18 and 19, but it needs to be created with the `accountadmin` role, as SELECT relies on the `execute as owner` property.

## Step 2: Connect your Snowflake Account

Now that we've created the new Snowflake user and associated resources, we can plug those details into the SELECT UI under Settings -> Accounts -> Add Snowflake account.

![SELECT connect Snowflake account](https://cdn.sanity.io/images/wn0ylp2c/production/a6f9ba24eced8d574249d0d2c946a04a64e87bf3-1890x1647.png)

The last few lines in the script above contain a `select` statement you can run to assist you in copy / pasting into the form in SELECT.  When me mention "script output" below, we are referring to this:

![Details needed for connecting SELECT app to Snowflake](https://cdn.sanity.io/images/wn0ylp2c/production/f7d49b4ee6efb5bbab555982114c3f2e00386878-1311x352.png)

Here are some more details on each field:

- **Friendly Account Name:** You can put whatever you want here. If you only have one Snowflake account, then it’s common to just put your company name, for example: “Cyberco”. If you plan to connect multiple Snowflake account to SELECT, name them in a way you’ll be able to differentiate them using the account switcher on the top left. For example, you could have “Cyberco Prod” and “Cyberco Dev”.
- **Account Identifier:** An account identifier uniquely identifies a Snowflake accounts within your organization. There are two ways to find it, [outlined below](https://select.dev/docs/reference/setup-guide/connect-snowflake-account#locating-your-snowflake-account-identifier).  Or, from the worksheet you ran the onboarding script, copy / paste the account identifier from the script output, shown in the screenshot above.  (Near line 241 of the script.)
- **Username:** This will be the name of the new Snowflake user you created for SELECT. For example, `SELECT_USER`. This is the variable you defined on line 4 and can be fetched using the final `select` statement in the script.
- **Private Key:** Paste the entire Secret Key you created in step 1.  You should copy / paste the entire thing as-is, including the Begin and End tags as well as all of the line breaks.
- **Passphrase:** If you created an encrypted private key, put the passphrase here.  Leave blank if you opted for an unencrypted private key.
- **[OPTIONAL] Role:** The script applies a default role to the user, so you can leave this blank if you ran the script as-is.  Or, fill this out to meet your requirements.
- **[OPTIONAL] Warehouse:** The script applies a default warehouse to the user, so you can leave this blank if you ran the script as-is.  Or, fill this out to meet your requirements.
- **Stage:** In the drop down for Export Method, leave it as **Stage**, and pass the stage name returned from the script output on line 241.
   - The stage name must be fully qualified.

![](https://cdn.sanity.io/images/wn0ylp2c/production/a9e95da6a2d6c70479abdd9d62ec7870c133d4d6-595x186.png)

- **Procedure Name:** To get the Procedure Name, copy and paste the result from the script output.
- **Advanced:** Only fill out the "Advanced" section if Snowflake was purchased through a reseller (i.e. AWS Marketplace) and you do not have access to the `organization_usage` views, mentioned [above](https://select.dev/docs/reference/setup-guide/account-setup-guide#3-verify-your-account-has-access-to-organization_usage-views).

![](https://cdn.sanity.io/images/wn0ylp2c/production/756d72ea1422b638d501d0dae5459f2a6bb339d1-560x299.png)

Once you click Add, the app will let you know if there were any errors.  In most cases the error message should be clear, but reach out to us via the in-app chat if there are any problems you cannot clear up on your own.

## Additional resources

### Workload identity federation

SELECT supports Snowflake’s [Workload identity federation](https://docs.snowflake.com/en/user-guide/workload-identity-federation) mechanism which is more secure than traditional methods like username/password or key-pair auth since it doesn’t involve sharing any secrets with SELECT.

To leverage this method, select “Workload Identity Federation” under the authentication method section. You will then be shown a SELECT Service Account ID (these are specific to each organization).

![](https://cdn.sanity.io/images/wn0ylp2c/production/dc70892cd6e2946d69b66ce4eb495071cad83abc-3334x2142.png)

On the Snowflake side, rather than setting a password or public key, configure the Snowflake service user you created for SELECT with the workload_identity attribute set as follows:

```sql
create user select_user
  workload_identity = (
    type = gcp
    subject = 'REPLACE_ME' -- Replace with the SELECT Service Account ID from the form
  )
  type = service;

```

No additional action is required outside of changing the way the Snowflake user is initially setup. Everything else in the setup script can be followed as normal.

To switch an existing password/key-pair user to workload identity, you can run:

```sql
alter user select_user set type = 'SERVICE';
alter user select_user set workload_identity = (
    type = gcp
    subject = 'REPLACE_ME' -- Replace with the SELECT Service Account ID from the form
  )
```

### Locating your Snowflake Account Identifier

In the bottom left corner if the Snowsight UI, click your User Name / Profile, then choose "Connect a tool to Snowflake". On the subsequent screen you will find your account identifier.

![](https://cdn.sanity.io/images/wn0ylp2c/production/27537e6dc7394916482ee42ebc3431bd04c66b6e-287x619.png)

![](https://cdn.sanity.io/images/wn0ylp2c/production/d96cc28ae69f7aa5f87f86fe5063643fde2405b4-901x649.png)

### Notes

  We recommend using a tool like 1Password to both randomly generate and save the password. Alternatively, you can use a [password generator website](https://passwordsgenerator.net/) and store the password securely elsewhere.

![](https://cdn.sanity.io/images/wn0ylp2c/production/d6834e348775868b2d078411bf9456d164136515-1860x474.png)