Snowflake

Snowflake is a cloud-based data platform that enables organizations to manage and analyze vast amounts of data efficiently and securely. By integrating Snowflake with Upriver, you can unlock automated data governance, prevent issues at the source, and ensure consistency across your entire data pipeline. Upriver empowers your teams to build trusted, high-quality data that drives business success.

Upriver only supports snowflake in a SaaS deployment, or in a hybrid deployment on a different cloud provider.

Prepare Your Snowflake Environment

To connect Upriver to Snowflake, you need to create specific roles and permissions within Snowflake. This is done using an SQL script provided by Upriver.

Grant Upriver access

Replace the placeholder <UPRIVER_PUBLIC_KEY> with the key provided to you by Upriver.

If you change any of the other parameters (such as the role or user name), please let the Upriver representative assisting you know the values you've set.

-- setup variables for the user/role/warehouse you'll create
-- if you change any of these, let an upriver representative know the values you've used
set user_name = 'UPRIVER_USER';
set role_name = 'UPRIVER_ROLE';
set warehouse_name = 'UPRIVER_WAREHOUSE';

-- change role for user/ role setup
use role accountadmin;

-- create the role for Upriver
create role if not exists identifier($role_name);

-- create the user for Upriver and setup default role/warehouse for it
create user if not exists identifier($user_name)
default_role = $role_name
default_warehouse = $warehouse_name;

-- allow Upriver to log in to the user using an rsa key
alter user identifier($user_name) set RSA_PUBLIC_KEY='<UPRIVER_PUBLIC_KEY>';

-- grant the user access to the role
grant role identifier($role_name) to user identifier($user_name);

-- create the warehouse for Upriver
create warehouse if not exists identifier($warehouse_name)
with
    warehouse_size = 'xsmall',
    warehouse_type = 'standard',
    auto_suspend = 120,
    auto_resume = true,
    initially_suspended = true;
 
-- give Upriver role access to the warehouse
grant usage on warehouse identifier($warehouse_name) to role identifier($role_name);

-- give Upriver role access to query history, monitoring access and tasks
grant imported privileges on database "SNOWFLAKE" to ROLE identifier($role_name);
grant database role USAGE_VIEWER to role UPRIVER_ROLE;
grant monitor execution on account to role identifier($role_name);

Provide access to your data

After you've created the role for Upriver to use, you need to grant the role access to the data you wish to monitor. This section will provide multiple methods to grant Upriver read-only access to your data, please choose one according to your needs.

Provide access to specific schemas

Replace <YOUR_DATABASE_NAME> and <YOUR_SCHEMA_NAME> with the schema you wish to give Upriver access to. If you've used a custom name for the upriver role in it's creation, replace role_name with it as well.

This option uses schema level grants. In snowflake, if a schema has schema level future grants, it ignore database level future grants defined for other roles. If you use database level future grants in your workspace, you should grant access on a database level instead.

-- setup variables for the schema you wish to grant access to
set database_name = '<YOUR_DATABASE_NAME>';
set schema_name = '<YOUR_SCHEMA_NAME>';
set role_name = 'UPRIVER_ROLE';

-- the full name of the schema
set full_schema_name = $database_name || '.' || $schema_name;

-- grant the upriver role access to the schema
grant usage on database identifier($database_name) to role identifier($role_name);
grant usage on schema identifier($full_schema_name) to role identifier($role_name);
grant select on all tables in schema identifier($full_schema_name) to role identifier($role_name);
grant select on future tables in schema identifier($full_schema_name) to role identifier($role_name);
grant select on all views in schema identifier($full_schema_name) to role identifier($role_name);
grant select on future views in schema identifier($full_schema_name) to role identifier($role_name);
grant select on all external tables in schema identifier($full_schema_name) to role identifier($role_name);
grant select on future external tables in schema identifier($full_schema_name) to role identifier($role_name);
grant select on all event tables in schema identifier($full_schema_name) to role identifier($role_name);
grant select on future event tables in schema identifier($full_schema_name) to role identifier($role_name);
grant select on all dynamic tables in schema identifier($full_schema_name) to role identifier($role_name);
grant select on future dynamic tables in schema identifier($full_schema_name) to role identifier($role_name);

Provide read-only access to an entire database

Replace <YOUR_DATABASE_NAME> with the schema you wish to give Upriver access to. If you've used a custom name for the upriver role in it's creation, replace role_name with it as well.

This option uses database level grants. In snowflake, if a schema has schema level future grants, it ignore database level future grants defined for other roles. If you use schema level future grants in your workspace, please refer to the previous section instead.

-- setup variables for the database you wish to grant access to
set database_name = '<YOUR_DATABASE_NAME>';
set role_name = 'UPRIVER_ROLE';

-- grant the upriver role access to the database
grant usage on database identifier($database_name) to role identifier($role_name);
grant usage on all schemas in database identifier($database_name) to role identifier($role_name);
grant usage on future schemas in database identifier($database_name) to role identifier($role_name);
grant select on all tables in database identifier($database_name) to role identifier($role_name);
grant select on future tables in database identifier($database_name) to role identifier($role_name);
grant select on all views in database identifier($database_name) to role identifier($role_name);
grant select on future views in database identifier($database_name) to role identifier($role_name);
grant select on all external tables in database identifier($database_name) to role identifier($role_name);
grant select on future external tables in database identifier($database_name) to role identifier($role_name);
grant select on all event tables in database identifier($database_name) to role identifier($role_name);
grant select on future event tables in database identifier($database_name) to role identifier($role_name);
grant select on all dynamic tables in database identifier($database_name) to role identifier($role_name);
grant select on future dynamic tables in database identifier($database_name) to role identifier($role_name);

Provide access to a shared table

Shared tables permissions work differently than normal tables, and read access can be granted using the following command.

Replace <YOUR_DATABASE_NAME> with the schema you wish to give Upriver access to. If you've used a custom name for the upriver role in it's creation, please replace role_name with it as well.

-- setup variables for the shared database you wish to grant access to
set database_name = '<YOUR_DATABASE_NAME>';
set role_name = 'UPRIVER_ROLE';

-- grant the upriver role access to the shared database
grant imported privileges on database identifier($database_name) to role identifier($role_name);

Limiting access by IP

If you're using network policies to limit access to your snowflake, you'll need to add a policy to allow Upriver access. In a hybrid deployment, the address will be determined based on your deployment. You may contract an Upriver representative for help figuring out the IP used.

For a SaaS deployment, the IP used will be:

Cloud Provider	IP address
AWS	18.215.205.156, 184.72.146.68
GCP	34.148.202.141

For more info on network roles in snowflake, please refer to snowflake's documentation.

Executing SQL scripts

1. Log in to your Snowflake account using your preferred client (e.g., Snowflake Web Interface, SnowSQL, or a compatible tool).

   1. You will need the accountadmin permission to run our scripts.

2. Replace the placeholders in the script with your values.

3. Execute the script to create the required roles, permissions, and users.

---

Configure the Data Source in Upriver app

After completing the setup in Snowflake, configure the data source in Upriver.

1. Configure a new Data Source - Data Source Configuration.

2. Fill in the connection details:

   • Database: Enter the name of the Snowflake database configured in Step 1.

   • Schema: Specify the schema within the database that Upriver should access.

   • Table: Provide the table name you want Upriver to connect to or monitor.

---

Monitor and Manage Your Data

With the Snowflake datasource configured, Upriver will begin tracking data activity, identifying potential issues, and providing insights to ensure the quality and consistency of your data pipeline.