Snowflake Installation Instructions

1. Overview

Ursa Studio supports Snowflake implementations in both its AWS and Azure deployment scenarios. The Snowflake database should be set up in the same datacenter (cloud provider and region, e.g. AWS us-east-1) as the Ursa Studio app and the storage containers. The following instructions should be combined with either the AWS Cloud Installation Instructions or the Azure Cloud Installation Instructions to complete a Snowflake implementation.

2. Provision Database

When provisioning objects like databases and warehouses, you should be working in the SYSADMIN role, not the ACCOUNTADMIN role.

To set up a database, go to the pertinent account and use the “Create” button in the Databases tab.

Next, create a warehouse for the app service account, under the “Warehouses” tab. Small is a perfectly fine place to start, and the defaults are otherwise fine.

Next, create a role via the query editor:

create role ursa_app_role;

Next, create a user that represents the app service account. Default warehouse should be the one you just set up. Default role should be the role you just set up. Default namespace should be the name of the database. Do not give the user a (different) login name. For the sake of these examples we’re calling the database ursadb but you can call it whatever you want.

grant role ursa_app_role to user URSA_SERVICE_ACCOUNT;
grant usage on database ursadb to role ursa_app_role;
grant all on schema ursadb.ursa to role ursa_app_role;
grant usage on warehouse URSA_APP_WH to role ursa_app_role;

3. Hook up Ursa Studio

CLIENT_DBMS should be snowflake

CLIENT_DATABASE_URL will be in the format snowflake://my_service_account:mysuperstrongpassword@your-account.url.snowflakecomputing.com/URSADB

Make sure that you use a strong password!

Set URSA_ADMIN_ROLE to none, or to e.g. ursa_human_role if you’ve set up a role for human users.

Access to users without MFA (= the service account for the app) should be limited to a set of whitelisted IP addresses. In Azure, you can find a list of outbound addresses for the App Service in the Networking tab. In AWS, the outbound IP address is the EIP for the customer’s VPC. Copy this value(s) into a new Account->Policy, called e.g. URSA_APP_POLICY, then run

alter user URSA_SERVICE_ACCOUNT set NETWORK_POLICY=URSA_APP_POLICY;

4. Create Stages

During the installation process, manually create a stage for each storage container that will be used. It should be the same name as the container, but uppercase. You need to add quotes around the name of the stage (but not the schema) if the container name has dashes or similar characters. There is one important exception to this rule, that the stage name should be the uppercased bucket/container name, which is the URSAHEALTH-STANDARD-REFERENCE-FILES, as described below, if you're not operating in us-east-1.

You’ll furthermore want to create a stage, called URSAHEALTH-STANDARD-REFERENCE-FILES, which will point to our standard reference files. Ursa Health can give you the appropriate stage URL name for your cloud provider and region. This stage name might not be the same as the name of the storage container. For example for an AWS deployment in us-east-2, the stage should be named URSAHEALTH-STANDARD-REFERENCE-FILES even as the bucket is named ursahealth-standard-reference-files-us-east-2.

4.1 Create Stages in AWS

We recommend you use Snowflake's storage integration feature to securely link Snowflake stages to S3. First create an IAM role with trusted entity type “AWS Service” -> S3 -> S3. Add the bucket policies. Copy the ARN to clipboard.

In a Snowflake worksheet run the command

CREATE STORAGE INTEGRATION name_of_s3_integration
TYPE = EXTERNAL_STAGE
STORAGE_PROVIDER = S3
ENABLED = TRUE
STORAGE_AWS_ROLE_ARN = 'your role ARN'
STORAGE_ALLOWED_LOCATIONS = ('s3://your-import-bucket-name', 's3://your-export-bucket-name', 's3://ursahealth-standard-reference-files');

Updating the region of the standard-reference bucket appropriately. Once that’s successful, run

desc INTEGRATION name_of_s3_integration;

And note the STORAGE_AWS_IAM_USER_ARN and STORAGE_AWS_EXTERNAL_ID values.

Now go back into the new role’s Trust Relationships tab and update the JSON policy. Replace

"Principal": {
    "Service": "s3.amazonaws.com"
},

with

"Principal": {
    "AWS": "<the user ARN>"
 },

And after the “Action” attribute add

"Condition": {
  "StringEquals": {
     "sts:ExternalId": "<the external ID>"
   }
 }

Then in snowflake run

create stage ursa."URSAHEALTH-STANDARD-REFERENCE-FILES" 
URL = 's3://ursahealth-standard-reference-files/
STORAGE_INTEGRATION = name_of_s3_integration;

And so forth for all the stages you’ll need, then grant access to those stages to your app role:

grant all privileges on stage ursa."URSAHEALTH-STANDARD-REFERENCE-FILES"  to role name_of_app_role;

If you prefer not to use storage integration it's possible to create these stages by passing in the AWS Key ID and AWS Secret Key of an appropriately credentialed AWS IAM user during the create stage command.

Verify that you have created the stage with the correct permissions by running @list on the stage from the Snowflake console as the Snowflake role that Ursa Studio adopts permissions from. This might require temporarility granting yourself access to this role, so as to be able to inhabit it in the Snowflake console. If you cannot successfully list the contents of the stage from within the Snowflake console, Ursa Studio will not be able to access these stages.

4.2 Create Stages in Azure

From within the “Shared Access Tokens” blade of the Azure Storage Container, create an HTTPS-only account key with the appropriate permissions (read + list for import containers, all for export containers).

create stage ursa."URSA-SNOWFLAKE-IMPORT"
  url='azure://your.url.blob.core.windows.net/ursa-snowflake-import/'
  credentials=(azure_sas_token='sp=racw…’);

Next, grant the appropriate privileges for these stages to the app_role:

grant all privileges on stage ursa."URSA-SNOWFLAKE-IMPORT" to role ursa_app_role;

Verify that you have created the stage with the correct permissions by running @list on the stage from the Snowflake console as the Snowflake role that Ursa Studio adopts permissions from. This might require temporarility granting yourself access to this role, so as to be able to inhabit it in the Snowflake console. If you cannot successfully list the contents of the stage from within the Snowflake console, Ursa Studio will not be able to access these stages.