Snowflake Horizon Catalog

The Snowflake Horizon Catalog is an enterprise catalog offering from Snowflake. You can use this to sync Onehouse tables to Snowflake as external Apache Iceberg tables using an external volume.

Cloud Provider Support

• AWS: ✅ Supported
• GCP: ✅ Supported

Setup guide

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

Example scenario

We will sync the following Onehouse table to Snowflake:

• Onehouse table name: 'orders_by_product'
• Onehouse database name: 'orders'

The following will be created in Snowflake if it they do not already exist:

• Snowflake table name: 'orders_by_product'
• Snowflake schema name: 'orders'

Role permissions

The following permissions are required for the role used by the user account for Snowflake 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 the below error while creating the catalog in Onehouse, ensure that the <snowflake_username> has access to the role <snowflake_role_name>.

  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.

  • If required, execute the following command in Snowflake to grant the role to the user:

    GRANT ROLE <snowflake_role_name> TO USER <snowflake_username>;