Working with a Snowflake warehouse connection

Applies to: Kyvos Enterprise Kyvos Cloud (SaaS on AWS) Kyvos AWS Marketplace

Kyvos Azure Marketplace Kyvos GCP Marketplace Kyvos Single Node Installation (Kyvos SNI)

You can set up a data connection to connect to your Snowflake data warehouse on AWS and Azure.

Note

From Kyvos 2024.1 onwards, you can include the username in the Snowflake query as a comment in the SQL statement. To do this, you need to set the value of the kyvos.connection.sql.addcomment to ‘True’ in the Snowflake connection.
If you have enabled Network policies in Snowflake, you must use a Databricks workspace with the option Deploy Azure Databricks workspace with Secure Cluster Connectivity (No Public IP) as Yes. Additionally, add the NAT Gateway Public IP of Databricks VNet to the Network policies of Snowflake.
From the Kyvos 2023.1 release onwards, for Kyvos data security, if you want to use external security configured at the cluster level, such as user impersonation to enforced role and permissions while viewing Snowflake semantic model, you must change the property value from 2 to 1 in the QUERYING_SECURITY_LEVEL property (biserver.properties).
You can create multiple Snowflake connections for raw data querying. The connections are available on the semantic model designer page, where you can select the connection to be used for a particular semantic model.
See the Working with non-materialized or raw data semantic models section for more details.

Prerequisites for creating a Snowflake connection

The Access role used for creating the connection must have the following permissions assigned to your Snowflake staging database.
Grant usage on the database:
GRANT USAGE ON DATABASE <database> TO ROLE <role>;
Grant usage on the schema:
GRANT USAGE ON SCHEMA <database>.public TO ROLE <role>;
Grant create stage on the public schema:
GRANT CREATE STAGE ON SCHEMA <database>.public TO ROLE <role>;
In OAuth token-based authentication, you need an access token to access Snowflake resources and a refresh token to be used to get a new access token in case it expires.
To be able to fetch tokens from any Snowflake server, the Kyvos application should be registered with the target Snowflake server and this configuration must be done by the Snowflake Admin user (outside of Kyvos). Once this is done, Admin gets client_id, client_secret, authorization URL, and token URL. You need these property values when creating a Kyvos connection to enable the Kyvos server to fetch tokens from the Snowflake server.
If you have deployed VPC with Internet Gateway, then you must assign an elastic IP to the BI Server.

To set up or view a Snowflake warehouse connection, perform the following steps.

From the Toolbox, click Connections.
From the Actions menu ( ⋮ ) click Add Connection.
Enter a name or select it from the Connection list.
After you finish configuring the settings using the table shown below, click the Test button from the top left to validate the connection settings.
If the connection is valid, click the Save button.
To refresh connections, click the Actions menu ( ⋮ ) at the top of the Connections column and select Refresh.

Connection Details in Kyvos for Sanity Suite

To create Snowflake Warehouse connection for sanity suite, perform the following steps.

Parameter	Description

Parameter	Description
Name	Enter SanityConnection as a name.
Category	Select the Warehouse option.
Provider	Select the Snowflake option.
Authentication Type	Select the Snowflake option.
Use As Source	Select this checkbox.
Is Default SQL Engine	To enable the connection for raw data, click the Is Default SQL Engine checkbox to set this connection to run the default SQL engine.
Properties	Click Properties to view or set properties. You need to add a property to make the connection visible: Name: kyvos.connection.isRead Value: true