Configure a Snowflake Warehouse to Query Iceberg Tables

This guide details the manual setup steps required to configure a Snowflake connection to query Iceberg tables. This guide assumes that the general Iceberg setup has already been done.

Step 1. Create an IAM role for Snowflake

1. Create a new IAM role that Snowflake will use to access Glue and S3 in the AWS console here .

2. Select AWS account and This account, and click Next.

3. Skip adding policies.

4. Give the role a meaningful name, optionally a description, and click Create role.

5. Search for the role you just created and click Add permissions → Create Inline policy.

6. Click JSON and paste the policy below, and make the following replacements:

   • ICEBERG_BUCKET_NAME is the bucket you created in step 1.1 of the Iceberg Connection Setup guide.
   • AWS_ACCOUNT_ID is your 12-digit account ID.
   • GLUE_DATABASE_NAME is the Glue database you created in step 1.2 of the Iceberg Connection Setup guide.
   • AWS_REGION is the region the resources have been created in.

   {
     "Version": "2012-10-17",
     "Statement": [
       {
         "Effect": "Allow",
         "Action": ["s3:GetObject"],
         "Resource": ["arn:aws:s3:::ICEBERG_BUCKET_NAME", "arn:aws:s3:::ICEBERG_BUCKET_NAME/*"]
       },
       {
         "Effect": "Allow",
         "Action": ["glue:GetTable", "glue:GetDatabase"],
         "Resource": [
           "arn:aws:glue:AWS_REGION:AWS_ACCOUNT_ID:catalog",
           "arn:aws:glue:AWS_REGION:AWS_ACCOUNT_ID:database/GLUE_DATABASE_NAME",
           "arn:aws:glue:AWS_REGION:AWS_ACCOUNT_ID:table/GLUE_DATABASE_NAME/*"
         ]
       }
     ]
   }

7. Click Next, give the policy a meaningful name, and click Create policy.

8. Copy the role ARN.

Step 2. Create the Catalog Integration and External Volume

1. In a Snowflake SQL worksheet, configure the Glue and S3 integrations with the following replacements:

   • SNOWFLAKE_IAM_ROLE_ARN is the ARN of the role you created in the previous step.
   • AWS_ACCOUNT_ID is your 12-digit account ID.
   • ICEBERG_BUCKET_NAME is the name of the S3 bucket you created in step 1.1 of the Iceberg Connection Setup guide.
   • GLUE_DATABASE_NAME is the Glue database you created in step 1.2 of the Iceberg Connection Setup guide.
   • AWS_REGION is the region the resources have been created in.

   CREATE CATALOG INTEGRATION ETLEAP_ICEBERG_CATALOG
   CATALOG_SOURCE = GLUE
   GLUE_AWS_ROLE_ARN = 'SNOWFLAKE_IAM_ROLE_ARN'
   GLUE_CATALOG_ID = 'AWS_ACCOUNT_ID'
   CATALOG_NAMESPACE = 'GLUE_DATABASE_NAME'
   GLUE_REGION = 'AWS_REGION'
   TABLE_FORMAT = ICEBERG
   ENABLED = TRUE;
    
   CREATE EXTERNAL VOLUME ETLEAP_ICEBERG_EXTERNAL_VOLUME
   STORAGE_LOCATIONS = ((
       NAME = 'etleap_iceberg_bucket'
       STORAGE_PROVIDER = 'S3'
       STORAGE_AWS_ROLE_ARN = 'SNOWFLAKE_IAM_ROLE_ARN'
       STORAGE_BASE_URL = 's3://ICEBERG_BUCKET_NAME/'
   ))
   ALLOW_WRITES = FALSE;

Step 3. Grant the Snowflake user access to the IAM role

1. Get the snowflake user and external id used to assume the role for the CATALOG INTEGRATION. Copy the values for:

   • GLUE_AWS_IAM_USER_ARN
   • GLUE_AWS_EXTERNAL_ID

   DESC CATALOG INTEGRATION ETLEAP_ICEBERG_CATALOG;

   
   

2. Get the snowflake user and external id used to assume the role for the EXTERNAL VOLUME. Copy the values for:

   • STORAGE_AWS_IAM_USER_ARN
   • STORAGE_AWS_EXTERNAL_ID

   -- Run both statements in series:
    
   DESC EXTERNAL VOLUME ETLEAP_ICEBERG_EXTERNAL_VOLUME;
    
   SELECT
     PARSE_JSON("property_value")['STORAGE_AWS_IAM_USER_ARN'] STORAGE_AWS_IAM_USER_ARN,
     PARSE_JSON("property_value")['STORAGE_AWS_EXTERNAL_ID'] STORAGE_AWS_EXTERNAL_ID
   FROM table(RESULT_SCAN(LAST_QUERY_ID())) WHERE "property" = 'STORAGE_LOCATION_1';

   
   

3. On the IAM role page in the AWS console, go to the Trust relationships tab and click Edit trust policy.

4. Paste the following policy using the values returned in step 3.1 and 3.2 as the replacements:

   {
     "Version": "2012-10-17",
     "Statement": [
       {
         "Effect": "Allow",
         "Principal": {
           "AWS": ["GLUE_AWS_IAM_USER_ARN"]
         },
         "Action": "sts:AssumeRole",
         "Condition": {
           "StringEquals": {
             "sts:ExternalId": "GLUE_AWS_EXTERNAL_ID"
           }
         }
       },
       {
         "Effect": "Allow",
         "Principal": {
           "AWS": ["STORAGE_AWS_IAM_USER_ARN"]
         },
         "Action": "sts:AssumeRole",
         "Condition": {
           "StringEquals": {
             "sts:ExternalId": "STORAGE_AWS_EXTERNAL_ID"
           }
         }
       }
     ]
   }

5. Click Update policy.

Step 4. Create a Snowflake connection in Etleap

1. Create a new Snowflake connection in Etleap here 
2. Ensure the role specified in the connection has the following permissions in the schema that Etleap will be creating Iceberg tables in:
   • USAGE
   • CREATE ICEBERG TABLE
   • CREATE VIEW
3. In a Snowflake SQL worksheet, allow access to the catalog and external volume to the role you specified in the Etleap Snowflake connection, where ETLEAP_ROLE is the role used by the Etleap connection.

   GRANT USAGE ON EXTERNAL VOLUME ETLEAP_ICEBERG_EXTERNAL_VOLUME TO ROLE "ETLEAP_ROLE";
   GRANT USAGE ON INTEGRATION ETLEAP_ICEBERG_CATALOG TO ROLE "ETLEAP_ROLE";