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:
  • For password-based authentication: copy-paste the below text and replace it with the correct username and password:
  JSON

  {
    "user": "<username>",
    "password": "<password>"
  }
  

  • For key-pair authentication: copy-paste the below text and replace it with the correct username and unencrypted private key:
  JSON

  {
    "user": "<username>",
    "private_key": "<private_key>"
  }
  

📘

Key-pair authentication

Sifflet supports key pair authentication for Snowflake, offering enhanced authentication security as an alternative to using a username and password.

To use key pair authentication, create the key pair by following the guide provided by Snowflake and then use the unencrypted private key when adding the credentials to Sifflet as mentioned above.

When generating the private key, make sure to add the -nocrypt option to your command.

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: corresponds to the username and password or key-pair 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:

• for "https://abcd123.eu-west-2.snowflakecomputing.com", your Account Identifier will be "abcd123.eu-west-2"
• for "https://acme-marketing_test_account.snowflakecomputing.com", your Account Identifier will be "acme-marketing_test_account"
• for https://app.snowflake.com/eu-west-2.aws/abcd123, your Account Identifier will be "abcd123.eu-west-2"

📘

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.

FAQ

What tables are accessed by Sifflet, and how are they used?

Sifflet accesses various tables in your Snowflake account to provide a complete user experience. Restricting access to some of those databases/schemas/tables might reduce the number of functionalities Sifflet provides.

I refreshed the Snowflake sources on Sifflet but some of my latest SQL transformations, tags, etc. do not appear. Why?

Snowflake declares a range data latency times due to the process of extracting the data from Snowflake’s internal metadata store.

Overall, for ACCOUNT_USAGE tables, you should expect a latency of up to 3 hours from Snowflake. For more detailed information, please refer to the "Data latency" section of the Snowflake Account Usage documentation..