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:

Contents:

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;

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.

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.

Snowflake Data TypeGoodData LDM Data Type
ARRAYNot supported
BIGINTINT
BINARYNot supported
BOOLEANVARCHAR (5)
BYTEINTINT
CHARVARCHAR (1)
CHARACTERVARCHAR (1)
DATEDATE
DECIMALINT
DECIMAL (precision, scale)DECIMAL (precision, scale)
DOUBLEDECIMAL (12, 2)
DOUBLEPRECISIONDECIMAL (12, 2)
FLOATDECIMAL (12, 2)
FLOAT4DECIMAL (12, 2)
FLOAT8DECIMAL (12, 2)
INTINT
INTEGERINT
NUMBERINT
NUMBER (precision, scale)DECIMAL (precision, scale)
NUMERICINT
NUMERIC (precision, scale)DECIMAL (precision, scale)
OBJECTNot supported
REALDECIMAL (12, 2)
SMALLINTINT
STRINGVARCHAR (128)
TEXTVARCHAR (10_000)
TIMENot supported
TIMESTAMPDATE
TIMESTAMP_LTZDATE
TIMESTAMP_NTZDATE
TIMESTAMP_TZDATE
TINYINTINT
VARBINARYNot supported
VARCHARVARCHAR (128)
VARCHAR (length)VARCHAR (length)
VARIANNot supported
Powered by Atlassian Confluence and Scroll Viewport.