# Configuring Snowflake workspace data connections {% hint style="info" %} Snowflake is phasing out support for using a simple username and password for authentication. For more information, go to the [Snowflake documentation](https://docs.snowflake.com/en/user-guide/security-mfa-rollout#deprecation-timeline). We strongly recommend that you use key pair authentication for all database connections. {% endhint %} {% hint style="info" %} Structural supports Snowflake on AWS and Azure Blob Storage. If you use Snowflake on Google Cloud Platform (GCP), contact Tonic.ai. {% endhint %} In the workspace configuration: 1. Under **Connection Type**, select **Snowflake.** 2. In the **Source Settings** section, under **Snowflake Type:** * To connect to a Snowflake database on AWS, click **AWS**. * To connect to a Snowflake database on Azure, click **Azure**. The workspace configuration for AWS and Azure is mostly the same, except that the configuration for temporary file storage is slightly different: * [AWS](#aws-setting-the-storage-location-for-temporary-files) * [Azure](#snowflake-azure-temp-file-storage) ## Connecting to the source database In the **Source Settings** section, provide the details for the connection to the source database. ### Providing the connection details To connect to the source database, you can either: * Populate the connection fields. * Use a connection string. You can also use key pair authentication instead of a password. #### **Populating the connection fields** By default, **Use connection string** is off, and you provide the connection values in the individual fields: 1. In the **Server** field, provide the server where the database is located.\ \ You must provide the full path to the server. `https://` is optional.\ \ So the format of the server value can be either: * `..snowflakecomputing.com` * `https://..snowflakecomputing.com` For example: `abc123456.us-east-1.snowflakecomputing.com` or `https://abc123456.us-east-1.snowflakecomputing.com` 2. In the **Database** field, provide the name of the database. 3. In the **Username** field, provide the username for the account to use to connect to the database. 4. For **Password**, you can either specify the password manually or, if secrets managers are available, you can [select a secret name from a secrets manager](https://docs.tonic.ai/app/workspace/managing-workspaces/workspace-configuration-settings/secrets-manager/selecting-a-secrets-manager-secret). #### **Using a connection string** To use a connection string to connect to the source database: 1. Toggle **Use connection string** to the on position. 2. In the **Connection String** field, provide the connection string. 3. For **Password**, you can either specify the password manually or, if secrets managers are available, you can [select a secret name from a secrets manager](https://docs.tonic.ai/app/workspace/managing-workspaces/workspace-configuration-settings/secrets-manager/selecting-a-secrets-manager-secret). The connection string uses the following format: {% code overflow="wrap" %} ``` account=;host=..snowflakecomputing.com;user=;db= ``` {% endcode %} ### Using key pair authentication for the connection Instead of providing a password, you can use key pair authentication. To do this: 1. Toggle **Use Key Pair Authentication** to the on position. 2. Expand the **Key Pair Authentication Settings**. 3. For **RSA Private Key**, click **Browse**, then select the key file. 4. If the key is encrypted, then in the **Encrypted Key Passphrase** field, provide the passphrase to use to decrypt the key. ### Indicating whether to trust the server certificate To trust the server certificate, and ignore the certificate authority's revocation list, toggle **Trust Server Certificate** to the on position. This option can be useful when your Tonic Structural instance cannot connect to the certificate authority. ### Enabling a proxy connection You can use a proxy server to connect to the source database. {% hint style="info" %} When you use a connection string to connect to the source database, Structural automatically adds the configured proxy connection parameters to the connection string. If you manually include proxy connection parameters in the connection string, and also configure the proxy connection settings, the connection string will have duplicate proxy connection parameters. We recommend that you use the configuration fields to enable the proxy connection, and do not include proxy connection parameters in the connection string. {% endhint %} To use a proxy server to connect to the source database: 1. Toggle **Enable proxy connection** to the on position. 2. In the **Proxy Host** field, provide the host name for the proxy connection. 3. In the **Proxy Port** field, provide the port for the proxy connection. 4. Optionally, in the **Proxy User** field, provide the name of the user for the proxy connection. 5. If you provide a proxy user, then in the **Proxy Password** field, provide the password for the specified user. 6. Optionally, in the **Non-Proxy Hosts** field, provide the list of hosts for which to bypass the proxy server and connect to directly. Use a pipe symbol (`|`) to separate the host names. For example, `host1|host2|host3`. You can also use an asterisk (`*`) as a wildcard. For example, to connect directly to all hosts with host names that start with myhost, use `myhost*`. ### Limiting the included schemas By default, the source database includes all of the schemas. To specify a list of specific schemas to either include or exclude: 1. Toggle **Limit Schemas** to the on position. 2. From the filter option dropdown list, select whether to include or exclude the listed schemas. 3. In the field, provide the list of schemas to either include or exclude. Use commas or semicolons to separate the schemas. Do not exclude schemas that are referred to by included schemas, unless you create those schemas manually outside of Structural. ### Testing the source connection To test the connection to the source database, click **Test Source Connection**. ## Connecting to the destination database In the **Destination Settings** section, you specify the connection information for the destination database. If the destination database is in the same location as the source database, then you can copy the connection and authentication details from the source database. The copied details include the proxy connection configuration. If the destination database is in a different location, then you can either: * Populate the connection fields. * Use a connection string. You can also use key pair authentication instead of a password. ### Copying the source database connection details To copy the connection details from the source database: 1. Click **Copy Settings from Source**. 2. For **Password**, you can either specify the password manually or, if secrets managers are available, you can [select a secret name from a secrets manager](https://docs.tonic.ai/app/workspace/managing-workspaces/workspace-configuration-settings/secrets-manager/selecting-a-secrets-manager-secret). 3. To test the connection to the destination database, click **Test Destination Connection**. ### Providing destination database connection details If you do not copy the details from the source database, then you can either populate the connection fields or use a connection string. #### **Populating the connection fields** By default, **Use connection string** is off, and you provide the connection values in the individual fields: 1. In the **Server** field, provide the server where the database is located.\ \ You must provide the full path to the server. The `https://` is optional.\ \ So the format of the server value can be either: * `..snowflakecomputing.com` * `https://..snowflakecomputing.com` For example: `abc123456.us-east-1.snowflakecomputing.com` or `https://abc123456.us-east-1.snowflakecomputing.com` 2. In the **Database** field, provide the name of the database. 3. In the **Username** field, provide the username for the account to use to connect to the database. 4. For **Password**, you can either specify the password manually or, if secrets managers are available, you can [select a secret name from a secrets manager](https://docs.tonic.ai/app/workspace/managing-workspaces/workspace-configuration-settings/secrets-manager/selecting-a-secrets-manager-secret). #### **Using a connection string** To use a connection string to connect to the destination database: 1. Toggle **Use connection string** to the on position. 2. In the **Connection String** field, provide the connection string. 3. For **Password**, you can either specify the password manually or, if secrets managers are available, you can [select a secret name from a secrets manager](https://docs.tonic.ai/app/workspace/managing-workspaces/workspace-configuration-settings/secrets-manager/selecting-a-secrets-manager-secret). The connection string uses the following format: {% code overflow="wrap" %} ``` account=;host=..snowflakecomputing.com;user=;db= ``` {% endcode %} ### Using key pair authentication for the connection Instead of providing a password, you can use key pair authentication. To do this: 1. Toggle **Use Key Pair Authentication** to the on position. 2. Expand the **Key Pair Authentication Settings**. 3. For **RSA Private Key**, click **Browse**, then select the key file. 4. If the key is encrypted, then in the **Encrypted Key Passphrase** field, provide the passphrase to use to decrypt the key. ### **Testing the destination database connection** To test the connection to the destination database, click **Test Destination Connection**. ### Indicating whether to trust the server certificate To trust the server certificate, and ignore the certificate authority's revocation list, toggle **Trust Server Certificate** to the on position. This option can be useful when your Structural instance cannot connect to the certificate authority. ### **Enabling a proxy connection** You can use a proxy server to connect to the destination database. {% hint style="info" %} When you use a connection string to connect to the destination database, Structural adds the proxy connection parameters to the connection string. If you manually include proxy connection parameters in the connection string, and also configure the proxy connection settings, the connection string will have duplicate proxy connection parameters. We recommend that you use the configuration fields to enable the proxy connection, and do not include proxy connection parameters in the connection string. {% endhint %} To enable and configure the proxy connection: 1. Toggle **Enable proxy connection** to the on position. 2. In the **Proxy Host** field, provide the host name for the proxy connection. 3. In the **Proxy Port** field, provide the port for the proxy connection. 4. Optionally, in the **Proxy User** field, provide the name of the user for the proxy connection. 5. If you provide a proxy user, then in the **Proxy Password** field, provide the password for the specified user. 6. Optionally, in the **Non-Proxy Hosts** field, provide the list of hosts for which to bypass the proxy server and connect to directly. Use a pipe symbol (`|`) to separate the host names. For example, `host1|host2|host3`. You can also use an asterisk (`*`) as a wildcard. For example, to connect directly to all hosts whose host names start with myhost, use `myhost*`. ## AWS - Setting the storage location for temporary files During data generation, Structural uses temporary CSV files to load and unload Snowflake tables. You can either: * Use external stages instead of S3 buckets. * Provide separate paths for the source and destination files. ### Setting the type of storage to use By default, the temporary files are stored in S3 buckets. To instead use external stages, toggle **Use External Stage** to the on position. ### Enabling separate paths for source and destination files By default, you provide a single S3 bucket path or external stage. Within that path: * Structural copies the files that contain the source data into an `input` folder. * After it applies the generators, Structural copies the files that contain the destination data into an `output` folder. To instead provide separate paths for the source and destination files, toggle **Use Separate Destination Location** to the on position. ### Setting S3 bucket locations If **Use Separate Destination Location** is off, then in the **S3 Bucket Path** field, specify the S3 bucket. If **Use Separate Destination Location** is on, then: 1. In the **Source S3 Bucket** field, enter the path to the S3 bucket to use for the source files. 2. In the **Destination S3 Bucket** field, enter the path to the S3 bucket to use for the destination files. ### Setting external stage locations If **Use External Stage** is on, then you provide external stage locations instead of S3 buckets. For each stage, the format is: `..` Where: * `` is the name of the database where the stage is located. * `` is the name of the schema that contains the stage. * `` is the name of the stage. If **Use Separate Destination Location** is off, then in the **Source Snowflake External Stage Name** field, enter the external stage. If **Use Separate Destination Location** is on, then: 1. in the **Source Snowflake External Stage Name** field, enter the external stage to use for the source files. 2. In the **Destination Snowflake External Stage Name** field, enter the external stage to use for the destination files. ### Providing AWS credentials for the storage locations For each S3 bucket or external stage, you can optionally provide specific AWS credentials. If you do not provide credentials in the workspace configuration, then Structural uses either: * The credentials set in the following [environment settings](https://docs.tonic.ai/app/admin/environment-variables-setting): * `TONIC_AWS_ACCESS_KEY_ID` - An AWS access key that is associated with an IAM user or role. * `TONIC_AWS_SECRET_ACCESS_KEY` - The secret key that is associated with the access key. * `TONIC_AWS_REGION` - The AWS Region to send the authentication request to. * The credentials for the IAM role on the host machine. * The credentials in a credentials file. To provide the credentials: 1. For the S3 bucket or external stage, to display the credentials field, click **AWS Credentials**. 2. In the **AWS Access Key** field, enter the AWS access key that is associated with an IAM user or role. 3. In the **AWS Secret Key** field, enter the secret key that is associated with the access key. 4. From the **AWS Region** dropdown list, select the AWS Region to send the authentication request to. ## Azure - Setting the storage location for temporary files During data generation, Structural uses temporary CSV files to load and unload Snowflake tables. To store these temporary files, you can use either: * Azure Blob Storage * External stages During data generation, Structural copies source data CSV files to an `input` folder in the storage location. After it applies the generators, Structural copies destination data CSV files to an `output` folder. ### Selecting the type of storage Under **File Storage Options**, by default, the **Use External Stage** toggle is off, which indicates to use Azure Blob Storage for the temporary files. To instead use external stages, toggle **Use External Stage** to the on position. ### Setting the Azure Blob Storage location for temporary files When **Use External Stage** is off, in the **Source** **Azure Storage Account Name** field, provide the name of the account that Structural uses to load and unload Snowflake data in Azure Blob Storage. Do not provide the URI. You only need the account name. In the **Source** **Azure Blob Storage Path** field, provide the container path to the Azure Blob Storage location where Structural places temporary CSV files that it uses to load and unload Snowflake tables. ### Setting the external stage location for temporary files When **Use External Stage** is on, in the **Source Snowflake External Stage Name** field, provide the path to the external stage to use for temporary files. The format is: `..` Where: * `` is the name of the database where the stage is located. * `` is the name of the schema that contains the stage. * `` is the name of the stage.