Snowflake Iceberg (Edge)

Prerequisites

• An active Snowflake account is available.

• A Snowflake warehouse and database are created.

• An Amazon S3 bucket is available, and an IAM policy that grants Snowflake access to the bucket is created.

• An IAM role that Snowflake can use to access your S3 bucket is created, and its ARN is available.

• An external volume that specifies your S3 location is created in your Snowflake account.

• The required permissions are granted to the Snowflake user that Hevo can use to connect to your account.

Perform the following steps to configure your Snowflake Iceberg Destination:

(Optional) Create a Snowflake Account

Create and Configure your Snowflake Warehouse

Hevo provides you with a ready-to-use script to create the warehouse and the database that your Snowflake Iceberg Destination uses.

Perform the following steps to run the script:

1. Log in to your Snowflake account.

2. In the left navigation pane, click Projects.

   

3. In the Workspaces tab, click + Add new, and then click SQL file to create a SQL worksheet.

   

4. In the role selector at the top right of the worksheet, ensure that the SYSADMIN role or a higher role is selected. If it is not, click the drop-down and select that role.

   

5. Copy the following script and paste it into the worksheet. Replace the sample values provided for warehouse_name and database_name (in lines 2-3 of the script) with your own. If the names that you specify do not exist, the script creates the warehouse and database for you. If they already exist, the script leaves them unchanged.

   -- Create variables for the warehouse and database names (must be in uppercase)
   set warehouse_name = 'HOGWARTS'; -- Replace "HOGWARTS" with the name of your warehouse
   set database_name = 'RON'; -- Replace "RON" with the name of your database
   
   begin;
   
   -- Create a warehouse for Hevo only if it does not exist
   create
   warehouse if not exists identifier($warehouse_name)
   warehouse_size = xsmall
   warehouse_type = standard
   auto_suspend = 60
   auto_resume = true
   initially_suspended = true;
   
   -- Create a database for Hevo only if it does not exist
   create database if not exists identifier($database_name);
   
   commit;
   

   Note:

   • Specify the values for warehouse_name and database_name in uppercase. Snowflake stores and resolves unquoted identifiers in uppercase, and the Warehouse and Database Name values that you specify while configuring your Destination are case-sensitive and must match the names that Snowflake creates.

   • The script sets auto_resume to true, as Hevo cannot load data into a suspended warehouse. If you are using an existing warehouse, ensure that it is configured to resume automatically.

6. Click the Run icon to execute the script.

Once the script runs successfully, use the warehouse and database names (from lines 2-3 of the script) while configuring Snowflake Iceberg as a Destination.

Create an IAM Policy for the S3 Bucket

Snowflake writes the data and metadata files of your Iceberg tables to your S3 bucket. To allow this, you must create an IAM policy with the following permissions:

If you do not have an S3 bucket, refer to Create an Amazon S3 Bucket to create one.

Perform the following steps to create the IAM policy:

1. Log in to the AWS IAM Console.

2. In the left navigation pane, under Access management, click Policies.

3. On the Policies page, click Create policy.

   

4. On the Specify permissions page, click JSON.

   

5. Paste the following JSON statements in the Policy editor:

   {
       "Version": "2012-10-17",
       "Statement": [
           {
               "Effect": "Allow",
               "Action": [
                   "s3:PutObject",
                   "s3:GetObject",
                   "s3:GetObjectVersion",
                   "s3:DeleteObject",
                   "s3:DeleteObjectVersion"
               ],
               "Resource": "arn:aws:s3:::<bucket_name>/<path>/*"
           },
           {
               "Effect": "Allow",
               "Action": [
                   "s3:ListBucket",
                   "s3:GetBucketLocation"
               ],
               "Resource": "arn:aws:s3:::<bucket_name>",
               "Condition": {
                   "StringLike": {
                       "s3:prefix": [
                           "<path>/*"
                       ]
                   }
               }
           }
       ]
   }
   

   Note:

   • Replace the placeholder values in the statements above with your own. For example, <bucket_name> with iceberg-docs, and <path> with the folder in which you want Snowflake to store your table files.

   • Note the bucket name and path that you specify, as you must use the same values while creating the external volume.

