Before you create a Snowflake on Azure workspace

Configuring the source and destination users

Tonic.ai recommends that you create separate accounts for the source and destination databases, even if the databases are in the same Snowflake account. This allows each account to have the minimum permissions needed in the source and destination databases.

If you use the same account, then you must combine the relevant permissions for the source and destination databases into a single role. Snowflake only allows each account to have a single active primary role.

User permissions on the source database

The below permissions create a role with the necessary permissions to act as the source database user. A user is then created and assigned into that role.

The below permissions give read-only data access to a specific database on all current and future schemas, tables, and sequences.

It also grants the role access to a specified warehouse.

CREATE ROLE TONIC_SOURCE_DATABASE_ROLE;

GRANT USAGE ON WAREHOUSE <warehouse name> TO ROLE TONIC_SOURCE_DATABASE_ROLE;

GRANT USAGE ON DATABASE <source database name> TO ROLE TONIC_SOURCE_DATABASE_ROLE;

GRANT USAGE ON ALL SCHEMAS IN DATABASE <source database name> TO ROLE TONIC_SOURCE_DATABASE_ROLE;
GRANT USAGE ON FUTURE SCHEMAS IN DATABASE <source database name> TO ROLE TONIC_SOURCE_DATABASE_ROLE;

GRANT SELECT ON FUTURE TABLES IN DATABASE <source database name> TO ROLE TONIC_SOURCE_DATABASE_ROLE;
GRANT SELECT ON ALL TABLES IN DATABASE <source database name> TO ROLE TONIC_SOURCE_DATABASE_ROLE;

GRANT USAGE ON FUTURE SEQUENCES IN DATABASE <source database name> TO ROLE TONIC_SOURCE_DATABASE_ROLE;
GRANT USAGE ON ALL SEQUENCES IN DATABASE <source database name> TO ROLE TONIC_SOURCE_DATABASE_ROLE;

CREATE USER <user name> password='<password>' default_role = TONIC_SOURCE_DATABASE_ROLE default_warehouse = <warehouse name>;

GRANT ROLE TONIC_SOURCE_DATABASE_ROLE TO USER <user name>;


-- Structural requires this parameter to be set at either the account or user level.
ALTER USER <user name> set QUOTED_IDENTIFIERS_IGNORE_CASE = false;

User permissions on the destination database

The destination database must exist before Structural can connect to it. The user that you provide to Tonic Structural for connecting to the destination database should be the owner of the database and of all of the objects in the database.

We suggest that you first create a specific Structural destination database user. Then create the destination database from that user's account. If you create the database with another account such as ACCOUNTADMIN, then you must transfer ownership of the database and all of its objects to the new account.

As part of a Structural data generation job, this user must be able to:

• DROP and then create schemas on the output database

• Copy data from Azure Blob Storage into tables in the database

The below permissions create a role with the necessary permissions to act as the destination database user. A user is then created and assigned into that role.

The following permissions gives full access to the destination database. They also grant the role access to a specified warehouse. To accommodate a situation where the database was created by another user such as ACCOUNTADMIN, this includes the required transfer of ownership to the role.

CREATE ROLE TONIC_DESTINATION_DATABASE_ROLE;

GRANT OWNERSHIP ON DATABASE <destination database name> TO TONIC_DESTINATION_DATABASE_ROLE;
GRANT USAGE ON WAREHOUSE <warehouse name> TO TONIC_DESTINATION_DATABASE_ROLE;

CREATE USER <user name> password='<password>' default_role = TONIC_DESTINATION_DATABASE_ROLE default_warehouse = <warehouse name>;
GRANT ROLE TONIC_DESTINATION_DATABASE_ROLE TO USER <user name>;

-- If the destination database was already created with another user, 
-- existing schemas (including default PUBLIC) must have ownership transferred
-- to the Structural user role.
GRANT OWNERSHIP on SCHEMA <destination database name>.PUBLIC to TONIC_DESTINATION_DATABASE_ROLE;
GRANT OWNERSHIP on SCHEMA <destination database name>.<additional schemas> to TONIC_DESTINATION_DATABASE_ROLE;


-- Structural requires this parameter to be set at either the account or user level.
ALTER USER <user name> set QUOTED_IDENTIFIERS_IGNORE_CASE = false;

Additional user configuration

Structural requires that the parameter QUOTED_IDENTIFIERS_IGNORE_CASE = false at either the account or user level.

To set it at the user level, run:

ALTER USER <user name> set QUOTED_IDENTIFIERS_IGNORE_CASE = false;

Setting the access key for the Azure Blob Storage account

Structural uses an Azure storage account to load and unload data to and from Azure Blob Storage. You provide the storage account name in the workspace configuration.

You provide the access key for the storage account as the value of the environment setting TONIC_AZURE_BLOB_STORAGE_ACCOUNT_KEY. See Configuring environment settings.

You can obtain the access key from the storage account portal under Access keys. Structural uses this key to authorize API calls when it unloads and loads data to and from Azure Blob Storage.