Snowflake

Description

Snowflake is a data analytics platform that enables users to discover, manage and query data. Using Onetable support, Onehouse user's can ingest and store data compatible with Iceberg format. Tables such created will get automatically synced to Snowflake.

Setup guide

1. Enter a Name to identify the data catalog in Onehouse
2. Select Snowflake as the type
3. Snowflake Account Identifier in the format: <org-name>-<account-name>
4. Warehouse name in Snowflake. Create a warehouse in Snowflake if it doesn't already exist. Refer to the Snowflake docs for warehouse creation.
5. Secret ARN, ARN of the secret storing the username and password, expected to be stored in the same account as dataplane. You are required to enable 'Bring Your Own Secret' option as described here. Save the username/password in the format described in the 'JSON format for storing secrets' section below.
6. Snowflake role name to be used for connection. If you are creating a new role for this connection, ensure that the role has the required permissions as described in the 'Role permissions' section below. To create a new role, refer to the Snowflake docs
7. Snowflake DB name where all the tables will be created. To create a database in Snowflake, refer to the Snowflake docs
8. External Volume to be used to create Iceberg Tables in Snowflake. Refer Snowflake docs for external volume creation.
9. Name of the Catalog Integration to be used. Refer Snowflake docs for catalog integration creation, particularly Iceberg metadata in object storage section.

Example scenario

A Onehouse Table with 'Table Name'='orders_by_product' in Onehouse Database with 'Database Name'='orders' will get synced in Snowflake as 'Schema'='orders' and 'Table'='orders_by_product' in the Snowflake Database provided in catalog creation form. Schema named 'orders' will be created in Snowflake if doesn't already exist.

Role permissions

Following permissions are required for the role used by the user account for connection:

GRANT USAGE ON external volume <volume_name> TO ROLE <snowflake_role_name>;
GRANT CREATE INTEGRATION ON ACCOUNT TO ROLE <snowflake_role_name>;
GRANT USAGE ON INTEGRATION <catalog_integration_name> TO ROLE <snowflake_role_name>;
GRANT USAGE ON WAREHOUSE <snowflake_warehouse_name> TO ROLE <snowflake_role_name>;
GRANT USAGE ON DATABASE <snowflake_db_name> TO <snowflake_role_name>;
GRANT CREATE SCHEMA ON DATABASE <snowflake_db_name> TO <snowflake_role_name>;
GRANT MONITOR ON ALL SCHEMAS IN DATABASE <snowflake_db_name> TO <snowflake_role_name>;
GRANT MONITOR ON FUTURE SCHEMAS IN DATABASE <snowflake_db_name> TO <snowflake_role_name>;
GRANT CREATE ICEBERG TABLE ON FUTURE SCHEMAS IN DATABASE <snowflake_db_name> TO <snowflake_role_name>;
GRANT ALL PRIVILEGES ON FUTURE ICEBERG TABLES IN DATABASE <snowflake_db_name> TO <snowflake_role_name>;

JSON format for storing secrets

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

For example:

{
"username":"john",
"password":"johnspassword"
}

Troubleshooting

• If you run into INVALID_ARGUMENT: Failed to connect to Snowflake. java.io.UncheckedIOException: Failed to deserialize while creating the catalog in Onehouse, ensure that the secret ARN is stored in the same account and region as the linked cloud account and the username/password are stored in the correct JSON format as described above.
• If you run into Metastore validation failed. Error: INVALID_ARGUMENT: Failed to connect to Snowflake. Role 'AJAX_ICEBERG_INTEGRATION_ROLE' specified in the connect string is not granted to this user. Contact your local system administrator, or attempt to login with another role, e.g. PUBLIC. while creating the catalog in Onehouse, ensure that the <snowflake_username> has access to the role <snowflake_role_name>.
  • If required, execute the following command in Snowflake to grant the role to the user: GRANT ROLE <snowflake_role_name> TO USER <snowflake_username>;