6. At the bottom of the page, click Next.

   

7. On the Review and create page, specify a Policy name and a Description, and then click Create policy.

   

You must assign this policy to the IAM role that you create for Snowflake so that it can access your S3 bucket.

Create an IAM Role and Obtain its ARN

Snowflake uses an IAM role in your AWS account to access your S3 bucket. Perform the following steps to create this role:

1. Log in to the AWS IAM Console.

2. In the left navigation pane, under Access management, click Roles.

3. On the Roles page, click Create role.

   

4. In the Select trusted entity section, choose AWS account.

   

5. In the An AWS account section, choose This account.

6. In the Options section, select the Require external ID check box, specify an External ID of your choice, and then click Next.

   

7. On the Add Permissions page, search and select the policy that you created in the Create an IAM Policy for the S3 Bucket section, and at the bottom of the page, click Next.

   

8. On the Name, review, and create page, specify a Role name and a Description.

   

9. At the bottom of the page, click Create role.

   You are redirected to the Roles page.

10. Search for and click the role that you created.

    

11. In the Summary section, click the copy icon below the ARN field and save the value securely.

    

Use this ARN while creating the external volume.

Create an External Volume

An external volume is the Snowflake object that holds the S3 location of your Iceberg table files, along with the identity that Snowflake uses to access that location. Snowflake writes both the data and the metadata files of your tables to this location.

Perform the following steps to create the external volume:

1. Follow steps 1-3 from the Create and Configure your Snowflake Warehouse section to open a SQL worksheet.

2. In the role selector at the top right of the worksheet, ensure that the ACCOUNTADMIN role or a role that has the CREATE EXTERNAL VOLUME privilege on the account is selected. If it is not, click the drop-down and select that role.

   

3. Run the following command to create the external volume:

   CREATE EXTERNAL VOLUME <external_volume_name>
     STORAGE_LOCATIONS = (
       (
         NAME = '<storage_location_name>'
         STORAGE_PROVIDER = 'S3'
         STORAGE_BASE_URL = 's3://<bucket_name>/<path>/'
         STORAGE_AWS_ROLE_ARN = '<iam_role_arn>'
       )
     )
     ALLOW_WRITES = TRUE;
   

   Note:

   • Replace the placeholder values in the command above with your own. For example, <external_volume_name> with hevo_iceberg_volume.

   • Specify the same bucket name and path in STORAGE_BASE_URL that you used in the Create an IAM Policy for the S3 Bucket section. Snowflake cannot access your table files if these values do not match.

   • The command sets ALLOW_WRITES to TRUE, as Hevo cannot load data into a read-only external volume. If you are using an existing external volume, ensure that this option is enabled.

4. Run the following command to retrieve the identity that Snowflake uses to access your S3 bucket:

   DESC EXTERNAL VOLUME <external_volume_name>;
   

   The command returns output similar to the following:

   parent_property	property	property_type	property_value	property_default
   	ALLOW_WRITES	Boolean	true	true
   STORAGE_LOCATIONS	STORAGE_LOCATION_1	String	{"NAME":"hevo-iceberg-s3","STORAGE_PROVIDER":"S3", ...}
   STORAGE_LOCATIONS	ACTIVE	String	hevo-iceberg-s3

5. In the property_value column of the STORAGE_LOCATION_1 row, copy and save the values of the STORAGE_AWS_IAM_USER_ARN and STORAGE_AWS_EXTERNAL_ID properties. A sample output is shown below:

   {
     "NAME": "hevo-iceberg-s3",
     "STORAGE_PROVIDER": "S3",
     "STORAGE_BASE_URL": "s3://iceberg-docs/hevo/",
     "STORAGE_ALLOWED_LOCATIONS": ["s3://iceberg-docs/hevo/*"],
     "STORAGE_REGION": "ap-south-1",
     "PRIVILEGES_VERIFIED": true,
     "STORAGE_AWS_ROLE_ARN": "arn:aws:iam::123456789012:role/hevo-iceberg-role",
     "STORAGE_AWS_IAM_USER_ARN": "arn:aws:iam::987654321098:user/abc1-a-self1234",
     "STORAGE_AWS_EXTERNAL_ID": "iceberg_table_external_id",
     "ENCRYPTION_TYPE": "NONE",
     "ENCRYPTION_KMS_KEY_ID": ""
   }
   

   Note: The PRIVILEGES_VERIFIED property indicates whether Snowflake can successfully use the IAM role to access your S3 bucket.

