Skip to main content

Snowflake Integration Guide

About the Snowflake Integration​

In order to query your Snowflake Account, you will need to create a Snowflake User configured as a Service Account that uses a keypair for programmatic access. This Service Account will be denoted with “TYPE = SERVICE” and will allow Vega to provide information about your Snowflake usage and spending.

By leveraging Snowflake Database Roles you are able to grant Vega Snowflake-managed access to the ACCOUNT_USAGE schema to allow us to analyze your usage data.

note

Due to the high level of variation that is present in each organization's standards we are unable to cover all eventualities. A thorough review of Snowflake best practices, your organization’s standards, and the provided Snowflake Worksheet are recommended to ensure alignment with your internal processes.

Snowflake Access Control Considerations

SNOWFLAKE.ACCOUNT_USAGE Access Control Considerations

Created User and Roles​

Here is a summary of what the steps that follow will be accomplishing:

  • Create the user: vegadiscovery_svc
  • Create the role: vegadiscovery
  • Set user type to SERVICE
  • Grant the following roles:
    • SNOWFLAKE.OBJECT_VIEWER
    • SNOWFLAKE.USAGE_VIEWER
    • SNOWFLAKE.ORGANIZATION_BILLING_VIEWER
    • SNOWFLAKE.ORGANIZATION_USAGE_VIEWER
    • SNOWFLAKE.ORGANIZATION_ACCOUNTS_VIEWER
    • SNOWFLAKE.GOVERNANCE_VIEWER
    • SNOWFLAKE.SECURITY_VIEWER

Begin Integration​

Modify and run the provided Snowflake worksheet in your Snowflake Account

  1. In your Snowflake account, navigate to Worksheets and click the blue “+” box to add a worksheet. Select SQL Worksheet variety from the choices.
    • (Optional) You can rename your Worksheet if you like, using the button next to the current name, on the active worksheet tab. Otherwise, the date will be used.
  2. Copy the data between -- START OF WORKSHEET / -- END OF WORKSHEET in the Provided Snowflake Worksheet below and paste into the newly created worksheet.
  3. If you are not using ‘COMPUTE_WH’ for your Snowflake Warehouse, you will need to replace both COMPUTE_WH references with the name of your Snowflake Warehouse.
  4. You can verify that the user vegadiscovery_svc and role vegadiscovery were created in Admin > Users & Roles. Follow steps at the end to provide required configuration info back to Vega.

Provided Snowflake Worksheet​

-	START OF WORKSHEET
USE ROLE USERADMIN;
CREATE USER vegadiscovery_svc
TYPE = SERVICE
COMMENT = 'Service Account for Vega Cloud'
DEFAULT_ROLE = 'vegadiscovery'
DEFAULT_WAREHOUSE = 'COMPUTE_WH'
USE ROLE SECURITYADMIN; 
CREATE ROLE vegadiscovery; 
GRANT ROLE vegadiscovery TO USER vegadiscovery_svc; 
GRANT USAGE ON WAREHOUSE COMPUTE_WH TO ROLE vegadiscovery; 
USE ROLE ACCOUNTADMIN; 
GRANT DATABASE ROLE SNOWFLAKE.OBJECT_VIEWER TO ROLE vegadiscovery; 
GRANT DATABASE ROLE SNOWFLAKE.USAGE_VIEWER TO ROLE vegadiscovery; 
GRANT DATABASE ROLE SNOWFLAKE.ORGANIZATION_BILLING_VIEWER TO ROLE vegadiscovery; 
GRANT DATABASE ROLE SNOWFLAKE.ORGANIZATION_USAGE_VIEWER TO ROLE vegadiscovery; 
GRANT DATABASE ROLE SNOWFLAKE.ORGANIZATION_ACCOUNTS_VIEWER TO ROLE vegadiscovery; 
GRANT DATABASE ROLE SNOWFLAKE.GOVERNANCE_VIEWER TO ROLE vegadiscovery; 
GRANT DATABASE ROLE SNOWFLAKE.SECURITY_VIEWER TO ROLE vegadiscovery;
-	END OF WORKSHEET

Once the above is complete, you will either need to assign the user to an authentication policy you already have or create a new authentication policy that allows KEYPAIR only.

The following statement will create an authentication policy for service accounts and assign the vegadiscovery_svc account to the policy.

CREATE AUTHENTICATION POLICY service_account_auth
AUTHENTICATION_METHODS = ('KEYPAIR')
CLIENT_TYPES = ('DRIVERS')
COMMENT = 'Minimal auth policy to allow programmatic access to the target account.';
 
ALTER USER VEGADISCOVERY_SVC SET AUTHENTICATION POLICY service_account_auth;

Provide configuration to Vega​

Vega will need the five pieces of information below to configure your Snowflake data. Your account ID and Cloud Region information can be found in the Admin > Accounts section of Snowflake.

Necessary Information​

  1. ACCOUNT_ID - "<SNOWFLAKE ACCOUNT ID>" (eight-digit ID - example: XYZ12345)
  2. SNOWFLAKE_ROLE - "vegadiscovery" (default, unless you chose a custom role name)
  3. SNOWFLAKE_USERNAME - "vegadiscovery_svc" (default, unless you chose a custom user name)
  4. SNOWFLAKE_REGION - "<Cloud Region ID>" (enter the Cloud Region ID associated with your Cloud Region. Link below provides all Cloud Region to Cloud Region ID mappings across Cloud Solution Providers)

Once we receive this information, Vega will create a keypair on our end and provide you with the Public Key for your Service Account.

Update Service Account with Public Key​

  1. Run the following statement in order to assign the Public Key to the vegadiscovery_svc user
ALTER USER vegadiscovery_svc SET RSA_PUBLIC_KEY = '<PASTE_PUBLIC_KEY_HERE>';