As outlined in our security documentation, SELECT requires a new user to be created with read-only access to your Snowflake account metadata database. In the sections below, we'll walk you through the access commands step by step. Alternatively, you can skip ahead to the full script and run it in one go.
Ensure you have access to run these commands!
In order to run this setup script, your user will need to have access to the
useradmin roles. If you're not sure whether you have those roles, you can run
show grants to user <your_username> to check.
Step 1: Create new user & role
First off, create a new user and role that will only be used by SELECT. You'll need to update the
PASSWORD_HERE with a randomly generated password
1. Be sure to save it, since you'll need to enter it into the SELECT web application later on.
We recommend using the
useradmin role when creating new users, and the
sysadmin role when creating a new warehouse in step 2. If you prefer not to, you can run all these commands with the
use role useradmin; -- Snowflake best practicecreate user if not exists select_userpassword = 'PASSWORD_HERE'default_role = select_userdefault_warehouse = select_usercomment = 'Used by select.dev';create role if not exists select_user comment = 'Used by select.dev';grant role select_user to role sysadmin; -- Snowflake best practicegrant role select_user to user select_user;
Step 2: Create a new warehouse
Next, create a new warehouse for SELECT. SELECT will use this warehouse to query your Snowflake metadata once per day. The cost of running this warehouse will be minimal. If preferred, you can instead grant us access to run the queries from an existing warehouse 2.
use role sysadmin; -- Snowflake best practicecreate warehouse if not exists select_userwarehouse_size=xsmallauto_suspend=60initially_suspended=truecomment = 'Used by select.dev';grant monitor, operate, usage, modifyon warehouse select_user to role select_user;
Step 3: Create a storage integration
To export your account's metadata in the fastest and most cost-efficient way, we export directly to a secure Google Cloud Storage (GCS) bucket. The commands below create a storage integration and allow the
select_user role to write the Snowflake metadata to SELECT's GCS bucket using it.
use role accountadmin;create storage integration if not exists select_exporttype = external_stagestorage_provider = 'GCS'enabled = truestorage_allowed_locations = ('*');grant usage on integration select_export to role select_user;
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 used by the product 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, each customer's Snowflake metadata is stored in a dedicated Google Cloud Storage bucket. If you would like to restrict the
storage_allowed_locations to just that bucket, please reach out to the SELECT team (via email or using the Intercom chat at the bottom right) and share your Snowflake account identifier and our team will provide you with the GCP bucket name for your account.
Data is stored in the us-east-1 Google Cloud region. There is a data egress cost 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).
Optional - Copy through a stage
By default, SELECT attempts to use the storage integration
select_export directly. If using the PREVENT_UNLOAD_TO_INLINE_URL parameter, create a stage to represent SELECT's GCS bucket and grant the
select_user usage access.
-- change this location and stage name as per your preferencecreate stage my_db.my_schema.select_export-- we will provide this GCS bucket nameurl='gcs://select_bucket_name/'storage_integration = select_export;-- change as neededgrant usage on database my_db to role select_user;grant usage on schema my_db.my_schema to role select_user;grant usage on stage my_db.my_schema.select_export to role select_user;
Step 4: Grant user read only access to Snowflake metadata
-- Allows SELECT to query Snowflake metadatagrant imported privileges on database snowflake to role select_user;-- Allows SELECT to monitor warehousesgrant monitor usage on account to role select_user;
Step 5: Ensure your account has ORGADMIN access
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:
use role accountadmin;select *from snowflake.organization_usage.rate_sheet_daily;
If records are returned, you're all set and can move on to the next step.
If no records are returned, you can run the following command to make these views accessible in your account as per Snowflake's instructions:
use role orgadmin;alter account my_account1 set is_org_admin = true;
It can take up to 1 day for these views 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.
Purchased your Snowflake credits through a reseller?
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. Please share a copy of your latest Snowflake invoice with the SELECT team (via chat or by emailing [email protected]) and we will manually input the applicable rates for your account.
Step 6: Grant user ability to monitor warehouses
Lastly, we give
select_user the ability to monitor and adjust warehouse settings through the manage warehouses permission.
This permission is also required for SELECT to retrieve query statistics using get_query_operator_stats.
grant manage warehouses on account to role select_user;
After updating the
PASSWORD_HERE in the second command, you can run the entire script at once.
-- Step 1: Create new useruse role useradmin;create user if not exists select_userpassword = 'PASSWORD_HERE'default_role = select_userdefault_warehouse = select_usercomment = 'Used by select.dev';create role if not exists select_user comment = 'Used by select.dev';grant role select_user to role sysadmin; -- Snowflake best practicegrant role select_user to user select_user;-- Step 2: Create a new X-SMALL warehouse for SELECT to use when reading Snowflake metadatause role sysadmin;create warehouse if not exists select_userwarehouse_size=xsmallauto_suspend=60initially_suspended=truecomment = 'Used by select.dev';grant usage on warehouse select_user to role select_user;use role accountadmin;-- Step 3: Create a storage integration in GCS so SELECT can export metadata required to power cost dashboards-- Used to power dashboards in select.devcreate storage integration if not exists select_exporttype = external_stagestorage_provider = 'GCS'enabled = truestorage_allowed_locations = ('*');grant usage on integration select_export to role select_user;-- Step 4: Grant SELECT_USER required privileges-- SELECT will get read only access to Snowflake account metadata database:-- https://docs.snowflake.com/en/sql-reference/snowflake-db.html-- Allows SELECT to query Snowflake metadatagrant imported privileges on database snowflake to role select_user;-- Allows SELECT to monitor warehousesgrant monitor usage on account to role select_user;-- Step 5: Give SELECT the ability to modify each warehouse in your account and retrieve query statistics-- through get_query_operator_statsgrant manage warehouses on account to role select_user;
If your Snowflake account restricts access to certain IP addresses, be sure to update your network policies with our IP address, covered in the next section.
If not, you can skip ahead to connect your Snowflake account.
2 If you'd prefer to have SELECT run the metadata extracts from an existing warehouse, you can run the commands below instead:
use role sysadmin; -- Snowflake best practicegrant monitor, operate, usage on warehouse YOUR_PREFERED_WAREHOUSE_NAME to role select_user;alter user select_user set default_warehouse=YOUR_PREFERED_WAREHOUSE_NAMEuse role accountadmin;