Snowflake has now created the external volume and generated its own identity for accessing your S3 bucket. This identity is not yet trusted by your IAM role, so Snowflake cannot write to the bucket. To grant it access, update the trust policy of your IAM role with the STORAGE_AWS_IAM_USER_ARN and STORAGE_AWS_EXTERNAL_ID values that you saved.

Perform the following steps to update the trust policy:

1. On the Roles page of the AWS IAM Console, click the IAM role that you specified in STORAGE_AWS_ROLE_ARN while creating the external volume.

2. Click the Trust relationships tab, and then click Edit trust policy.

   

3. Replace the existing policy with the following JSON statements:

   {
     "Version": "2012-10-17",
     "Statement": [
       {
         "Sid": "",
         "Effect": "Allow",
         "Principal": {
           "AWS": "<storage_aws_iam_user_arn>"
         },
         "Action": "sts:AssumeRole",
         "Condition": {
           "StringEquals": {
             "sts:ExternalId": "<storage_aws_external_id>"
           }
         }
       }
     ]
   }
   

   Note: Replace <storage_aws_iam_user_arn> and <storage_aws_external_id> with the values you saved from the DESC EXTERNAL VOLUME output. These values replace the external ID you specified when creating the IAM role.

4. Click Update policy.

Verify that Snowflake can access the external volume before you proceed. In a SQL worksheet, run the following command:

SELECT SYSTEM$VERIFY_EXTERNAL_VOLUME('<external_volume_name>');

Use this external volume while configuring Snowflake Iceberg as a Destination.

Create a Snowflake User and Grant Permissions

Hevo connects to your Snowflake account as a database user with a non-administrative role. The following table lists the privileges that this role requires:

Perform the following steps to create the user and the role:

1. Follow steps 1-3 from the Create and Configure your Snowflake Warehouse section to open a SQL worksheet.

2. In the role selector at the top right of the worksheet, ensure that the ACCOUNTADMIN or SECURITYADMIN role is selected. If it is not, click the drop-down and select that role.

   

3. Copy the following script and paste it into the worksheet:

   -- Create a role for Hevo
   CREATE ROLE <hevo_role>;
   
   -- Grant access to the warehouse and the database
   GRANT USAGE ON WAREHOUSE <warehouse_name> TO ROLE <hevo_role>;
   GRANT USAGE, MONITOR, CREATE SCHEMA ON DATABASE <database_name> TO ROLE <hevo_role>;
   
   -- Grant access to the schemas and tables that Hevo creates
   GRANT ALL ON FUTURE SCHEMAS IN DATABASE <database_name> TO ROLE <hevo_role>;
   GRANT ALL ON FUTURE TABLES IN DATABASE <database_name> TO ROLE <hevo_role>;
   
   -- Grant access to the external volume
   GRANT USAGE ON EXTERNAL VOLUME <external_volume_name> TO ROLE <hevo_role>;
   
   -- Create a user for Hevo and assign the role to it
   CREATE USER <hevo_user> PASSWORD = '<password>' DEFAULT_ROLE = <hevo_role>;
   GRANT ROLE <hevo_role> TO USER <hevo_user>;
   

   Note: Replace the placeholder values in the commands above with your own values. For example, replace <hevo_user> with hevo_iceberg_user.

4. Click the Run options drop-down, and then click Run all to run every command in the script.

   

Use this user while configuring Snowflake Iceberg as a Destination.

Obtain a Private and Public Key Pair (Recommended Method)

You can authenticate Hevo’s connection to your Snowflake account using a public-private key pair. For this, you need to:

1. Generate a private key.

2. Generate a public key for your private key.

