GoodData-Snowflake Integration Details

When setting up direct data distribution from your Snowflake data warehouse, pay attention to the considerations and best practices listed in this article.

This article is applicable to all use cases of GoodData and Snowflake integration:

• Integrate a Data Source for a Data Warehouse (a getting-started guide based on sample data)
• Integrate Data Warehouses Directly to GoodData based on an Existing LDM

Default auto_resume Mode

Configure your Snowflake data warehouse to automatically start if it was suspended. Otherwise, data loading may fail.

To do so, set auto_resume mode as default for your warehouse (see https://docs.snowflake.com/en/user-guide/warehouses-overview.html#auto-suspension-and-auto-resumption).

Run the following command in your warehouse:

ALTER WAREHOUSE {warehouse_name} SET AUTO_RESUME = TRUE;

Authentication Methods

GoodData supports two authentication methods for connecting to Snowflake:

1. Basic Authentication - Using username and password
2. Key-pair Authentication - Using a database user, private key, and optional passphrase

User Access Rights

We recommend that you create a dedicated user and user role for integration with the GoodData platform.

Steps:

1. Create a user role and grant the following access rights to it:

   GRANT USAGE ON WAREHOUSE {warehouse_name} TO ROLE {role_name};
   GRANT USAGE ON DATABASE {database_name} TO ROLE {role_name};
   GRANT USAGE ON SCHEMA {database_name}.{schema_name} TO ROLE {role_name};
   GRANT CREATE STAGE ON SCHEMA {database_name}.{schema_name} TO ROLE {role_name};
   GRANT SELECT ON ALL TABLES IN SCHEMA {database_name}.{schema_name} TO ROLE {role_name};
   GRANT SELECT ON FUTURE TABLES IN SCHEMA {database_name}.{schema_name} TO ROLE {role_name};
   GRANT SELECT ON ALL VIEWS IN SCHEMA {database_name}.{schema_name} TO ROLE {role_name};
   GRANT SELECT ON FUTURE VIEWS IN SCHEMA {database_name}.{schema_name} TO ROLE {role_name};
   

2. Create a user and grant it with the user role:

   GRANT ROLE {role_name} TO USER {user_name};
   

3. Make the user role default for the user:

   ALTER USER {user_name} SET DEFAULT_ROLE={role_name};
   

   If you do not make the user role default, enter the role as part of the connection URL when creating a Data Source:

   jdbc:snowflake://{account_name}.snowflakecomputing.com?role={role_name}
   

   Otherwise, GoodData may not be able to access your Snowflake objects.

Key-pair Authentication Setup

For enhanced security, you can use key-pair authentication instead of username and password. This method requires generating an RSA key pair and associating the public key with your Snowflake user.

Steps:

1. Generate an RSA private key (minimum 2048-bit):

   openssl genrsa -out rsa_key.pem 2048
   

2. Generate the corresponding public key:

   openssl rsa -in rsa_key.pem -pubout -out rsa_key.pub
   

3. (Optional) If you want to encrypt the private key with a passphrase:

   openssl genrsa -aes256 -out rsa_key.pem 2048
   

4. Assign the public key to your Snowflake user. Remove the header and footer lines from the public key file and concatenate the remaining lines into a single line:

   ALTER USER {user_name} SET RSA_PUBLIC_KEY='{public_key_string}';
   

   The public key string should not include the -----BEGIN PUBLIC KEY----- and -----END PUBLIC KEY----- lines, and all line breaks should be removed.

5. When creating a Data Source in GoodData, select Key-pair authentication and provide:

   • Database user: Your Snowflake username
   • Private key: Enter the private key exactly as generated, including the prefix (-----BEGIN PRIVATE KEY-----), suffix (-----END PRIVATE KEY-----), and line breaks
   • Pass phrase: The passphrase used to encrypt the private key (if applicable)

For more information about key-pair authentication in Snowflake, see the Snowflake documentation.

Object Names

Case Sensitivity

In GoodData, names of objects such as tables and views are not case-sensitive. If you use case sensitivity as a distinctive function for your Snowflake objects (for example, EMPLOYEES and employees), rename one of the objects before you use them in the data warehouse schema or the Output Stage.

Special Characters

Special characters in object names are not supported.

Data Types

The Snowflake data warehouse provides a wide range of data types. During mapping the Snowflake schema and the GoodData logical data model (LDM), data types are automatically converted from a Snowflake data type to a GoodData LDM data type. Some columns may be ignored because their data type is not supported within GoodData or their type may lead to performance issues. If you want to prevent automatic changes, update the schema manually.