Data Engine on Your Warehouse - Snowflake

👤 This documentation page is intended for customers who are connecting their own Snowflake database into Sisense as a writable destination. If you have a Redshift database for this purpose, please see the documentation page here.

Sisense normally only requires read access to your warehouses. In order to use Data Engine on Your Warehouse (DEYW), Sisense also requires read-write access to parts of your warehouse. To avoid any accidental issues, it's important that you carefully grant write permissions to only the necessary resources. See the Acknowledgements section at the bottom of this page for more information.

The following instructions will walk through establishing a Snowflake database connection via a read-user, and then defining and applying a write-access user.

1. Configure Casing in Identifiers

Within the Snowflake account, verify that identifiers will ignore case sensitivity. This can be done by applying the parameter discussed here:‍

1. https://docs.snowflake.net/manuals/sql-reference/parameters.html#label-quoted-identifiers-ignore-cas

2. Prepare an Uncached Space

“Data Engine on Your Warehouse” will only work on sites/spaces that do not have a cached database. A quick way to check whether a space is cached is to see if the “Cache” tab is available underneath in the “Settings” gear icon:

If the site is not cached, then proceed to Step #3.

If the site has the Sisense cache, then a new space must be created. This new space will be used for all subsequent steps. By default, the new space will cache upon connecting a database. After completing Step #3, please contact Sisense Support to un-cache that space. In the interim, you may proceed with the following steps.

3. Connect the Snowflake Database (Read User)

If the Snowflake database has not already been connected within the Sisense account, the following documentation page walks through how to add it:

1. https://doc.periscopedata.com/article/connecting-snowflake
   

The database should be connected in the same space verified from Step #2. The database user must have read-access at a minimum. Sisense recommends that this be the same database that the user will query and build charts against.

Note: If a Snowflake database had previously been connected by following these instructions, it may be worthwhile to perform them once again. The documentation has since been updated to reflect a change in the naming conventions supported by “Data Engine on Your Warehouse”. In particular, the user role must match exactly with the username.

4. Prepare an Admin User

In order to allow view materialization from within Sisense, it is necessary to configure a database user and role with the proper level of write-access (Step #5).

The following credentials are required to properly create and grant permissions to such a user/role:

1. Admin username (e.g. sisense_admin)
2. Admin password
3. Admin role (must be the same as the admin username, e.g. sisense_admin)
4. Read role (must be the same as the read username, e.g. sisense_read)
5. Database name (the same name as the origin Snowflake database connected in Step #3)

Do not proceed unless the above credentials are verified and available for use in Step #5.

5. Create and Permission Write User/Role

The following commands must be run via an admin with the credentials from Step #4. When performing these commands, it is important to verify that the password is secure for your organization.

Create WRITE Access User and ROLE

CREATE ROLE <sisense_write>;
CREATE USER <sisense_write> PASSWORD = '<password>' DEFAULT_ROLE=<sisense_write> DEFAULT_WAREHOUSE=<warehouse>;

Grant WRITE Access User Privileges

GRANT ROLE <sisense_write> TO USER <sisense_write>;

GRANT OWNERSHIP ON USER <sisense_write> TO ROLE SYSADMIN;
GRANT OWNERSHIP ON ROLE <sisense_write> TO ROLE SYSADMIN;

GRANT USAGE ON DATABASE <database> TO ROLE <sisense_write>;
GRANT USAGE ON WAREHOUSE <warehouse> TO ROLE <sisense_write>;

CREATE SCHEMA <database>.periscope_views;
GRANT OWNERSHIP ON SCHEMA <database>.periscope_views TO ROLE <sisense_write>;

Additionally, it is highly recommended to grant the write-user both "usage" and "select" access to the same set of schemas and tables that the read-user will have access to.

Grant READ Access User Privileges

The baseline read-only privileges should have already been configured for the Read User defined in Step 3. Now that the "periscope_views" schema has been created, there will be one additional command to run:

GRANT USAGE ON SCHEMA <database>.periscope_views TO ROLE <sisense_read>;

6. Apply the Write User

Click on the “Settings” gear icon in the lower-left corner of Sisense, and navigate to the same “Database” page you used to connect the database in Step #3. If there is a drop-down available, ensure that the database from Step #3 has been selected.

At the bottom of the form, there should be a section to input the “Warehouse Admin Credentials”. This will be the username and password configured in Step #5. Fill in the text boxes with the correct credentials and hit the “Submit” button.

7. (Optional) Manage Permissions to “periscope_views”

Upon completion, Sisense will be reading/writing to a schema called “periscope_views” for any materialized views. For additional levels of permissioning on this schema, please see the “ON FUTURE” parameter that can be used when running the GRANT commands: ‍

https://docs.snowflake.net/manuals/user-guide/security-access-control-considerations.html#simplifying-grant-management-using-future-grants.

Note: In order to successfully grant these future permissions, the grant commands must be run through either the AccountAdmin or SysAdmin.

Acknowledgements

Capitalized terms used herein shall have the meanings assigned to them in database language.

“DEYW Service”: Sisense’s Data Engine on Your Warehouse (“DEYW”) Service allows and offers a User, in addition to, connecting an origin Database where they can only QUERY that database; bring User’s choice of warehouse, where User can leverage Sisense’s technology to extract tables from other sources, and MATERIALIZE view into said warehouse.

“USER”: Any entity, including but not limited to, a prospect or an existing customer of Sisense allowing Sisense access as set forth herein to perform the DEYW Service.

“Sisense”: Sisense, Inc. The Service Provider for DEYW Service.

“READ”: The ability to DISPLAY, LIST, VIEW.

‘WRITE”: The ability to modify, to the extent that such command shall permit but are not limited to, ADD, CREATE, DELETE, RENAME, UPDATE.

As expressly set forth herein, User grants Sisense READ, and WRITE permissions in order to perform the DEYW Service. This permission allows Sisense to READ existing schema, tables, and rows and allows Sisense to WRITE a schema, WRITE tables within that schema, and WRITE to those tables within User’s data warehouse of choice.

By virtue of executing the aforementioned instructions and providing Sisense READ, WRITE access to your warehouse, you expressly accept the DEYW Service governed by Sisense for Cloud Data Team's Terms & Conditions.

‍