3. Assign the public key to a Snowflake user.

1. Generate a private key

Hevo supports private keys in Public-Key Cryptography Standards (PKCS) #8-based triple DES algorithm format. You can use either an encrypted or an unencrypted private key, as supported by the Destination configuration.

Open a terminal window, and on the command line, do one of the following:

• To generate an unencrypted private key, run the command:

  openssl genrsa 2048 | openssl pkcs8 -topk8 -inform PEM -out <unencrypted_key_name> -nocrypt
  

• To generate an encrypted private key, run the command:

  openssl genrsa 2048 | openssl pkcs8 -topk8 -v2 des3 -inform PEM -out <encrypted_key_name>
  

  You will be prompted to set an encryption password. This passphrase is required when you connect to your Snowflake Iceberg Destination using key pair authentication. The encrypted private key is stored in the PKCS #8 format and protected with this passphrase.

Note: Replace the placeholder values in the commands above with your own. For example, <encrypted_key_name> with encrypted_rsa_key.p8.

The private key is generated in the PEM format.

-----BEGIN ENCRYPTED PRIVATE KEY-----
MIIFJDBWBg...
-----END ENCRYPTED PRIVATE KEY-----

Open the private key file and remove the extra blank space or empty line at the bottom of the file. Save the private key file in a secure location and provide it while connecting to your Snowflake Iceberg Destination using key pair authentication.

2. Generate a public key

To use a key pair for authentication, you must generate a public key for the private key that you created in the Generate a private key section. For this:

Open a terminal window, and on the command line, run the following command:

openssl rsa -in <private_key_file> -pubout -out <public_key_file>

Note:

• Replace the placeholder values in the command above with your own. For example, <private_key_file> with encrypted_rsa_key.p8.

• If you are generating a public key for an encrypted private key, you will need to provide the encryption password used to create the private key.

The public key is generated in the PEM format.

-----BEGIN PUBLIC KEY-----
MIIBIjANBgk...
-----END PUBLIC KEY-----

Save the public key file in a secure location. You must associate this public key with the Snowflake user that you created for Hevo.

3. Assign the public key to a Snowflake user

To authenticate Hevo’s connection to your Snowflake account using a key pair, you must associate the public key that you generated in the Generate a public key section with the user that you created for Hevo. Perform the following steps to assign the public key:

1. Follow steps 1-3 from the Create and Configure your Snowflake Warehouse section to open a SQL worksheet.

2. In the role selector at the top right of the worksheet, ensure that the SECURITYADMIN role or a higher role is selected. If it is not, click the drop-down and select that role.

   

3. Run the following command to assign the public key to your Snowflake user:

   ALTER USER <your_snowflake_user> SET RSA_PUBLIC_KEY='<public_key>';
   
   -- Example
   
   ALTER USER HARRY_POTTER set RSA_PUBLIC_KEY='MIIBIjANBgk...';
   

   Note:

   • Replace the placeholder values in the command above with your own values. For example, replace <your_snowflake_user> with HARRY_POTTER.

   • Set the public key value to the content between -----BEGIN PUBLIC KEY----- and -----END PUBLIC KEY-----.

To check whether the public key is configured correctly, you can follow the steps provided in the verify the user’s public key fingerprint section.

Obtain your Snowflake Account URL

Hevo identifies your Snowflake account using its account URL, which is in the https://<account_identifier>.snowflakecomputing.com format.

Perform the following steps to obtain your Snowflake account URL:

1. Log in to your Snowflake account.

2. In the bottom left corner, click your account name to open the account menu.

   

3. Hover over Account, and then click View Account Details.

   

4. In the Account Details dialog, click the Copy icon corresponding to the Account/Server URL value, and save it securely.

   

   Note: Snowflake displays this value without the https:// prefix. Add the prefix to obtain your account URL. For example, if the copied value is HEVODATA-ABCDXYZ.snowflakecomputing.com, your account URL is https://HEVODATA-ABCDXYZ.snowflakecomputing.com.

Use this URL while configuring Snowflake Iceberg as a Destination.

Configure S3 Bucket for Staging Data