Snowflake

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
set database_name = 'DATABASE_CHANGE_ME'; -- TO REPLACE VALUE, database you want monitored

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 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);

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

📘

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

18001800

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