HomeChangelogBlog

Snowflake

Suggest Edits

You can integrate with Snowflake by following these steps:

  1. Create the service account: a dedicated read-only role, user, and warehouse (the warehouse is optional but recommended)
  2. Grant privileges to databases to be discovered and monitored
  3. Create the Snowflake connection using Sifflet's integrations page

1- Create the service account

To create the user, role and warehouse needed, you will need to run the following SQL snippet: 

-- Set all your variables values:  role, user, password, warehouse and database
set role_name = 'ROLE_CHANGE_ME'; -- TO REPLACE VALUE, make sure this role is not already created
set user_name = 'USER_CHANGE_ME'; -- TO REPLACE VALUE, make sure this user is not already created
set user_password = 'password_change_me'; -- TO REPLACE VALUE
set warehouse_name = 'SIFFLET_WAREHOUSE'; -- TO REPLACE VALUE

use role accountadmin; -- needed to create user/role

-- Create Sifflet Role
create role if not exists identifier($role_name);
grant role identifier($role_name) to role SYSADMIN;

-- Create Sifflet User
create user if not exists identifier($user_name)
password = $user_password
default_role = $role_name
default_warehouse = $warehouse_name;
grant role identifier($role_name) to user identifier($user_name);

-- Create a dedicated warehouse
create warehouse if not exists identifier($warehouse_name)
warehouse_size = xsmall
warehouse_type = standard
auto_suspend = 5
auto_resume = true
initially_suspended = true;

-- Grant the role access to the warehouse
grant USAGE,MONITOR on warehouse identifier($warehouse_name) to role identifier($role_name);

-- Grant access to query history
grant imported privileges on database "SNOWFLAKE" to role identifier($role_name);

❗

Snowflake is case sensitive

Please create the Snowflake user with the following recommendations:

  • use an uppercase role_name, make sure the role_name is not already created
  • use an uppercase user_name, make sure the user_name is no already created
  • use the same case for the warehouse_name, database_name and schema_name as the ones in your Snowflake instance

2- Grant privileges to databases to be discovered and monitored

Run the following SQL snippet for every Schema you want to connect with Sifflet: 

-- Read-only access to specific schemas
set database_name = 'DATABASE_CHANGE_ME'; -- TO REPLACE VALUE, database you want monitored
set schema_name = 'DATABASE_NAME.SCHEMA_NAME'; --TO REPLACE VALUE

grant USAGE on database identifier($database_name) to role identifier($role_name);
grant USAGE on schema identifier($schema_name) to role identifier($role_name);
grant SELECT on all tables in schema identifier($schema_name) to role identifier($role_name);
grant SELECT on future tables in schema identifier($schema_name) to role identifier($role_name);
grant SELECT on all external tables in schema identifier($schema_name) to role identifier($role_name);
grant SELECT on future external tables in schema identifier($schema_name) to role identifier($role_name);
grant SELECT on all views in schema identifier($schema_name) to role identifier($role_name);
grant SELECT on future views in schema identifier($schema_name) to role identifier($role_name);
grant SELECT on all streams in schema identifier($schema_name) to role identifier($role_name);
grant SELECT on future streams in schema identifier($schema_name) to role identifier($role_name);

3- Create the Snowflake connection using Sifflet's integrations page

To create the Snowflake secret, follow the below steps:

  • In "Integration" --> submenu "Secrets", create a new secret
  • In the "Secret" area, copy-paste the below text and replace it with the correct username and password:
{
  "user": "<username>",
  "password": "<password>"
}

To connect to Snowflake on Sifflet, you will need three items:

  • The connection details: your Account Identifier, the Warehouse name, your Database name, and Schema.
  • the secret which corresponds to the username and password you previously chose.
  • the frequency: how often you want the information to be refreshed

📘

To retrieve your account identifier:

  1. Click the arrow down at the bottom left corner of the screen.
  2. Hover over the middle part of the widget
  3. Click the link icon

This will copy your Snowflake console URL in your clipboard

The Account Identifier is the string of characters that precedes "snowflakecomputing.com" in your Snowflake console URL.
For instance:

📘

Several account identifiers for one Snowflake instance

There might be several ways to access your Snowflake environment (more details on Snowflake's docs). For instance: https://<orgname>-<account_name>.snowflakecomputing.com and https://<accountlocator>.<region>.<cloud>.snowflakecomputing.com
If you used one specific URL when connecting other tools - such as a BI tool - to your Snowflake instance, please use the same one when adding the data source in Sifflet.

You can refer to this page on adding a data source in Sifflet.

Updated 5 months ago