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. .
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.
The destination database must exist before Structural can connect to it. The user that you provide to 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 Amazon S3 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.
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:
AWS instance profile permissions
Configure the required permissions for Tonic Structural to work with AWS components.
Configuration related to Lambda
If you use Lambda processing, configure the Lambda role and set related environment settings.
Required database permissions
Configure the required permissions for source and destination databases.
Tonic Structural orchestrates the creation, usage, and deletion of several AWS components when it uses Snowflake. The required permissions to do so are taken from the Instance Profile role of the machine that runs Structural's server. This role needs the below permissions.
For both types of Snowflake on AWS data generation, the instance profile role requires Amazon S3 permissions. For the Lambda-based processing, the instance profile role also requires Amazon SQS and Lambda permissions.
Note that these permissions are starting point. Based on your exact AWS setup, you might need to add other permissions. For example, if you use AWS Key Management Service (KMS) on your S3 buckets, then you might need to grant AWS KMS access.
These example policies allow Structural to properly orchestrate jobs in your AWS infrastructure. It assumes that you use default names for objects in AWS, and that your source and destination S3 bucket names begin with the "tonic-" prefix.
For the default data generation, the instance profile requires the following permissions:
The Lambda data generation for Snowflake on AWS requires the following permissions:
For the Lambda version of Snowflake on AWS data generation, you must also configure the AWS Lambda role and related Tonic Structural environment settings.
The AWS Lambda function that Structural sets up requires an AWS role. The name of this role is configured in the following environment setting:
The policy for this role should look like this:
The above policy grants the Lambda function the required access to Amazon SQS, Amazon S3, and CloudWatch.
This policy assumes that the S3 buckets and Amazon SQS queues that are used begin with the tonic- prefix.
After you create the role, you must allow the Lambda service to assume the role.
For the role, the Trust relationships in the AWS IAM role should be configured to look like the following:
Structural allows you to set the following Snowflake-specific environment settings that make it easier to adapt our Snowflake integration into your specific AWS environment. You configure these settings in the Structural worker container.
By default, during each data generation job, Structural creates the database schema for the destination database tables, then populates the database tables based on the workspace configuration.
If you prefer to manage the destination database schema yourself, then set the environment setting TONIC_SNOWFLAKE_SKIP_CREATE_DB to true. You can add this setting manually to the Environment Settings list on Structural Settings.
The environment setting applies to both Snowflake on AWS and Snowflake on Azure.
When TONIC_SNOWFLAKE_SKIP_CREATE_DB is true, then Structural does not create the destination database schema. Before you run data generation, you must create the destination database with the full schema.
During data generation, Structural deletes the data from the destination database tables, except for tables that use Preserve Destination mode. It then populates the tables with the new destination data.